pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedBidirectional index traversal
4+ messages / 2 participants
[nested] [flat]
* Bidirectional index traversal
@ 2010-09-16 14:39 Alanoly Andrews <alanolya@invera.com>
2010-09-16 14:56 ` Re: Bidirectional index traversal Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Alanoly Andrews @ 2010-09-16 14:39 UTC (permalink / raw)
To: pgsql-admin
Hello,
I'd like to know whether Postgres (8.4) supports bidirectional traversal of indexes.
Thanks.
Alanoly.
****************************************************
This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately.
Ce courriel est confidentiel et protégé. L'expéditeur ne renonce pas aux droits et obligations qui s'y rapportent. Toute diffusion, utilisation ou copie de ce message ou des renseignements qu'il contient par une personne autre que le (les) destinataire(s) désigné(s) est interdite. Si vous recevez ce courriel par erreur, veuillez m'en aviser immédiatement, par retour de courriel ou par un autre moyen.
****************************************************
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Bidirectional index traversal
2010-09-16 14:39 Bidirectional index traversal Alanoly Andrews <alanolya@invera.com>
@ 2010-09-16 14:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-09-16 15:02 ` Re: Bidirectional index traversal Alanoly Andrews <alanolya@invera.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2010-09-16 14:56 UTC (permalink / raw)
To: Alanoly Andrews <alanolya@invera.com>; +Cc: pgsql-admin
Alanoly Andrews <alanolya@invera.com> writes:
> I'd like to know whether Postgres (8.4) supports bidirectional traversal of indexes.
If you defined exactly what you meant by that, you might get useful
answers. There are some features in there that might be what you mean,
or then again maybe not.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Bidirectional index traversal
2010-09-16 14:39 Bidirectional index traversal Alanoly Andrews <alanolya@invera.com>
2010-09-16 14:56 ` Re: Bidirectional index traversal Tom Lane <tgl@sss.pgh.pa.us>
@ 2010-09-16 15:02 ` Alanoly Andrews <alanolya@invera.com>
2010-09-16 15:14 ` Re: Bidirectional index traversal Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Alanoly Andrews @ 2010-09-16 15:02 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
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) or does it need to a full table scan and then sort values in col1 in the descending order? If the former, then there is bidirectional index traversal. That example was for a numeric field. What about character, date and timestamp fields? Does bidirectional index traversal exist, or do we need to create a second index to handle such cases?
Alanoly.
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, September 16, 2010 10:57 AM
To: Alanoly Andrews
Cc: 'pgsql-admin@postgresql.org'
Subject: Re: [ADMIN] Bidirectional index traversal
Alanoly Andrews <alanolya@invera.com> writes:
> I'd like to know whether Postgres (8.4) supports bidirectional traversal of indexes.
If you defined exactly what you meant by that, you might get useful
answers. There are some features in there that might be what you mean,
or then again maybe not.
regards, tom lane
****************************************************
This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately.
Ce courriel est confidentiel et protégé. L'expéditeur ne renonce pas aux droits et obligations qui s'y rapportent. Toute diffusion, utilisation ou copie de ce message ou des renseignements qu'il contient par une personne autre que le (les) destinataire(s) désigné(s) est interdite. Si vous recevez ce courriel par erreur, veuillez m'en aviser immédiatement, par retour de courriel ou par un autre moyen.
****************************************************
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Bidirectional index traversal
2010-09-16 14:39 Bidirectional index traversal Alanoly Andrews <alanolya@invera.com>
2010-09-16 14:56 ` Re: Bidirectional index traversal Tom Lane <tgl@sss.pgh.pa.us>
2010-09-16 15:02 ` Re: Bidirectional index traversal Alanoly Andrews <alanolya@invera.com>
@ 2010-09-16 15:14 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2010-09-16 15:14 UTC (permalink / raw)
To: Alanoly Andrews <alanolya@invera.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2010-09-16 15:14 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-09-16 14:39 Bidirectional index traversal Alanoly Andrews <alanolya@invera.com>
2010-09-16 14:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-09-16 15:02 ` Alanoly Andrews <alanolya@invera.com>
2010-09-16 15:14 ` Tom Lane <tgl@sss.pgh.pa.us>
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