Re: Skipping VACUUM of indexes when no work required - Mailing list pgsql-patches

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