Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [206.210.65.6]) by hub.org (8.9.3/8.9.3) with ESMTP id NAA20195 for ; Mon, 31 May 1999 13:29:38 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id NAA16217; Mon, 31 May 1999 13:28:52 -0400 (EDT) To: Heiko Wilms cc: "pgsql-sql@postgreSQL.org" Subject: Re: [SQL] indexes In-reply-to: Your message of Mon, 31 May 1999 17:17:38 +0200 <3752A811.40AC6471@stud.fh-hannover.de> Date: Mon, 31 May 1999 13:28:52 -0400 Message-ID: <16215.928171732@sss.pgh.pa.us> From: Tom Lane Heiko Wilms 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