條件索引
更新時間 2025-02-14 10:21:40
最近更新時間: 2025-02-14 10:21:40
分享文章
本文為您介紹如何使用條件索引。
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)
