Re: Any "guide to indexes" exists?

On May 7, 2007, at 9:44 AM, Pavel Stehule wrote:
>> Hello,
>> I would need more info about index types in postgre (btree, hash,
>> gin and
>> gist) - is there any guide that explains in detail when to use
>> which index
>> type? These index types have different performance with certain
>> collumn
>> types and data characteristics store in them. There's not much
>> info about it
>> pg docs. So, does any document describing detailed index usage or
>> do you
>> have any personal recomendations when to use which index?
>
> http://people.planetpostgresql.org/mha/index.php?/archives/112-GIN-
> performance-postgresql.org-websearch-part-2.html
> http://people.planetpostgresql.org/xzilla/index.php?/archives/278-
> PostgreSQL-full-text-search-testing.html
>
>
> Summary:
> * fulltext, GIS, arrays .. gin, gist indexes (gin is faster, but
> slow update)
> * others .. btree index (I don't know anybody who use hash index)

GiST can also be useful if you have to query in multiple dimensions,
which can occur outside the normal case of geometry. Best example I
know of is a table containing duration information in the form of
start_time and end_time. Trying to query for what events happened on
5/28/2005 will generally be much cheaper with a GiST index than a b-
tree.
--
Jim Nasby jim(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)