Indexes slower when used in decending vs. ascending order?

From: Alasdair Young <ayoung(at)vigilos(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Indexes slower when used in decending vs. ascending order?
Date: 2006-04-11 18:06:16
Message-ID: 1144778777.23012.2.camel@localhost.localdomain
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

I was under the impression that Postgres did not need explicit
descending order indexes created and that standard indexes would work
fine.

I am noticing a significant speed difference between the following two
queries (the one using "ORDER BY clientkey desc, premiseskey desc,
logtime desc, logkey desc" takes 19 seconds to execute, versus almost
immediate execution of the "ORDER BY clientkey, premiseskey, logtime,
logkey") and was wondering if there was anything I could do to make
execution any faster.

Any thoughts?

Queries follow:

The slow one:

vigprem=> explain SELECT * FROM log WHERE clientkey in
('000000004000000000010000000001') AND premiseskey in
('000000004000000000030000000001') and logicaldel = 'N' /*VIG_GEN*/
ORDER BY clientkey desc, premiseskey desc, logtime desc, logkey desc
LIMIT 20 OFFSET 0;
QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..74.89 rows=20 width=548)
-> Index Scan Backward using logtime_index on log
(cost=0.00..6195163.59 rows=1654578 width=548)
Index Cond: ((clientkey =
'000000004000000000010000000001'::bpchar) AND (premiseskey =
'000000004000000000030000000001'::bpchar))
Filter: (logicaldel = 'N'::bpchar)
(4 rows)

The fast one:

vigprem=> explain SELECT * FROM log WHERE clientkey in
('000000004000000000010000000001') AND premiseskey in
('000000004000000000030000000001') and logicaldel = 'N' /*VIG_GEN*/
ORDER BY clientkey, premiseskey, logtime, logkey LIMIT 20 OFFSET 0;
QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..74.89 rows=20 width=548)
-> Index Scan using logtime_index on log (cost=0.00..6195163.59
rows=1654578 width=548)
Index Cond: ((clientkey =
'000000004000000000010000000001'::bpchar) AND (premiseskey =
'000000004000000000030000000001'::bpchar))
Filter: (logicaldel = 'N'::bpchar)
(4 rows)

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Tom Lane 2006-04-11 18:18:07 Re: Indexes slower when used in decending vs. ascending order?
Previous Message Lan Barnes 2006-04-11 18:01:46 Re: SELECT of a glob