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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Bruno Wolff III | 2006-04-11 20:29:33 | Re: advice on setting up schema sought |
Previous Message | Alasdair Young | 2006-04-11 20:11:28 | Re: Indexes slower when used in decending vs. ascending |