From: Theo Kramer <theo@flame.co.za>
To: pgsql-performance@postgresql.org
Subject: Indexes with descending date columns
Date: Thu, 16 Mar 2006 22:34:51 +0200
Message-ID: <1142541290.2812.45.camel@josh> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: theo@flame.co.za
Subject: Re: Indexes with descending date columns
In-Reply-To: <1142541290.2812.45.camel@josh>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox