条件索引

• 复制
  复制成功

postgres=# create table t_sex(id int,sex text) ;
postgres=# create index t_sex_sex_idx on t_sex (sex);
postgres=# insert into t_sex select t,'男' from generate_series(1,1000000) as t;                      
postgres=# insert into t_sex select t,'女' from generate_series(1,100) as t;                     
postgres=# analyze t_sex ;
ANALYZE
postgres=# 

postgres=# explain  select * from t_sex where sex ='女';       
                                    QUERY PLAN                                    
----------------------------------------------------------------------------------
 Remote Fast Query Execution  (cost=0.00..0.00 rows=0 width=0)
   Node/s: dn001, dn002
   ->  Index Scan using t_sex_sex_idx on t_sex  (cost=0.42..5.81 rows=67 width=8)
         Index Cond: (sex = '女'::text)
(4 rows)

#索引对于条件为男的情况下无效

postgres=# explain  select * from t_sex where sex ='男';     
                            QUERY PLAN                             
-------------------------------------------------------------------
 Remote Fast Query Execution  (cost=0.00..0.00 rows=0 width=0)
   Node/s: dn001, dn002
   ->  Seq Scan on t_sex  (cost=0.00..9977.58 rows=500539 width=8)
         Filter: (sex = '男'::text)
(4 rows)

#连接dn节点查看索引点用空间大，而且度数也高

postgres=# \di+
                                    List of relations
 Schema |     Name      | Type  | Owner |  Table  | Size  | Allocated Size | Description 
--------+---------------+-------+-------+---------+-------+----------------+-------------
 tbase  | t_sex_sex_idx | index | tbase | t_sex   | 14 MB | 14 MB          | 
 tbase  | t_upper_mc    | index | tbase | t_upper | 14 MB | 14 MB          | 
(2 rows)

postgres=# \q

postgres=# drop index t_sex_sex_idx;
DROP INDEX
postgres=#  create index t_sex_sex_idx on t_sex (sex) where sex='女';  
CREATE INDEX
postgres=# analyze t_sex;
ANALYZE
postgres=# explain  select * from t_sex where sex ='女';       
                                    QUERY PLAN                                    
----------------------------------------------------------------------------------
 Remote Fast Query Execution  (cost=0.00..0.00 rows=0 width=0)
   Node/s: dn001, dn002
   ->  Index Scan using t_sex_sex_idx on t_sex  (cost=0.14..6.69 rows=33 width=8)
(3 rows)

postgres=# explain  select * from t_sex where sex ='男';     
                            QUERY PLAN                             
-------------------------------------------------------------------
 Remote Fast Query Execution  (cost=0.00..0.00 rows=0 width=0)
   Node/s: dn001, dn002
   ->  Seq Scan on t_sex  (cost=0.00..9977.58 rows=500573 width=8)
         Filter: (sex = '男'::text)
(4 rows)


postgres=# \di+
                                    List of relations
 Schema |     Name      | Type  | Owner |  Table  | Size  | Allocated Size | Description 
--------+---------------+-------+-------+---------+-------+----------------+-------------
 tbase  | t_sex_sex_idx | index | tbase | t_sex   | 16 kB | 16 kB          | 
 tbase  | t_upper_mc    | index | tbase | t_upper | 14 MB | 14 MB          | 
(2 rows)