Re: Bidirectional index traversal

Alanoly Andrews <alanolya(at)invera(dot)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