From: Markus Schaber <schabi@logix-tt.com>
To: pgsql-performance@postgresql.org
Subject: Re: Indexes with descending date columns
Date: Tue, 11 Apr 2006 19:59:39 +0200
Message-ID: <443BEE8B.4030301@logix-tt.com> (raw)
In-Reply-To: <200604090326.k393Qpm08170@candle.pha.pa.us>
References: <200604090326.k393Qpm08170@candle.pha.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: schabi@logix-tt.com
Subject: Re: Indexes with descending date columns
In-Reply-To: <443BEE8B.4030301@logix-tt.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox