On Mon, 2007-09-10 at 12:17 +0100, Heikki Linnakangas wrote:
> Bruce Momjian wrote:
> > (Can someone time the access time for following a chain that fills an
> > entire page (the worst case) vs. having a single tuple on the page?)
> Here is some results, on my laptop.
> HEAD HOT HOT-opt HOT-pruned
> seqscan 19.9 21.1 20.1 11.5
> idxscan 27.8 31.4 30.4 13.7
> Comparing the idxscan columns, it looks like following the chain *is*
> more expensive than having to go through killed index pointers. Pruning
> clearly does help.
> Given that this test is pretty much the worst case scenario, I'm ok with
> not pruning for the purpose of keeping chains short.
I wasn't expecting that result and had accepted the counter argument.
IMHO it overturns the argument, or at least begs more test results.
Perhaps we need to compare this with a situation where we do 50% reads
and 50% writes where we try to prune aggressively on reads. There are
clearly big wins to be had in comparison with HEAD, but also the
possibility of regressions (in multiple directions) for various worst
ISTM that if we made 1 in every 16 index scans prune chains longer than
N (=16?) then we would avoid worst cases like this for almost no
additional (amortized) cost.
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not