Re: [SQL] indexes

Heiko Wilms <wilms(at)stud(dot)fh-hannover(dot)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