gin索引-分布式融合数据库HTAP-私有云产品历史版本-V2.0.0版本产品文档-开发手册-数据库基础开发-DDL操作-索引管理

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)

应用商城

合作伙伴

开发者

支持与服务

了解天翼云

查看所有产品

分布式融合数据库HTAP

分布式融合数据库HTAP

活动

应用商城

合作伙伴

开发者

支持与服务

了解天翼云

查看所有产品

分布式融合数据库HTAP

分布式融合数据库HTAP