Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group