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

Re: Indexes slower when used in decending vs. ascending order?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Alasdair Young <ayoung(at)vigilos(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Indexes slower when used in decending vs. ascending order?
Date: 2006-04-11 20:24:32
Message-ID: 10269.1144787072@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
Alasdair Young <ayoung(at)vigilos(dot)com> writes:
> On Tue, 2006-04-11 at 14:18 -0400, Tom Lane wrote:
>> I'd bet that the problem is the "filter" on logicaldel --- is the value
>> of that heavily correlated with the index ordering?

> Removing the logicaldel seems to give the same results. 

Hmm.  Maybe a whole lot of recently-dead row versions near the upper end
of the index range?

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

Yeah, the scan speed should be essentially the same in either direction,
I'd think.  I have to suppose that the backwards scan is fetching a
whole lot of rows that it ends up not returning.  Offhand the only
reasons I can think of for that are that the rows are not visible
according to the current MVCC snapshot, or because of a post-index
filter condition.

>  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)

That's pretty spectacular.  There is no way that Postgres is only
fetching one row per second; it's got to be discarding a whole lot
of rows under the hood.  It'd be useful to run VACUUM VERBOSE on
this table and see what it's got to say.

			regards, tom lane

In response to

Responses

pgsql-novice by date

Next:From: Bruno Wolff IIIDate: 2006-04-11 20:29:33
Subject: Re: advice on setting up schema sought
Previous:From: Alasdair YoungDate: 2006-04-11 20:11:28
Subject: Re: Indexes slower when used in decending vs. ascending

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