Re: Skipping VACUUM of indexes when no work required

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Simon Riggs <simon(at)2ndquadrant(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-patches(at)postgresql(dot)org
Subject: Re: Skipping VACUUM of indexes when no work required
Date: 2006-02-11 16:59:05
Message-ID: 200602111659.k1BGx5Q24289@candle.pha.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

In response to

Responses

Browse pgsql-patches by date

  From Date Subject
Next Message Bruce Momjian 2006-02-11 16:59:09 pgsql: Allow VACUUM to complete faster by avoiding scanning the indexes
Previous Message Tom Lane 2006-02-11 15:52:50 Re: ignore_killed_tuples is always true