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