From: Bruce Momjian <pgman@candle.pha.pa.us>
To: Jim C. Nasby <jnasby@pervasive.com>
Cc: Theo Kramer <theo@flame.co.za>
Cc: pgsql-performance@postgresql.org
Subject: Re: Indexes with descending date columns
Date: Sat, 8 Apr 2006 23:26:51 -0400 (EDT)
Message-ID: <200604090326.k393Qpm08170@candle.pha.pa.us> (raw)
In-Reply-To: <20060331155554.GO49405@pervasive.com>
Jim C. Nasby wrote:
> On Wed, Mar 29, 2006 at 12:52:31PM +0200, Theo Kramer wrote:
> > On Fri, 2006-03-24 at 12:21, Jim C. Nasby wrote:
> > > On Thu, Mar 23, 2006 at 01:09:49PM +0200, Theo Kramer wrote:
> > > > ii If no to i, is it feasible to extend PostgreSQL to allow traversing
> > > > an index in column descending and column ascending order - assuming
> > > > an order by on more than one column with column order not
> > > > in the same direction and indexes existing? ... if that makes sense.
> > >
> > > Yes.
> > >
> > > stats=# explain select * from email_contrib order by project_id desc, id desc, date desc limit 10;
> > > QUERY PLAN
> > > ------------------------------------------------------------------------------------------------------------------------
> > > Limit (cost=0.00..31.76 rows=10 width=24)
> > > -> Index Scan Backward using email_contrib_pkey on email_contrib (cost=0.00..427716532.18 rows=134656656 width=24)
> > > (2 rows)
> >
> > Not quite what I mean - redo the above as follows and then see what
> > explain returns
> >
> > explain select * from email_contrib order by project_id, id, date desc
> > limit 10;
>
> 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.
--
Bruce Momjian http://candle.pha.pa.us
+ If your life is a hard drive, Christ can be your backup. +
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: pgman@candle.pha.pa.us, jnasby@pervasive.com, theo@flame.co.za
Subject: Re: Indexes with descending date columns
In-Reply-To: <200604090326.k393Qpm08170@candle.pha.pa.us>
* 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