To: Alanoly Andrews <alanolya@invera.com>
cc: "'pgsql-admin@postgresql.org'" <pgsql-admin@postgresql.org>
Subject: Re: Bidirectional index traversal 
In-reply-to: <09B23E7BF70425478C1330D893A722C602FEC01A2D@MailSVR.invera.com> 
References: <09B23E7BF70425478C1330D893A722C602FEC01A2C@MailSVR.invera.com>
	<12592.1284648990@sss.pgh.pa.us>
	<09B23E7BF70425478C1330D893A722C602FEC01A2D@MailSVR.invera.com>
Comments: In-reply-to Alanoly Andrews <alanolya@invera.com>
	message dated "Thu, 16 Sep 2010 11:02:07 -0400"
Date: Thu, 16 Sep 2010 11:14:35 -0400
Message-ID: <13510.1284650075@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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