Re: Fwd: Index on table when using DESC clause

Поиск
Список
Период
Сортировка
От Yves Vindevogel
Тема Re: Fwd: Index on table when using DESC clause
Дата
Msg-id adcc4892d7715a10069cfd0c62028a9b@implements.be
обсуждение исходный текст
Ответ на Fwd: Index on table when using DESC clause  (Yves Vindevogel <yves.vindevogel@implements.be>)
Список pgsql-performance
God I love the sheer brilliance of that minus trick   :-))

Tnx a lot


BTW: Are there any plans to change this kind of indexing behaviour ?

It makes no sense at all, and, it makes databases slow when you don't
know about this.


On 23 May 2005, at 23:15, Andrew Lazarus wrote:


<excerpt>What you are trying to do makes perfect sense, but for some
strange reason, Postgres doesn't like to do it. In a PG index, all of
the columns are always stored in ascending order. So if you have an
ORDER BY that is all ASC, it can start from the start of the index.
And if you have an ORDER BY that is all DESC, it can start from the
end. But if you want one column (like pages) DESC and the other
(description) ASC, then PG will use a sequential scan or something
else slow and stupid.


Other RDBMS know how to do this, by supporting the


CREATE INDEX foo ON bar(baz DESC, baz2 ASC)


syntax. For PG, you need to fool it with an index on an expression, or
a custom operator, or something. I once just made an extra column and
used a trigger to be sure that -myvariable was in it at all times
(-pages for you) and then made my index on the extra column. Since the
extra column in ASC order is the same as the original in DESC, it
works.

<<andrew.vcf>

</excerpt>Met vriendelijke groeten,

Bien à vous,

Kind regards,


<bold>Yves Vindevogel</bold>

<bold>Implements</bold>

<smaller>

</smaller>God I love the sheer brilliance of that minus trick   :-))
Tnx a lot

BTW: Are there any plans to change this kind of indexing behaviour ?
It makes no sense at all, and, it makes databases slow when you don't
know about this.

On 23 May 2005, at 23:15, Andrew Lazarus wrote:

> What you are trying to do makes perfect sense, but for some strange
> reason, Postgres doesn't like to do it. In a PG index, all of the
> columns are always stored in ascending order. So if you have an ORDER
> BY that is all ASC, it can start from the start of the index. And if
> you have an ORDER BY that is all DESC, it can start from the end. But
> if you want one column (like pages) DESC and the other (description)
> ASC, then PG will use a sequential scan or something else slow and
> stupid.
>
> Other RDBMS know how to do this, by supporting the
>
> CREATE INDEX foo ON bar(baz DESC, baz2 ASC)
>
> syntax. For PG, you need to fool it with an index on an expression, or
> a custom operator, or something. I once just made an extra column and
> used a trigger to be sure that -myvariable was in it at all times
> (-pages for you) and then made my index on the extra column. Since the
> extra column in ASC order is the same as the original in DESC, it
> works.
> <andrew.vcf>
Met vriendelijke groeten,
Bien à vous,
Kind regards,

Yves Vindevogel
Implements

<smaller>


Mail: yves.vindevogel@implements.be  - Mobile: +32 (478) 80 82 91


Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76


Web: http://www.implements.be

<italic><x-tad-smaller>

First they ignore you.  Then they laugh at you.  Then they fight you.
Then you win.

Mahatma Ghandi.</x-tad-smaller></italic></smaller>



Mail: yves.vindevogel@implements.be  - Mobile: +32 (478) 80 82 91

Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76

Web: http://www.implements.be

First they ignore you.  Then they laugh at you.  Then they fight you.
Then you win.
Mahatma Ghandi.

Вложения

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

Предыдущее
От: "David Parker"
Дата:
Сообщение: seqential vs random io
Следующее
От: John A Meinel
Дата:
Сообщение: Re: seqential vs random io