Re: GIST versus GIN indexes for intarrays

On Feb 12, 2009, at 1:54 PM, Tom Lane wrote:

> Rusty Conover <rconover@infogears.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@infogears.com
InfoGears Inc / GearBuyer.com / FootwearBuyer.com
http://www.infogears.com
http://www.gearbuyer.com
http://www.footwearbuyer.com