On Sat, Dec 6, 2008 at 6:46 AM, hubert depesz lubaczewski
<depesz@depesz.com> wrote:
> hi,
> i tried to test new "visibility map" feature.
here's the test again in a more illustrative way:
postgres=# INSERT INTO test_1 SELECT generate_series(1, 100000000);
INSERT 0 100000000
Time: 136229.455 ms
postgres=# VACUUM test_1;
VACUUM
Time: 40643.705 ms <-- setting hint bits
postgres=# VACUUM test_1;
VACUUM
Time: 6112.946 ms <-- fast now!
postgres=# VACUUM test_1;
VACUUM
Time: 5906.454 ms <-- just to be sure!!
postgres=# update test_1 set i = i where i = 99999999;
UPDATE 1
Time: 10201.296 ms
postgres=# VACUUM test_1;
VACUUM
Time: 5896.648 ms <-- still fast
postgres=# UPDATE test_1 SET i = i + 1 WHERE i < 90000000;
UPDATE 89999999
Time: 352955.281 ms <--uggh!
postgres=# VACUUM test_1;
VACUUM
Time: 200082.556 ms <-- not bad
postgres=# VACUUM test_1;
VACUUM
Time: 17313.576 ms <-- faster now!
postgres=# UPDATE test_1 SET i = i + 1 WHERE i < 10000000;
UPDATE 9999998
Time: 55188.942 ms
postgres=# VACUUM test_1;
VACUUM
Time: 21353.182 ms < -- fast!
So what do we gather from this? Well, the feature works as
advertised. I think that as long as your updates are not uniformly
distributed across pages, vismap is a huge performance win for many
workloads. I think the benefit will increase as the feature is
tweaked in future versions. vacuum times are one of the things that
make dealing with large tables difficult, and force us to use
partitioning (which is, frankly, a hack).
Why are new pages initialized dirty? Do inserts on pages set the dirty bit?
merlin