teledb=# create table t_sex(id int,sex text) ;
CREATE TABLE
teledb=# 
teledb=# create index t_sex_sex_idx on t_sex (sex);
CREATE INDEX
teledb=# insert into t_sex select t,'男' from generate_series(1,1000000) as t;
INSERT 0 1000000
teledb=# insert into t_sex select t,'女' from generate_series(1,100) as t; 
INSERT 0 100
teledb=# analyze t_sex ;
ANALYZE
teledb=# explain  select * from t_sex where sex ='女'; 
                                    QUERY PLAN                                  
----------------------------------------------------------------------------------
 Remote Fast Query Execution  (cost=0.00..0.00 rows=0 width=0)
   Node/s: dn01, dn02
   ->  Index Scan using t_sex_sex_idx on t_sex  (cost=0.42..9.30 rows=50 width=8)
         Index Cond: (sex = '女'::text)
(4 rows)

索引对于条件在性别为男的情况下无效。

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

连接DN 节点查看索引占用空间大，而且度数也高。

teledb=# \di+ t_sex_sex_idx 
                                    List of relations
 Schema |     Name      | Type  |  Owner  | Table | Size  | Allocated Size | Description 
--------+---------------+-------+---------+-------+-------+----------------+-------------
 public | t_sex_sex_idx | index | teledb | t_sex | 14 MB | 14 MB          | 
(1 row)

teledb=# \q
[teledb@localhost bin]$ ./telesql -p 11111 -U teledb -d teledb
Password for user teledb: 
telesql (TeleDB V6)
Type "help" for help.

teledb=# drop index t_sex_sex_idx;
DROP INDEX
teledb=# create index t_sex_sex_idx on t_sex (sex) where sex = '女';
CREATE INDEX
teledb=# analyze t_sex;
ANALYZE
teledb=# explain  select * from t_sex where sex ='男';   
                            QUERY PLAN                           
-------------------------------------------------------------------
 Remote Fast Query Execution  (cost=0.00..0.00 rows=0 width=0)
   Node/s: dn01, dn02
   ->  Seq Scan on t_sex  (cost=0.00..9966.58 rows=500539 width=8)
         Filter: (sex = '男'::text)
(4 rows)

teledb=# explain  select * from t_sex where sex ='女'; 
                                    QUERY PLAN                                   
-----------------------------------------------------------------------------------
 Remote Fast Query Execution  (cost=0.00..0.00 rows=0 width=0)
   Node/s: dn01, dn02
   ->  Index Scan using t_sex_sex_idx on t_sex  (cost=0.14..13.15 rows=67 width=8)
(3 rows)

teledb=# \q
[teledb@localhost bin]$ ./telesql -p 22222 -U teledb -d teledb
Password for user teledb: 
telesql (TeleDB V6)
Type "help" for help.

teledb=# \di+ t_sex_sex_idx 
                                    List of relations
 Schema |     Name      | Type  |  Owner  | Table | Size  | Allocated Size | Description 
--------+---------------+-------+---------+-------+-------+----------------+-------------
 public | t_sex_sex_idx | index | teledb | t_sex | 16 kB | 16 kB          | 
(1 row)