postgres创建索引分析

查看每列的散列程度
select tablename,attname,n_distinct,correlation from pg_stats where tablename='table_name';
n_distinct解释   
-1表示唯一，也就是说这个列的每一行都不一样.  
>=1时，表示这个列有多少唯一值.  
<1时，表示这个列的  唯一值数量/总数.    
correlation解释  
表示该列与数据堆存储的线性相关性, 1表示正向完全相关。越接近0表示数据分布越离散。<0表示反向相关。 

btree:
（1）索引适合选择性好的列（n_distinct很大，或者=-1），唯一值比例越高越适合btree。
（2）线性相关越好，扫描或返回多条数据的效率越高。

gin:
（1）与btree相反，选择性越差，采用GIN索引效率越高。
（2）GIN的倒排特性，还特别适合多值类型的元素组合查询，例如数组、全文检索类型、TOKEN类型、等等。
（3）GIN索引接口是开放的，用户可以根据数据特征，自定义GIN索引。支持更多的数据类型。
（4）对于松散列，建立gin索引（倒排），以达到最好的效果；
（5）为了让普通类型支持gin，需要创建btree_gin插件；
postgres=# create extension btree_gin;  
CREATE EXTENSION
（6）如果是普通类型，则线性相关越好，扫描或返回多条数据的效率越高。
（7）如果是多值类型（如数组、全文检索、TOKENs），则元素越集中（元素聚类分析，横坐标为行号，纵坐标为元素值，数据分布越集中），效率越高。

brin:
（1）当数据与堆存储线性相关性很好时，可以采用BRIN索引。
（2）BRIN是块级索引，存储每个（或者每一段连续的）数据块的原子信息（最大值，最小值，平均值，空值比例，COUNT等）。
（3）默认是每128个连续数据块区间存储一个字段取值的区间，所以这个索引的信息量是将1000个数据块划分为几个连续的128个块的区间，然后存储每个区间ID值的取值范围
（4）特别适合范围扫描。
（5）线性相关越好，扫描或返回多条数据的效率越高。
==================================================test_data============================================

postgres=# analyze test_data ;  
ANALYZE 

MH=> select tablename,attname,n_distinct,correlation from pg_stats where tablename='test_data';
   tablename   |    attname     | n_distinct | correlation 
---------------+----------------+------------+-------------
 test_data | data_id        |         -1 |    0.471723
 test_data | parent_house_id|     421535 |    0.469814
 test_data | test_id        |          2 |    0.215617
 test_data | data_name      |     294808 |  -0.0140928
 test_data | size           |       7730 |    0.529041
 test_data | type           |         12 |    0.624537
 test_data | ident_Id       |          2 |    0.996693
 test_data | city_Id        |          3 |    0.996512
 test_data | create_ts      |      83770 |    0.119125
 test_data | modify_ts      |      36360 |    0.249871
(10 rows)

MH=>

分析：
data_id  btree 
parent_house_id      btree
test_id  不建索引
size btree
type brin
ident_Id brin
city_Id    brin 
data_name  create_ts modify_ts  gin 

建索引：
create index idx_test_data_1 on test_data using btree (data_id);
create index idx_test_data_2 on test_data using btree (parent_house_id);
create index idx_test_data_3 on test_data using btree (size);

create index idx_test_data_4 on test_data using brin (type);
create index idx_test_data_5 on test_data using brin (ident_Id);
create index idx_test_data_6 on test_data using brin (city_Id);

create index idx_test_data_7 on test_data using gin (data_name,create_ts,modify_ts);


===============================test_house：=======================================
postgres=# analyze test_house ;  
ANALYZE 

MH=> select tablename,attname,n_distinct,correlation from pg_stats where tablename='test_house';
   tablename   |    attname     | n_distinct | correlation 
---------------+----------------+------------+-------------
 test_house | house_id        |         -1 |    0.405785
 test_house | parent_house_id |     365044 |    0.402222
 test_house | test_id         |          2 |    0.232351
 test_house | house_name      |      86129 |    0.062862
 test_house | type            |          5 |    0.874647
 test_house | ident_Id        |          3 |    0.998939
 test_house | city_Id         |          4 |    0.998827
 test_house | create_ts       |     145307 |    0.831058
 test_house | modify_ts       |      49521 |    0.364961
(9 rows)


MH=>

分析：
house_id  btree
parent_house_id btree
test_id 不建索引
type brin
ident_Id brin
city_Id brin
house_name create_ts modify_ts   gin


建索引：
create index idx_test_house_1 on test_house using btree (house_id);
create index idx_test_house_2 on test_house using btree (parent_house_id);

create index idx_test_house_3 on test_house using brin (type);
create index idx_test_house_4 on test_house using brin (ident_Id);
create index idx_test_house_5 on test_house using brin (city_Id);

create index idx_test_house_6 on test_house using gin (house_name,create_ts,modify_ts);