Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 702059F9F47 for ; Sat, 30 Dec 2006 14:07:01 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024) with ESMTP id 00427-08 for ; Sat, 30 Dec 2006 14:06:49 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 101039F9CE3 for ; Sat, 30 Dec 2006 14:06:55 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id kBUI6osl013437; Sat, 30 Dec 2006 13:06:50 -0500 (EST) To: Bruno Wolff III cc: Rajesh Kumar Mallah , Lista Postgres Subject: Re: Is unique index always used if query is on first constituent column ? In-reply-to: <20061230171528.GA2803@wolff.to> References: <20061230171528.GA2803@wolff.to> Comments: In-reply-to Bruno Wolff III message dated "Sat, 30 Dec 2006 11:15:28 -0600" Date: Sat, 30 Dec 2006 13:06:50 -0500 Message-ID: <13436.1167502010@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/364 X-Sequence-Number: 24156 Bruno Wolff III writes: > Rajesh Kumar Mallah wrote: >> create unique index ab on t(a,b); >> create index b on t(b); > If the b data is large you might get some performance increase from adding > an index on just a. You will be trading off increased work when doing updates > to handle the extra index versus faster searches in a more compact index. This is covered in some detail in TFM: http://www.postgresql.org/docs/8.2/static/indexes-multicolumn.html http://www.postgresql.org/docs/8.2/static/indexes-bitmap-scans.html regards, tom lane