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