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)
Список: pgsql-performance

Скрыть дерево обсуждения

Fwd: Index on table when using DESC clause  (Yves Vindevogel, )
 Re: Fwd: Index on table when using DESC clause  ("Steinar H. Gunderson", )
  Re: Fwd: Index on table when using DESC clause  (Yves Vindevogel, )
   Re: Fwd: Index on table when using DESC clause  (Oleg Bartunov, )
 Re: Fwd: Index on table when using DESC clause  (Andrew Lazarus, )
 Re: Fwd: Index on table when using DESC clause  (Yves Vindevogel, )

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:   - 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:   - 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 по дате сообщения:

От: "Anjan Dave"
Дата:
Сообщение: Re: seqential vs random io
От: mark durrant
Дата:
Сообщение: Select performance vs. mssql