Alanoly Andrews <alanolya@xxxxxxxxxx> 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 -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
