Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 3DF0B9FB689 for ; Tue, 24 Jul 2007 04:57:57 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 61268-06 for ; Tue, 24 Jul 2007 04:57:51 -0300 (ADT) X-Greylist: delayed 00:47:18.46861 by SQLgrey-1.7.5 Received: from pillette.com (adsl-67-119-5-202.dsl.snfc21.pacbell.net [67.119.5.202]) by postgresql.org (Postfix) with ESMTP id DAB0E9FB61F for ; Tue, 24 Jul 2007 04:57:53 -0300 (ADT) Received: (from andrew@localhost) by pillette.com (8.11.6/8.11.6) id l6O7AUu24090; Tue, 24 Jul 2007 00:10:30 -0700 Date: Tue, 24 Jul 2007 00:10:30 -0700 From: andrew@pillette.com Message-Id: <200707240710.l6O7AUu24090@pillette.com> Subject: Re: multicolumn index column order To: valgog Cc: pgsql-performance@postgresql.org X-Originating-IP: 71.135.56.73 X-Mailer: Webmin 0.940 MIME-Version: 1.0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.758 tagged_above=0 required=5 tests=AWL=-1.013, BAYES_50=0.001, FH_HOST_EQ_PACBELL_D=1.67, RDNS_DYNAMIC=0.1 X-Spam-Level: Content-Type: text/plain Content-Transfer-Encoding: 7bit X-Archive-Number: 200707/309 X-Sequence-Number: 25993 valgog wrote .. > On Jul 23, 7:00 pm, t...@sss.pgh.pa.us (Tom Lane) wrote: > > valgog writes: > > > how to build an multicolumn index with one column order ASCENDING and > > > another column order DESCENDING? > > > > Use 8.3 ;-) > > > > In existing releases you could fake it with a custom reverse-sorting > > operator class, but it's a pain in the neck to create one. I've often gotten what I want by using a calculated index on (f1, -f2). ORDER BY will take an expression, e.g. ORDER BY f1, -f2. Simpler than a custom operator.