gin索引
最近更新时间: 2025-02-18 16:02:00
- pg_trgm索引。
postgres=# drop index t_trgm_trgm_idx;
DROP INDEX
Time: 55.954 ms
postgres=# create index t_trgm_trgm_idx on t_trgm using gin(trgm gin_trgm_ops);- jsonb索引。
postgres=# create table t_jsonb(id int,f_jsonb jsonb);
postgres=# create index t_jsonb_f_jsonb_idx on t_jsonb using gin(f_jsonb);- 数组索引。
postgres=# create table t_array(id int, mc text[]);
postgres=# insert into t_array select t,('{'||md5(t::text)||'}')::text[] from generate_series(1,1000000) as t;
postgres=# analyze;
ANALYZE
postgres=# \timing
Timing is on.
postgres=# 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: dn001, dn002
-> Gather (cost=1000.00..12060.25 rows=2503 width=61)
Workers Planned: 2
-> Parallel Seq Scan on t_array (cost=0.00..10809.95 rows=1043 width=61)
Filter: (mc @> ('{c4ca4238a0b923820dcc509a6f75849b}'::cstring)::text[])
(6 rows)
Time: 4.105 ms
postgres=# select * from t_array where mc @> ('{'||md5('1')||'}')::text[];
id | mc
----+------------------------------------
1 | {c4ca4238a0b923820dcc509a6f75849b}
(1 row)
Time: 494.371 ms
postgres=# create index t_array_mc_idx on t_array using gin(mc);
CREATE INDEX
Time: 8195.387 ms (00:08.195)
postgres=# 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: dn001, dn002
-> Bitmap Heap Scan on t_array (cost=29.40..3172.64 rows=2503 width=61)
Recheck Cond: (mc @> ('{c4ca4238a0b923820dcc509a6f75849b}'::cstring)::text[])
-> Bitmap Index Scan on t_array_mc_idx (cost=0.00..28.78 rows=2503 width=0)
Index Cond: (mc @> ('{c4ca4238a0b923820dcc509a6f75849b}'::cstring)::text[])
(6 rows)
Time: 1.716 ms
postgres=# select * from t_array where mc @> ('{'||md5('1')||'}')::text[];
id | mc
----+------------------------------------
1 | {c4ca4238a0b923820dcc509a6f75849b}
(1 row)
Time: 2.980 ms- Btree_gin任意字段索引。
postgres=# create table gin_mul(f1 int, f2 int, f3 timestamp, f4 text, f5 numeric, f6 text);
postgres=# insert into gin_mul select random()*5000, random()6000, now()+((30000-60000random())||' sec')::interval , md5(random()::text), round((random()*100000)::numeric,2), md5(random()::text) from generate_series(1,1000000);
postgres=# create extension btree_gin;
postgres=# create index gin_mul_gin_idx on gin_mul using gin(f1,f2,f3,f4,f5,f6);
#单字段查询
postgres=# 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: dn002
-> Bitmap Heap Scan on gin_mul (cost=11.51..369.70 rows=194 width=90)
Recheck Cond: (f1 = 10)
-> Bitmap Index Scan on gin_mul_gin_idx (cost=0.00..11.46 rows=194 width=0)
Index Cond: (f1 = 10)
(6 rows)
postgres=#
postgres=# 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: dn001, dn002
-> Bitmap Heap Scan on gin_mul (cost=10.01..12.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..10.01 rows=1 width=0)
Index Cond: (f3 = '2019-02-18 23:01:01'::timestamp without time zone)
(6 rows)
postgres=# 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: dn001, dn002
-> Bitmap Heap Scan on gin_mul (cost=10.01..12.02 rows=1 width=90)
Recheck Cond: (f4 = '2364d9969c8b66402c9b7d17a6d5b7d3'::text)
-> Bitmap Index Scan on gin_mul_gin_idx (cost=0.00..10.01 rows=1 width=0)
Index Cond: (f4 = '2364d9969c8b66402c9b7d17a6d5b7d3'::text)
(6 rows)
postgres=# explain select * from gin_mul where f5=85375.30;
QUERY PLAN
-------------------------------------------------------------------------------------
Remote Fast Query Execution (cost=0.00..0.00 rows=0 width=0)
Node/s: dn001, dn002
-> Bitmap Heap Scan on gin_mul (cost=10.01..12.02 rows=1 width=90)
Recheck Cond: (f5 = 85375.30)
-> Bitmap Index Scan on gin_mul_gin_idx (cost=0.00..10.01 rows=1 width=0)
Index Cond: (f5 = 85375.30)
(6 rows)
#二个字段组合
postgres=# 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: dn001
-> Bitmap Heap Scan on gin_mul (cost=18.00..20.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..18.00 rows=1 width=0)
Index Cond: ((f1 = 2) AND (f3 = '2019-02-18 16:59:52.872523'::timestamp without time zone))
(6 rows)
#三字段组合查询
postgres=# 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: dn001
-> Bitmap Heap Scan on gin_mul (cost=26.00..28.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..26.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)
postgres=#