Skip to content

Commit bf32494

Browse files
bmomjianbmomjian
committed
Allow VACUUM to complete faster by avoiding scanning the indexes when no
rows were removed from the heap by the VACUUM. Simon Riggs
1 parent 85d8ee8 commit bf32494

File tree

7 files changed

+65
-29
lines changed

7 files changed

+65
-29
lines changed

‎src/backend/access/gist/gistvacuum.c

Lines changed: 12 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/access/gist/gistvacuum.c,v 1.11 2005/11/22 18:17:05 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/access/gist/gistvacuum.c,v 1.12 2006/02/11 16:59:08 momjian Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -125,7 +125,7 @@ gistVacuumUpdate(GistVacuum *gv, BlockNumber blkno, bool needunion)
125125
if (chldtuple.ituplen > 1)
126126
{
127127
/*
128-
* child was splitted, so we need mark completion
128+
* child was split, so we need mark completion
129129
* insert(split)
130130
*/
131131
int j;
@@ -329,9 +329,9 @@ gistVacuumUpdate(GistVacuum *gv, BlockNumber blkno, bool needunion)
329329
}
330330

331331
/*
332-
* For usial vacuum just update FSM, for full vacuum
332+
* For usual vacuum just update FSM, for full vacuum
333333
* reforms parent tuples if some of childs was deleted or changed,
334-
* update invalid tuples (they can exsist from last crash recovery only),
334+
* update invalid tuples (they can exist from last crash recovery only),
335335
* tries to get smaller index
336336
*/
337337

@@ -505,10 +505,15 @@ gistbulkdelete(PG_FUNCTION_ARGS)
505505
*ptr;
506506
bool needLock;
507507

508-
stack = (GistBDItem *) palloc0(sizeof(GistBDItem));
508+
if (callback_state)
509+
{
510+
stack = (GistBDItem *) palloc0(sizeof(GistBDItem));
509511

510-
stack->blkno = GIST_ROOT_BLKNO;
511-
needFullVacuum = false;
512+
stack->blkno = GIST_ROOT_BLKNO;
513+
needFullVacuum = false;
514+
}
515+
else
516+
stack = NULL;
512517

513518
while (stack)
514519
{

‎src/backend/access/hash/hash.c

Lines changed: 12 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/access/hash/hash.c,v 1.83 2006/01/25 23:26:11 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/access/hash/hash.c,v 1.84 2006/02/11 16:59:09 momjian Exp $
1212
*
1313
* NOTES
1414
* This file contains only the public interface routines.
@@ -496,6 +496,17 @@ hashbulkdelete(PG_FUNCTION_ARGS)
496496
tuples_removed = 0;
497497
num_index_tuples = 0;
498498

499+
/* return statistics */
500+
num_pages = RelationGetNumberOfBlocks(rel);
501+
502+
result = (IndexBulkDeleteResult *) palloc0(sizeof(IndexBulkDeleteResult));
503+
result->num_pages = num_pages;
504+
505+
if (!callback_state)
506+
{
507+
PG_RETURN_POINTER(result);
508+
}
509+
499510
/*
500511
* Read the metapage to fetch original bucket and tuple counts. Also, we
501512
* keep a copy of the last-seen metapage so that we can use its
@@ -644,11 +655,6 @@ hashbulkdelete(PG_FUNCTION_ARGS)
644655

645656
_hash_wrtbuf(rel, metabuf);
646657

647-
/* return statistics */
648-
num_pages = RelationGetNumberOfBlocks(rel);
649-
650-
result = (IndexBulkDeleteResult *) palloc0(sizeof(IndexBulkDeleteResult));
651-
result->num_pages = num_pages;
652658
result->num_index_tuples = num_index_tuples;
653659
result->tuples_removed = tuples_removed;
654660

‎src/backend/access/index/indexam.c

Lines changed: 6 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/access/index/indexam.c,v 1.87 2005/12/03 05:51:00 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/access/index/indexam.c,v 1.88 2006/02/11 16:59:09 momjian Exp $
1212
*
1313
* INTERFACE ROUTINES
1414
* index_open - open an index relation by relation OID
@@ -685,6 +685,11 @@ index_getmulti(IndexScanDesc scan,
685685
* callback routine tells whether a given main-heap tuple is
686686
* to be deleted
687687
*
688+
* passing NULL callback_state can be interpreted by the
689+
* index access method as meaning that the index does not need
690+
* to be scanned in logical sequence to remove rows for this call
691+
* index_vacuum_cleanup is always required after this, however.
692+
*
688693
* return value is an optional palloc'd struct of statistics
689694
* ----------------
690695
*/

‎src/backend/access/nbtree/nbtree.c

Lines changed: 16 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -12,7 +12,7 @@
1212
* Portions Copyright (c) 1994, Regents of the University of California
1313
*
1414
* IDENTIFICATION
15-
* $PostgreSQL: pgsql/src/backend/access/nbtree/nbtree.c,v 1.136 2006/01/25 23:04:20 tgl Exp $
15+
* $PostgreSQL: pgsql/src/backend/access/nbtree/nbtree.c,v 1.137 2006/02/11 16:59:09 momjian Exp $
1616
*
1717
*-------------------------------------------------------------------------
1818
*/
@@ -543,8 +543,9 @@ btbulkdelete(PG_FUNCTION_ARGS)
543543
double num_index_tuples;
544544
OffsetNumber deletable[MaxOffsetNumber];
545545
int ndeletable;
546-
Buffer buf;
546+
Buffer buf = NULL;
547547
BlockNumber num_pages;
548+
bool scanindex = true;
548549

549550
tuples_removed = 0;
550551
num_index_tuples = 0;
@@ -565,8 +566,15 @@ btbulkdelete(PG_FUNCTION_ARGS)
565566
* skip obtaining exclusive lock on empty pages though, since no indexscan
566567
* could be stopped on those.
567568
*/
568-
buf = _bt_get_endpoint(rel, 0, false);
569-
if (BufferIsValid(buf)) /* check for empty index */
569+
if (callback_state)
570+
{
571+
buf = _bt_get_endpoint(rel, 0, false);
572+
scanindex = BufferIsValid(buf); /* check for empty index */
573+
}
574+
else
575+
scanindex = false;
576+
577+
if (scanindex)
570578
{
571579
for (;;)
572580
{
@@ -649,7 +657,10 @@ btbulkdelete(PG_FUNCTION_ARGS)
649657

650658
result = (IndexBulkDeleteResult *) palloc0(sizeof(IndexBulkDeleteResult));
651659
result->num_pages = num_pages;
652-
result->num_index_tuples = num_index_tuples;
660+
if (scanindex)
661+
result->num_index_tuples = num_index_tuples;
662+
else
663+
result->num_index_tuples = -1;
653664
result->tuples_removed = tuples_removed;
654665

655666
PG_RETURN_POINTER(result);

‎src/backend/commands/vacuum.c

Lines changed: 9 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -13,7 +13,7 @@
1313
*
1414
*
1515
* IDENTIFICATION
16-
* $PostgreSQL: pgsql/src/backend/commands/vacuum.c,v 1.321 2006/01/18 20:35:05 tgl Exp $
16+
* $PostgreSQL: pgsql/src/backend/commands/vacuum.c,v 1.322 2006/02/11 16:59:09 momjian Exp $
1717
*
1818
*-------------------------------------------------------------------------
1919
*/
@@ -719,7 +719,8 @@ vac_update_relstats(Oid relid, BlockNumber num_pages, double num_tuples,
719719
/* overwrite the existing statistics in the tuple */
720720
pgcform = (Form_pg_class) GETSTRUCT(&rtup);
721721
pgcform->relpages = (int32) num_pages;
722-
pgcform->reltuples = (float4) num_tuples;
722+
if (num_tuples >= 0 )
723+
pgcform->reltuples = (float4) num_tuples;
723724
pgcform->relhasindex = hasindex;
724725

725726
/*
@@ -2961,15 +2962,18 @@ scan_index(Relation indrel, double num_tuples)
29612962
if (!stats)
29622963
return;
29632964

2964-
/* now update statistics in pg_class */
2965+
/* now update statistics in pg_class
2966+
* we use the number of tuples from the table because we have not
2967+
* actually scanned the index, so don't know the number of tuples in index
2968+
*/
29652969
vac_update_relstats(RelationGetRelid(indrel),
2966-
stats->num_pages, stats->num_index_tuples,
2970+
stats->num_pages, num_tuples,
29672971
false);
29682972

29692973
ereport(elevel,
29702974
(errmsg("index \"%s\" now contains %.0f row versions in %u pages",
29712975
RelationGetRelationName(indrel),
2972-
stats->num_index_tuples,
2976+
num_tuples,
29732977
stats->num_pages),
29742978
errdetail("%u index pages have been deleted, %u are currently reusable.\n"
29752979
"%s.",

‎src/backend/commands/vacuumlazy.c

Lines changed: 7 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -31,7 +31,7 @@
3131
*
3232
*
3333
* IDENTIFICATION
34-
* $PostgreSQL: pgsql/src/backend/commands/vacuumlazy.c,v 1.63 2005/11/22 18:17:09 momjian Exp $
34+
* $PostgreSQL: pgsql/src/backend/commands/vacuumlazy.c,v 1.64 2006/02/11 16:59:09 momjian Exp $
3535
*
3636
*-------------------------------------------------------------------------
3737
*/
@@ -639,16 +639,19 @@ lazy_scan_index(Relation indrel, LVRelStats *vacrelstats)
639639
if (!stats)
640640
return;
641641

642-
/* now update statistics in pg_class */
642+
/* now update statistics in pg_class
643+
* we use the number of tuples from the table because we have not
644+
* actually scanned the index, so don't know the number of tuples in index
645+
*/
643646
vac_update_relstats(RelationGetRelid(indrel),
644647
stats->num_pages,
645-
stats->num_index_tuples,
648+
vacrelstats->rel_tuples,
646649
false);
647650

648651
ereport(elevel,
649652
(errmsg("index \"%s\" now contains %.0f row versions in %u pages",
650653
RelationGetRelationName(indrel),
651-
stats->num_index_tuples,
654+
vacrelstats->rel_tuples,
652655
stats->num_pages),
653656
errdetail("%u index pages have been deleted, %u are currently reusable.\n"
654657
"%s.",

‎src/include/access/genam.h

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/access/genam.h,v 1.54 2005/12/03 05:51:03 tgl Exp $
10+
* $PostgreSQL: pgsql/src/include/access/genam.h,v 1.55 2006/02/11 16:59:09 momjian Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -38,6 +38,8 @@ typedef struct IndexBulkDeleteResult
3838
BlockNumber num_pages; /* pages remaining in index */
3939
BlockNumber pages_removed; /* # removed by bulk-delete operation */
4040
double num_index_tuples; /* tuples remaining */
41+
/* should set to -1 if index not scanned */
42+
/* because no records to delete */
4143
double tuples_removed; /* # removed by bulk-delete operation */
4244
BlockNumber pages_deleted; /* # unused pages in index */
4345
BlockNumber pages_free; /* # pages available for reuse */

0 commit comments

Comments
 (0)