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 cases. 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. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---------------------------(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 match