Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 229E59FA294 for ; Sat, 30 Dec 2006 13:14:32 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 69120-09 for ; Sat, 30 Dec 2006 13:14:27 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from wolff.to (wolff.to [66.93.197.194]) by postgresql.org (Postfix) with SMTP id 9D6449FA217 for ; Sat, 30 Dec 2006 13:14:27 -0400 (AST) Received: (qmail 3555 invoked by uid 500); 30 Dec 2006 17:15:28 -0000 Date: Sat, 30 Dec 2006 11:15:28 -0600 From: Bruno Wolff III To: Rajesh Kumar Mallah Cc: Lista Postgres Subject: Re: Is unique index always used if query is on first constituent column ? Message-ID: <20061230171528.GA2803@wolff.to> Mail-Followup-To: Bruno Wolff III , Rajesh Kumar Mallah , Lista Postgres References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.4.2.1i X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/363 X-Sequence-Number: 24155 On Sat, Dec 30, 2006 at 16:08:39 +0530, Rajesh Kumar Mallah wrote: > Hi, > > suppose we have 2 columns a and b . we need to query by a and sometimes > by b for data consistency UNIQUE(a,b) also needs to be there. we create > indexes below: > > create unique index ab on t(a,b); > create index b on t(b); > > it has been observed that index ab is used while querying > for a , can we always avoid creating the extra index on a ie, > create index a on t(a); (does other databases also behave > similar?) 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.