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

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