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

Mohamed <mohamed5432154321(at)gmail(dot)com> writes:

> 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.

Can you give examples of actual WHERE clauses? It's the combination of
restrictions that actually matters. Are there specific fields which will never
be used on their own, only in combination with others?

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

No index is going to be particularly effective for boolean columns unless
they're very heavily skewed. You might find it useful to build separate
partial indexes on other keys for each value though.

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

GIN and GIST are used for fairly specialized purposes. Full text searching,
geometric data types, etc.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's RemoteDBA services!