PostgreSQL 10 High Performance - Third Edition

B-tree GIN versus bitmap indexes

PostgreSQL doesn't have bitmap indexes as Oracle does, but GIN is also very good at handling duplicates. The internal structure of a GIN index is actually quite similar to a bitmap index. Bitmax indexes are used for fields with low cardinality, and GIN indices are used in the same way. Let's now look an example of using the GIN indexes as substitutes for bitmap indexes:

pgbench=# create table test_gin (n1 integer,n2 integer); CREATE TABLE pgbench=# insert into test_gin (n1, n2) select n, n % 2 from generate_series(1, 1000000) n; INSERT 0 1000000 pgbench=# create extension btree_gin ; CREATE EXTENSION pgbench=# create index n2_btree on test_gin using btree(n2); CREATE INDEX pgbench=# create index n2_gin on ...