Received: from mail2.uniserve.com (mail2.uniserve.com [204.244.156.10]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f331i4K78212 for ; Mon, 2 Apr 2001 21:44:04 -0400 (EDT) (envelope-from gutz@kalador.com) Received: from van12d17.dial.uniserve.ca ([204.244.161.80] helo=gutz) by mail2.uniserve.com with smtp (Exim 3.13 #1) id 14kFrP-0002DX-00 for pgsql-sql@postgresql.org; Mon, 02 Apr 2001 18:43:58 -0700 From: "Gerald Gutierrez" To: Subject: Appropriate indices to create for these queries Date: Mon, 2 Apr 2001 18:49:44 -0700 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0) Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6700 X-Archive-Number: 200104/21 X-Sequence-Number: 2360 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) ? 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. 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? Thanks.