Message-ID: <443BEE8B.4030301@logix-tt.com>
Date: Tue, 11 Apr 2006 19:59:39 +0200
From: Markus Schaber <schabi@logix-tt.com>
Organization: Logical Tracking and Tracing International AG, Switzerland
User-Agent: Debian Thunderbird 1.0.7 (X11/20051017)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Indexes with descending date columns
References: <200604090326.k393Qpm08170@candle.pha.pa.us>
In-Reply-To: <200604090326.k393Qpm08170@candle.pha.pa.us>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Hi, Bruce,

Bruce Momjian wrote:

>>Ahh. There's a hack to do that by defining a new opclass that reverses <
>>and >, and then doing ORDER BY project_id, id, date USING new_opclass.
>>
>>I think there's a TODO about this, but I'm not sure...
> 
> Yes, and updated:
> 
> 	* Allow the creation of indexes with mixed ascending/descending
> 	  specifiers
> 	
> 	  This is possible now by creating an operator class with reversed sort
> 	  operators.  One complexity is that NULLs would then appear at the start
> 	  of the result set, and this might affect certain sort types, like
> 	  merge join.

I think it would be better to allow "index zig-zag scans" for
multi-column index.[1]

So it traverses in a given order on the higher order column, and the sub
trees for each specific high order value is traversed in reversed order.
From my knowledge at least of BTrees, and given correct commutator
definitions, this should be not so complicated to implement.[2]

This would allow the query planner to use the same index for arbitrary
ASC/DESC combinations of the given columns.


Just a thought,
Markus


[1] It may make sense to implement the mixed specifiers on indices as
well, to allow CLUSTERing on mixed search order.

[2] But I admit that I currently don't have enough knowledge in
PostgreSQL index scan internals to know whether it really is easy to
implement.


-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org