From: Tom Lane <tgl@sss.pgh.pa.us>
To: Heiko Wilms <wilms@stud.fh-hannover.de>
Cc: pgsql-sql@postgreSQL.org <pgsql-sql@postgreSQL.org>
Subject: Re: [SQL] indexes
Date: Mon, 31 May 1999 13:28:52 -0400
Message-ID: <16215.928171732@sss.pgh.pa.us> (raw)
In-Reply-To: <3752A811.40AC6471@stud.fh-hannover.de>
Heiko Wilms <wilms@stud.fh-hannover.de> writes:
> indices speed up database queries. Tell me if I'm wrong.
> Does it make sense to create indices on all fields involved in
> a query?
Well, indexes can speed up queries, but they also slow down inserts
and updates, because each index has to be updated whenever you update
the table. So it's a tradeoff. You probably don't want to make indexes
that are only useful for seldom-used queries, unless updates to the
table are even less frequent than the queries.
If you are wondering *which* fields to make indexes on, one way to
decide is to create a whole set of indexes and then run EXPLAIN on
the kinds of queries you do most often. EXPLAIN will show you which
indexes the optimizer is actually using --- then you can get rid of
the rest. (Be sure to load the tables to representative sizes and
do a VACUUM ANALYZE before you trust the results from EXPLAIN very
much.)
> If so, what is the best access method (btree,rtree,hash)?
btree is the best general-purpose index type. rtree is only suitable
for 2-D data (polygons and similar data types). hash is less flexible
than btree because you cannot use it for an ordered scan of the table.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, wilms@stud.fh-hannover.de
Subject: Re: [SQL] indexes
In-Reply-To: <16215.928171732@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox