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)
