摘要： 標籤PostgreSQL , btree , hash , gin , gist , sp-gist , brin , bloom , rum , zombodb, bitmap 幕後PostgreSQL 擁有眾多開放屬性，例如1、開放的資料類型介面，使得PG支援超級豐富的資料類型，除了傳統資料庫支援的類型，還支援GIS，JSON，RANGE，IP，ISBN，影像特徵值，化學，DNA等等擴充的類型，使用者還可以根據實際商務擴充其他的類型。
postgres=# create table t_btree(id int, info text);
CREATE TABLE
postgres=# insert into t_btree select generate_series(1,10000), md5(random()::text) ;
INSERT 0 10000
postgres=# create index idx_t_btree_1 on t_btree using btree (id);
CREATE INDEX
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from t_btree where id=1;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_t_btree_1 on public.t_btree(cost=0.29..3.30 rows=1 width=37) (actual time=0.027..0.027 rows=1 loops=1)
Output: id, info
Index Cond: (t_btree.id = 1)
Buffers: shared hit=1 read=2
Planning time: 0.292 ms
Execution time: 0.050 ms
(6 rows)
postgres=# create table t_hash (id int, info text);
CREATE TABLE
postgres=# insert into t_hash select generate_series(1,100), repeat(md5(random()::text),10000);
INSERT 0 100
--使用b-tree索引會報錯，因為長度超過了1/3的索引頁大小
postgres=# create index idx_t_hash_1 on t_hash using btree (info);
ERROR:index row size 3720 exceeds maximum 2712 for index "idx_t_hash_1"
HINT:Values larger than 1/3 of a buffer page cannot be indexed.
Consider a function index of an MD5 hash of the value, or use full text indexing.
postgres=# create index idx_t_hash_1 on t_hash using hash (info);
CREATE INDEX
postgres=# set enable_hashjoin=off;
SET
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from t_hash where info in (select info from t_hash limit 1);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Nested Loop(cost=0.03..3.07 rows=1 width=22) (actual time=0.859..0.861 rows=1 loops=1)
Output: t_hash.id, t_hash.info
Buffers: shared hit=11
->HashAggregate(cost=0.03..0.04 rows=1 width=18) (actual time=0.281..0.281 rows=1 loops=1)
Output: t_hash_1.info
Group Key: t_hash_1.info
Buffers: shared hit=3
->Limit(cost=0.00..0.02 rows=1 width=18) (actual time=0.012..0.012 rows=1 loops=1)
Output: t_hash_1.info
Buffers: shared hit=1
->Seq Scan on public.t_hash t_hash_1(cost=0.00..2.00 rows=100 width=18) (actual time=0.011..0.011 rows=1 loops=1)
Output: t_hash_1.info
Buffers: shared hit=1
->Index Scan using idx_t_hash_1 on public.t_hash(cost=0.00..3.02 rows=1 width=22) (actual time=0.526..0.527 rows=1 loops=1)
Output: t_hash.id, t_hash.info
Index Cond: (t_hash.info = t_hash_1.info)
Buffers: shared hit=6
Planning time: 0.159 ms
Execution time: 0.898 ms
(19 rows)
postgres=# create table t_gin1 (id int, arr int[]);
CREATE TABLE
postgres=# do language plpgsql $$
postgres$# declare
postgres$# begin
postgres$#for i in 1..10000 loop
postgres$#insert into t_gin1 select i, array(select random()*1000 from generate_series(1,10));
postgres$#end loop;
postgres$# end;
postgres$# $$;
DO
postgres=# select * from t_gin1 limit 3;
id |arr
----+-------------------------------------------
1 | {128,700,814,592,414,838,615,827,274,210}
2 | {284,452,824,556,132,121,21,705,537,865}
3 | {65,185,586,872,627,330,574,227,827,64}
(3 rows)
postgres=# create index idx_t_gin1_1 on t_gin1 using gin (arr);
CREATE INDEX
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from t_gin1 where arr && array[1,2];
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on public.t_gin1(cost=8.93..121.24 rows=185 width=65) (actual time=0.058..0.207 rows=186 loops=1)
Output: id, arr
Recheck Cond: (t_gin1.arr && '{1,2}'::integer[])
Heap Blocks: exact=98
Buffers: shared hit=103
->Bitmap Index Scan on idx_t_gin1_1(cost=0.00..8.89 rows=185 width=0) (actual time=0.042..0.042 rows=186 loops=1)
Index Cond: (t_gin1.arr && '{1,2}'::integer[])
Buffers: shared hit=5
Planning time: 0.208 ms
Execution time: 0.245 ms
(10 rows)
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from t_gin1 where arr @> array[1,2];
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on public.t_gin1(cost=7.51..9.02 rows=1 width=65) (actual time=0.022..0.022 rows=0 loops=1)
Output: id, arr
Recheck Cond: (t_gin1.arr @> '{1,2}'::integer[])
Buffers: shared hit=5
->Bitmap Index Scan on idx_t_gin1_1(cost=0.00..7.51 rows=1 width=0) (actual time=0.020..0.020 rows=0 loops=1)
Index Cond: (t_gin1.arr @> '{1,2}'::integer[])
Buffers: shared hit=5
Planning time: 0.116 ms
Execution time: 0.044 ms
(9 rows)2、單值稀疏資料搜尋
postgres=# create extension btree_gin;
CREATE EXTENSION
postgres=# create table t_gin2 (id int, c1 int);
CREATE TABLE
postgres=# insert into t_gin2 select generate_series(1,100000), random()*10 ;
INSERT 0 100000
postgres=# create index idx_t_gin2_1 on t_gin2 using gin (c1);
CREATE INDEX
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from t_gin2 where c1=1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on public.t_gin2(cost=84.10..650.63 rows=9883 width=8) (actual time=0.925..3.685 rows=10078 loops=1)
Output: id, c1
Recheck Cond: (t_gin2.c1 = 1)
Heap Blocks: exact=443
Buffers: shared hit=448
->Bitmap Index Scan on idx_t_gin2_1(cost=0.00..81.62 rows=9883 width=0) (actual time=0.867..0.867 rows=10078 loops=1)
Index Cond: (t_gin2.c1 = 1)
Buffers: shared hit=5
Planning time: 0.252 ms
Execution time: 4.234 ms
(10 rows)3、多列任意搜尋
postgres=# create table t_gin3 (id int, c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int, c9 int);
CREATE TABLE
postgres=# insert into t_gin3 select generate_series(1,100000), random()*10, random()*20, random()*30, random()*40, random()*50, random()*60, random()*70, random()*80, random()*90;
INSERT 0 100000
postgres=# create index idx_t_gin3_1 on t_gin3 using gin (c1,c2,c3,c4,c5,c6,c7,c8,c9);
CREATE INDEX
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from t_gin3 where c1=1 or c2=1 and c3=1 or c4=1 and (c6=1 or c7=2) or c8=9 or c9=10;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on public.t_gin3(cost=154.03..1364.89 rows=12286 width=40) (actual time=1.931..5.634 rows=12397 loops=1)
Output: id, c1, c2, c3, c4, c5, c6, c7, c8, c9
Recheck Cond: ((t_gin3.c1 = 1) OR ((t_gin3.c2 = 1) AND (t_gin3.c3 = 1)) OR (((t_gin3.c4 = 1) AND (t_gin3.c6 = 1)) OR ((t_gin3.c4 = 1) AND (t_gin3.c7 = 2))) OR (t_gin3.c8 = 9) OR (t_gin3.c9 = 10))
Heap Blocks: exact=834
Buffers: shared hit=867
->BitmapOr(cost=154.03..154.03 rows=12562 width=0) (actual time=1.825..1.825 rows=0 loops=1)
Buffers: shared hit=33
->Bitmap Index Scan on idx_t_gin3_1(cost=0.00..83.85 rows=9980 width=0) (actual time=0.904..0.904 rows=10082 loops=1)
Index Cond: (t_gin3.c1 = 1)
Buffers: shared hit=6
->Bitmap Index Scan on idx_t_gin3_1(cost=0.00..9.22 rows=172 width=0) (actual time=0.355..0.355 rows=164 loops=1)
Index Cond: ((t_gin3.c2 = 1) AND (t_gin3.c3 = 1))
Buffers: shared hit=8
->BitmapOr(cost=21.98..21.98 rows=83 width=0) (actual time=0.334..0.334 rows=0 loops=1)
Buffers: shared hit=13
->Bitmap Index Scan on idx_t_gin3_1(cost=0.00..7.92 rows=42 width=0) (actual time=0.172..0.172 rows=36 loops=1)
Index Cond: ((t_gin3.c4 = 1) AND (t_gin3.c6 = 1))
Buffers: shared hit=6
->Bitmap Index Scan on idx_t_gin3_1(cost=0.00..7.91 rows=41 width=0) (actual time=0.162..0.162 rows=27 loops=1)
Index Cond: ((t_gin3.c4 = 1) AND (t_gin3.c7 = 2))
Buffers: shared hit=7
->Bitmap Index Scan on idx_t_gin3_1(cost=0.00..14.38 rows=1317 width=0) (actual time=0.124..0.124 rows=1296 loops=1)
Index Cond: (t_gin3.c8 = 9)
Buffers: shared hit=3
->Bitmap Index Scan on idx_t_gin3_1(cost=0.00..12.07 rows=1010 width=0) (actual time=0.102..0.102 rows=1061 loops=1)
Index Cond: (t_gin3.c9 = 10)
Buffers: shared hit=3
Planning time: 0.272 ms
Execution time: 6.349 ms
(29 rows)
postgres=# create table t_gist (id int, pos point);
CREATE TABLE
postgres=# insert into t_gist select generate_series(1,100000), point(round((random()*1000)::numeric, 2), round((random()*1000)::numeric, 2));
INSERT 0 100000
postgres=# select * from t_gistlimit 3;
id |pos
----+-----------------
1 | (325.43,477.07)
2 | (257.65,710.94)
3 | (502.42,582.25)
(3 rows)
postgres=# create index idx_t_gist_1 on t_gist using gist (pos);
CREATE INDEX
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from t_gist where circle '((100,100) 10)'@> pos;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on public.t_gist(cost=2.55..125.54 rows=100 width=20) (actual time=0.072..0.132 rows=46 loops=1)
Output: id, pos
Recheck Cond: ('<(100,100),10>'::circle @> t_gist.pos)
Heap Blocks: exact=41
Buffers: shared hit=47
->Bitmap Index Scan on idx_t_gist_1(cost=0.00..2.53 rows=100 width=0) (actual time=0.061..0.061 rows=46 loops=1)
Index Cond: ('<(100,100),10>'::circle @> t_gist.pos)
Buffers: shared hit=6
Planning time: 0.147 ms
Execution time: 0.167 ms
(10 rows)
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from t_gist where circle '((100,100) 1)' @> pos order by pos <-> '(100,100)' limit 10;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
Limit(cost=0.28..14.60 rows=10 width=28) (actual time=0.045..0.048 rows=2 loops=1)
Output: id, pos, ((pos <-> '(100,100)'::point))
Buffers: shared hit=5
->Index Scan using idx_t_gist_1 on public.t_gist(cost=0.28..143.53 rows=100 width=28) (actual time=0.044..0.046 rows=2 loops=1)
Output: id, pos, (pos <-> '(100,100)'::point)
Index Cond: ('<(100,100),1>'::circle @> t_gist.pos)
Order By: (t_gist.pos <-> '(100,100)'::point)
Buffers: shared hit=5
Planning time: 0.092 ms
Execution time: 0.076 ms
(10 rows)2、純量類型排序
postgres=# create extension btree_gist;
CREATE EXTENSION
postgres=# create index idx_t_btree_2 on t_btree using gist(id);
CREATE INDEX
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from t_btree order by id <-> 100 limit 1;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Limit(cost=0.15..0.19 rows=1 width=41) (actual time=0.046..0.046 rows=1 loops=1)
Output: id, info, ((id <-> 100))
Buffers: shared hit=3
->Index Scan using idx_t_btree_2 on public.t_btree(cost=0.15..408.65 rows=10000 width=41) (actual time=0.045..0.045 rows=1 loops=1)
Output: id, info, (id <-> 100)
Order By: (t_btree.id <-> 100)
Buffers: shared hit=3
Planning time: 0.085 ms
Execution time: 0.076 ms
(9 rows)
postgres=# create table t_spgist (id int, rg int4range);
CREATE TABLE
postgres=# insert into t_spgist select id, int4range(id, id+(random()*200)::int) from generate_series(1,100000) t(id);
INSERT 0 100000
postgres=# select * from t_spgistlimit 3;
id |rg
----+---------
1 | [1,138)
2 | [2,4)
3 | [3,111)
(3 rows)
postgres=# set maintenance_work_mem ='32GB';
SET
postgres=# create index idx_t_spgist_1 on t_spgist using spgist (rg);
CREATE INDEX
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from t_spgist where rg && int4range(1,100);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on public.t_spgist(cost=2.55..124.30 rows=99 width=17) (actual time=0.059..0.071 rows=99 loops=1)
Output: id, rg
Recheck Cond: (t_spgist.rg && '[1,100)'::int4range)
Heap Blocks: exact=1
Buffers: shared hit=6
->Bitmap Index Scan on idx_t_spgist_1(cost=0.00..2.52 rows=99 width=0) (actual time=0.043..0.043 rows=99 loops=1)
Index Cond: (t_spgist.rg && '[1,100)'::int4range)
Buffers: shared hit=5
Planning time: 0.133 ms
Execution time: 0.111 ms
(10 rows)
postgres=# set enable_bitmapscan=off;
SET
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from t_spgist where rg && int4range(1,100);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_t_spgist_1 on public.t_spgist(cost=0.28..141.51 rows=99 width=17) (actual time=0.021..0.051 rows=99 loops=1)
Output: id, rg
Index Cond: (t_spgist.rg && '[1,100)'::int4range)
Buffers: shared hit=8
Planning time: 0.097 ms
Execution time: 0.074 ms
(6 rows)
postgres=# create table t_brin (id int, info text, crt_time timestamp);
CREATE TABLE
postgres=# insert into t_brin select generate_series(1,1000000), md5(random()::text), clock_timestamp();
INSERT 0 1000000
postgres=# select ctid,* from t_brin limit 3;
ctid| id |info|crt_time
-------+----+----------------------------------+----------------------------
(0,1) |1 | e48a6cd688b6cc8e86ee858fa993b31b | 2017-06-27 22:50:19.172224
(0,2) |2 | e79c335c679b0bf544e8ba5f01569df7 | 2017-06-27 22:50:19.172319
(0,3) |3 | b75ec6db320891a620097164b751e682 | 2017-06-27 22:50:19.172323
(3 rows)
postgres=# select correlation from pg_stats where tablename='t_brin' and attname='id';
correlation
-------------
1
(1 row)
postgres=# select correlation from pg_stats where tablename='t_brin' and attname='crt_time';
correlation
-------------
1
(1 row)
postgres=# create index idx_t_brin_1 on t_brin using brin (id) with (pages_per_range=1);
CREATE INDEX
postgres=# create index idx_t_brin_2 on t_brin using brin (crt_time) with (pages_per_range=1);
CREATE INDEX
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from t_brin where id between 100 and 200;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on public.t_brin(cost=43.52..199.90 rows=74 width=45) (actual time=1.858..1.876 rows=101 loops=1)
Output: id, info, crt_time
Recheck Cond: ((t_brin.id >= 100) AND (t_brin.id <= 200))
Rows Removed by Index Recheck: 113
Heap Blocks: lossy=2
Buffers: shared hit=39
->Bitmap Index Scan on idx_t_brin_1(cost=0.00..43.50 rows=107 width=0) (actual time=1.840..1.840 rows=20 loops=1)
Index Cond: ((t_brin.id >= 100) AND (t_brin.id <= 200))
Buffers: shared hit=37
Planning time: 0.174 ms
Execution time: 1.908 ms
(11 rows)
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from t_brin where crt_time between '2017-06-27 22:50:19.172224' and '2017-06-27 22:50:19.182224';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on public.t_brin(cost=59.63..4433.67 rows=4474 width=45) (actual time=1.860..2.603 rows=4920 loops=1)
Output: id, info, crt_time
Recheck Cond: ((t_brin.crt_time >= '2017-06-27 22:50:19.172224'::timestamp without time zone) AND (t_brin.crt_time <= '2017-06-27 22:50:19.182224'::timestamp without time zone))
Rows Removed by Index Recheck: 2
Heap Blocks: lossy=46
Buffers: shared hit=98
->Bitmap Index Scan on idx_t_brin_2(cost=0.00..58.51 rows=4494 width=0) (actual time=1.848..1.848 rows=460 loops=1)
Index Cond: ((t_brin.crt_time >= '2017-06-27 22:50:19.172224'::timestamp without time zone) AND (t_brin.crt_time <= '2017-06-27 22:50:19.182224'::timestamp without time zone))
Buffers: shared hit=52
Planning time: 0.091 ms
Execution time: 2.884 ms
(11 rows)
postgres=# select to_tsvector('english', 'hello digoal');
to_tsvector
----------------------
'digoal':2 'hello':1
(1 row)
postgres=# select to_tsvector('english', 'hello i digoal');
to_tsvector
----------------------
'digoal':3 'hello':1
(1 row)
postgres=# select to_tsvector('english', 'hello i am digoal');
to_tsvector
----------------------
'digoal':4 'hello':1
(1 row)
postgres=# select to_tsquery('english', 'hello <1> digoal');
to_tsquery
----------------------
'hello' <-> 'digoal'
(1 row)
postgres=# select to_tsquery('english', 'hello <2> digoal');
to_tsquery
----------------------
'hello' <2> 'digoal'
(1 row)
postgres=# select to_tsquery('english', 'hello <3> digoal');
to_tsquery
----------------------
'hello' <3> 'digoal'
(1 row)
postgres=# select to_tsvector('hello digoal') @@ to_tsquery('english', 'hello <1> digoal');
?column?
----------
t
(1 row)
postgres=# select to_tsvector('hello digoal') @@ to_tsquery('english', 'hello <2> digoal');
?column?
----------
f
(1 row)
postgres=# select to_tsvector('hello i digoal') @@ to_tsquery('english', 'hello <2> digoal');
?column?
----------
t
(1 row)套用場景 《PostgreSQL全文檢索索引加速 快到沒有朋友 -RUM索引介面(潘朵拉魔盒)》 《從難纏的柔邊查詢聊開 -PostgreSQL獨門絕招之一 GIN , GiST , SP-GiST , RUM 索引原理與技術幕後》 《PostgreSQL結合余弦、線性相關演算法 在文字、圖片、陣列相似 等領域的套用 - 3rum, smlar套用場景剖析》 例子
postgres=# create table rum_test(c1 tsvector);
CREATE TABLE
postgres=# CREATE INDEX rumidx ON rum_test USING rum (c1 rum_tsvector_ops);
CREATE INDEX
$ vi test.sql
insert into rum_test select to_tsvector(string_agg(c1::text,',')) from(select (100000*random())::int from generate_series(1,100)) t(c1);
$ pgbench -M prepared -n -r -P 1 -f ./test.sql -c 50 -j 50 -t 200000
postgres=# explain analyze select * from rum_test where c1 @@ to_tsquery('english','1 | 2') order by c1 <=> to_tsquery('english','1 | 2') offset 19000 limit 100;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Limit(cost=18988.45..19088.30 rows=100 width=1391) (actual time=58.912..59.165 rows=100 loops=1)
->Index Scan using rumidx on rum_test(cost=16.00..99620.35 rows=99749 width=1391) (actual time=16.426..57.892 rows=19100 loops=1)
Index Cond: (c1 @@ '''1'' | ''2'''::tsquery)
Order By: (c1 <=> '''1'' | ''2'''::tsquery)
Planning time: 0.133 ms
Execution time: 59.220 ms
(6 rows)
postgres=# create table test15(c1 tsvector);
CREATE TABLE
postgres=# insert into test15 values (to_tsvector('jiebacfg', 'hello china, i''m digoal')), (to_tsvector('jiebacfg', 'hello world, i''m postgresql')), (to_tsvector('jiebacfg', 'how are you, i''m digoal'));
INSERT 0 3
postgres=# select * from test15;
c1
-----------------------------------------------------
' ':2,5,9 'china':3 'digoal':10 'hello':1 'm':8
' ':2,5,9 'hello':1 'm':8 'postgresql':10 'world':3
' ':2,4,7,11 'digoal':12 'm':10
(3 rows)
postgres=# create index idx_test15 on test15 using rum(c1 rum_tsvector_ops);
CREATE INDEX
postgres=# select *,c1 <=> to_tsquery('hello') from test15;
c1| ?column?
-----------------------------------------------------+----------
' ':2,5,9 'china':3 'digoal':10 'hello':1 'm':8|16.4493
' ':2,5,9 'hello':1 'm':8 'postgresql':10 'world':3 |16.4493
' ':2,4,7,11 'digoal':12 'm':10| Infinity
(3 rows)
postgres=# explain select *,c1 <=> to_tsquery('postgresql') from test15 order by c1 <=> to_tsquery('postgresql');
QUERY PLAN
--------------------------------------------------------------------------------
Index Scan using idx_test15 on test15(cost=3600.25..3609.06 rows=3 width=36)
Order By: (c1 <=> to_tsquery('postgresql'::text))
(2 rows)GINVS RUM GIN
postgres=# create table t_gin_1 (id int, ts tsvector);
CREATE TABLE
postgres=# insert into t_gin_1 values (1, to_tsvector('hello digoal')),(2, to_tsvector('hello i digoal')),(3, to_tsvector('hello i am digoal'));
INSERT 0 3
postgres=# create index idx_t_gin_1_1 on t_gin_1 using gin (ts);
CREATE INDEX
postgres=# explain select * from t_gin_1 where ts @@ to_tsquery('english', 'hello <1> digoal');
QUERY PLAN
--------------------------------------------------------
Seq Scan on t_gin_1(cost=0.00..1.04 rows=1 width=36)
Filter: (ts @@ '''hello'' <-> ''digoal'''::tsquery)
(2 rows)
postgres=# set enable_seqscan=off;
SET
postgres=# explain select * from t_gin_1 where ts @@ to_tsquery('english', 'hello <1> digoal');
QUERY PLAN
----------------------------------------------------------------------------
Bitmap Heap Scan on t_gin_1(cost=4.50..6.01 rows=1 width=36)
Recheck Cond: (ts @@ '''hello'' <-> ''digoal'''::tsquery)
->Bitmap Index Scan on idx_t_gin_1_1(cost=0.00..4.50 rows=1 width=0)
Index Cond: (ts @@ '''hello'' <-> ''digoal'''::tsquery)
(4 rows)
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from t_gin_1 where ts @@ to_tsquery('english', 'hello <1> digoal');
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on public.t_gin_1(cost=4.50..6.01 rows=1 width=36) (actual time=0.029..0.030 rows=1 loops=1)
Output: id, ts
Recheck Cond: (t_gin_1.ts @@ '''hello'' <-> ''digoal'''::tsquery)
Rows Removed by Index Recheck: 2
Heap Blocks: exact=1
Buffers: shared hit=4
->Bitmap Index Scan on idx_t_gin_1_1(cost=0.00..4.50 rows=1 width=0) (actual time=0.018..0.018 rows=3 loops=1)
Index Cond: (t_gin_1.ts @@ '''hello'' <-> ''digoal'''::tsquery)
Buffers: shared hit=3
Planning time: 0.106 ms
Execution time: 0.061 ms
(11 rows)RUM
postgres=# create table t_gin_1 (id int, ts tsvector);
CREATE TABLE
postgres=# insert into t_gin_1 values (1, to_tsvector('hello digoal')),(2, to_tsvector('hello i digoal')),(3, to_tsvector('hello i am digoal'));
INSERT 0 3
postgres=#create index idx_t_gin_1_1 on t_gin_1 using rum (ts rum_tsvector_ops);
CREATE INDEX
postgres=# explain select * from t_gin_1 where ts @@ to_tsquery('english', 'hello <1> digoal');
QUERY PLAN
--------------------------------------------------------
Seq Scan on t_gin_1(cost=0.00..1.04 rows=1 width=36)
Filter: (ts @@ '''hello'' <-> ''digoal'''::tsquery)
(2 rows)
postgres=# set enable_seqscan =off;
SET
postgres=# explain select * from t_gin_1 where ts @@ to_tsquery('english', 'hello <1> digoal');
QUERY PLAN
------------------------------------------------------------------------------
Index Scan using idx_t_gin_1_1 on t_gin_1(cost=2.00..4.01 rows=1 width=36)
Index Cond: (ts @@ '''hello'' <-> ''digoal'''::tsquery)
(2 rows)
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from t_gin_1 where ts @@ to_tsquery('english', 'hello <1> digoal');
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_t_gin_1_1 on public.t_gin_1(cost=2.00..4.01 rows=1 width=36) (actual time=0.049..0.049 rows=1 loops=1)
Output: id, ts
Index Cond: (t_gin_1.ts @@ '''hello'' <-> ''digoal'''::tsquery)
Buffers: shared hit=3
Planning time: 0.288 ms
Execution time: 0.102 ms
(6 rows)
CREATE INDEX bloomidx ON tbloom USING bloom (i1,i2,i3)
WITH (length=80, col1=2, col2=2, col3=4);
簽章長度 80 bit,最大允許4096 bits
col1 - col32，分別指定每列的bits，預設長度2，最大允許4095 bits.bloomprovides an index access method based on Bloom filters. ABloom filter is a space-efficient data structure that is used to test whetheran element is a member of a set. In the case of an index access method, itallows fast exclusion of non-matching tuples via signatures whose size isdetermined at index creation. Thistype of index is most useful when a table has many attributes and queries testarbitrary combinations of them. 套用場景 bloom索引適合多列任意群組查詢。 《PostgreSQL9.6 黑科技 bloom 演算法索引，一個索引支架任意列群組查詢》 例子
=# CREATE TABLE tbloom AS
SELECT
(random() * 1000000)::int as i1,
(random() * 1000000)::int as i2,
(random() * 1000000)::int as i3,
(random() * 1000000)::int as i4,
(random() * 1000000)::int as i5,
(random() * 1000000)::int as i6
FROM
generate_series(1,10000000);
SELECT 10000000
=# CREATE INDEX bloomidx ON tbloom USING bloom (i1, i2, i3, i4, i5, i6);
CREATE INDEX
=# SELECT pg_size_pretty(pg_relation_size('bloomidx'));
pg_size_pretty
----------------
153 MB
(1 row)
=# CREATE index btreeidx ON tbloom (i1, i2, i3, i4, i5, i6);
CREATE INDEX
=# SELECT pg_size_pretty(pg_relation_size('btreeidx'));
pg_size_pretty
----------------
387 MB
(1 row)
=# EXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 = 898732 AND i5 = 123451;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tbloom(cost=178435.39..178439.41 rows=1 width=24) (actual time=76.698..76.698 rows=0 loops=1)
Recheck Cond: ((i2 = 898732) AND (i5 = 123451))
Rows Removed by Index Recheck: 2439
Heap Blocks: exact=2408
->Bitmap Index Scan on bloomidx(cost=0.00..178435.39 rows=1 width=0) (actual time=72.455..72.455 rows=2439 loops=1)
Index Cond: ((i2 = 898732) AND (i5 = 123451))
Planning time: 0.475 ms
Execution time: 76.778 ms
(8 rows)
-- Install the extension:
CREATE EXTENSION zombodb;
-- Create a table:
CREATE TABLE products (
id SERIAL8 NOT NULL PRIMARY KEY,
name text NOT NULL,
keywords varchar(64)[],
short_summary phrase,
long_description fulltext,
price bigint,
inventory_count integer,
discontinued boolean default false,
availability_date date
);
-- insert some data
-- Index it:
CREATE INDEX idx_zdb_products
ON products
USING zombodb(zdb('products', products.ctid), zdb(products))
WITH (url='http://localhost:9200/', shards=5, replicas=1);
-- Query it:
SELECT *
FROM products
WHERE zdb('products', ctid) ==> 'keywords:(sports,box) or long_description:(wooden w/5 away) and price < 100000';
postgres=# create table t_bitmap(id int, info text, c1 int);
NOTICE:Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Greenplum Database data distribution key for this table.
HINT:The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE
postgres=# insert into t_bitmap select generate_series(1,1000000), 'test', random()*1000;
INSERT 0 1000000
postgres=# create index idx_t_bitmap_1 on t_bitmap using bitmap(c1);
CREATE INDEX
postgres=# explain analyze select * from t_bitmap where c1=1;
QUERY PLAN
----------------------------------------------------------------------------------------
Gather Motion 3:1(slice1; segments: 3)(cost=0.00..200.27 rows=1 width=13)
Rows out:0 rows at destination with 3.769 ms to end, start offset by 0.250 ms.
->Index Scan using idx_t_bitmap_1 on t_bitmap(cost=0.00..200.27 rows=1 width=13)
Index Cond: c1 = 1
Rows out:0 rows (seg0) with 0.091 ms to end, start offset by 3.004 ms.
Slice statistics:
(slice0)Executor memory: 115K bytes.
(slice1)Executor memory: 273K bytes avg x 3 workers, 273K bytes max (seg0).
Statement statistics:
Memory used: 128000K bytes
Total runtime: 4.110 ms
(11 rows)
postgres=# explain analyze select * from t_bitmap where c1<=10;
QUERY PLAN
----------------------------------------------------------------------------------------
Gather Motion 3:1(slice1; segments: 3)(cost=0.00..200.27 rows=1 width=13)
Rows out:0 rows at destination with 2.952 ms to end, start offset by 0.227 ms.
->Index Scan using idx_t_bitmap_1 on t_bitmap(cost=0.00..200.27 rows=1 width=13)
Index Cond: c1 <= 10
Rows out:0 rows (seg0) with 0.055 ms to end, start offset by 3.021 ms.
Slice statistics:
(slice0)Executor memory: 115K bytes.
(slice1)Executor memory: 273K bytes avg x 3 workers, 273K bytes max (seg0).
Statement statistics:
Memory used: 128000K bytes
Total runtime: 3.278 ms
(11 rows)
create table test(id int, info text, crt_time timestamp, active boolean);
create index idx_test_id on test(id) where active;
select * from test where active and id=?;--可以使用部份索引
create table t_express (id int, pos geometry);
create index idx_t_express_1 on t_express using gist ( ( ST_Transform(pos, 26986) ) );
select * from t_express order by ST_Transform(pos, 26986) <-> ST_Transform(ST_GeomFromText('POINT(108.50000000001 22.8)', 4326), 26986) limit 10;
test=# SELECT * FROM bt_page_stats('pg_cast_oid_index', 1);
-[ RECORD 1 ]-+-----
blkno| 1
type| l
live_items| 256
dead_items| 0
avg_item_size | 12
page_size| 8192
free_size| 4056
btpo_prev| 0
btpo_next| 0
btpo| 0
btpo_flags| 3
test=# SELECT * FROM bt_page_items('pg_cast_oid_index', 1);
itemoffset |ctid| itemlen | nulls | vars |data
------------+---------+---------+-------+------+-------------
1 | (0,1)|12 | f| f| 23 27 00 00
2 | (0,2)|12 | f| f| 24 27 00 00
3 | (0,3)|12 | f| f| 25 27 00 00
4 | (0,4)|12 | f| f| 26 27 00 00
5 | (0,5)|12 | f| f| 27 27 00 00
6 | (0,6)|12 | f| f| 28 27 00 00
7 | (0,7)|12 | f| f| 29 27 00 00
8 | (0,8)|12 | f| f| 2a 27 00 00
test=# SELECT * FROM brin_page_items(get_raw_page('brinidx', 5),
'brinidx')
ORDER BY blknum, attnum LIMIT 6;
itemoffset | blknum | attnum | allnulls | hasnulls | placeholder |value
------------+--------+--------+----------+----------+-------------+--------------
137 |0 |1 | t| f| f|
137 |0 |2 | f| f| f| {1 .. 88}
138 |4 |1 | t| f| f|
138 |4 |2 | f| f| f| {89 .. 176}
139 |8 |1 | t| f| f|
139 |8 |2 | f| f| f| {177 .. 264}
相關產品：
1.物件隱藏OSS
2.DataV資料視覺效果
3.巨量資料計算服務(MaxCompute)
4.雲端服務器ECS
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
