Re: new vacuum is slower for small tables

Pavel Stehule wrote:
> I did small tests and I found so for small tables (less 1000 rows)
> VACUUM based on visibility maps are slower than old implementation
>
> it is about 5ms X 20ms

How did you measure that?

I tried to reproduce that here, and indeed it seems to be much slower on
CVS HEAD than on PG 8.3. I tried to short-circuit the vacuum completely,
making it a no-op:

diff --git a/src/backend/commands/vacuumlazy.c
b/src/backend/commands/vacuumlazy
index 475c38a..c31917d 100644
--- a/src/backend/commands/vacuumlazy.c
+++ b/src/backend/commands/vacuumlazy.c
@@ -275,6 +275,7 @@ lazy_scan_heap(Relation onerel, LVRelStats *vacrelstats,

lazy_space_alloc(vacrelstats, nblocks);

+ nblocks = 0;
for (blkno = 0; blkno < nblocks; blkno++)
{
Buffer buf;

but that made no difference at all; vacuuming a one page table on CVS
HEAD with that hack is still slower than PG 8.3 without that hack. Which
suggests that the slowdown is not related to visibility map.

Oprofile suggests that most of the time is actually spent in
pgstat_vacuum_stat. And more precisely in pstat_collect_oids, which is
called by pgstat_vacuum_stat.

We added support for tracking call counts and elapsed runtime of
user-defined functions back in May. That added the code to
pgstat_vacuum_stat to tell stats collector about deleted functions,
which involves populating a hash table of all functions in the database.
It looks like *that's* what's causing the slowdown.

I think we can live with the extra overhead.

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