Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 0F7C7475D99 for ; Thu, 3 Oct 2002 23:03:40 -0400 (EDT) Received: from wolff.to (wolff.to [66.93.197.35]) by postgresql.org (Postfix) with SMTP id 3DF34475AA1 for ; Thu, 3 Oct 2002 23:03:39 -0400 (EDT) Received: (qmail 4007 invoked by uid 500); 4 Oct 2002 03:12:11 -0000 Date: Thu, 3 Oct 2002 22:12:11 -0500 From: Bruno Wolff III To: Josh Berkus Cc: Vincent-Olivier Arsenault , pgsql-sql@postgresql.org Subject: Re: order by x DESC, y ASC indexing problem Message-ID: <20021004031211.GB3484@wolff.to> Mail-Followup-To: Josh Berkus , Vincent-Olivier Arsenault , pgsql-sql@postgresql.org References: <3D9CF7EC.8080102@up4c.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.3.25i X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200210/48 X-Sequence-Number: 9766 On Thu, Oct 03, 2002 at 19:30:59 -0700, Josh Berkus wrote: > Vincent, > > > SELECT * FROM TABLE1 ORDER BY X DESC, Y ASC; > > > > (X is a date and Y a varchar) > > > > What would that index be? > > > > Is there a function I can use, to invert x (the date), so that I can > > make a query / index set like : > > PostgreSQL's sorting mechanism can use an index either ascending or > descending; it does not make a difference. Note this is referring to a multicolumn index. To use all columns of a multicolumn index the sort directions all need to be the same. However you are right to point out that this may not be a big deal for many applications where using the index on the first column gets the majority of the speed up.