Skip site navigation (1) Skip section navigation (2)

GIST versus GIN indexes for intarrays

From: Rusty Conover <rconover(at)infogears(dot)com>
To: psql performance <pgsql-performance(at)postgresql(dot)org>
Subject: GIST versus GIN indexes for intarrays
Date: 2009-02-12 20:09:14
Message-ID: BD68A118-B850-4659-AFBB-9C154F99F3DA@infogears.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-performance
Hi Guys,

I'm a bit confused when the proper way to use GIST versus GIN indexes  
with integer arrays.

The documentation states:

http://www.postgresql.org/docs/current/static/intarray.html

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
rconover(at)infogears(dot)com
InfoGears Inc / GearBuyer.com / FootwearBuyer.com
http://www.infogears.com
http://www.gearbuyer.com
http://www.footwearbuyer.com

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2009-02-12 20:54:16
Subject: Re: GIST versus GIN indexes for intarrays
Previous:From: Scott CareyDate: 2009-02-12 19:50:42
Subject: Re: col1 ILIKE 'foo%' not behaving the same as lower(col1) LIKE 'foo%'

pgsql-hackers by date

Next:From: Robert HaasDate: 2009-02-12 20:09:42
Subject: Re: DISCARD ALL failing to acquire locks on pg_listen
Previous:From: Alvaro HerreraDate: 2009-02-12 20:08:49
Subject: Re: fillfactor for toast tables is useless?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group