Re: Indexes with descending date columns

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. +