Re: Skipping VACUUM of indexes when no work required

On Wed, 2005-12-07 at 17:40 +0000, Simon Riggs wrote:
> On Wed, 2005-12-07 at 09:55 -0500, Tom Lane wrote:
> > Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
> > > We discussed an optimization of VACUUM here
> > > http://archives.postgresql.org/pgsql-hackers/2005-09/msg00046.php
> > > that would allow VACUUM to complete faster by avoiding scanning the
> > > indexes when no rows were removed from the heap by the VACUUM.
> >
> > Unfortunately I can't read that message right now because archives
> > isn't responding, but this seems like a pretty bad idea to me.
> > You still have to do the vacuum cleanup pass (at least in the btree
> > case, and the only reason gist doesn't need it is it's not yet up
> > to speed) so there's no real savings.
>
> There are real savings; this is not a theoretical patch.
>
> One pass of an index is faster than two, always.

Test results on a 1.2GB table, 10^6 rows and 3 indexes:

w/o optimization 87s
with optimization 56s

Timings taken with primed cache, to allow reasonable comparison without
confusing the issue with hint bit updates etc.

Performance gain is dependant upon:
1. size of index
2. logical/physical ordering of index pages

These tests performed immediately after load, which is best case, but
also the main case for which I seek to optimize.

postgres=# select pg_relation_size('vactest');
pg_relation_size
------------------
1204707328

vacuum verbose vactest;
psql:vacnout1.sql:3: INFO: vacuuming "public.vactest"
psql:vacnout1.sql:3: INFO: index "vactest_idx1" now contains 10000000
row versions in 21899 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.25s/0.03u sec elapsed 5.81 sec.
psql:vacnout1.sql:3: INFO: index "vactest_idx2" now contains 10000000
row versions in 21899 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.26s/0.04u sec elapsed 5.78 sec.
psql:vacnout1.sql:3: INFO: index "vactest_idx3" now contains 10000000
row versions in 21899 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.23s/0.05u sec elapsed 5.69 sec.
psql:vacnout1.sql:3: INFO: "vactest": found 0 removable, 10000000
nonremovable row versions in 147059 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 2 unused item pointers.
0 pages are entirely empty.
CPU 2.59s/0.58u sec elapsed 56.02 sec.
psql:vacnout1.sql:3: INFO: vacuuming "pg_toast.pg_toast_16415"
psql:vacnout1.sql:3: INFO: index "pg_toast_16415_index" now contains 0
row versions in 1 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
psql:vacnout1.sql:3: INFO: "pg_toast_16415": found 0 removable, 0
nonremovable row versions in 0 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM

update vactest set col1a =1 where col1a = 1 and col1b = 1 and col1c = 1;
UPDATE 1
vacuum verbose vactest;
psql:vacnout1.sql:6: INFO: vacuuming "public.vactest"
psql:vacnout1.sql:6: INFO: index "vactest_idx1" now contains 10000000
row versions in 21899 pages
DETAIL: 1 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.94s/0.45u sec elapsed 15.29 sec.
psql:vacnout1.sql:6: INFO: index "vactest_idx2" now contains 10000000
row versions in 21899 pages
DETAIL: 1 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 1.03s/0.40u sec elapsed 16.80 sec.
psql:vacnout1.sql:6: INFO: index "vactest_idx3" now contains 10000000
row versions in 21899 pages
DETAIL: 1 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.94s/0.49u sec elapsed 15.84 sec.
psql:vacnout1.sql:6: INFO: "vactest": removed 1 row versions in 1 pages
DETAIL: CPU 0.00s/0.00u sec elapsed 0.02 sec.
psql:vacnout1.sql:6: INFO: "vactest": found 1 removable, 10000000
nonremovable row versions in 147059 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 1 unused item pointers.
0 pages are entirely empty.
CPU 4.99s/1.85u sec elapsed 87.20 sec.
psql:vacnout1.sql:6: INFO: vacuuming "pg_toast.pg_toast_16415"
psql:vacnout1.sql:6: INFO: index "pg_toast_16415_index" now contains 0
row versions in 1 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
psql:vacnout1.sql:6: INFO: "pg_toast_16415": found 0 removable, 0
nonremovable row versions in 0 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM

Other details available.

Best Regards, Simon Riggs