Re: Indexes with descending date columns

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