pg_trgm索引
注意此功能需加载插件 pg_trgm。
teledb=# drop index t_trgm_trgm_idx;
DROP INDEX
teledb=# create index t_trgm_trgm_idx on t_trgm using gin(trgm gin_trgm_ops);
CREATE INDEX
jsonb索引
teledb=# create table t_jsonb(id int,f_jsonb jsonb);
CREATE TABLE
teledb=# create index t_jsonb_f_jsonb_idx on t_jsonb using gin(f_jsonb);
CREATE INDEX
数组索引
teledb=# create table t_array(id int, mc text[]);
CREATE TABLE
teledb=# insert into t_array select t,('{'||md5(t::text)||'}')::text[] from generate_series(1,1000000) as t;
INSERT 0 1000000
teledb=# analyze;
ANALYZE
teledb=# \timing
Timing is on.
teledb=# explain select * from t_array where mc @> ('{'||md5('1')||'}')::text[];
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Remote Fast Query Execution (cost=0.00..0.00 rows=0 width=0)
Node/s: dn01, dn02
-> Gather (cost=1000.00..13094.09 rows=2503 width=61)
Workers Planned: 2
-> Parallel Seq Scan on t_array (cost=0.00..11843.79 rows=1043 width=61)
Filter: (mc @> ((('{'::text || 'c4ca4238a0b923820dcc509a6f75849b'::text) || '}'::text))::text[])
(6 rows)
Time: 21.368 ms
teledb=# select * from t_array where mc @> ('{'||md5('1')||'}')::text[];
id | mc
----+------------------------------------
1 | {c4ca4238a0b923820dcc509a6f75849b}
(1 row)
Time: 431.788 ms
teledb=# create index t_array_mc_idx on t_array using gin(mc);
CREATE INDEX
Time: 7997.138 ms (00:07.997)
teledb=# explain select * from t_array where mc @> ('{'||md5('1')||'}')::text[];
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Remote Fast Query Execution (cost=0.00..0.00 rows=0 width=0)
Node/s: dn01, dn02
-> Bitmap Heap Scan on t_array (cost=39.41..5129.82 rows=2503 width=61)
Recheck Cond: (mc @> ((('{'::text || 'c4ca4238a0b923820dcc509a6f75849b'::text) || '}'::text))::text[])
-> Bitmap Index Scan on t_array_mc_idx (cost=0.00..38.78 rows=2503 width=0)
Index Cond: (mc @> ((('{'::text || 'c4ca4238a0b923820dcc509a6f75849b'::text) || '}'::text))::text[])
(6 rows)
Time: 6.136 ms
teledb=# select * from t_array where mc @> ('{'||md5('1')||'}')::text[];
id | mc
----+------------------------------------
1 | {c4ca4238a0b923820dcc509a6f75849b}
(1 row)
Time: 2.689 ms
btree_gin任意字段索引
teledb=# create table gin_mul(f1 int, f2 int, f3 timestamp, f4 text, f5 numeric, f6 text);
CREATE TABLE
teledb=# insert into gin_mul select random()*5000, random()*6000, now()+((30000-60000*random())||' sec')::interval , md5(random()::text), round((random()*100000)::numeric,2), md5(random()::text) from generate_series(1,1000000);
INSERT 0 1000000
teledb=# create extension btree_gin;
CREATE EXTENSION
teledb=# create index gin_mul_gin_idx on gin_mul using gin(f1,f2,f3,f4,f5,f6);
CREATE INDEX
单字段索引
teledb=# explain select * from gin_mul where f1=10;
QUERY PLAN
---------------------------------------------------------------------------------------
Remote Fast Query Execution (cost=0.00..0.00 rows=0 width=0)
Node/s: dn02
-> Bitmap Heap Scan on gin_mul (cost=21.51..710.58 rows=195 width=90)
Recheck Cond: (f1 = 10)
-> Bitmap Index Scan on gin_mul_gin_idx (cost=0.00..21.46 rows=195 width=0)
Index Cond: (f1 = 10)
(6 rows)
teledb=# explain select * from gin_mul where f3='2019-02-18 23:01:01';
QUERY PLAN
-------------------------------------------------------------------------------------
Remote Fast Query Execution (cost=0.00..0.00 rows=0 width=0)
Node/s: dn01, dn02
-> Bitmap Heap Scan on gin_mul (cost=20.01..24.02 rows=1 width=90)
Recheck Cond: (f3 = '2019-02-18 23:01:01'::timestamp without time zone)
-> Bitmap Index Scan on gin_mul_gin_idx (cost=0.00..20.01 rows=1 width=0)
Index Cond: (f3 = '2019-02-18 23:01:01'::timestamp without time zone)
(6 rows)
teledb=# explain select * from gin_mul where f4='2364d9969c8b66402c9b7d17a6d5b7d3';
QUERY PLAN
-------------------------------------------------------------------------------------
Remote Fast Query Execution (cost=0.00..0.00 rows=0 width=0)
Node/s: dn01, dn02
-> Bitmap Heap Scan on gin_mul (cost=20.01..24.02 rows=1 width=90)
Recheck Cond: (f4 = '2364d9969c8b66402c9b7d17a6d5b7d3'::text)
-> Bitmap Index Scan on gin_mul_gin_idx (cost=0.00..20.01 rows=1 width=0)
Index Cond: (f4 = '2364d9969c8b66402c9b7d17a6d5b7d3'::text)
(6 rows)
两个字段组合索引
teledb=# explain select * from gin_mul where f1=2 and f3='2019-02-18 16:59:52.872523';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
Remote Fast Query Execution (cost=0.00..0.00 rows=0 width=0)
Node/s: dn01
-> Bitmap Heap Scan on gin_mul (cost=36.00..40.02 rows=1 width=90)
Recheck Cond: ((f1 = 2) AND (f3 = '2019-02-18 16:59:52.872523'::timestamp without time zone))
-> Bitmap Index Scan on gin_mul_gin_idx (cost=0.00..36.00 rows=1 width=0)
Index Cond: ((f1 = 2) AND (f3 = '2019-02-18 16:59:52.872523'::timestamp without time zone))
(6 rows)
三字段组合索引
teledb=# explain select * from gin_mul where f1=2 and f3='2019-02-18 16:59:52.872523' and f6='fa627dc16c2bd026150afa0453a0991d';
QUERY PLAN
Remote Fast Query Execution (cost=0.00..0.00 rows=0 width=0)
Node/s: dn01
-> Bitmap Heap Scan on gin_mul (cost=52.00..56.02 rows=1 width=90)
Recheck Cond: ((f1 = 2) AND (f3 = '2019-02-18 16:59:52.872523'::timestamp without time zone) AND (f6 = 'fa627dc16c2bd026150afa0453a0991d'::text))
-> Bitmap Index Scan on gin_mul_gin_idx (cost=0.00..52.00 rows=1 width=0)
Index Cond: ((f1 = 2) AND (f3 = '2019-02-18 16:59:52.872523'::timestamp without time zone) AND (f6 = 'fa627dc16c2bd026150afa0453a0991d'::text))
(6 rows)
