Re: Indexes slower when used in decending vs. ascending

>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@vigilos.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