From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Alanoly Andrews <alanolya(at)invera(dot)com> |
Cc: | "'pgsql-admin(at)postgresql(dot)org'" <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: Bidirectional index traversal |
Date: | 2010-09-16 15:14:35 |
Message-ID: | 13510.1284650075@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Aras Angelo | 2010-09-16 22:54:47 | incrementing updates and locks |
Previous Message | Alanoly Andrews | 2010-09-16 15:02:07 | Re: Bidirectional index traversal |