Backwards index scan

From: "Carlos Oliva" <carlos(at)pbsinet(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Backwards index scan
Date: 2006-06-06 14:59:30
Message-ID: 200606061459.KAA06172@pbsi.pbsinet.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Are there any configurations/flags that we should re-set for the database (v
7.4.x) in order to enable a backwards scan on an index? We are trying to
query a table in descending order. We added an index that we were hoping
would be scanned backwards but EXPLAIN never indicates that the optimizer
will carry out a backwards scan on the index that we added to the table.
EXPLAIN indicates that the optimizer will always use a sequential scan if we
order the query in descending order.

OUR TESTS

We are conducting a simple test to asses if the optimizer ever uses the
index. The table has several columns and the select statement is as
follows: select * from ord0007 order by prtnbr, ordschdte desc. The index
that we added is "ord0007_k" btree (prtnbr, ordschdte). Prtnbr is
numeric(10,0) not null, and ordschdte is date.

We find that the optimizer uses the index for the query if we set
enable_sort to off and the query uses ordschdte in ascending order as
follows: select * from ord0007 order by prtnbr, ordschdte. For this query,
EXPLAIN returns the following output:

QUERY PLAN

----------------------------------------------------------------------------
----

Index Scan using ord0007_k on ord0007 (cost=0.00..426.03 rows=232
width=1816)

(1 row)

However the optimizer uses a sequential scan if we order by a descending
ordschdte as follows: select * from ord0007 order by prtnbr, ordschdte desc.
For this query, whether we set the enable_sort to on or off, EXPLAIN returns
the following output:

QUERY PLAN

--------------------------------------------------------------------

Sort (cost=100000155.44..100000156.02 rows=232 width=1816)

Sort Key: prtnbr, ordschdte

-> Seq Scan on ord0007 (cost=0.00..146.32 rows=232 width=1816)

(3 rows)

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alan Hodgson 2006-06-06 15:04:45 Re: Backwards index scan
Previous Message Arnaud Lesauvage 2006-06-06 14:49:43 levenshtein contrib installation