GIN vs GIST multicolumn index

Поиск
Список
Период
Сортировка
От Oleg Mürk
Тема GIN vs GIST multicolumn index
Дата
Msg-id CAEsn3yZW_bf4R24ykCUHZGXSSDuwWUz+JO6grm6CY7AV1p4F4Q@mail.gmail.com
обсуждение исходный текст
Список 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

В списке pgsql-general по дате отправления:

Предыдущее
От: Guillermo Echevarria Quintana-Gurt
Дата:
Сообщение: Re: password help
Следующее
От: "Little, Douglas"
Дата:
Сообщение: conversion from epoch