On 9/10/07, Simon Riggs <simon@2ndquadrant.com> wrote: 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.
Yes, I go with Simon. I am also surprised that HOT-pruned did
so well in this setup. I always thought that HOT would do well
in update-intensive scenarios, but from the results it seems that
HOT is also doing well for read-mostly queries.
In this particular example, the first SELECT after the 250 UPDATEs
would have pruned all the dead tuples and reduced HOT chain
to a single tuple. Hence the total time for subsequent SELECTs improved
tremendously.
Thanks,
Pavan
--
Pavan Deolasee
EnterpriseDB
http://www.enterprisedb.com