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
Index: src/backend/access/gist/gistvacuum.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/access/gist/gistvacuum.c,v
retrieving revision 1.11
diff -c -r1.11 gistvacuum.c
*** src/backend/access/gist/gistvacuum.c 22 Nov 2005 18:17:05 -0000 1.11
--- src/backend/access/gist/gistvacuum.c 7 Dec 2005 11:52:43 -0000
***************
*** 125,131 ****
if (chldtuple.ituplen > 1)
{
/*
! * child was splitted, so we need mark completion
* insert(split)
*/
int j;
--- 125,131 ----
if (chldtuple.ituplen > 1)
{
/*
! * child was split, so we need mark completion
* insert(split)
*/
int j;
***************
*** 329,337 ****
}
/*
! * For usial vacuum just update FSM, for full vacuum
* reforms parent tuples if some of childs was deleted or changed,
! * update invalid tuples (they can exsist from last crash recovery only),
* tries to get smaller index
*/
--- 329,337 ----
}
/*
! * For usual vacuum just update FSM, for full vacuum
* reforms parent tuples if some of childs was deleted or changed,
! * update invalid tuples (they can exist from last crash recovery only),
* tries to get smaller index
*/
***************
*** 505,514 ****
*ptr;
bool needLock;
! stack = (GistBDItem *) palloc0(sizeof(GistBDItem));
!
! stack->blkno = GIST_ROOT_BLKNO;
! needFullVacuum = false;
while (stack)
{
--- 505,519 ----
*ptr;
bool needLock;
! if (callback_state)
! {
! stack = (GistBDItem *) palloc0(sizeof(GistBDItem));
!
! stack->blkno = GIST_ROOT_BLKNO;
! needFullVacuum = false;
! }
! else
! stack = NULL;
while (stack)
{
Index: src/backend/access/hash/hash.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/access/hash/hash.c,v
retrieving revision 1.82
diff -c -r1.82 hash.c
*** src/backend/access/hash/hash.c 6 Nov 2005 19:29:00 -0000 1.82
--- src/backend/access/hash/hash.c 7 Dec 2005 11:52:43 -0000
***************
*** 504,509 ****
--- 504,520 ----
tuples_removed = 0;
num_index_tuples = 0;
+ /* return statistics */
+ num_pages = RelationGetNumberOfBlocks(rel);
+
+ result = (IndexBulkDeleteResult *) palloc0(sizeof(IndexBulkDeleteResult));
+ result->num_pages = num_pages;
+
+ if (!callback_state)
+ {
+ PG_RETURN_POINTER(result);
+ }
+
/*
* Read the metapage to fetch original bucket and tuple counts. Also, we
* keep a copy of the last-seen metapage so that we can use its
***************
*** 652,662 ****
_hash_wrtbuf(rel, metabuf);
- /* return statistics */
- num_pages = RelationGetNumberOfBlocks(rel);
-
- result = (IndexBulkDeleteResult *) palloc0(sizeof(IndexBulkDeleteResult));
- result->num_pages = num_pages;
result->num_index_tuples = num_index_tuples;
result->tuples_removed = tuples_removed;
--- 663,668 ----
Index: src/backend/access/index/indexam.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/access/index/indexam.c,v
retrieving revision 1.87
diff -c -r1.87 indexam.c
*** src/backend/access/index/indexam.c 3 Dec 2005 05:51:00 -0000 1.87
--- src/backend/access/index/indexam.c 7 Dec 2005 11:52:44 -0000
***************
*** 685,690 ****
--- 685,695 ----
* callback routine tells whether a given main-heap tuple is
* to be deleted
*
+ * passing NULL callback_state can be interpreted by the
+ * index access method as meaning that the index does not need
+ * to be scanned in logical sequence to remove rows for this call
+ * index_vacuum_cleanup is always required after this, however.
+ *
* return value is an optional palloc'd struct of statistics
* ----------------
*/
Index: src/backend/access/nbtree/nbtree.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/access/nbtree/nbtree.c,v
retrieving revision 1.134
diff -c -r1.134 nbtree.c
*** src/backend/access/nbtree/nbtree.c 22 Nov 2005 18:17:06 -0000 1.134
--- src/backend/access/nbtree/nbtree.c 7 Dec 2005 11:52:45 -0000
***************
*** 587,592 ****
--- 587,593 ----
int ndeletable;
Buffer buf;
BlockNumber num_pages;
+ bool scanindex = true;
tuples_removed = 0;
num_index_tuples = 0;
***************
*** 607,614 ****
* skip obtaining exclusive lock on empty pages though, since no indexscan
* could be stopped on those.
*/
! buf = _bt_get_endpoint(rel, 0, false);
! if (BufferIsValid(buf)) /* check for empty index */
{
for (;;)
{
--- 608,622 ----
* skip obtaining exclusive lock on empty pages though, since no indexscan
* could be stopped on those.
*/
! if (callback_state)
! {
! buf = _bt_get_endpoint(rel, 0, false);
! scanindex = BufferIsValid(buf); /* check for empty index */
! }
! else
! scanindex = false;
!
! if (scanindex)
{
for (;;)
{
***************
*** 691,697 ****
result = (IndexBulkDeleteResult *) palloc0(sizeof(IndexBulkDeleteResult));
result->num_pages = num_pages;
! result->num_index_tuples = num_index_tuples;
result->tuples_removed = tuples_removed;
PG_RETURN_POINTER(result);
--- 699,708 ----
result = (IndexBulkDeleteResult *) palloc0(sizeof(IndexBulkDeleteResult));
result->num_pages = num_pages;
! if (scanindex)
! result->num_index_tuples = num_index_tuples;
! else
! result->num_index_tuples = -1;
result->tuples_removed = tuples_removed;
PG_RETURN_POINTER(result);
Index: src/backend/commands/vacuum.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/commands/vacuum.c,v
retrieving revision 1.319
diff -c -r1.319 vacuum.c
*** src/backend/commands/vacuum.c 22 Nov 2005 18:17:09 -0000 1.319
--- src/backend/commands/vacuum.c 7 Dec 2005 11:52:48 -0000
***************
*** 709,715 ****
/* overwrite the existing statistics in the tuple */
pgcform = (Form_pg_class) GETSTRUCT(&rtup);
pgcform->relpages = (int32) num_pages;
! pgcform->reltuples = (float4) num_tuples;
pgcform->relhasindex = hasindex;
/*
--- 709,716 ----
/* overwrite the existing statistics in the tuple */
pgcform = (Form_pg_class) GETSTRUCT(&rtup);
pgcform->relpages = (int32) num_pages;
! if (num_tuples >= 0 )
! pgcform->reltuples = (float4) num_tuples;
pgcform->relhasindex = hasindex;
/*
***************
*** 2951,2965 ****
if (!stats)
return;
! /* now update statistics in pg_class */
vac_update_relstats(RelationGetRelid(indrel),
! stats->num_pages, stats->num_index_tuples,
false);
ereport(elevel,
(errmsg("index \"%s\" now contains %.0f row versions in %u pages",
RelationGetRelationName(indrel),
! stats->num_index_tuples,
stats->num_pages),
errdetail("%u index pages have been deleted, %u are currently reusable.\n"
"%s.",
--- 2952,2969 ----
if (!stats)
return;
! /* now update statistics in pg_class
! * we use the number of tuples from the table because we have not
! * actually scanned the index, so don't know the number of tuples in index
! */
vac_update_relstats(RelationGetRelid(indrel),
! stats->num_pages, num_tuples,
false);
ereport(elevel,
(errmsg("index \"%s\" now contains %.0f row versions in %u pages",
RelationGetRelationName(indrel),
! num_tuples,
stats->num_pages),
errdetail("%u index pages have been deleted, %u are currently reusable.\n"
"%s.",
Index: src/backend/commands/vacuumlazy.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/commands/vacuumlazy.c,v
retrieving revision 1.63
diff -c -r1.63 vacuumlazy.c
*** src/backend/commands/vacuumlazy.c 22 Nov 2005 18:17:09 -0000 1.63
--- src/backend/commands/vacuumlazy.c 7 Dec 2005 11:52:50 -0000
***************
*** 639,654 ****
if (!stats)
return;
! /* now update statistics in pg_class */
vac_update_relstats(RelationGetRelid(indrel),
stats->num_pages,
! stats->num_index_tuples,
false);
ereport(elevel,
(errmsg("index \"%s\" now contains %.0f row versions in %u pages",
RelationGetRelationName(indrel),
! stats->num_index_tuples,
stats->num_pages),
errdetail("%u index pages have been deleted, %u are currently reusable.\n"
"%s.",
--- 639,657 ----
if (!stats)
return;
! /* now update statistics in pg_class
! * we use the number of tuples from the table because we have not
! * actually scanned the index, so don't know the number of tuples in index
! */
vac_update_relstats(RelationGetRelid(indrel),
stats->num_pages,
! vacrelstats->rel_tuples,
false);
ereport(elevel,
(errmsg("index \"%s\" now contains %.0f row versions in %u pages",
RelationGetRelationName(indrel),
! vacrelstats->rel_tuples,
stats->num_pages),
errdetail("%u index pages have been deleted, %u are currently reusable.\n"
"%s.",
Index: src/include/access/genam.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/access/genam.h,v
retrieving revision 1.54
diff -c -r1.54 genam.h
*** src/include/access/genam.h 3 Dec 2005 05:51:03 -0000 1.54
--- src/include/access/genam.h 7 Dec 2005 11:52:51 -0000
***************
*** 38,43 ****
--- 38,45 ----
BlockNumber num_pages; /* pages remaining in index */
BlockNumber pages_removed; /* # removed by bulk-delete operation */
double num_index_tuples; /* tuples remaining */
+ /* should set to -1 if index not scanned */
+ /* because no records to delete */
double tuples_removed; /* # removed by bulk-delete operation */
BlockNumber pages_deleted; /* # unused pages in index */
BlockNumber pages_free; /* # pages available for reuse */
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
