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
