Re: Indexes slower when used in decending vs. ascending

On Tue, 2006-04-11 at 14:18 -0400, Tom Lane wrote:
> Alasdair Young <ayoung@vigilos.com> writes:
> > 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.
>
> I'd bet that the problem is the "filter" on logicaldel --- is the value
> of that heavily correlated with the index ordering?  You could be
> scanning through many many rows of the index to reach the first (last)
> one with logicaldel = 'N'.
>
>             regards, tom lane


Removing the logicaldel seems to give the same results.

(The archives seem to indicate the two queries should take roughly the
same amount of time)


Any other ideas?


- alasdair

The slow version:
vigprem=> explain analyze SELECT * FROM log WHERE clientkey in
('000000004000000000010000000001')  AND premiseskey in
('000000004000000000030000000001') /*VIG_GEN*/  ORDER BY clientkey desc,
premiseskey desc, logtime desc, logkey desc LIMIT 20 OFFSET 0;

QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..74.84 rows=20 width=548) (actual
time=19799.54..19799.95 rows=20 loops=1)
   ->  Index Scan Backward using logtime_index on log
(cost=0.00..6191056.91 rows=1654586 width=548) (actual
time=19799.54..19799.92 rows=21 loops=1)
         Index Cond: ((clientkey =
'000000004000000000010000000001'::bpchar) AND (premiseskey =
'000000004000000000030000000001'::bpchar))
 Total runtime: 19800.03 msec
(4 rows)




The fast version:
vigprem=> explain analyze SELECT * FROM log WHERE clientkey in
('000000004000000000010000000001')  AND premiseskey in
('000000004000000000030000000001') /*VIG_GEN*/  ORDER BY clientkey,
premiseskey, logtime, logkey LIMIT 20 OFFSET 0;
QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..74.84 rows=20 width=548) (actual time=0.35..0.75
rows=20 loops=1)
   ->  Index Scan using logtime_index on log  (cost=0.00..6191056.91
rows=1654586 width=548) (actual time=0.35..0.72 rows=21 loops=1)
         Index Cond: ((clientkey =
'000000004000000000010000000001'::bpchar) AND (premiseskey =
'000000004000000000030000000001'::bpchar))
 Total runtime: 0.80 msec
(4 rows)