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@xxxxxxxxx
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
