Received: from www-uk-05.digiserve.com ([195.97.231.11]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f3384UK32871 for ; Tue, 3 Apr 2001 04:04:30 -0400 (EDT) (envelope-from dev@archonet.com) Received: from mainbox.archonet.com (mwynhau.demon.co.uk [193.237.186.96]) by www-uk-05.digiserve.com (8.9.1/8.9.1) with ESMTP id JAA07325; Tue, 3 Apr 2001 09:05:17 +0100 (BST) Received: from client (client.archonet.com [192.168.1.16]) by mainbox.archonet.com (Postfix) with SMTP id 563152A3AB; Tue, 3 Apr 2001 09:03:06 +0100 (BST) Message-ID: <00a101c0bc14$82eb4b40$1001a8c0@archonet.com> From: "Richard Huxton" To: "Gerald Gutierrez" , References: Subject: Re: Appropriate indices to create for these queries Date: Tue, 3 Apr 2001 09:03:02 +0100 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 X-Archive-Number: 200104/23 X-Sequence-Number: 2362 From: "Gerald Gutierrez" > > I've been looking into indices and which ones to create and I'm getting > myself a little confused. The "PostgreSQL Introduction and Concepts" book > didn't help very much. I wonder if a kind soul can give me some tips. > > SELECT * FROM T1 WHERE a=1 and b='hello'; > > Is the appropriate index for this query: > > CREATE INDEX ndx ON T1 (a, b) ? Maybe - you seem to have got to the core of the matter below... > When I was testing, it seemed that even if I created the index with only > "a", EXPLAIN told me that it would just do an index scan, seemingly > indicating that it didn't matter whether I had an "a" index, or an "a, b" > index. For the above query, any of : index on "a" , "b", "a,b" will probably be used (if you have enough data to justify it). > How about for WHERE a=1 or b='hello' and other more complex forms? Is there > documentation that describes a variety of different queries and what kind of > indices are best? In this case, an index on "a,b" isn't much use since the b='hello' values are presumably scattered amongst all the various 'a' values. In practice, unless you do a lot of a=1 and b="hello" queries you're probably better off with separate indexes on a and b, or possibly even just on one of them. I tend to apply indexes to fields that take part in a join then add them one at a time to other fields as it becomes clear which takes part in important queries. Don't forget that it takes a certain amount of effort to maintain an index. You've already found the EXPLAIN command - this is your best guide to where an index can be useful. - Richard Huxton