Re: Order-by and indexes
| От | Odd Hogstad |
|---|---|
| Тема | Re: Order-by and indexes |
| Дата | |
| Msg-id | BANLkTi=4yJV9Y=nrQ6g9WvdH0mRa7vvwQA@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Order-by and indexes ("Jean-Yves F. Barbier" <12ukwn@gmail.com>) |
| Ответы |
Re: Order-by and indexes
|
| Список | pgsql-novice |
> SELECT * FROM "data" WHERE "data"."fk" = 238496 ORDER BY "data"."id" DESC
From the docs:
By default, B-tree indexes store their entries in ascending order with nulls last. This means that a forward scan of an index on column x produces output satisfying ORDER BY x (or more verbosely, ORDER BY x ASC NULLS LAST). The index can also be scanned backward, producing output satisfying ORDER BY x DESC (or more verbosely, ORDER BY x DESC NULLS FIRST, since NULLS FIRST is the default for ORDER BY DESC).
Doesn't this mean that when I'm not using the order by clause, and it uses a Index Scan, I will always get the latest value in return? Also I don't understand why the order by query is scanning backwards, when the record I want is in the other end?
Thanks!
Odd-R.
> LIMIT 1Unfortunately (and AFAIK), you don't have any other solution as you want the
>
> This query is quite slow. If I do a explain on it, it seems that it uses an
> Index Scan Backward.
>
> If I omit the order by on the query:
>
> SELECT * FROM "data" WHERE "data"."fk" = 238496 LIMIT 1
>
> It is very fast. And the explain says that it uses Index scan. This is also
> very fast if there aren't any matches. But I've read that I'm not guaranteed
> to get the correct match If I do not use a order by, postgres just returns
> its fastest possible match. Is this right? But will not the fastest possible
> match always be the first match in the index? Is there another way to make
> the order by query go faster?
*latest* row; may be often clustering this table in this order would help a
bit.
Perhaps creating fragmented indexes could also help (1 >= data.fk < 50001, and so on)
From the docs:
By default, B-tree indexes store their entries in ascending order with nulls last. This means that a forward scan of an index on column x produces output satisfying ORDER BY x (or more verbosely, ORDER BY x ASC NULLS LAST). The index can also be scanned backward, producing output satisfying ORDER BY x DESC (or more verbosely, ORDER BY x DESC NULLS FIRST, since NULLS FIRST is the default for ORDER BY DESC).
Doesn't this mean that when I'm not using the order by clause, and it uses a Index Scan, I will always get the latest value in return? Also I don't understand why the order by query is scanning backwards, when the record I want is in the other end?
Thanks!
Odd-R.
В списке pgsql-novice по дате отправления: