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

Re: GIST versus GIN indexes for intarrays

From: Rusty Conover <rconover(at)infogears(dot)com>
To: psql performance <pgsql-performance(at)postgresql(dot)org>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: Re: GIST versus GIN indexes for intarrays
Date: 2009-02-12 21:05:02
Message-ID: 1363CC06-551F-46E7-A662-8DABB42A8910@infogears.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-performance
On Feb 12, 2009, at 1:54 PM, Tom Lane wrote:

> Rusty Conover <rconover(at)infogears(dot)com> writes:
>> Since 100% of my queries are for retrieval, I should use GIN but it
>> never appears to be used unlike how GIST indexes are:
>
> You haven't shown us either the table or the index declaration,
> so it's a bit tough to comment on that.  It's worth noting though
> that your GIST example appears to rely on a nonstandard operator  
> class.
>
> 			regards, tom lane
>

Hi Tom,

My apologies, below is the table definition, and the GIN index creation.

The gist__int_ops is the default operator class for integer[] arrays,  
as shown at:

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

gearbuyer_ig=# \d items
                                   Table "public.items"
          Column          |   Type    |                     Modifiers
-------------------------+----------- 
+---------------------------------------------------
  item_id                 | integer   | not null default  
nextval('generic_seq'::regclass)
  gb_product_url          | text      | not null
  group_id                | integer   |
  category_id             | integer   |
  product_name            | text      | not null
  gender                  | text      | not null
  description_extract     | text      | not null
  sort_price              | real      | not null
  price_range             | text      | not null
  brand_id                | integer   | not null
  xapian_doc_id           | integer   |
  average_rating          | uint1     |
  reviews_count           | smallint  |
  store_count             | uint1     |
  default_image_id        | integer   |
  available_sizes         | integer[] |
  fast_colors             | integer[] |
  has_coupons             | boolean   | not null default false
  age_low                 | uint1     |
  sale_percentage_low     | uint1     |
  store_count_low         | uint1     |
  price_range_low         | smallint  |
  offering_stores         | integer[] |
  subclassification_ids   | integer[] |
  popularity_rank         | integer   |
  default_similarity_type | uint1     |
  default_similarity_id   | integer   |
  gc_lookup_id            | integer   |

The GIN index was created via:

CREATE INDEX items_fast_colors_rdtree_idx ON items USING gin  
(fast_colors);

Cheers,

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

In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2009-02-12 21:29:38
Subject: Re: [PERFORM] GIST versus GIN indexes for intarrays
Previous:From: Tom LaneDate: 2009-02-12 20:54:16
Subject: Re: GIST versus GIN indexes for intarrays

pgsql-hackers by date

Next:From: John ListerDate: 2009-02-12 21:11:07
Subject: Re: database corruption help
Previous:From: Tom LaneDate: 2009-02-12 20:54:16
Subject: Re: GIST versus GIN indexes for intarrays

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