Hello,
I need to index a table with two columns:
column_t timestamp
column_ia integer[]
The number of rows is around 100M. Each integer array (column_ia)
contains on average 4 values (100 possible integer values altogether)
I created two indexes:
create index idx_gist on my_table using gist (date_trunc('month',
column_t), column_ia gist__int_ops);
create index idx_gin on my_table using gin (date_trunc('month',
column_t), column_ia gin__int_ops);
I am running the following query:
> select count(*) from my_table where date_trunc('month', column_t) = '2012-03-01' and column_ia && ('{322}'::int[])
count
---------
1343197
(1 row)
For some reason query run time is 4x faster when using GIST index than
using GIN index (when all data is in RAM).
Both query runs perform Bitmap Index Scan. At the same time GIST index
takes 4x more space.
Is this an expected behavior or how can I speed up GIN index speed?
(or reduce GIST index size?)
P.S. Probably bitmap indexes would have been the best option.
Thank You!
Oleg Mürk
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
