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 <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] 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 ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
