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)