X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id BBC89D1B4B6 for ; Fri, 23 Apr 2004 14:33:14 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 05702-04 for ; Fri, 23 Apr 2004 14:33:13 -0300 (ADT) Received: from wolff.to (wolff.to [66.93.249.74]) by svr1.postgresql.org (Postfix) with SMTP id 11351D1DB99 for ; Fri, 23 Apr 2004 14:33:11 -0300 (ADT) Received: (qmail 8015 invoked by uid 500); 23 Apr 2004 17:37:16 -0000 Date: Fri, 23 Apr 2004 12:37:16 -0500 From: Bruno Wolff III To: Antal Attila Cc: pgsql-sql@postgresql.org Subject: Re: Multi ordered select and indexing Message-ID: <20040423173716.GA7767@wolff.to> Mail-Followup-To: Antal Attila , pgsql-sql@postgresql.org References: <000301c4293f$ebf6b930$0b02010a@atesz> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <000301c4293f$ebf6b930$0b02010a@atesz> User-Agent: Mutt/1.5.6i X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200404/262 X-Sequence-Number: 17637 On Fri, Apr 23, 2004 at 16:33:14 +0200, Antal Attila wrote: > > In our experience, postgres cannot use a multi-colum index on (col1, > col2) in this situation, becouse there are different directions after > ORDER BY. Is custom operator class the easiest solution, which can solve > the reverse indexing on col2? Our problem with this solution, is that we > have to replace "DESC" with "USING myoperator". Is it possible, that > postgres can recognize "myoperator" without replacing "DESC"? Another option you might have is using functional indexes. If you are using 7.4.x or greater and one of the columns has a reasonable - operator (pretty much this is the numeric types) then you can have an index on (col1, (-col2)) and then order by col1, -col2. The main advantage is that this is simpler than making a new opclass. If you have already gone to that trouble it may be better to stick with it.