Re: Indexes with descending date columns

Поиск
Список
Период
Сортировка
От Bruce Momjian
Тема Re: Indexes with descending date columns
Дата
Msg-id 200604090326.k393Qpm08170@candle.pha.pa.us
обсуждение исходный текст
Ответ на Re: Indexes with descending date columns  ("Jim C. Nasby" <jnasby@pervasive.com>)
Ответы Re: Indexes with descending date columns  (Markus Schaber <schabi@logix-tt.com>)
Список pgsql-performance
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. +

В списке pgsql-performance по дате отправления:

Предыдущее
От: Gavin Hamill
Дата:
Сообщение: Re: pg 8.1.3, AIX, huge box, painfully slow.
Следующее
От: Brendan Duddridge
Дата:
Сообщение: OT: Data structure design question: How do they count so fast?