Re: GIST versus GIN indexes for intarrays

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