Hi Guys,
I'm a bit confused when the proper way to use GIST versus GIN indexes with integer arrays.
The documentation states:
The choice between GiST and GIN indexing depends on the relative performance characteristics of GiST and GIN, which are discussed elsewhere. As a rule of thumb, a GIN index is faster to search than a GiST index, but slower to build or update; so GIN is better suited for static data and GiST for often-updated data.
Since 100% of my queries are for retrieval, I should use GIN but it never appears to be used unlike how GIST indexes are:
gearbuyer_ig=# select version();
version
----------------------------------------------------------------------------------------------------
PostgreSQL 8.3.6 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20070925 (Red Hat 4.1.2-33)
(1 row)
With just a GIN index I get this plan (no use of GIN):
gearbuyer_ig=# explain select count(*) from items where items.fast_colors @> ARRAY[0];
QUERY PLAN
-----------------------------------------------------------------
Aggregate (cost=21194.27..21194.28 rows=1 width=0)
-> Seq Scan on items (cost=0.00..21193.64 rows=251 width=0)
Filter: (fast_colors @> '{0}'::integer[])
(3 rows)
With a GIST index created like:
gearbuyer_ig=# CREATE INDEX items_fast_colors_rdtree2_idx ON items USING gist (fast_colors gist__int_ops);
gearbuyer_ig=# explain select count(*) from items where items.fast_colors @> ARRAY[0];
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Aggregate (cost=929.81..929.82 rows=1 width=0)
-> Bitmap Heap Scan on items (cost=14.30..929.18 rows=251 width=0)
Recheck Cond: (fast_colors @> '{0}'::integer[])
-> Bitmap Index Scan on items_fast_colors_rdtree2_idx (cost=0.00..14.24 rows=251 width=0)
Index Cond: (fast_colors @> '{0}'::integer[])
(5 rows)
Any insight is greatly appreciated. Could this be a regression from 8.3.5 and 8.3.6?
Thanks,
Rusty
--
Rusty Conover
InfoGears Inc / GearBuyer.com / FootwearBuyer.com