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)
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 |