Indexes with descending date columns

From: Theo Kramer <theo(at)flame(dot)co(dot)za>
To: pgsql-performance(at)postgresql(dot)org
Subject: Indexes with descending date columns
Date: 2006-03-16 20:34:51
Message-ID: 1142541290.2812.45.camel@josh
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Browse pgsql-performance by date

  From Date Subject
Next Message Ron Peacetree 2006-03-16 20:41:54 Re: 1 TB of memory
Previous Message Simon Riggs 2006-03-16 20:00:19 Re: BETWEEN optimizer problems with single-value