X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 881FE9DC9DB for ; Fri, 24 Mar 2006 00:59:23 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 12177-04 for ; Fri, 24 Mar 2006 00:59:22 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 407AC9DC803 for ; Fri, 24 Mar 2006 00:59:20 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k2O4xDDH027699; Thu, 23 Mar 2006 23:59:13 -0500 (EST) To: theo@flame.co.za cc: pgsql-performance@postgresql.org Subject: Re: Indexes with descending date columns In-reply-to: <1143174749.2820.2.camel@josh> References: <200603170625.k2H6PJ926955@pillette.com> <1143112188.2810.27.camel@theo-laptop.int.coza.net.za> <20060323141622.GC4881@surnet.cl> <1143174749.2820.2.camel@josh> Comments: In-reply-to Theo Kramer message dated "Fri, 24 Mar 2006 06:32:29 +0200" Date: Thu, 23 Mar 2006 23:59:13 -0500 Message-ID: <27697.1143176353@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.114 required=5 tests=[AWL=0.114] X-Spam-Score: 0.114 X-Spam-Level: X-Archive-Number: 200603/479 X-Sequence-Number: 17916 Theo Kramer writes: > If so, I would appreciate any pointers on where to start on this - > already fumbling my way through Interfacing Extensions To Indexes in the > manual... Search the PG list archives for discussions of reverse-sort opclasses. It's really pretty trivial, once you've created a negated btree comparison function for the datatype. This is the sort of thing that we are almost but not quite ready to put into the standard distribution. The issues that are bugging me have to do with whether NULLs sort low or high --- right now, if you make a reverse-sort opclass, it will effectively sort NULLs low instead of high, and that has some unpleasant consequences because the rest of the system isn't prepared for variance on the point (in particular I'm afraid this could break mergejoins). I'd like to see us make "NULLs low" vs "NULLs high" be a defined property of opclasses, and deal with the fallout from that, and then we could put reverse-sort opclasses for all the standard datatypes into the regular distribution. regards, tom lane