Thread

Indexes with descending date columns

Theo Kramer <theo@flame.co.za> — 2006-03-16T20:34:51Z

Hi

I have a performance problem when traversing a table in index order with
multiple columns including a date column in date reverse order. Below
follows a simplified description of the table, the index and the
associated query

\d prcdedit
 prcdedit_prcd       | character(20)               |
 prcdedit_date       | timestamp without time zone |

Indexes:
    "prcdedit_idx" btree (prcdedit_prcd, prcdedit_date)

When invoking a query such as 

select oid, prcdedit_prcd, prcdedit_date, 'dd/mm/yyyy hh24:mi:ss') as
mydate where prcdedit_prcd > 'somevalue' order by prcdedit_prcd,
prcdedit_date desc;

the peformance is dismal.

However removing the 'desc' qualifier as follows the query flys

select oid, prcdedit_prcd, prcdedit_date, 'dd/mm/yyyy hh24:mi:ss') as
mydate where prcdedit_prcd > 'somevalue' order by prcdedit_prcd,
prcdedit_date;

PostgreSQL Version = 8.1.2

Row count on the table is > 300000

Explain is as follows for desc
 Limit  (cost=81486.35..81486.41 rows=25 width=230) (actual
time=116619.652..116619.861 rows=25 loops=1)
   ->  Sort  (cost=81486.35..82411.34 rows=369997 width=230) (actual
time=116619.646..116619.729 rows=25 loops=1)
         Sort Key: prcdedit_prcd, prcdedit_date, oid
         ->  Bitmap Heap Scan on prcdedit  (cost=4645.99..23454.94
rows=369997 width=230) (actual time=376.952..11798.834 rows=369630
loops=1)
               Recheck Cond: (prcdedit_prcd > '063266             
'::bpchar)
               ->  Bitmap Index Scan on prcdedit_idx 
(cost=0.00..4645.99 rows=369997 width=0) (actual time=366.048..366.048
rows=369630 loops=1)
                     Index Cond: (prcdedit_prcd > '063266             
'::bpchar)
 Total runtime: 116950.175 ms

and as follows when I remove the 'desc'

 Limit  (cost=0.00..2.34 rows=25 width=230) (actual time=0.082..0.535
rows=25 loops=1)
   ->  Index Scan using prcdedit_idx on prcdedit  (cost=0.00..34664.63
rows=369997 width=230) (actual time=0.075..0.405 rows=25 loops=1)
         Index Cond: (prcdedit_prcd > '063266              '::bpchar)
 Total runtime: 0.664 ms


Any assistance/advice much appreciated.

-- 
Regards
Theo