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:40:52
Message-ID: 1144788052.23922.5.camel@localhost.localdomain (view raw or flat)
Thread:
Lists: pgsql-novice
>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.

vigprem=# vacuum verbose log;
INFO:  --Relation public.log--
INFO:  Pages 82731: Changed 0, Empty 0; Tup 1654586: Vac 0, Keep 0,
UnUsed 0.
        Total CPU 0.70s/0.26u sec elapsed 10.63 sec.
VACUUM

Doesn't mean a lot to me. (I've only just starting using postgres,
coming from an Oracle background) I'm going to go try to work out what
possible post-index filter conditions could be in place...

Thanks for your help. If you have any further ideas, please post them.
I'm convinced I can get this to go faster :)

- alasdair

On Tue, 2006-04-11 at 16:24 -0400, Tom Lane wrote:
> 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: Vishal Kashyap Date: 2006-04-11 20:53:12
Subject: How to query for Interval
Previous:From: Bruno Wolff IIIDate: 2006-04-11 20:29:33
Subject: Re: advice on setting up schema sought

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