Indices types, what to use. Btree, Hash, Gin or Gist

From: Mohamed <mohamed5432154321(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Indices types, what to use. Btree, Hash, Gin or Gist
Date: 2009-01-31 19:21:17
Message-ID: 861fed220901311121sfcfc490sc408889e71e555c6@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,
I have several fields that use to match with my queries. I am curious to
what index types is best for what. Here is some examples that will help you
understand.

Say I have a 1000 000 rows.

Speed is of the essence here, insertions and updates happens relatively less
frequent than search.

I want to match against a boolean field, that is, only true or false is
possible. I am thinking Btree but not sure.. correct?

Match against a field which is an big in and contains the ids for the states
of america (say 50 different values), a btree again?

Against an integer field? Using > < = and between

Date (no timezone), I would like to have this field presorted in desc order.
How does that creation look like?

I understand the Hash is not recommended. When should I use the Gin index ?

Thanks in advance / Moe

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jasen Betts 2009-01-31 19:35:17 Re: Pet Peeves?
Previous Message Jasen Betts 2009-01-31 19:18:25 Re: Pet Peeves?