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 43B95D1BAD5 for ; Fri, 23 Apr 2004 13:41:23 -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 85136-06 for ; Fri, 23 Apr 2004 13:41:21 -0300 (ADT) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id 039F7D1BA98 for ; Fri, 23 Apr 2004 13:41:21 -0300 (ADT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 505DD35463; Fri, 23 Apr 2004 09:41:21 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 4F2BF353C5; Fri, 23 Apr 2004 09:41:21 -0700 (PDT) Date: Fri, 23 Apr 2004 09:41:21 -0700 (PDT) From: Stephan Szabo To: Antal Attila Cc: pgsql-sql@postgresql.org Subject: Re: Multi ordered select and indexing In-Reply-To: <000301c4293f$ebf6b930$0b02010a@atesz> Message-ID: <20040423093937.B17459@megazone.bigpanda.com> References: <000301c4293f$ebf6b930$0b02010a@atesz> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII 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/261 X-Sequence-Number: 17636 On Fri, 23 Apr 2004, Antal Attila wrote: > Hi! > > We have a complex problematic area. What is the simplest solution for > the next query type: > > SELECT * FROM tablename ORDER BY col1 ASC, col2 DESC; > > 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"? Yes. You should be able to make an opclass where the operators are the standard operators but in a different order which should get used when doing DESC. I think there should be examples in the archives.