Re: visibility map - what do i miss?

Guillaume Smet wrote:
> On Sat, Dec 6, 2008 at 12:53 PM, hubert depesz lubaczewski
> <depesz(at)depesz(dot)com> wrote:
>> First run - without visibility maps, timing of vacuums:
>> Time: 267844.822 ms
>> Time: 138854.592 ms
>> Time: 305467.950 ms
>> Time: 487133.179 ms
>>
>> Second run - on head:
>>
>> Time: 252218.609 ms
>> Time: 234388.763 ms
>> Time: 334016.413 ms
>> Time: 575698.750 ms
>>
>> Now - as I understand the change - visilibity maps should make second run much faster?
>
> If I understand correctly what Heikki explained, not if you run VACUUM
> only once (and you confirmed me on IRC you run it only once). It's the
> VACUUM which sets the PD_ALL_VISIBLE flag on the pages so the first
> VACUUM should be slower with HEAD than with 8.3 as it has far more
> work to do.
>
> The second VACUUM should then be faster.

That diagnosis is not quite right, but the prognosis is correct. The
first VACUUM after the UPDATEs doesn't set the PD_ALL_VISIBLE flags,
because there's still dead tuples on the pages. The dead tuples are
removed in the 2nd pass of the first vacuum, but it doesn't try to set
the PD_ALL_VISIBLE flags; that's only done in the first phase.

The second vacuum is just as slow as the first one, because the
visibility map doesn't have any bits set yet. The second vacuum will set
the bits, though, so the *third* vacuum should go faster.

So setting the PD_ALL_VISIBLE flags doesn't slow things down. That
should be just a tiny bit of extra CPU work per vacuumed page, not
something that would show up in performance tests.

This is the 1st issue I mentioned in this mail:

http://archives.postgresql.org/message-id/4925664C.3090605@enterprisedb.com

There was some suggestions in that thread, but none has been implemented.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com