Re: Indexes slower when used in decending vs. ascending

From: Alasdair Young <ayoung(at)vigilos(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Indexes slower when used in decending vs. ascending
Date: 2006-04-11 20:11:28
Message-ID: 1144786289.23592.4.camel@localhost.localdomain
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

On Tue, 2006-04-11 at 14:18 -0400, Tom Lane wrote:
> Alasdair Young <ayoung(at)vigilos(dot)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)

In response to

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Tom Lane 2006-04-11 20:24:32 Re: Indexes slower when used in decending vs. ascending order?
Previous Message operationsengineer1 2006-04-11 19:54:58 Re: Curses interface