Performance of PostgreSQL Multi-Field Random Combination Searches

By Digoal

In one of my previous articles, we looked at the concepts of PostgreSQL optimization using indexes. For PostgreSQL multi-field and random combination searches, there are three optimization techniques:

  1. GIN indexes (supports queries of any field combinations)
  2. Bloom indexes (supports equivalent queries of any read-only combinations)
  3. Every single-column B-tree index (supports queries of any field combinations)

Example

create table test(c1 int, c2 int, c3 int, c4 int, c5 int);

Methods for Creating Bloom, GIN, and Multiple B-Tree Indexes

1. Bloom

postgres=# create extension bloom ;  
CREATE EXTENSION
postgres=# create index idx_test12_1 on test12 using bloom (c1,c2,c3,c4,c5);
CREATE INDEX
postgres=# explain select * from test12 where c1=1;
QUERY PLAN
----------------------------------------------------------------------------
Bitmap Heap Scan on test12 (cost=13.95..20.32 rows=8 width=20)
Recheck Cond: (c1 = 1)
-> Bitmap Index Scan on idx_test12_1 (cost=0.00..13.95 rows=8 width=0)
Index Cond: (c1 = 1)
(4 rows)
postgres=# explain select * from test12 where c1=1 and c2=1;
QUERY PLAN
----------------------------------------------------------------------------
Bitmap Heap Scan on test12 (cost=18.20..19.42 rows=1 width=20)
Recheck Cond: ((c1 = 1) AND (c2 = 1))
-> Bitmap Index Scan on idx_test12_1 (cost=0.00..18.20 rows=1 width=0)
Index Cond: ((c1 = 1) AND (c2 = 1))
(4 rows)
postgres=# explain select * from test12 where c1=1 or c2=1;
QUERY PLAN
----------------------------------------------------------------------------------
Bitmap Heap Scan on test12 (cost=27.91..38.16 rows=17 width=20)
Recheck Cond: ((c1 = 1) OR (c2 = 1))
-> BitmapOr (cost=27.91..27.91 rows=17 width=0)
-> Bitmap Index Scan on idx_test12_1 (cost=0.00..13.95 rows=8 width=0)
Index Cond: (c1 = 1)
-> Bitmap Index Scan on idx_test12_1 (cost=0.00..13.95 rows=8 width=0)
Index Cond: (c2 = 1)
(7 rows)

2. Gin

postgres=# create extension btree_gin;  
CREATE EXTENSION
postgres=# create index idx_test12_1 on test12 using gin (c1,c2,c3,c4,c5);
CREATE INDEX
postgres=# explain select * from test12 where c1=1 or c2=1;
QUERY PLAN
---------------------------------------------------------------------------------
Bitmap Heap Scan on test12 (cost=4.94..15.19 rows=17 width=20)
Recheck Cond: ((c1 = 1) OR (c2 = 1))
-> BitmapOr (cost=4.94..4.94 rows=17 width=0)
-> Bitmap Index Scan on idx_test12_1 (cost=0.00..2.46 rows=8 width=0)
Index Cond: (c1 = 1)
-> Bitmap Index Scan on idx_test12_1 (cost=0.00..2.46 rows=8 width=0)
Index Cond: (c2 = 1)
(7 rows)

postgres=# explain select * from test12 where c1=1 and c2=1;
QUERY PLAN
---------------------------------------------------------------------------
Bitmap Heap Scan on test12 (cost=3.60..4.82 rows=1 width=20)
Recheck Cond: ((c1 = 1) AND (c2 = 1))
-> Bitmap Index Scan on idx_test12_1 (cost=0.00..3.60 rows=1 width=0)
Index Cond: ((c1 = 1) AND (c2 = 1))
(4 rows)

3. Multi-btree

postgres=# drop index idx_test12_1 ;  
DROP INDEX
postgres=# create index idx_test12_1 on test12 using btree(c1);
CREATE INDEX
postgres=# create index idx_test12_2 on test12 using btree(c2);
CREATE INDEX
postgres=# create index idx_test12_3 on test12 using btree(c3);
CREATE INDEX
postgres=# create index idx_test12_4 on test12 using btree(c4);
CREATE INDEX
postgres=# create index idx_test12_5 on test12 using btree(c5);
CREATE INDEX

postgres=# explain select * from test12 where c1=1 and c2=1;
QUERY PLAN
---------------------------------------------------------------------------------
Bitmap Heap Scan on test12 (cost=3.08..4.29 rows=1 width=20)
Recheck Cond: ((c2 = 1) AND (c1 = 1))
-> BitmapAnd (cost=3.08..3.08 rows=1 width=0)
-> Bitmap Index Scan on idx_test12_2 (cost=0.00..1.41 rows=8 width=0)
Index Cond: (c2 = 1)
-> Bitmap Index Scan on idx_test12_1 (cost=0.00..1.41 rows=8 width=0)
Index Cond: (c1 = 1)
(7 rows)

postgres=# explain select * from test12 where c1=1 or c2=1;
QUERY PLAN
---------------------------------------------------------------------------------
Bitmap Heap Scan on test12 (cost=2.83..13.09 rows=17 width=20)
Recheck Cond: ((c1 = 1) OR (c2 = 1))
-> BitmapOr (cost=2.83..2.83 rows=17 width=0)
-> Bitmap Index Scan on idx_test12_1 (cost=0.00..1.41 rows=8 width=0)
Index Cond: (c1 = 1)
-> Bitmap Index Scan on idx_test12_2 (cost=0.00..1.41 rows=8 width=0)
Index Cond: (c2 = 1)
(7 rows)

What are the performances of GIN, Bloom, and B-Tree bitmap scan?

Wide tables with 1600 columns, search performance of any field combinations

1. Create a table

postgres=# do language plpgsql 
$$

declare
sql text;
begin
sql := 'create table test1 (';
for i in 1..1600 loop
sql := sql||' c'||i||' int2 default random()*100,';
end loop;
sql := rtrim(sql,',');
sql := sql||')';
execute sql;

for i in 1..1600 loop
execute 'create index idx_test1_'||i||' on test1 (c'||i||')';
end loop;
end;
$$
;
DO

2. Write test data

postgres=# insert into test1 (c1)  select generate_series(1,10000);  
INSERT 0 10000

3. Test scripts

vi test.sql  

\set c2 random(1,100)
\set c3 random(1,100)
\set c4 random(1,100)
\set c5 random(1,100)
\set c6 random(1,100)
\set c7 random(1,100)
select c1600 from test1 where c2=:c2 and c3=:c3 and c4=:c4 or (c5=:c5 and c6=:c6 and c7=:c7);

4. Testing

pgbench -M prepared -n -r -P 1 -f ./test.sql -c 64 -j 64 -T 120

5. Performance

progress: 33.0 s, 208797.8 tps, lat 0.307 ms stddev 0.016  
progress: 34.0 s, 208516.0 tps, lat 0.307 ms stddev 0.032
progress: 35.0 s, 208574.0 tps, lat 0.307 ms stddev 0.050
progress: 36.0 s, 208858.2 tps, lat 0.306 ms stddev 0.013
progress: 37.0 s, 208686.8 tps, lat 0.307 ms stddev 0.043
progress: 38.0 s, 208764.2 tps, lat 0.307 ms stddev 0.013

Note: Using prepared statements can reduce hard parsing and improve the performance.

Based on the test, searches of any fields can achieve a response time of 0.3 milliseconds.

Reference:https://www.alibabacloud.com/blog/performance-of-postgresql-multi-field-random-combination-searches_594289?spm=a2c41.12440645.0.0PostgreSQL

Written by

Follow me to keep abreast with the latest technology news, industry insights, and developer trends.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store