GIN vs GIST multicolumn index

From: Oleg Mürk <oleg(dot)myrk(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: GIN vs GIST multicolumn index
Date: 2012-07-30 13:51:11
Message-ID: CAEsn3yZW_bf4R24ykCUHZGXSSDuwWUz+JO6grm6CY7AV1p4F4Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Browse pgsql-general by date

  From Date Subject
Next Message Little, Douglas 2012-07-30 14:36:52 conversion from epoch
Previous Message Craig Ringer 2012-07-30 11:08:55 Re: pg_ctl kill QUIT