Performance of PostgreSQL Multi-Field Random Combination Searches
Learn more about PostgreSQL optimization and enhanced performance through multi-field random combination searches.
Join the DZone community and get the full member experience.
Join For FreeIn 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:
- GIN indexes (supports queries of any field combinations)
- Bloom indexes (supports equivalent queries of any read-only combinations)
- 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 performance.
Based on the test, searches of any fields can achieve a response time of 0.3 milliseconds.
Published at DZone with permission of Leona Zhang. See the original article here.
Opinions expressed by DZone contributors are their own.
Comments