Thread: Skipping VACUUM of indexes when no work required
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. Patch applies cleanly on cvstip; make check passes. Tests shows clear performance gain when no rows removed by VACUUM. Not as useful as may once have been, but certainly no loss either, whatever happens in the future with VACUUM. Best Regards, Simon Riggs
Attachment
Simon Riggs <simon@2ndquadrant.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. regards, tom lane
On Wed, 2005-12-07 at 09:55 -0500, Tom Lane wrote: > Simon Riggs <simon@2ndquadrant.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. resolved. > > 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. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: PLphp, PLperl, ODBCng - http://www.commandprompt.com/
On Wed, 2005-12-07 at 09:55 -0500, Tom Lane wrote: > Simon Riggs <simon@2ndquadrant.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. Best Regards, Simon Riggs
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@2ndquadrant.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
Patch applied. Thanks. --------------------------------------------------------------------------- Simon Riggs wrote: > 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@2ndquadrant.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 > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Patch applied. Thanks.
Please revert. You cannot skip scanning indexes simply because there
was no heap activity. btree for instance does post-cleanup on the
next vacuum.
regards, tom lane
Simon Riggs <simon@2ndquadrant.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. After looking at this, I think it is salvageable, but the patch as written complicates the vacuum-to-index-AM API more than necessary; there's no reason why the AM has to expose the fact that it skipped doing anything. I'll clean it up and reapply. regards, tom lane
On Sat, 2006-02-11 at 12:04 -0500, Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Patch applied. Thanks. > > Please revert. You cannot skip scanning indexes simply because there > was no heap activity. btree for instance does post-cleanup on the > next vacuum. The patch skips only the first scan, not the second phase which does the post-cleanup. We discussed this before and I listened... I believe this is safe. Best Regards, Simon Riggs
Simon Riggs <simon@2ndquadrant.com> writes:
> I believe this is safe.
I won't insult your intelligence by pointing out how I know that you
didn't even test the patch against hash or gist.
The major problem with the patch is that it's incapable of producing
correct tuple-count stats for partial indexes, which is really not
acceptable from a planning standpoint. What I'm currently fooling with
is skipping the bulkdelete scan only if the index isn't partial...
regards, tom lane
Here's the patch as-applied. Note one major difference from your
original: the logic about whether an indexscan can be skipped is now
entirely local to the index AMs, rather than allowing VACUUM to make
assumptions that may not be warranted for particular AMs. For the
same reason, the AM is still responsible for providing the tuple
count statistic.
regards, tom lane
Attachment
On Sat, 2006-02-11 at 16:36 -0500, Tom Lane wrote: > Simon Riggs <simon@2ndquadrant.com> writes: > > I believe this is safe. > > I won't insult your intelligence by pointing out how I know that you > didn't even test the patch against hash or gist. I don't recall either way, though from what you say it seems I did not test those cases. Thanks for catching my error. > The major problem with the patch is that it's incapable of producing > correct tuple-count stats for partial indexes, which is really not > acceptable from a planning standpoint. What I'm currently fooling with > is skipping the bulkdelete scan only if the index isn't partial... Thanks for spotting this case. I strive to learn. Best Regards, Simon Riggs