From: Tom Lane <tgl@sss.pgh.pa.us>
To: Alanoly Andrews <alanolya@invera.com>
Cc: 'pgsql-admin@postgresql.org' <pgsql-admin@postgresql.org>
Subject: Re: Bidirectional index traversal
Date: Thu, 16 Sep 2010 11:14:35 -0400
Message-ID: <13510.1284650075@sss.pgh.pa.us> (raw)
In-Reply-To: <09B23E7BF70425478C1330D893A722C602FEC01A2D@MailSVR.invera.com>
References: <09B23E7BF70425478C1330D893A722C602FEC01A2C@MailSVR.invera.com>
<12592.1284648990@sss.pgh.pa.us>
<09B23E7BF70425478C1330D893A722C602FEC01A2D@MailSVR.invera.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, alanolya@invera.com
Subject: Re: Bidirectional index traversal
In-Reply-To: <13510.1284650075@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox