| 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 |
