Re: Bidirectional index traversal

Поиск
Список
Период
Сортировка
От Tom Lane
Тема Re: Bidirectional index traversal
Дата
Msg-id 13510.1284650075@sss.pgh.pa.us
обсуждение исходный текст
Ответ на Re: Bidirectional index traversal  (Alanoly Andrews <alanolya@invera.com>)
Список pgsql-admin
Alanoly Andrews <alanolya@invera.com> writes:
> To expand on that question:
> Suppose I have a table with the following schema:
>      tab1(col1 decimal(3,0), col2 char(3)).
> There is an index defined on it as : create index tab1ind1 on tab1(col1)

> Now, if I have a query as: "select * from tab1 order by col1", I
> expect the Optimizer to use the index tab1ind1. But if I have a query
> like: "select * from tab1 order by col1 desc", does the Postgres
> Optimizer use the same index as above (but in the reverse direction)

Yes, it will, as you could easily find by reading the manual:
http://www.postgresql.org/docs/8.4/static/indexes-ordering.html
or by experimentation:

regression=# create table tab1(col1 decimal(3,0), col2 char(3));
CREATE TABLE
regression=# create index tab1ind1 on tab1(col1);
CREATE INDEX
regression=# explain select * from tab1 order by col1;
                                QUERY PLAN
--------------------------------------------------------------------------
 Index Scan using tab1ind1 on tab1  (cost=0.00..70.20 rows=1730 width=17)
(1 row)

regression=# explain select * from tab1 order by col1 desc;
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Index Scan Backward using tab1ind1 on tab1  (cost=0.00..70.20 rows=1730 width=17)
(1 row)

(Now, whether the optimizer will prefer an index over seqscan-and-sort
depends on a lot of factors.  But backwards scan isn't a problem.)

            regards, tom lane

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

Предыдущее
От: Alanoly Andrews
Дата:
Сообщение: Re: Bidirectional index traversal
Следующее
От: Aras Angelo
Дата:
Сообщение: incrementing updates and locks