public inbox for [email protected]
help / color / mirror / Atom feed[PATCH v31 06/11] Add Incremental View Maintenance support
21+ messages / 5 participants
[nested] [flat]
* [PATCH v31 06/11] Add Incremental View Maintenance support
@ 2023-05-31 09:59 Yugo Nagata <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Yugo Nagata @ 2023-05-31 09:59 UTC (permalink / raw)
In this implementation, AFTER triggers are used to collect
tuplestores containing transition table contents. When multiple tables
are changed, multiple AFTER triggers are invoked, then the final AFTER
trigger performs actual update of the matview. In addition, BEFORE
triggers are also used to handle global information for view
maintenance.
To calculate view deltas, we need both pre-state and post-state of base
tables. Post-update states are available in AFTER trigger, and pre-update
states can be calculated by removing inserted tuples and appending deleted
tuples. Insterted tuples are filtered using the snapshot taken before
table modiication, and deleted tuples are contained in the old transition
table.
Incrementally Maintainable Materialized Views (IMMV) can contain
duplicated tuples.
This patch also allows self-join, simultaneous updates of more than
one base table, and multiple updates of the same base table.
---
src/backend/access/transam/xact.c | 5 +
src/backend/commands/createas.c | 682 ++++++++++++++
src/backend/commands/matview.c | 1468 ++++++++++++++++++++++++++++-
src/include/catalog/pg_proc.dat | 10 +
src/include/commands/createas.h | 4 +
src/include/commands/matview.h | 9 +
6 files changed, 2143 insertions(+), 35 deletions(-)
diff --git a/src/backend/access/transam/xact.c b/src/backend/access/transam/xact.c
index df5a67e4c3..a2d5404912 100644
--- a/src/backend/access/transam/xact.c
+++ b/src/backend/access/transam/xact.c
@@ -36,6 +36,7 @@
#include "catalog/pg_enum.h"
#include "catalog/storage.h"
#include "commands/async.h"
+#include "commands/matview.h"
#include "commands/tablecmds.h"
#include "commands/trigger.h"
#include "common/pg_prng.h"
@@ -2860,6 +2861,7 @@ AbortTransaction(void)
AtAbort_Notify();
AtEOXact_RelationMap(false, is_parallel_worker);
AtAbort_Twophase();
+ AtAbort_IVM();
/*
* Advertise the fact that we aborted in pg_xact (assuming that we got as
@@ -5198,6 +5200,9 @@ AbortSubTransaction(void)
pgstat_progress_end_command();
UnlockBuffers();
+ /* Clean up hash entries for incremental view maintenance */
+ AtAbort_IVM();
+
/* Reset WAL record construction state */
XLogResetInsertion();
diff --git a/src/backend/commands/createas.c b/src/backend/commands/createas.c
index 62050f4dc5..04a5ee9e37 100644
--- a/src/backend/commands/createas.c
+++ b/src/backend/commands/createas.c
@@ -29,15 +29,27 @@
#include "access/tableam.h"
#include "access/xact.h"
#include "catalog/namespace.h"
+#include "catalog/index.h"
+#include "catalog/pg_constraint.h"
+#include "catalog/pg_inherits.h"
+#include "catalog/pg_trigger.h"
#include "catalog/toasting.h"
#include "commands/createas.h"
+#include "commands/defrem.h"
#include "commands/matview.h"
#include "commands/prepare.h"
#include "commands/tablecmds.h"
+#include "commands/tablespace.h"
+#include "commands/trigger.h"
#include "commands/view.h"
#include "miscadmin.h"
+#include "optimizer/optimizer.h"
+#include "optimizer/prep.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
+#include "parser/parser.h"
+#include "parser/parsetree.h"
+#include "parser/parse_clause.h"
#include "rewrite/rewriteHandler.h"
#include "tcop/tcopprot.h"
#include "utils/builtins.h"
@@ -68,6 +80,12 @@ static bool intorel_receive(TupleTableSlot *slot, DestReceiver *self);
static void intorel_shutdown(DestReceiver *self);
static void intorel_destroy(DestReceiver *self);
+static void CreateIvmTriggersOnBaseTablesRecurse(Query *qry, Node *node, Oid matviewOid,
+ Relids *relids, bool ex_lock);
+static void CreateIvmTrigger(Oid relOid, Oid viewOid, int16 type, int16 timing, bool ex_lock);
+static void check_ivm_restriction(Node *node);
+static bool check_ivm_restriction_walker(Node *node, void *context);
+static Bitmapset *get_primary_key_attnos_from_query(Query *query, List **constraintList);
/*
* create_ctas_internal
@@ -277,6 +295,18 @@ ExecCreateTableAs(ParseState *pstate, CreateTableAsStmt *stmt,
save_nestlevel = NewGUCNestLevel();
}
+ if (is_matview && into->ivm)
+ {
+ /* check if the query is supported in IMMV definition */
+ if (contain_mutable_functions((Node *) query))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("mutable function is not supported on incrementally maintainable materialized view"),
+ errhint("functions must be marked IMMUTABLE")));
+
+ check_ivm_restriction((Node *) query);
+ }
+
if (into->skipData)
{
/*
@@ -353,6 +383,27 @@ ExecCreateTableAs(ParseState *pstate, CreateTableAsStmt *stmt,
/* Restore userid and security context */
SetUserIdAndSecContext(save_userid, save_sec_context);
+
+ if (into->ivm)
+ {
+ Oid matviewOid = address.objectId;
+ Relation matviewRel = table_open(matviewOid, NoLock);
+
+ /*
+ * Mark relisivm field, if it's a matview and into->ivm is true.
+ */
+ SetMatViewIVMState(matviewRel, true);
+
+ if (!into->skipData)
+ {
+ /* Create an index on incremental maintainable materialized view, if possible */
+ CreateIndexOnIMMV((Query *) into->viewQuery, matviewRel);
+
+ /* Create triggers on incremental maintainable materialized view */
+ CreateIvmTriggersOnBaseTables((Query *) into->viewQuery, matviewOid);
+ }
+ table_close(matviewRel, NoLock);
+ }
}
return address;
@@ -630,3 +681,634 @@ intorel_destroy(DestReceiver *self)
{
pfree(self);
}
+
+/*
+ * CreateIvmTriggersOnBaseTables -- create IVM triggers on all base tables
+ */
+void
+CreateIvmTriggersOnBaseTables(Query *qry, Oid matviewOid)
+{
+ Relids relids = NULL;
+ bool ex_lock = false;
+ RangeTblEntry *rte;
+
+ /* Immediately return if we don't have any base tables. */
+ if (list_length(qry->rtable) < 1)
+ return;
+
+ /*
+ * If the view has more than one base tables, we need an exclusive lock
+ * on the view so that the view would be maintained serially to avoid
+ * the inconsistency that occurs when two base tables are modified in
+ * concurrent transactions. However, if the view has only one table,
+ * we can use a weaker lock.
+ *
+ * The type of lock should be determined here, because if we check the
+ * view definition at maintenance time, we need to acquire a weaker lock,
+ * and upgrading the lock level after this increases probability of
+ * deadlock.
+ */
+
+ rte = list_nth(qry->rtable, 0);
+ if (list_length(qry->rtable) > 1 || rte->rtekind != RTE_RELATION)
+ ex_lock = true;
+
+ CreateIvmTriggersOnBaseTablesRecurse(qry, (Node *)qry, matviewOid, &relids, ex_lock);
+
+ bms_free(relids);
+}
+
+static void
+CreateIvmTriggersOnBaseTablesRecurse(Query *qry, Node *node, Oid matviewOid,
+ Relids *relids, bool ex_lock)
+{
+ if (node == NULL)
+ return;
+
+ /* This can recurse, so check for excessive recursion */
+ check_stack_depth();
+
+ switch (nodeTag(node))
+ {
+ case T_Query:
+ {
+ Query *query = (Query *) node;
+
+ CreateIvmTriggersOnBaseTablesRecurse(qry, (Node *)query->jointree, matviewOid, relids, ex_lock);
+ }
+ break;
+
+ case T_RangeTblRef:
+ {
+ int rti = ((RangeTblRef *) node)->rtindex;
+ RangeTblEntry *rte = rt_fetch(rti, qry->rtable);
+
+ if (rte->rtekind == RTE_RELATION && !bms_is_member(rte->relid, *relids))
+ {
+ CreateIvmTrigger(rte->relid, matviewOid, TRIGGER_TYPE_INSERT, TRIGGER_TYPE_BEFORE, ex_lock);
+ CreateIvmTrigger(rte->relid, matviewOid, TRIGGER_TYPE_DELETE, TRIGGER_TYPE_BEFORE, ex_lock);
+ CreateIvmTrigger(rte->relid, matviewOid, TRIGGER_TYPE_UPDATE, TRIGGER_TYPE_BEFORE, ex_lock);
+ CreateIvmTrigger(rte->relid, matviewOid, TRIGGER_TYPE_TRUNCATE, TRIGGER_TYPE_BEFORE, true);
+ CreateIvmTrigger(rte->relid, matviewOid, TRIGGER_TYPE_INSERT, TRIGGER_TYPE_AFTER, ex_lock);
+ CreateIvmTrigger(rte->relid, matviewOid, TRIGGER_TYPE_DELETE, TRIGGER_TYPE_AFTER, ex_lock);
+ CreateIvmTrigger(rte->relid, matviewOid, TRIGGER_TYPE_UPDATE, TRIGGER_TYPE_AFTER, ex_lock);
+ CreateIvmTrigger(rte->relid, matviewOid, TRIGGER_TYPE_TRUNCATE, TRIGGER_TYPE_AFTER, true);
+
+ *relids = bms_add_member(*relids, rte->relid);
+ }
+ }
+ break;
+
+ case T_FromExpr:
+ {
+ FromExpr *f = (FromExpr *) node;
+ ListCell *l;
+
+ foreach(l, f->fromlist)
+ CreateIvmTriggersOnBaseTablesRecurse(qry, lfirst(l), matviewOid, relids, ex_lock);
+ }
+ break;
+
+ case T_JoinExpr:
+ {
+ JoinExpr *j = (JoinExpr *) node;
+
+ CreateIvmTriggersOnBaseTablesRecurse(qry, j->larg, matviewOid, relids, ex_lock);
+ CreateIvmTriggersOnBaseTablesRecurse(qry, j->rarg, matviewOid, relids, ex_lock);
+ }
+ break;
+
+ default:
+ elog(ERROR, "unrecognized node type: %d", (int) nodeTag(node));
+ }
+}
+
+/*
+ * CreateIvmTrigger -- create IVM trigger on a base table
+ */
+static void
+CreateIvmTrigger(Oid relOid, Oid viewOid, int16 type, int16 timing, bool ex_lock)
+{
+ ObjectAddress refaddr;
+ ObjectAddress address;
+ CreateTrigStmt *ivm_trigger;
+ List *transitionRels = NIL;
+
+ Assert(timing == TRIGGER_TYPE_BEFORE || timing == TRIGGER_TYPE_AFTER);
+
+ refaddr.classId = RelationRelationId;
+ refaddr.objectId = viewOid;
+ refaddr.objectSubId = 0;
+
+ ivm_trigger = makeNode(CreateTrigStmt);
+ ivm_trigger->relation = NULL;
+ ivm_trigger->row = false;
+
+ ivm_trigger->timing = timing;
+ ivm_trigger->events = type;
+
+ switch (type)
+ {
+ case TRIGGER_TYPE_INSERT:
+ ivm_trigger->trigname = (timing == TRIGGER_TYPE_BEFORE ? "IVM_trigger_ins_before" : "IVM_trigger_ins_after");
+ break;
+ case TRIGGER_TYPE_DELETE:
+ ivm_trigger->trigname = (timing == TRIGGER_TYPE_BEFORE ? "IVM_trigger_del_before" : "IVM_trigger_del_after");
+ break;
+ case TRIGGER_TYPE_UPDATE:
+ ivm_trigger->trigname = (timing == TRIGGER_TYPE_BEFORE ? "IVM_trigger_upd_before" : "IVM_trigger_upd_after");
+ break;
+ case TRIGGER_TYPE_TRUNCATE:
+ ivm_trigger->trigname = (timing == TRIGGER_TYPE_BEFORE ? "IVM_trigger_truncate_before" : "IVM_trigger_truncate_after");
+ break;
+ default:
+ elog(ERROR, "unsupported trigger type");
+ }
+
+ if (timing == TRIGGER_TYPE_AFTER)
+ {
+ if (type == TRIGGER_TYPE_INSERT || type == TRIGGER_TYPE_UPDATE)
+ {
+ TriggerTransition *n = makeNode(TriggerTransition);
+ n->name = "__ivm_newtable";
+ n->isNew = true;
+ n->isTable = true;
+
+ transitionRels = lappend(transitionRels, n);
+ }
+ if (type == TRIGGER_TYPE_DELETE || type == TRIGGER_TYPE_UPDATE)
+ {
+ TriggerTransition *n = makeNode(TriggerTransition);
+ n->name = "__ivm_oldtable";
+ n->isNew = false;
+ n->isTable = true;
+
+ transitionRels = lappend(transitionRels, n);
+ }
+ }
+
+ /*
+ * XXX: When using DELETE or UPDATE, we must use exclusive lock for now
+ * because apply_old_delta(_with_count) uses ctid to identify the tuple
+ * to be deleted/deleted, but doesn't work in concurrent situations.
+ *
+ * If the view doesn't have aggregate, distinct, or tuple duplicate,
+ * then it would work even in concurrent situations. However, we don't have
+ * any way to guarantee the view has a unique key before opening the IMMV
+ * at the maintenance time because users may drop the unique index.
+ */
+
+ if (type == TRIGGER_TYPE_DELETE || type == TRIGGER_TYPE_UPDATE)
+ ex_lock = true;
+
+ ivm_trigger->funcname =
+ (timing == TRIGGER_TYPE_BEFORE ? SystemFuncName("IVM_immediate_before") : SystemFuncName("IVM_immediate_maintenance"));
+
+ ivm_trigger->columns = NIL;
+ ivm_trigger->transitionRels = transitionRels;
+ ivm_trigger->whenClause = NULL;
+ ivm_trigger->isconstraint = false;
+ ivm_trigger->deferrable = false;
+ ivm_trigger->initdeferred = false;
+ ivm_trigger->constrrel = NULL;
+ ivm_trigger->args = list_make2(
+ makeString(DatumGetPointer(DirectFunctionCall1(oidout, ObjectIdGetDatum(viewOid)))),
+ makeString(DatumGetPointer(DirectFunctionCall1(boolout, BoolGetDatum(ex_lock))))
+ );
+
+ address = CreateTrigger(ivm_trigger, NULL, relOid, InvalidOid, InvalidOid,
+ InvalidOid, InvalidOid, InvalidOid, NULL, true, false);
+
+ recordDependencyOn(&address, &refaddr, DEPENDENCY_AUTO);
+
+ /* Make changes-so-far visible */
+ CommandCounterIncrement();
+}
+
+/*
+ * check_ivm_restriction --- look for specify nodes in the query tree
+ */
+static void
+check_ivm_restriction(Node *node)
+{
+ check_ivm_restriction_walker(node, NULL);
+}
+
+static bool
+check_ivm_restriction_walker(Node *node, void *context)
+{
+ if (node == NULL)
+ return false;
+
+ /*
+ * We currently don't support Sub-Query.
+ */
+ if (IsA(node, SubPlan) || IsA(node, SubLink))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("subquery is not supported on incrementally maintainable materialized view")));
+
+ /* This can recurse, so check for excessive recursion */
+ check_stack_depth();
+
+ switch (nodeTag(node))
+ {
+ case T_Query:
+ {
+ Query *qry = (Query *)node;
+ ListCell *lc;
+ List *vars;
+
+ /* if contained CTE, return error */
+ if (qry->cteList != NIL)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("CTE is not supported on incrementally maintainable materialized view")));
+ if (qry->havingQual != NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg(" HAVING clause is not supported on incrementally maintainable materialized view")));
+ if (qry->sortClause != NIL) /* There is a possibility that we don't need to return an error */
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("ORDER BY clause is not supported on incrementally maintainable materialized view")));
+ if (qry->limitOffset != NULL || qry->limitCount != NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("LIMIT/OFFSET clause is not supported on incrementally maintainable materialized view")));
+ if (qry->distinctClause)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("DISTINCT is not supported on incrementally maintainable materialized view")));
+ if (qry->hasDistinctOn)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("DISTINCT ON is not supported on incrementally maintainable materialized view")));
+ if (qry->hasWindowFuncs)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("window functions are not supported on incrementally maintainable materialized view")));
+ if (qry->groupingSets != NIL)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("GROUPING SETS, ROLLUP, or CUBE clauses is not supported on incrementally maintainable materialized view")));
+ if (qry->setOperations != NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("UNION/INTERSECT/EXCEPT statements are not supported on incrementally maintainable materialized view")));
+ if (list_length(qry->targetList) == 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("empty target list is not supported on incrementally maintainable materialized view")));
+ if (qry->rowMarks != NIL)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("FOR UPDATE/SHARE clause is not supported on incrementally maintainable materialized view")));
+
+ /* system column restrictions */
+ vars = pull_vars_of_level((Node *) qry, 0);
+ foreach(lc, vars)
+ {
+ if (IsA(lfirst(lc), Var))
+ {
+ Var *var = (Var *) lfirst(lc);
+ /* if system column, return error */
+ if (var->varattno < 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("system column is not supported on incrementally maintainable materialized view")));
+ }
+ }
+
+ /* restrictions for rtable */
+ foreach(lc, qry->rtable)
+ {
+ RangeTblEntry *rte = (RangeTblEntry *) lfirst(lc);
+
+ if (rte->subquery)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("subquery is not supported on incrementally maintainable materialized view")));
+
+ if (rte->tablesample != NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("TABLESAMPLE clause is not supported on incrementally maintainable materialized view")));
+
+ if (rte->relkind == RELKIND_PARTITIONED_TABLE)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("partitioned table is not supported on incrementally maintainable materialized view")));
+
+ if (rte->relkind == RELKIND_RELATION && has_superclass(rte->relid))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("partitions is not supported on incrementally maintainable materialized view")));
+
+ if (rte->relkind == RELKIND_RELATION && find_inheritance_children(rte->relid, NoLock) != NIL)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("inheritance parent is not supported on incrementally maintainable materialized view")));
+
+ if (rte->relkind == RELKIND_FOREIGN_TABLE)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("foreign table is not supported on incrementally maintainable materialized view")));
+
+ if (rte->relkind == RELKIND_VIEW ||
+ rte->relkind == RELKIND_MATVIEW)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("VIEW or MATERIALIZED VIEW is not supported on incrementally maintainable materialized view")));
+
+ if (rte->rtekind == RTE_VALUES)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("VALUES is not supported on incrementally maintainable materialized view")));
+
+ }
+
+ query_tree_walker(qry, check_ivm_restriction_walker, NULL, QTW_IGNORE_RANGE_TABLE);
+
+ break;
+ }
+ case T_TargetEntry:
+ {
+ TargetEntry *tle = (TargetEntry *)node;
+ if (isIvmName(tle->resname))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("column name %s is not supported on incrementally maintainable materialized view", tle->resname)));
+
+ expression_tree_walker(node, check_ivm_restriction_walker, NULL);
+ break;
+ }
+ case T_JoinExpr:
+ {
+ JoinExpr *joinexpr = (JoinExpr *)node;
+
+ if (joinexpr->jointype > JOIN_INNER)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("OUTER JOIN is not supported on incrementally maintainable materialized view")));
+
+ expression_tree_walker(node, check_ivm_restriction_walker, NULL);
+ }
+ break;
+ case T_Aggref:
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("aggregate function is not supported on incrementally maintainable materialized view")));
+ break;
+ default:
+ expression_tree_walker(node, check_ivm_restriction_walker, (void *) context);
+ break;
+ }
+ return false;
+}
+
+/*
+ * CreateIndexOnIMMV
+ *
+ * Create a unique index on incremental maintainable materialized view.
+ * If the view definition query has a GROUP BY clause, the index is created
+ * on the columns of GROUP BY expressions. Otherwise, if the view contains
+ * all primary key attritubes of its base tables in the target list, the index
+ * is created on these attritubes. In other cases, no index is created.
+ */
+void
+CreateIndexOnIMMV(Query *query, Relation matviewRel)
+{
+ ListCell *lc;
+ IndexStmt *index;
+ ObjectAddress address;
+ List *constraintList = NIL;
+ char idxname[NAMEDATALEN];
+ List *indexoidlist = RelationGetIndexList(matviewRel);
+ ListCell *indexoidscan;
+ Bitmapset *key_attnos;
+
+ snprintf(idxname, sizeof(idxname), "%s_index", RelationGetRelationName(matviewRel));
+
+ index = makeNode(IndexStmt);
+
+ index->unique = true;
+ index->primary = false;
+ index->isconstraint = false;
+ index->deferrable = false;
+ index->initdeferred = false;
+ index->idxname = idxname;
+ index->relation =
+ makeRangeVar(get_namespace_name(RelationGetNamespace(matviewRel)),
+ pstrdup(RelationGetRelationName(matviewRel)),
+ -1);
+ index->accessMethod = DEFAULT_INDEX_TYPE;
+ index->options = NIL;
+ index->tableSpace = get_tablespace_name(matviewRel->rd_rel->reltablespace);
+ index->whereClause = NULL;
+ index->indexParams = NIL;
+ index->indexIncludingParams = NIL;
+ index->excludeOpNames = NIL;
+ index->idxcomment = NULL;
+ index->indexOid = InvalidOid;
+ index->oldNumber = InvalidRelFileNumber;
+ index->oldCreateSubid = InvalidSubTransactionId;
+ index->oldFirstRelfilelocatorSubid = InvalidSubTransactionId;
+ index->transformed = true;
+ index->concurrent = false;
+ index->if_not_exists = false;
+
+ /* create index on the base tables' primary key columns */
+ key_attnos = get_primary_key_attnos_from_query(query, &constraintList);
+ if (key_attnos)
+ {
+ foreach(lc, query->targetList)
+ {
+ TargetEntry *tle = (TargetEntry *) lfirst(lc);
+ Form_pg_attribute attr = TupleDescAttr(matviewRel->rd_att, tle->resno - 1);
+
+ if (bms_is_member(tle->resno - FirstLowInvalidHeapAttributeNumber, key_attnos))
+ {
+ IndexElem *iparam;
+
+ iparam = makeNode(IndexElem);
+ iparam->name = pstrdup(NameStr(attr->attname));
+ iparam->expr = NULL;
+ iparam->indexcolname = NULL;
+ iparam->collation = NIL;
+ iparam->opclass = NIL;
+ iparam->opclassopts = NIL;
+ iparam->ordering = SORTBY_DEFAULT;
+ iparam->nulls_ordering = SORTBY_NULLS_DEFAULT;
+ index->indexParams = lappend(index->indexParams, iparam);
+ }
+ }
+ }
+ else
+ {
+ /* create no index, just notice that an appropriate index is necessary for efficient IVM */
+ ereport(NOTICE,
+ (errmsg("could not create an index on materialized view \"%s\" automatically",
+ RelationGetRelationName(matviewRel)),
+ errdetail("This target list does not have all the primary key columns. "),
+ errhint("Create an index on the materialized view for efficient incremental maintenance.")));
+ return;
+ }
+
+ /* If we have a compatible index, we don't need to create another. */
+ foreach(indexoidscan, indexoidlist)
+ {
+ Oid indexoid = lfirst_oid(indexoidscan);
+ Relation indexRel;
+ bool hasCompatibleIndex = false;
+
+ indexRel = index_open(indexoid, AccessShareLock);
+
+ if (CheckIndexCompatible(indexRel->rd_id,
+ index->accessMethod,
+ index->indexParams,
+ index->excludeOpNames,
+ false))
+ hasCompatibleIndex = true;
+
+ index_close(indexRel, AccessShareLock);
+
+ if (hasCompatibleIndex)
+ return;
+ }
+
+ address = DefineIndex(RelationGetRelid(matviewRel),
+ index,
+ InvalidOid,
+ InvalidOid,
+ InvalidOid,
+ -1,
+ false, true, false, false, true);
+
+ ereport(NOTICE,
+ (errmsg("created index \"%s\" on materialized view \"%s\"",
+ idxname, RelationGetRelationName(matviewRel))));
+
+ /*
+ * Make dependencies so that the index is dropped if any base tables's
+ * primary key is dropped.
+ */
+ foreach(lc, constraintList)
+ {
+ Oid constraintOid = lfirst_oid(lc);
+ ObjectAddress refaddr;
+
+ refaddr.classId = ConstraintRelationId;
+ refaddr.objectId = constraintOid;
+ refaddr.objectSubId = 0;
+
+ recordDependencyOn(&address, &refaddr, DEPENDENCY_NORMAL);
+ }
+}
+
+
+/*
+ * get_primary_key_attnos_from_query
+ *
+ * Identify the columns in base tables' primary keys in the target list.
+ *
+ * Returns a Bitmapset of the column attnos of the primary key's columns of
+ * tables that used in the query. The attnos are offset by
+ * FirstLowInvalidHeapAttributeNumber as same as get_primary_key_attnos.
+ *
+ * If any table has no primary key or any primary key's columns is not in
+ * the target list, return NULL. We also return NULL if any pkey constraint
+ * is deferrable.
+ *
+ * constraintList is set to a list of the OIDs of the pkey constraints.
+ */
+static Bitmapset *
+get_primary_key_attnos_from_query(Query *query, List **constraintList)
+{
+ List *key_attnos_list = NIL;
+ ListCell *lc;
+ int i;
+ Bitmapset *keys = NULL;
+ Relids rels_in_from;
+
+ /*
+ * Collect primary key attributes from all tables used in query. The key attributes
+ * sets for each table are stored in key_attnos_list in order by RTE index.
+ */
+ foreach(lc, query->rtable)
+ {
+ RangeTblEntry *r = (RangeTblEntry*) lfirst(lc);
+ Bitmapset *key_attnos;
+ bool has_pkey = true;
+
+ /* for tables, call get_primary_key_attnos */
+ if (r->rtekind == RTE_RELATION)
+ {
+ Oid constraintOid;
+ key_attnos = get_primary_key_attnos(r->relid, false, &constraintOid);
+ *constraintList = lappend_oid(*constraintList, constraintOid);
+ has_pkey = (key_attnos != NULL);
+ }
+ /* for other RTEs, store NULL into key_attnos_list */
+ else
+ key_attnos = NULL;
+
+ /*
+ * If any table or subquery has no primary key or its pkey constraint is deferrable,
+ * we cannot get key attributes for this query, so return NULL.
+ */
+ if (!has_pkey)
+ return NULL;
+
+ key_attnos_list = lappend(key_attnos_list, key_attnos);
+ }
+
+ /* Collect key attributes appearing in the target list */
+ i = 1;
+ foreach(lc, query->targetList)
+ {
+ TargetEntry *tle = (TargetEntry *) flatten_join_alias_vars(NULL, query, lfirst(lc));
+
+ if (IsA(tle->expr, Var))
+ {
+ Var *var = (Var*) tle->expr;
+ Bitmapset *key_attnos = list_nth(key_attnos_list, var->varno - 1);
+
+ /* check if this attribute is from a base table's primary key */
+ if (bms_is_member(var->varattno - FirstLowInvalidHeapAttributeNumber, key_attnos))
+ {
+ /*
+ * Remove found key attributes from key_attnos_list, and add this
+ * to the result list.
+ */
+ key_attnos = bms_del_member(key_attnos, var->varattno - FirstLowInvalidHeapAttributeNumber);
+ if (bms_is_empty(key_attnos))
+ {
+ key_attnos_list = list_delete_nth_cell(key_attnos_list, var->varno - 1);
+ key_attnos_list = list_insert_nth(key_attnos_list, var->varno - 1, NULL);
+ }
+ keys = bms_add_member(keys, i - FirstLowInvalidHeapAttributeNumber);
+ }
+ }
+ i++;
+ }
+
+ /* Collect RTE indexes of relations appearing in the FROM clause */
+ rels_in_from = get_relids_in_jointree((Node *) query->jointree, false, false);
+
+ /*
+ * Check if all key attributes of relations in FROM are appearing in the target
+ * list. If an attribute remains in key_attnos_list in spite of the table is used
+ * in FROM clause, the target is missing this key attribute, so we return NULL.
+ */
+ i = 1;
+ foreach(lc, key_attnos_list)
+ {
+ Bitmapset *bms = (Bitmapset *)lfirst(lc);
+ if (!bms_is_empty(bms) && bms_is_member(i, rels_in_from))
+ return NULL;
+ i++;
+ }
+
+ return keys;
+}
diff --git a/src/backend/commands/matview.c b/src/backend/commands/matview.c
index 6d09b75556..1061c37b2c 100644
--- a/src/backend/commands/matview.c
+++ b/src/backend/commands/matview.c
@@ -23,23 +23,35 @@
#include "catalog/indexing.h"
#include "catalog/namespace.h"
#include "catalog/pg_am.h"
+#include "catalog/pg_depend.h"
+#include "catalog/pg_trigger.h"
#include "catalog/pg_opclass.h"
#include "commands/cluster.h"
#include "commands/matview.h"
#include "commands/tablecmds.h"
#include "commands/tablespace.h"
+#include "commands/createas.h"
#include "executor/executor.h"
#include "executor/spi.h"
+#include "executor/tstoreReceiver.h"
#include "miscadmin.h"
+#include "nodes/makefuncs.h"
+#include "parser/analyze.h"
+#include "parser/parse_clause.h"
+#include "parser/parse_func.h"
+#include "parser/parse_relation.h"
#include "pgstat.h"
#include "rewrite/rewriteHandler.h"
+#include "rewrite/rowsecurity.h"
#include "storage/lmgr.h"
#include "tcop/tcopprot.h"
#include "utils/builtins.h"
+#include "utils/fmgroids.h"
#include "utils/lsyscache.h"
#include "utils/rel.h"
#include "utils/snapmgr.h"
#include "utils/syscache.h"
+#include "utils/typcache.h"
typedef struct
@@ -53,6 +65,52 @@ typedef struct
BulkInsertState bistate; /* bulk insert state */
} DR_transientrel;
+#define MV_INIT_QUERYHASHSIZE 16
+
+/*
+ * MV_TriggerHashEntry
+ *
+ * Hash entry for base tables on which IVM trigger is invoked
+ */
+typedef struct MV_TriggerHashEntry
+{
+ Oid matview_id; /* OID of the materialized view */
+ int before_trig_count; /* count of before triggers invoked */
+ int after_trig_count; /* count of after triggers invoked */
+
+ Snapshot snapshot; /* Snapshot just before table change */
+
+ List *tables; /* List of MV_TriggerTable */
+ bool has_old; /* tuples are deleted from any table? */
+ bool has_new; /* tuples are inserted into any table? */
+} MV_TriggerHashEntry;
+
+/*
+ * MV_TriggerTable
+ *
+ * IVM related data for tables on which the trigger is invoked.
+ */
+typedef struct MV_TriggerTable
+{
+ Oid table_id; /* OID of the modified table */
+ List *old_tuplestores; /* tuplestores for deleted tuples */
+ List *new_tuplestores; /* tuplestores for inserted tuples */
+
+ List *rte_indexes; /* List of RTE index of the modified table */
+ RangeTblEntry *original_rte; /* the original RTE saved before rewriting query */
+
+ Relation rel; /* relation of the modified table */
+ TupleTableSlot *slot; /* for checking visibility in the pre-state table */
+} MV_TriggerTable;
+
+static HTAB *mv_trigger_info = NULL;
+
+static bool in_delta_calculation = false;
+
+/* ENR name for materialized view delta */
+#define NEW_DELTA_ENRNAME "new_delta"
+#define OLD_DELTA_ENRNAME "old_delta"
+
static int matview_maintenance_depth = 0;
static void transientrel_startup(DestReceiver *self, int operation, TupleDesc typeinfo);
@@ -60,7 +118,9 @@ static bool transientrel_receive(TupleTableSlot *slot, DestReceiver *self);
static void transientrel_shutdown(DestReceiver *self);
static void transientrel_destroy(DestReceiver *self);
static uint64 refresh_matview_datafill(DestReceiver *dest, Query *query,
- const char *queryString);
+ QueryEnvironment *queryEnv,
+ TupleDesc *resultTupleDesc,
+ const char *queryString);
static char *make_temptable_name_n(char *tempname, int n);
static void refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
int save_sec_context);
@@ -68,6 +128,37 @@ static void refresh_by_heap_swap(Oid matviewOid, Oid OIDNewHeap, char relpersist
static bool is_usable_unique_index(Relation indexRel);
static void OpenMatViewIncrementalMaintenance(void);
static void CloseMatViewIncrementalMaintenance(void);
+static Query *get_matview_query(Relation matviewRel);
+
+static Query *rewrite_query_for_preupdate_state(Query *query, List *tables,
+ ParseState *pstate, Oid matviewid);
+static void register_delta_ENRs(ParseState *pstate, Query *query, List *tables);
+static char *make_delta_enr_name(const char *prefix, Oid relid, int count);
+static RangeTblEntry *get_prestate_rte(RangeTblEntry *rte, MV_TriggerTable *table,
+ QueryEnvironment *queryEnv, Oid matviewid);
+static RangeTblEntry *replace_rte_with_delta(RangeTblEntry *rte, MV_TriggerTable *table, bool is_new,
+ QueryEnvironment *queryEnv);
+static Query *rewrite_query_for_counting(Query *query, ParseState *pstate);
+
+static void calc_delta(MV_TriggerTable *table, int rte_index, Query *query,
+ DestReceiver *dest_old, DestReceiver *dest_new,
+ TupleDesc *tupdesc_old, TupleDesc *tupdesc_new,
+ QueryEnvironment *queryEnv);
+static Query *rewrite_query_for_postupdate_state(Query *query, MV_TriggerTable *table, int rte_index);
+
+static void apply_delta(Oid matviewOid, Tuplestorestate *old_tuplestores, Tuplestorestate *new_tuplestores,
+ TupleDesc tupdesc_old, TupleDesc tupdesc_new,
+ Query *query);
+static void apply_old_delta(const char *matviewname, const char *deltaname_old,
+ List *keys);
+static void apply_new_delta(const char *matviewname, const char *deltaname_new,
+ StringInfo target_list);
+static char *get_matching_condition_string(List *keys);
+static void generate_equal(StringInfo querybuf, Oid opttype,
+ const char *leftop, const char *rightop);
+
+static void mv_InitHashTables(void);
+static void clean_up_IVM_hash_entry(MV_TriggerHashEntry *entry, bool is_abort);
/*
* SetMatViewPopulatedState
@@ -109,6 +200,46 @@ SetMatViewPopulatedState(Relation relation, bool newstate)
CommandCounterIncrement();
}
+/*
+ * SetMatViewIVMState
+ * Mark a materialized view as IVM, or not.
+ *
+ * NOTE: caller must be holding an appropriate lock on the relation.
+ */
+void
+SetMatViewIVMState(Relation relation, bool newstate)
+{
+ Relation pgrel;
+ HeapTuple tuple;
+
+ Assert(relation->rd_rel->relkind == RELKIND_MATVIEW);
+
+ /*
+ * Update relation's pg_class entry. Crucial side-effect: other backends
+ * (and this one too!) are sent SI message to make them rebuild relcache
+ * entries.
+ */
+ pgrel = table_open(RelationRelationId, RowExclusiveLock);
+ tuple = SearchSysCacheCopy1(RELOID,
+ ObjectIdGetDatum(RelationGetRelid(relation)));
+ if (!HeapTupleIsValid(tuple))
+ elog(ERROR, "cache lookup failed for relation %u",
+ RelationGetRelid(relation));
+
+ ((Form_pg_class) GETSTRUCT(tuple))->relisivm = newstate;
+
+ CatalogTupleUpdate(pgrel, &tuple->t_self, tuple);
+
+ heap_freetuple(tuple);
+ table_close(pgrel, RowExclusiveLock);
+
+ /*
+ * Advance command counter to make the updated pg_class row locally
+ * visible.
+ */
+ CommandCounterIncrement();
+}
+
/*
* ExecRefreshMatView -- execute a REFRESH MATERIALIZED VIEW command
*
@@ -135,8 +266,6 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
{
Oid matviewOid;
Relation matviewRel;
- RewriteRule *rule;
- List *actions;
Query *dataQuery;
Oid tableSpace;
Oid relowner;
@@ -150,6 +279,7 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
int save_sec_context;
int save_nestlevel;
ObjectAddress address;
+ bool oldPopulated;
/* Determine strength of lock needed. */
concurrent = stmt->concurrent;
@@ -176,6 +306,8 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
save_nestlevel = NewGUCNestLevel();
RestrictSearchPath();
+ oldPopulated = RelationIsPopulated(matviewRel);
+
/* Make sure it is a materialized view. */
if (matviewRel->rd_rel->relkind != RELKIND_MATVIEW)
ereport(ERROR,
@@ -196,32 +328,9 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
errmsg("%s and %s options cannot be used together",
"CONCURRENTLY", "WITH NO DATA")));
- /*
- * Check that everything is correct for a refresh. Problems at this point
- * are internal errors, so elog is sufficient.
- */
- if (matviewRel->rd_rel->relhasrules == false ||
- matviewRel->rd_rules->numLocks < 1)
- elog(ERROR,
- "materialized view \"%s\" is missing rewrite information",
- RelationGetRelationName(matviewRel));
-
- if (matviewRel->rd_rules->numLocks > 1)
- elog(ERROR,
- "materialized view \"%s\" has too many rules",
- RelationGetRelationName(matviewRel));
- rule = matviewRel->rd_rules->rules[0];
- if (rule->event != CMD_SELECT || !(rule->isInstead))
- elog(ERROR,
- "the rule for materialized view \"%s\" is not a SELECT INSTEAD OF rule",
- RelationGetRelationName(matviewRel));
+ dataQuery = get_matview_query(matviewRel);
- actions = rule->actions;
- if (list_length(actions) != 1)
- elog(ERROR,
- "the rule for materialized view \"%s\" is not a single action",
- RelationGetRelationName(matviewRel));
/*
* Check that there is a unique index with no WHERE clause on one or more
@@ -256,12 +365,6 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
errhint("Create a unique index with no WHERE clause on one or more columns of the materialized view.")));
}
- /*
- * The stored query was rewritten at the time of the MV definition, but
- * has not been scribbled on by the planner.
- */
- dataQuery = linitial_node(Query, actions);
-
/*
* Check for active uses of the relation in the current transaction, such
* as open scans.
@@ -289,6 +392,74 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
relpersistence = matviewRel->rd_rel->relpersistence;
}
+ /* delete IMMV triggers. */
+ if (RelationIsIVM(matviewRel) && stmt->skipData )
+ {
+ Relation tgRel;
+ Relation depRel;
+ ScanKeyData key;
+ SysScanDesc scan;
+ HeapTuple tup;
+ ObjectAddresses *immv_triggers;
+
+ immv_triggers = new_object_addresses();
+
+ tgRel = table_open(TriggerRelationId, RowExclusiveLock);
+ depRel = table_open(DependRelationId, RowExclusiveLock);
+
+ /* search triggers that depends on IMMV. */
+ ScanKeyInit(&key,
+ Anum_pg_depend_refobjid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(matviewOid));
+ scan = systable_beginscan(depRel, DependReferenceIndexId, true,
+ NULL, 1, &key);
+ while ((tup = systable_getnext(scan)) != NULL)
+ {
+ ObjectAddress obj;
+ Form_pg_depend foundDep = (Form_pg_depend) GETSTRUCT(tup);
+
+ if (foundDep->classid == TriggerRelationId)
+ {
+ HeapTuple tgtup;
+ ScanKeyData tgkey[1];
+ SysScanDesc tgscan;
+ Form_pg_trigger tgform;
+
+ /* Find the trigger name. */
+ ScanKeyInit(&tgkey[0],
+ Anum_pg_trigger_oid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(foundDep->objid));
+
+ tgscan = systable_beginscan(tgRel, TriggerOidIndexId, true,
+ NULL, 1, tgkey);
+ tgtup = systable_getnext(tgscan);
+ if (!HeapTupleIsValid(tgtup))
+ elog(ERROR, "could not find tuple for immv trigger %u", foundDep->objid);
+
+ tgform = (Form_pg_trigger) GETSTRUCT(tgtup);
+
+ /* If trigger is created by IMMV, delete it. */
+ if (strncmp(NameStr(tgform->tgname), "IVM_trigger_", 12) == 0)
+ {
+ obj.classId = foundDep->classid;
+ obj.objectId = foundDep->objid;
+ obj.objectSubId = foundDep->refobjsubid;
+ add_exact_object_address(&obj, immv_triggers);
+ }
+ systable_endscan(tgscan);
+ }
+ }
+ systable_endscan(scan);
+
+ performMultipleDeletions(immv_triggers, DROP_RESTRICT, PERFORM_DELETION_INTERNAL);
+
+ table_close(depRel, RowExclusiveLock);
+ table_close(tgRel, RowExclusiveLock);
+ free_object_addresses(immv_triggers);
+ }
+
/*
* Create the transient table that will receive the regenerated data. Lock
* it against access by any other process until commit (by which time it
@@ -302,7 +473,7 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
/* Generate the data, if wanted. */
if (!stmt->skipData)
- processed = refresh_matview_datafill(dest, dataQuery, queryString);
+ processed = refresh_matview_datafill(dest, dataQuery, NULL, NULL, queryString);
/* Make the matview match the newly generated data. */
if (concurrent)
@@ -337,6 +508,12 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
pgstat_count_heap_insert(matviewRel, processed);
}
+ if (!stmt->skipData && RelationIsIVM(matviewRel) && !oldPopulated)
+ {
+ CreateIndexOnIMMV(dataQuery, matviewRel);
+ CreateIvmTriggersOnBaseTables(dataQuery, matviewOid);
+ }
+
table_close(matviewRel, NoLock);
/* Roll back any GUC changes */
@@ -371,6 +548,8 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
*/
static uint64
refresh_matview_datafill(DestReceiver *dest, Query *query,
+ QueryEnvironment *queryEnv,
+ TupleDesc *resultTupleDesc,
const char *queryString)
{
List *rewritten;
@@ -407,7 +586,7 @@ refresh_matview_datafill(DestReceiver *dest, Query *query,
/* Create a QueryDesc, redirecting output to our tuple receiver */
queryDesc = CreateQueryDesc(plan, queryString,
GetActiveSnapshot(), InvalidSnapshot,
- dest, NULL, NULL, 0);
+ dest, NULL, queryEnv ? queryEnv: NULL, 0);
/* call ExecutorStart to prepare the plan for execution */
ExecutorStart(queryDesc, 0);
@@ -417,6 +596,9 @@ refresh_matview_datafill(DestReceiver *dest, Query *query,
processed = queryDesc->estate->es_processed;
+ if (resultTupleDesc)
+ *resultTupleDesc = CreateTupleDescCopy(queryDesc->tupDesc);
+
/* and clean up */
ExecutorFinish(queryDesc);
ExecutorEnd(queryDesc);
@@ -950,3 +1132,1219 @@ CloseMatViewIncrementalMaintenance(void)
matview_maintenance_depth--;
Assert(matview_maintenance_depth >= 0);
}
+
+/*
+ * get_matview_query - get the Query from a matview's _RETURN rule.
+ */
+static Query *
+get_matview_query(Relation matviewRel)
+{
+ RewriteRule *rule;
+ List * actions;
+
+ /*
+ * Check that everything is correct for a refresh. Problems at this point
+ * are internal errors, so elog is sufficient.
+ */
+ if (matviewRel->rd_rel->relhasrules == false ||
+ matviewRel->rd_rules->numLocks < 1)
+ elog(ERROR,
+ "materialized view \"%s\" is missing rewrite information",
+ RelationGetRelationName(matviewRel));
+
+ if (matviewRel->rd_rules->numLocks > 1)
+ elog(ERROR,
+ "materialized view \"%s\" has too many rules",
+ RelationGetRelationName(matviewRel));
+
+ rule = matviewRel->rd_rules->rules[0];
+ if (rule->event != CMD_SELECT || !(rule->isInstead))
+ elog(ERROR,
+ "the rule for materialized view \"%s\" is not a SELECT INSTEAD OF rule",
+ RelationGetRelationName(matviewRel));
+
+ actions = rule->actions;
+ if (list_length(actions) != 1)
+ elog(ERROR,
+ "the rule for materialized view \"%s\" is not a single action",
+ RelationGetRelationName(matviewRel));
+
+ /*
+ * The stored query was rewritten at the time of the MV definition, but
+ * has not been scribbled on by the planner.
+ */
+ return linitial_node(Query, actions);
+}
+
+
+/* ----------------------------------------------------
+ * Incremental View Maintenance routines
+ * ---------------------------------------------------
+ */
+
+/*
+ * IVM_immediate_before
+ *
+ * IVM trigger function invoked before base table is modified. If this is
+ * invoked firstly in the same statement, we save the transaction id and the
+ * command id at that time.
+ */
+Datum
+IVM_immediate_before(PG_FUNCTION_ARGS)
+{
+ TriggerData *trigdata = (TriggerData *) fcinfo->context;
+ char *matviewOid_text = trigdata->tg_trigger->tgargs[0];
+ char *ex_lock_text = trigdata->tg_trigger->tgargs[1];
+ Oid matviewOid;
+ MV_TriggerHashEntry *entry;
+ bool found;
+ bool ex_lock;
+
+ matviewOid = DatumGetObjectId(DirectFunctionCall1(oidin, CStringGetDatum(matviewOid_text)));
+ ex_lock = DatumGetBool(DirectFunctionCall1(boolin, CStringGetDatum(ex_lock_text)));
+
+ /* If the view has more than one tables, we have to use an exclusive lock. */
+ if (ex_lock)
+ {
+ /*
+ * Wait for concurrent transactions which update this materialized view at
+ * READ COMMITED. This is needed to see changes committed in other
+ * transactions. No wait and raise an error at REPEATABLE READ or
+ * SERIALIZABLE to prevent update anomalies of matviews.
+ * XXX: dead-lock is possible here.
+ */
+ if (!IsolationUsesXactSnapshot())
+ LockRelationOid(matviewOid, ExclusiveLock);
+ else if (!ConditionalLockRelationOid(matviewOid, ExclusiveLock))
+ {
+ /* try to throw error by name; relation could be deleted... */
+ char *relname = get_rel_name(matviewOid);
+
+ if (!relname)
+ ereport(ERROR,
+ (errcode(ERRCODE_LOCK_NOT_AVAILABLE),
+ errmsg("could not obtain lock on materialized view during incremental maintenance")));
+
+ ereport(ERROR,
+ (errcode(ERRCODE_LOCK_NOT_AVAILABLE),
+ errmsg("could not obtain lock on materialized view \"%s\" during incremental maintenance",
+ relname)));
+ }
+ }
+ else
+ LockRelationOid(matviewOid, RowExclusiveLock);
+
+ /*
+ * On the first call initialize the hashtable
+ */
+ if (!mv_trigger_info)
+ mv_InitHashTables();
+
+ entry = (MV_TriggerHashEntry *) hash_search(mv_trigger_info,
+ (void *) &matviewOid,
+ HASH_ENTER, &found);
+
+ /* On the first BEFORE to update the view, initialize trigger data */
+ if (!found)
+ {
+ /*
+ * Get a snapshot just before the table was modified for checking
+ * tuple visibility in the pre-update state of the table.
+ */
+ Snapshot snapshot = GetActiveSnapshot();
+
+ entry->matview_id = matviewOid;
+ entry->before_trig_count = 0;
+ entry->after_trig_count = 0;
+ entry->snapshot = RegisterSnapshot(snapshot);
+ entry->tables = NIL;
+ entry->has_old = false;
+ entry->has_new = false;
+ }
+
+ entry->before_trig_count++;
+
+ return PointerGetDatum(NULL);
+}
+
+/*
+ * IVM_immediate_maintenance
+ *
+ * IVM trigger function invoked after base table is modified.
+ * For each table, tuplestores of transition tables are collected.
+ * and after the last modification
+ */
+Datum
+IVM_immediate_maintenance(PG_FUNCTION_ARGS)
+{
+ TriggerData *trigdata = (TriggerData *) fcinfo->context;
+ Relation rel;
+ Oid relid;
+ Oid matviewOid;
+ Query *query;
+ Query *rewritten = NULL;
+ char *matviewOid_text = trigdata->tg_trigger->tgargs[0];
+ Relation matviewRel;
+ int old_depth = matview_maintenance_depth;
+
+ Oid relowner;
+ Tuplestorestate *old_tuplestore = NULL;
+ Tuplestorestate *new_tuplestore = NULL;
+ DestReceiver *dest_new = NULL, *dest_old = NULL;
+ Oid save_userid;
+ int save_sec_context;
+ int save_nestlevel;
+
+ MV_TriggerHashEntry *entry;
+ MV_TriggerTable *table;
+ bool found;
+
+ ParseState *pstate;
+ QueryEnvironment *queryEnv = create_queryEnv();
+ MemoryContext oldcxt;
+ ListCell *lc;
+ int i;
+
+
+ /* Create a ParseState for rewriting the view definition query */
+ pstate = make_parsestate(NULL);
+ pstate->p_queryEnv = queryEnv;
+ pstate->p_expr_kind = EXPR_KIND_SELECT_TARGET;
+
+ rel = trigdata->tg_relation;
+ relid = rel->rd_id;
+
+ matviewOid = DatumGetObjectId(DirectFunctionCall1(oidin, CStringGetDatum(matviewOid_text)));
+
+ /*
+ * On the first call initialize the hashtable
+ */
+ if (!mv_trigger_info)
+ mv_InitHashTables();
+
+ /* get the entry for this materialized view */
+ entry = (MV_TriggerHashEntry *) hash_search(mv_trigger_info,
+ (void *) &matviewOid,
+ HASH_FIND, &found);
+ Assert (found && entry != NULL);
+ entry->after_trig_count++;
+
+ /* search the entry for the modified table and create new entry if not found */
+ found = false;
+ foreach(lc, entry->tables)
+ {
+ table = (MV_TriggerTable *) lfirst(lc);
+ if (table->table_id == relid)
+ {
+ found = true;
+ break;
+ }
+ }
+ if (!found)
+ {
+ oldcxt = MemoryContextSwitchTo(TopTransactionContext);
+
+ table = (MV_TriggerTable *) palloc0(sizeof(MV_TriggerTable));
+ table->table_id = relid;
+ table->old_tuplestores = NIL;
+ table->new_tuplestores = NIL;
+ table->rte_indexes = NIL;
+ table->slot = MakeSingleTupleTableSlot(RelationGetDescr(rel), table_slot_callbacks(rel));
+ table->rel = table_open(RelationGetRelid(rel), NoLock);
+ entry->tables = lappend(entry->tables, table);
+
+ MemoryContextSwitchTo(oldcxt);
+ }
+
+ /* Save the transition tables and make a request to not free immediately */
+ if (trigdata->tg_oldtable)
+ {
+ oldcxt = MemoryContextSwitchTo(TopTransactionContext);
+ table->old_tuplestores = lappend(table->old_tuplestores, trigdata->tg_oldtable);
+ entry->has_old = true;
+ MemoryContextSwitchTo(oldcxt);
+ }
+ if (trigdata->tg_newtable)
+ {
+ oldcxt = MemoryContextSwitchTo(TopTransactionContext);
+ table->new_tuplestores = lappend(table->new_tuplestores, trigdata->tg_newtable);
+ entry->has_new = true;
+ MemoryContextSwitchTo(oldcxt);
+ }
+ if (entry->has_new || entry->has_old)
+ {
+ CmdType cmd;
+
+ if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event))
+ cmd = CMD_INSERT;
+ else if (TRIGGER_FIRED_BY_DELETE(trigdata->tg_event))
+ cmd = CMD_DELETE;
+ else if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
+ cmd = CMD_UPDATE;
+ else
+ elog(ERROR,"unsupported trigger type");
+
+ /* Prolong lifespan of transition tables to the end of the last AFTER trigger */
+ SetTransitionTablePreserved(relid, cmd);
+ }
+
+
+ /* If this is not the last AFTER trigger call, immediately exit. */
+ Assert (entry->before_trig_count >= entry->after_trig_count);
+ if (entry->before_trig_count != entry->after_trig_count)
+ return PointerGetDatum(NULL);
+
+ /*
+ * If this is the last AFTER trigger call, continue and update the view.
+ */
+
+ /*
+ * Advance command counter to make the updated base table row locally
+ * visible.
+ */
+ CommandCounterIncrement();
+
+ matviewRel = table_open(matviewOid, NoLock);
+
+ /* Make sure it is a materialized view. */
+ Assert(matviewRel->rd_rel->relkind == RELKIND_MATVIEW);
+
+ /*
+ * Get and push the latast snapshot to see any changes which is committed
+ * during waiting in other transactions at READ COMMITTED level.
+ */
+ PushActiveSnapshot(GetTransactionSnapshot());
+
+ /*
+ * Check for active uses of the relation in the current transaction, such
+ * as open scans.
+ *
+ * NB: We count on this to protect us against problems with refreshing the
+ * data using TABLE_INSERT_FROZEN.
+ */
+ CheckTableNotInUse(matviewRel, "refresh a materialized view incrementally");
+
+ /*
+ * Switch to the owner's userid, so that any functions are run as that
+ * user. Also arrange to make GUC variable changes local to this command.
+ * We will switch modes when we are about to execute user code.
+ */
+ relowner = matviewRel->rd_rel->relowner;
+ GetUserIdAndSecContext(&save_userid, &save_sec_context);
+ SetUserIdAndSecContext(relowner,
+ save_sec_context | SECURITY_RESTRICTED_OPERATION);
+ save_nestlevel = NewGUCNestLevel();
+
+ /* get view query*/
+ query = get_matview_query(matviewRel);
+
+ /*
+ * When a base table is truncated, the view content will be empty if the
+ * view definition query does not contain an aggregate without a GROUP clause.
+ * Therefore, such views can be truncated.
+ */
+ if (TRIGGER_FIRED_BY_TRUNCATE(trigdata->tg_event))
+ {
+ ExecuteTruncateGuts(list_make1(matviewRel), list_make1_oid(matviewOid),
+ NIL, DROP_RESTRICT, false, false);
+
+ /* Clean up hash entry and delete tuplestores */
+ clean_up_IVM_hash_entry(entry, false);
+
+ /* Pop the original snapshot. */
+ PopActiveSnapshot();
+
+ table_close(matviewRel, NoLock);
+
+ /* Roll back any GUC changes */
+ AtEOXact_GUC(false, save_nestlevel);
+
+ /* Restore userid and security context */
+ SetUserIdAndSecContext(save_userid, save_sec_context);
+
+ return PointerGetDatum(NULL);
+ }
+
+ /*
+ * rewrite query for calculating deltas
+ */
+
+ rewritten = copyObject(query);
+
+ /* Replace resnames in a target list with materialized view's attnames */
+ i = 0;
+ foreach (lc, rewritten->targetList)
+ {
+ TargetEntry *tle = (TargetEntry *) lfirst(lc);
+ Form_pg_attribute attr = TupleDescAttr(matviewRel->rd_att, i);
+ char *resname = NameStr(attr->attname);
+
+ tle->resname = pstrdup(resname);
+ i++;
+ }
+
+ /* Set all tables in the query to pre-update state */
+ rewritten = rewrite_query_for_preupdate_state(rewritten, entry->tables,
+ pstate, matviewOid);
+ /* Rewrite for counting duplicated tuples */
+ rewritten = rewrite_query_for_counting(rewritten, pstate);
+
+ /* Create tuplestores to store view deltas */
+ if (entry->has_old)
+ {
+ oldcxt = MemoryContextSwitchTo(TopTransactionContext);
+
+ old_tuplestore = tuplestore_begin_heap(false, false, work_mem);
+ dest_old = CreateDestReceiver(DestTuplestore);
+ SetTuplestoreDestReceiverParams(dest_old,
+ old_tuplestore,
+ TopTransactionContext,
+ false,
+ NULL,
+ NULL);
+
+ MemoryContextSwitchTo(oldcxt);
+ }
+ if (entry->has_new)
+ {
+ oldcxt = MemoryContextSwitchTo(TopTransactionContext);
+
+ new_tuplestore = tuplestore_begin_heap(false, false, work_mem);
+ dest_new = CreateDestReceiver(DestTuplestore);
+ SetTuplestoreDestReceiverParams(dest_new,
+ new_tuplestore,
+ TopTransactionContext,
+ false,
+ NULL,
+ NULL);
+ MemoryContextSwitchTo(oldcxt);
+ }
+
+ /* for all modified tables */
+ foreach(lc, entry->tables)
+ {
+ ListCell *lc2;
+
+ table = (MV_TriggerTable *) lfirst(lc);
+
+ /* loop for self-join */
+ foreach(lc2, table->rte_indexes)
+ {
+ int rte_index = lfirst_int(lc2);
+ TupleDesc tupdesc_old;
+ TupleDesc tupdesc_new;
+
+ /* calculate delta tables */
+ calc_delta(table, rte_index, rewritten, dest_old, dest_new,
+ &tupdesc_old, &tupdesc_new, queryEnv);
+
+ /* Set the table in the query to post-update state */
+ rewritten = rewrite_query_for_postupdate_state(rewritten, table, rte_index);
+
+ PG_TRY();
+ {
+ /* apply the delta tables to the materialized view */
+ apply_delta(matviewOid, old_tuplestore, new_tuplestore,
+ tupdesc_old, tupdesc_new, query);
+ }
+ PG_CATCH();
+ {
+ matview_maintenance_depth = old_depth;
+ PG_RE_THROW();
+ }
+ PG_END_TRY();
+
+ /* clear view delta tuplestores */
+ if (old_tuplestore)
+ tuplestore_clear(old_tuplestore);
+ if (new_tuplestore)
+ tuplestore_clear(new_tuplestore);
+ }
+ }
+
+ /* Clean up hash entry and delete tuplestores */
+ clean_up_IVM_hash_entry(entry, false);
+ if (old_tuplestore)
+ {
+ dest_old->rDestroy(dest_old);
+ tuplestore_end(old_tuplestore);
+ }
+ if (new_tuplestore)
+ {
+ dest_new->rDestroy(dest_new);
+ tuplestore_end(new_tuplestore);
+ }
+
+ /* Pop the original snapshot. */
+ PopActiveSnapshot();
+
+ table_close(matviewRel, NoLock);
+
+ /* Roll back any GUC changes */
+ AtEOXact_GUC(false, save_nestlevel);
+
+ /* Restore userid and security context */
+ SetUserIdAndSecContext(save_userid, save_sec_context);
+
+ return PointerGetDatum(NULL);
+}
+
+/*
+ * rewrite_query_for_preupdate_state
+ *
+ * Rewrite the query so that base tables' RTEs will represent "pre-update"
+ * state of tables. This is necessary to calculate view delta after multiple
+ * tables are modified.
+ */
+static Query*
+rewrite_query_for_preupdate_state(Query *query, List *tables,
+ ParseState *pstate, Oid matviewid)
+{
+ ListCell *lc;
+ int num_rte = list_length(query->rtable);
+ int i;
+
+
+ /* register delta ENRs */
+ register_delta_ENRs(pstate, query, tables);
+
+ /* XXX: Is necessary? Is this right timing? */
+ AcquireRewriteLocks(query, true, false);
+
+ i = 1;
+ foreach(lc, query->rtable)
+ {
+ RangeTblEntry *r = (RangeTblEntry*) lfirst(lc);
+
+ ListCell *lc2;
+ foreach(lc2, tables)
+ {
+ MV_TriggerTable *table = (MV_TriggerTable *) lfirst(lc2);
+ /*
+ * if the modified table is found then replace the original RTE with
+ * "pre-state" RTE and append its index to the list.
+ */
+ if (r->relid == table->table_id)
+ {
+ List *securityQuals;
+ List *withCheckOptions;
+ bool hasRowSecurity;
+ bool hasSubLinks;
+
+ RangeTblEntry *rte_pre = get_prestate_rte(r, table, pstate->p_queryEnv, matviewid);
+
+ /*
+ * Set a row security poslicies of the modified table to the subquery RTE which
+ * represents the pre-update state of the table.
+ */
+ get_row_security_policies(query, table->original_rte, i,
+ &securityQuals, &withCheckOptions,
+ &hasRowSecurity, &hasSubLinks);
+
+ if (hasRowSecurity)
+ {
+ query->hasRowSecurity = true;
+ rte_pre->security_barrier = true;
+ }
+ if (hasSubLinks)
+ query->hasSubLinks = true;
+
+ rte_pre->securityQuals = securityQuals;
+ lfirst(lc) = rte_pre;
+
+ table->rte_indexes = lappend_int(table->rte_indexes, i);
+ break;
+ }
+ }
+
+ /* finish the loop if we processed all RTE included in the original query */
+ if (i++ >= num_rte)
+ break;
+ }
+
+ return query;
+}
+
+/*
+ * register_delta_ENRs
+ *
+ * For all modified tables, make ENRs for their transition tables
+ * and register them to the queryEnv. ENR's RTEs are also appended
+ * into the list in query tree.
+ */
+static void
+register_delta_ENRs(ParseState *pstate, Query *query, List *tables)
+{
+ QueryEnvironment *queryEnv = pstate->p_queryEnv;
+ ListCell *lc;
+ RangeTblEntry *rte;
+
+ foreach(lc, tables)
+ {
+ MV_TriggerTable *table = (MV_TriggerTable *) lfirst(lc);
+ ListCell *lc2;
+ int count;
+
+ count = 0;
+ foreach(lc2, table->old_tuplestores)
+ {
+ Tuplestorestate *oldtable = (Tuplestorestate *) lfirst(lc2);
+ EphemeralNamedRelation enr =
+ palloc(sizeof(EphemeralNamedRelationData));
+ ParseNamespaceItem *nsitem;
+
+ enr->md.name = make_delta_enr_name("old", table->table_id, count);
+ enr->md.reliddesc = table->table_id;
+ enr->md.tupdesc = NULL;
+ enr->md.enrtype = ENR_NAMED_TUPLESTORE;
+ enr->md.enrtuples = tuplestore_tuple_count(oldtable);
+ enr->reldata = oldtable;
+ register_ENR(queryEnv, enr);
+
+ nsitem = addRangeTableEntryForENR(pstate, makeRangeVar(NULL, enr->md.name, -1), true);
+ rte = nsitem->p_rte;
+
+ query->rtable = lappend(query->rtable, rte);
+
+ count++;
+ }
+
+ count = 0;
+ foreach(lc2, table->new_tuplestores)
+ {
+ Tuplestorestate *newtable = (Tuplestorestate *) lfirst(lc2);
+ EphemeralNamedRelation enr =
+ palloc(sizeof(EphemeralNamedRelationData));
+ ParseNamespaceItem *nsitem;
+
+ enr->md.name = make_delta_enr_name("new", table->table_id, count);
+ enr->md.reliddesc = table->table_id;
+ enr->md.tupdesc = NULL;
+ enr->md.enrtype = ENR_NAMED_TUPLESTORE;
+ enr->md.enrtuples = tuplestore_tuple_count(newtable);
+ enr->reldata = newtable;
+ register_ENR(queryEnv, enr);
+
+ nsitem = addRangeTableEntryForENR(pstate, makeRangeVar(NULL, enr->md.name, -1), true);
+ rte = nsitem->p_rte;
+
+ query->rtable = lappend(query->rtable, rte);
+
+ count++;
+ }
+ }
+}
+
+#define DatumGetItemPointer(X) ((ItemPointer) DatumGetPointer(X))
+#define PG_GETARG_ITEMPOINTER(n) DatumGetItemPointer(PG_GETARG_DATUM(n))
+
+/*
+ * ivm_visible_in_prestate
+ *
+ * Check visibility of a tuple specified by the tableoid and item pointer
+ * using the snapshot taken just before the table was modified.
+ */
+Datum
+ivm_visible_in_prestate(PG_FUNCTION_ARGS)
+{
+ Oid tableoid = PG_GETARG_OID(0);
+ ItemPointer itemPtr = PG_GETARG_ITEMPOINTER(1);
+ Oid matviewOid = PG_GETARG_OID(2);
+ MV_TriggerHashEntry *entry;
+ MV_TriggerTable *table = NULL;
+ ListCell *lc;
+ bool found;
+ bool result;
+
+ if (!in_delta_calculation)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("ivm_visible_in_prestate can be called only in delta calculation")));
+
+ entry = (MV_TriggerHashEntry *) hash_search(mv_trigger_info,
+ (void *) &matviewOid,
+ HASH_FIND, &found);
+ Assert (found && entry != NULL);
+
+ foreach(lc, entry->tables)
+ {
+ table = (MV_TriggerTable *) lfirst(lc);
+ if (table->table_id == tableoid)
+ break;
+ }
+
+ Assert (table != NULL);
+
+ result = table_tuple_fetch_row_version(table->rel, itemPtr, entry->snapshot, table->slot);
+
+ PG_RETURN_BOOL(result);
+}
+
+/*
+ * get_prestate_rte
+ *
+ * Rewrite RTE of the modified table to a subquery which represents
+ * "pre-state" table. The original RTE is saved in table->rte_original.
+ */
+static RangeTblEntry*
+get_prestate_rte(RangeTblEntry *rte, MV_TriggerTable *table,
+ QueryEnvironment *queryEnv, Oid matviewid)
+{
+ StringInfoData str;
+ RawStmt *raw;
+ Query *subquery;
+ Relation rel;
+ ParseState *pstate;
+ char *relname;
+ int i;
+
+ pstate = make_parsestate(NULL);
+ pstate->p_queryEnv = queryEnv;
+ pstate->p_expr_kind = EXPR_KIND_SELECT_TARGET;
+
+ /*
+ * We can use NoLock here since AcquireRewriteLocks should
+ * have locked the relation already.
+ */
+ rel = table_open(table->table_id, NoLock);
+ relname = quote_qualified_identifier(
+ get_namespace_name(RelationGetNamespace(rel)),
+ RelationGetRelationName(rel));
+ table_close(rel, NoLock);
+
+ /*
+ * Filtering inserted row using the snapshot taken before the table
+ * is modified. ctid is required for maintaining outer join views.
+ */
+ initStringInfo(&str);
+ appendStringInfo(&str,
+ "SELECT t.* FROM %s t"
+ " WHERE pg_catalog.ivm_visible_in_prestate(t.tableoid, t.ctid ,%d::pg_catalog.oid)",
+ relname, matviewid);
+
+ /*
+ * Append deleted rows contained in old transition tables.
+ */
+ for (i = 0; i < list_length(table->old_tuplestores); i++)
+ {
+ appendStringInfo(&str, " UNION ALL ");
+ appendStringInfo(&str," SELECT * FROM %s",
+ make_delta_enr_name("old", table->table_id, i));
+ }
+
+ /* Get a subquery representing pre-state of the table */
+ raw = (RawStmt*)linitial(raw_parser(str.data, RAW_PARSE_DEFAULT));
+ subquery = transformStmt(pstate, raw->stmt);
+
+ /* save the original RTE */
+ table->original_rte = copyObject(rte);
+
+ rte->rtekind = RTE_SUBQUERY;
+ rte->subquery = subquery;
+ rte->security_barrier = false;
+
+ /* Clear fields that should not be set in a subquery RTE */
+ rte->relid = InvalidOid;
+ rte->relkind = 0;
+ rte->rellockmode = 0;
+ rte->tablesample = NULL;
+ rte->perminfoindex = 0; /* no permission checking for this RTE */
+ rte->inh = false; /* must not be set for a subquery */
+
+ return rte;
+}
+
+/*
+ * make_delta_enr_name
+ *
+ * Make a name for ENR of a transition table from the base table's oid.
+ * prefix will be "new" or "old" depending on its transition table kind..
+ */
+static char*
+make_delta_enr_name(const char *prefix, Oid relid, int count)
+{
+ char buf[NAMEDATALEN];
+ char *name;
+
+ snprintf(buf, NAMEDATALEN, "__ivm_%s_%u_%u", prefix, relid, count);
+ name = pstrdup(buf);
+
+ return name;
+}
+
+/*
+ * replace_rte_with_delta
+ *
+ * Replace RTE of the modified table with a single table delta that combine its
+ * all transition tables.
+ */
+static RangeTblEntry*
+replace_rte_with_delta(RangeTblEntry *rte, MV_TriggerTable *table, bool is_new,
+ QueryEnvironment *queryEnv)
+{
+ Oid relid = table->table_id;
+ StringInfoData str;
+ ParseState *pstate;
+ RawStmt *raw;
+ Query *sub;
+ int num_tuplestores = list_length(is_new ? table->new_tuplestores : table->old_tuplestores);
+ int i;
+
+ /* the previous RTE must be a subquery which represents "pre-state" table */
+ Assert(rte->rtekind == RTE_SUBQUERY);
+
+ /* Create a ParseState for rewriting the view definition query */
+ pstate = make_parsestate(NULL);
+ pstate->p_queryEnv = queryEnv;
+ pstate->p_expr_kind = EXPR_KIND_SELECT_TARGET;
+
+ initStringInfo(&str);
+
+ for (i = 0; i < num_tuplestores; i++)
+ {
+ if (i > 0)
+ appendStringInfo(&str, " UNION ALL ");
+
+ appendStringInfo(&str,
+ " SELECT * FROM %s",
+ make_delta_enr_name(is_new ? "new" : "old", relid, i));
+ }
+
+ raw = (RawStmt*)linitial(raw_parser(str.data, RAW_PARSE_DEFAULT));
+ sub = transformStmt(pstate, raw->stmt);
+
+ /*
+ * Update the subquery so that it represent the combined transition
+ * table. Note that we leave the security_barrier and securityQuals
+ * fields so that the subquery relation can be protected by the RLS
+ * policy as same as the modified table.
+ */
+ rte->rtekind = RTE_SUBQUERY;
+ rte->subquery = sub;
+
+ return rte;
+}
+
+/*
+ * rewrite_query_for_counting
+ *
+ * Rewrite query for counting duplicated tuples.
+ */
+static Query *
+rewrite_query_for_counting(Query *query, ParseState *pstate)
+{
+ TargetEntry *tle_count;
+ FuncCall *fn;
+ Node *node;
+
+ /* Add count(*) for counting distinct tuples in views */
+ fn = makeFuncCall(SystemFuncName("count"), NIL, COERCE_EXPLICIT_CALL, -1);
+ fn->agg_star = true;
+ if (!query->groupClause && !query->hasAggs)
+ query->groupClause = transformDistinctClause(NULL, &query->targetList, query->sortClause, false);
+
+ node = ParseFuncOrColumn(pstate, fn->funcname, NIL, NULL, fn, false, -1);
+
+ tle_count = makeTargetEntry((Expr *) node,
+ list_length(query->targetList) + 1,
+ pstrdup("__ivm_count__"),
+ false);
+ query->targetList = lappend(query->targetList, tle_count);
+ query->hasAggs = true;
+
+ return query;
+}
+
+/*
+ * calc_delta
+ *
+ * Calculate view deltas generated under the modification of a table specified
+ * by the RTE index.
+ */
+static void
+calc_delta(MV_TriggerTable *table, int rte_index, Query *query,
+ DestReceiver *dest_old, DestReceiver *dest_new,
+ TupleDesc *tupdesc_old, TupleDesc *tupdesc_new,
+ QueryEnvironment *queryEnv)
+{
+ ListCell *lc = list_nth_cell(query->rtable, rte_index - 1);
+ RangeTblEntry *rte = (RangeTblEntry *) lfirst(lc);
+
+ in_delta_calculation = true;
+
+ /* Generate old delta */
+ if (list_length(table->old_tuplestores) > 0)
+ {
+ /* Replace the modified table with the old delta table and calculate the old view delta. */
+ replace_rte_with_delta(rte, table, false, queryEnv);
+ refresh_matview_datafill(dest_old, query, queryEnv, tupdesc_old, "");
+ }
+
+ /* Generate new delta */
+ if (list_length(table->new_tuplestores) > 0)
+ {
+ /* Replace the modified table with the new delta table and calculate the new view delta*/
+ replace_rte_with_delta(rte, table, true, queryEnv);
+ refresh_matview_datafill(dest_new, query, queryEnv, tupdesc_new, "");
+ }
+
+ in_delta_calculation = false;
+}
+
+/*
+ * rewrite_query_for_postupdate_state
+ *
+ * Rewrite the query so that the specified base table's RTEs will represent
+ * "post-update" state of tables. This is called after the view delta
+ * calculation due to changes on this table finishes.
+ */
+static Query*
+rewrite_query_for_postupdate_state(Query *query, MV_TriggerTable *table, int rte_index)
+{
+ ListCell *lc = list_nth_cell(query->rtable, rte_index - 1);
+
+ /* Retore the original RTE */
+ lfirst(lc) = table->original_rte;
+
+ return query;
+}
+
+/*
+ * apply_delta
+ *
+ * Apply deltas to the materialized view. In outer join cases, this requires
+ * the view maintenance graph.
+ */
+static void
+apply_delta(Oid matviewOid, Tuplestorestate *old_tuplestores, Tuplestorestate *new_tuplestores,
+ TupleDesc tupdesc_old, TupleDesc tupdesc_new,
+ Query *query)
+{
+ StringInfoData querybuf;
+ StringInfoData target_list_buf;
+ Relation matviewRel;
+ char *matviewname;
+ ListCell *lc;
+ int i;
+ List *keys = NIL;
+
+
+ /*
+ * get names of the materialized view and delta tables
+ */
+
+ matviewRel = table_open(matviewOid, NoLock);
+ matviewname = quote_qualified_identifier(get_namespace_name(RelationGetNamespace(matviewRel)),
+ RelationGetRelationName(matviewRel));
+
+ /*
+ * Build parts of the maintenance queries
+ */
+
+ initStringInfo(&querybuf);
+ initStringInfo(&target_list_buf);
+
+ /* build string of target list */
+ for (i = 0; i < matviewRel->rd_att->natts; i++)
+ {
+ Form_pg_attribute attr = TupleDescAttr(matviewRel->rd_att, i);
+ char *resname = NameStr(attr->attname);
+
+ if (i != 0)
+ appendStringInfo(&target_list_buf, ", ");
+ appendStringInfo(&target_list_buf, "%s", quote_qualified_identifier(NULL, resname));
+ }
+
+ i = 0;
+ foreach (lc, query->targetList)
+ {
+ TargetEntry *tle = (TargetEntry *) lfirst(lc);
+ Form_pg_attribute attr = TupleDescAttr(matviewRel->rd_att, i);
+
+ i++;
+
+ if (tle->resjunk)
+ continue;
+
+ keys = lappend(keys, attr);
+ }
+
+ /* Start maintaining the materialized view. */
+ OpenMatViewIncrementalMaintenance();
+
+ /* Open SPI context. */
+ if (SPI_connect() != SPI_OK_CONNECT)
+ elog(ERROR, "SPI_connect failed");
+
+ /* For tuple deletion */
+ if (old_tuplestores && tuplestore_tuple_count(old_tuplestores) > 0)
+ {
+ EphemeralNamedRelation enr = palloc(sizeof(EphemeralNamedRelationData));
+ int rc;
+
+ /* convert tuplestores to ENR, and register for SPI */
+ enr->md.name = pstrdup(OLD_DELTA_ENRNAME);
+ enr->md.reliddesc = InvalidOid;
+ enr->md.tupdesc = tupdesc_old;
+ enr->md.enrtype = ENR_NAMED_TUPLESTORE;
+ enr->md.enrtuples = tuplestore_tuple_count(old_tuplestores);
+ enr->reldata = old_tuplestores;
+
+ rc = SPI_register_relation(enr);
+ if (rc != SPI_OK_REL_REGISTER)
+ elog(ERROR, "SPI_register failed");
+
+ apply_old_delta(matviewname, OLD_DELTA_ENRNAME, keys);
+
+ }
+ /* For tuple insertion */
+ if (new_tuplestores && tuplestore_tuple_count(new_tuplestores) > 0)
+ {
+ EphemeralNamedRelation enr = palloc(sizeof(EphemeralNamedRelationData));
+ int rc;
+
+ /* convert tuplestores to ENR, and register for SPI */
+ enr->md.name = pstrdup(NEW_DELTA_ENRNAME);
+ enr->md.reliddesc = InvalidOid;
+ enr->md.tupdesc = tupdesc_new;;
+ enr->md.enrtype = ENR_NAMED_TUPLESTORE;
+ enr->md.enrtuples = tuplestore_tuple_count(new_tuplestores);
+ enr->reldata = new_tuplestores;
+
+ rc = SPI_register_relation(enr);
+ if (rc != SPI_OK_REL_REGISTER)
+ elog(ERROR, "SPI_register failed");
+
+ /* apply new delta */
+ apply_new_delta(matviewname, NEW_DELTA_ENRNAME, &target_list_buf);
+ }
+
+ /* We're done maintaining the materialized view. */
+ CloseMatViewIncrementalMaintenance();
+
+ table_close(matviewRel, NoLock);
+
+ /* Close SPI context. */
+ if (SPI_finish() != SPI_OK_FINISH)
+ elog(ERROR, "SPI_finish failed");
+}
+
+/*
+ * apply_old_delta
+ *
+ * Execute a query for applying a delta table given by deltname_old
+ * which contains tuples to be deleted from to a materialized view given by
+ * matviewname. This is used when counting is not required.
+ */
+static void
+apply_old_delta(const char *matviewname, const char *deltaname_old,
+ List *keys)
+{
+ StringInfoData querybuf;
+ StringInfoData keysbuf;
+ char *match_cond;
+ ListCell *lc;
+
+ /* build WHERE condition for searching tuples to be deleted */
+ match_cond = get_matching_condition_string(keys);
+
+ /* build string of keys list */
+ initStringInfo(&keysbuf);
+ foreach (lc, keys)
+ {
+ Form_pg_attribute attr = (Form_pg_attribute) lfirst(lc);
+ char *resname = NameStr(attr->attname);
+ appendStringInfo(&keysbuf, "%s", quote_qualified_identifier("mv", resname));
+ if (lnext(keys, lc))
+ appendStringInfo(&keysbuf, ", ");
+ }
+
+ /* Search for matching tuples from the view and update or delete if found. */
+ initStringInfo(&querybuf);
+ appendStringInfo(&querybuf,
+ "DELETE FROM %s WHERE ctid IN ("
+ "SELECT tid FROM (SELECT pg_catalog.row_number() over (partition by %s) AS \"__ivm_row_number__\","
+ "mv.ctid AS tid,"
+ "diff.\"__ivm_count__\""
+ "FROM %s AS mv, %s AS diff "
+ "WHERE %s) v "
+ "WHERE v.\"__ivm_row_number__\" OPERATOR(pg_catalog.<=) v.\"__ivm_count__\")",
+ matviewname,
+ keysbuf.data,
+ matviewname, deltaname_old,
+ match_cond);
+
+ if (SPI_exec(querybuf.data, 0) != SPI_OK_DELETE)
+ elog(ERROR, "SPI_exec failed: %s", querybuf.data);
+}
+
+/*
+ * apply_new_delta
+ *
+ * Execute a query for applying a delta table given by deltname_new
+ * which contains tuples to be inserted into a materialized view given by
+ * matviewname. This is used when counting is not required.
+ */
+static void
+apply_new_delta(const char *matviewname, const char *deltaname_new,
+ StringInfo target_list)
+{
+ StringInfoData querybuf;
+
+ /* Search for matching tuples from the view and update or delete if found. */
+ initStringInfo(&querybuf);
+ appendStringInfo(&querybuf,
+ "INSERT INTO %s (%s) SELECT %s FROM ("
+ "SELECT diff.*, pg_catalog.generate_series(1, diff.\"__ivm_count__\")"
+ " AS __ivm_generate_series__ "
+ "FROM %s AS diff) AS v",
+ matviewname, target_list->data, target_list->data,
+ deltaname_new);
+
+ if (SPI_exec(querybuf.data, 0) != SPI_OK_INSERT)
+ elog(ERROR, "SPI_exec failed: %s", querybuf.data);
+}
+
+/*
+ * get_matching_condition_string
+ *
+ * Build a predicate string for looking for a tuple with given keys.
+ */
+static char *
+get_matching_condition_string(List *keys)
+{
+ StringInfoData match_cond;
+ ListCell *lc;
+
+ /* If there is no key columns, the condition is always true. */
+ if (keys == NIL)
+ return "true";
+
+ initStringInfo(&match_cond);
+ foreach (lc, keys)
+ {
+ Form_pg_attribute attr = (Form_pg_attribute) lfirst(lc);
+ char *resname = NameStr(attr->attname);
+ char *mv_resname = quote_qualified_identifier("mv", resname);
+ char *diff_resname = quote_qualified_identifier("diff", resname);
+ Oid typid = attr->atttypid;
+
+ /* Considering NULL values, we can not use simple = operator. */
+ appendStringInfo(&match_cond, "(");
+ generate_equal(&match_cond, typid, mv_resname, diff_resname);
+ appendStringInfo(&match_cond, " OR (%s IS NULL AND %s IS NULL))",
+ mv_resname, diff_resname);
+
+ if (lnext(keys, lc))
+ appendStringInfo(&match_cond, " AND ");
+ }
+
+ return match_cond.data;
+}
+
+/*
+ * generate_equals
+ *
+ * Generate an equality clause using given operands' default equality
+ * operator.
+ */
+static void
+generate_equal(StringInfo querybuf, Oid opttype,
+ const char *leftop, const char *rightop)
+{
+ TypeCacheEntry *typentry;
+
+ typentry = lookup_type_cache(opttype, TYPECACHE_EQ_OPR);
+ if (!OidIsValid(typentry->eq_opr))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_FUNCTION),
+ errmsg("could not identify an equality operator for type %s",
+ format_type_be_qualified(opttype))));
+
+ generate_operator_clause(querybuf,
+ leftop, opttype,
+ typentry->eq_opr,
+ rightop, opttype);
+}
+
+/*
+ * mv_InitHashTables
+ */
+static void
+mv_InitHashTables(void)
+{
+ HASHCTL ctl;
+
+ memset(&ctl, 0, sizeof(ctl));
+ ctl.keysize = sizeof(Oid);
+ ctl.entrysize = sizeof(MV_TriggerHashEntry);
+ mv_trigger_info = hash_create("MV trigger info",
+ MV_INIT_QUERYHASHSIZE,
+ &ctl, HASH_ELEM | HASH_BLOBS);
+}
+
+/*
+ * AtAbort_IVM
+ *
+ * Clean up hash entries for all materialized views. This is called at
+ * transaction abort.
+ */
+void
+AtAbort_IVM()
+{
+ HASH_SEQ_STATUS seq;
+ MV_TriggerHashEntry *entry;
+
+ if (mv_trigger_info)
+ {
+ hash_seq_init(&seq, mv_trigger_info);
+ while ((entry = hash_seq_search(&seq)) != NULL)
+ clean_up_IVM_hash_entry(entry, true);
+ }
+ in_delta_calculation = false;
+}
+
+/*
+ * clean_up_IVM_hash_entry
+ *
+ * Clean up tuple stores and hash entries for a materialized view after its
+ * maintenance finished.
+ */
+static void
+clean_up_IVM_hash_entry(MV_TriggerHashEntry *entry, bool is_abort)
+{
+ bool found;
+ ListCell *lc;
+
+ foreach(lc, entry->tables)
+ {
+ MV_TriggerTable *table = (MV_TriggerTable *) lfirst(lc);
+
+ list_free(table->old_tuplestores);
+ list_free(table->new_tuplestores);
+ if (!is_abort)
+ {
+ ExecDropSingleTupleTableSlot(table->slot);
+ table_close(table->rel, NoLock);
+ }
+ }
+ list_free(entry->tables);
+
+ if (!is_abort)
+ UnregisterSnapshot(entry->snapshot);
+
+ hash_search(mv_trigger_info, (void *) &entry->matview_id, HASH_REMOVE, &found);
+}
+
+/*
+ * isIvmName
+ *
+ * Check if this is a IVM hidden column from the name.
+ */
+bool
+isIvmName(const char *s)
+{
+ if (s)
+ return (strncmp(s, "__ivm_", 6) == 0);
+ return false;
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 07023ee61d..cae088ede0 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12192,4 +12192,14 @@
proargtypes => 'int2',
prosrc => 'gist_stratnum_identity' },
+# IVM
+{ oid => '786', descr => 'ivm trigger (before)',
+ proname => 'IVM_immediate_before', provolatile => 'v', prorettype => 'trigger',
+ proargtypes => '', prosrc => 'IVM_immediate_before' },
+{ oid => '787', descr => 'ivm trigger (after)',
+ proname => 'IVM_immediate_maintenance', provolatile => 'v', prorettype => 'trigger',
+ proargtypes => '', prosrc => 'IVM_immediate_maintenance' },
+{ oid => '788', descr => 'ivm filetring ',
+ proname => 'ivm_visible_in_prestate', provolatile => 's', prorettype => 'bool',
+ proargtypes => 'oid tid oid', prosrc => 'ivm_visible_in_prestate' },
]
diff --git a/src/include/commands/createas.h b/src/include/commands/createas.h
index 94678e3834..396ad1bb4c 100644
--- a/src/include/commands/createas.h
+++ b/src/include/commands/createas.h
@@ -16,6 +16,7 @@
#include "catalog/objectaddress.h"
#include "nodes/params.h"
+#include "nodes/pathnodes.h"
#include "parser/parse_node.h"
#include "tcop/dest.h"
#include "utils/queryenvironment.h"
@@ -25,6 +26,9 @@ extern ObjectAddress ExecCreateTableAs(ParseState *pstate, CreateTableAsStmt *st
ParamListInfo params, QueryEnvironment *queryEnv,
QueryCompletion *qc);
+extern void CreateIvmTriggersOnBaseTables(Query *qry, Oid matviewOid);
+extern void CreateIndexOnIMMV(Query *query, Relation matviewRel);
+
extern int GetIntoRelEFlags(IntoClause *intoClause);
extern DestReceiver *CreateIntoRelDestReceiver(IntoClause *intoClause);
diff --git a/src/include/commands/matview.h b/src/include/commands/matview.h
index 817b2ba0b6..3257e1adff 100644
--- a/src/include/commands/matview.h
+++ b/src/include/commands/matview.h
@@ -15,6 +15,7 @@
#define MATVIEW_H
#include "catalog/objectaddress.h"
+#include "fmgr.h"
#include "nodes/params.h"
#include "nodes/parsenodes.h"
#include "tcop/dest.h"
@@ -23,6 +24,8 @@
extern void SetMatViewPopulatedState(Relation relation, bool newstate);
+extern void SetMatViewIVMState(Relation relation, bool newstate);
+
extern ObjectAddress ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
ParamListInfo params, QueryCompletion *qc);
@@ -30,4 +33,10 @@ extern DestReceiver *CreateTransientRelDestReceiver(Oid transientoid);
extern bool MatViewIncrementalMaintenanceIsEnabled(void);
+extern Datum IVM_immediate_before(PG_FUNCTION_ARGS);
+extern Datum IVM_immediate_maintenance(PG_FUNCTION_ARGS);
+extern Datum IVM_visible_in_prestate(PG_FUNCTION_ARGS);
+extern void AtAbort_IVM(void);
+extern bool isIvmName(const char *s);
+
#endif /* MATVIEW_H */
--
2.25.1
--Multipart=_Fri__29_Mar_2024_23_47_00_+0900_KGpmmDOIs1266Ib1
Content-Type: text/x-diff;
name="v31-0007-Add-DISTINCT-support-for-IVM.patch"
Content-Disposition: attachment;
filename="v31-0007-Add-DISTINCT-support-for-IVM.patch"
Content-Transfer-Encoding: 7bit
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Fix parallel vacuum buffer usage reporting
@ 2024-04-20 12:00 Alena Rybakina <[email protected]>
0 siblings, 1 reply; 21+ messages in thread
From: Alena Rybakina @ 2024-04-20 12:00 UTC (permalink / raw)
To: Anthonin Bonnefoy <[email protected]>; +Cc: pgsql-hackers; Masahiko Sawada <[email protected]>
Hi, thank you for your work with this subject.
While I was reviewing your code, I noticed that your patch conflicts
with another patch [0] that been committed.
[0]
https://www.postgresql.org/message-id/flat/CA%2BhUKGJkOiOCa%2Bmag4BF%2BzHo7qo%3Do9CFheB8%3Dg6uT5TUm2...
On 28.03.2024 13:12, Anthonin Bonnefoy wrote:
> Hi,
>
> Thanks for the review.
> On Thu, Mar 28, 2024 at 4:07 AM Masahiko Sawada
> <[email protected]> wrote:
>
> As for the proposed patch, the following part should handle the
> temp tables too:
>
>
> True, I've missed the local blocks. I've updated the patch:
> - read_rate and write_rate now include local block usage
> - I've added a specific output for reporting local blocks instead of
> combining them in the same output.
>
> Regards,
> Anthonin
--
Regards,
Alena Rybakina
Postgres Professional:http://www.postgrespro.com
The Russian Postgres Company
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Fix parallel vacuum buffer usage reporting
@ 2024-04-22 08:07 Anthonin Bonnefoy <[email protected]>
parent: Alena Rybakina <[email protected]>
0 siblings, 1 reply; 21+ messages in thread
From: Anthonin Bonnefoy @ 2024-04-22 08:07 UTC (permalink / raw)
To: Alena Rybakina <[email protected]>; +Cc: pgsql-hackers; Masahiko Sawada <[email protected]>
On Sat, Apr 20, 2024 at 2:00 PM Alena Rybakina <[email protected]>
wrote:
> Hi, thank you for your work with this subject.
>
> While I was reviewing your code, I noticed that your patch conflicts with
> another patch [0] that been committed.
>
> [0]
> https://www.postgresql.org/message-id/flat/CA%2BhUKGJkOiOCa%2Bmag4BF%2BzHo7qo%3Do9CFheB8%3Dg6uT5TUm2...
>
I've rebased the patch and also split the changes:
1: Use pgBufferUsage in Vacuum and Analyze block reporting
2: Stop tracking buffer usage in the now unused Vacuum{Hit,Miss,Dirty}
variables
3: Remove declarations of Vacuum{Hit,Miss,Dirty}
Attachments:
[application/octet-stream] v3-0003-Remove-declaration-of-Vacuum-block-usage-tracking.patch (1.4K, ../../CAO6_XqrJeHrPtE9z0dt3O9xqq3097-LWO9xNYbnjh_iMmurzgw@mail.gmail.com/3-v3-0003-Remove-declaration-of-Vacuum-block-usage-tracking.patch)
download | inline diff:
From 32ae6ab34586cfe071b063cb202950bf16b47711 Mon Sep 17 00:00:00 2001
From: Anthonin Bonnefoy <[email protected]>
Date: Mon, 22 Apr 2024 09:19:43 +0200
Subject: Remove declaration of Vacuum block usage tracking variables
---
src/backend/utils/init/globals.c | 4 ----
src/include/miscadmin.h | 4 ----
2 files changed, 8 deletions(-)
diff --git a/src/backend/utils/init/globals.c b/src/backend/utils/init/globals.c
index cc61937eef..ba7f45e8a2 100644
--- a/src/backend/utils/init/globals.c
+++ b/src/backend/utils/init/globals.c
@@ -151,10 +151,6 @@ int VacuumCostPageDirty = 20;
int VacuumCostLimit = 200;
double VacuumCostDelay = 0;
-int64 VacuumPageHit = 0;
-int64 VacuumPageMiss = 0;
-int64 VacuumPageDirty = 0;
-
int VacuumCostBalance = 0; /* working state for vacuum */
bool VacuumCostActive = false;
diff --git a/src/include/miscadmin.h b/src/include/miscadmin.h
index 90f9b21b25..6d07005d39 100644
--- a/src/include/miscadmin.h
+++ b/src/include/miscadmin.h
@@ -283,10 +283,6 @@ extern PGDLLIMPORT int VacuumCostPageDirty;
extern PGDLLIMPORT int VacuumCostLimit;
extern PGDLLIMPORT double VacuumCostDelay;
-extern PGDLLIMPORT int64 VacuumPageHit;
-extern PGDLLIMPORT int64 VacuumPageMiss;
-extern PGDLLIMPORT int64 VacuumPageDirty;
-
extern PGDLLIMPORT int VacuumCostBalance;
extern PGDLLIMPORT bool VacuumCostActive;
--
2.39.3 (Apple Git-146)
[application/octet-stream] v3-0001-Fix-parallel-vacuum-buffer-usage-reporting.patch (6.1K, ../../CAO6_XqrJeHrPtE9z0dt3O9xqq3097-LWO9xNYbnjh_iMmurzgw@mail.gmail.com/4-v3-0001-Fix-parallel-vacuum-buffer-usage-reporting.patch)
download | inline diff:
From a133e61ac2341f8a8a18a2c9b1933ef341a5846b Mon Sep 17 00:00:00 2001
From: Anthonin Bonnefoy <[email protected]>
Date: Mon, 22 Apr 2024 09:13:11 +0200
Subject: Fix parallel vacuum buffer usage reporting
Vacuum uses dedicated VacuumPage{Hit,Miss,Dirty} globals to track buffer
usage. However, those values are not collected at the end of parallel
vacuum workers, leading to incorrect buffer count.
Buffer tracking is already done in pgBufferUsage
shared_blks_{hit,read,dirtied}, making the vacuum specific variables
redundant. Moreover, pgBufferUsage is already collected at the end of
parallel workers, leading to a correct result of buffer used.
---
src/backend/access/heap/vacuumlazy.c | 24 +++++++++++-------------
src/backend/commands/analyze.c | 24 +++++++++++-------------
2 files changed, 22 insertions(+), 26 deletions(-)
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index de109acc89..b674719db2 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -309,9 +309,7 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
PgStat_Counter startreadtime = 0,
startwritetime = 0;
WalUsage startwalusage = pgWalUsage;
- int64 StartPageHit = VacuumPageHit,
- StartPageMiss = VacuumPageMiss,
- StartPageDirty = VacuumPageDirty;
+ BufferUsage startbufferusage = pgBufferUsage;
ErrorContextCallback errcallback;
char **indnames = NULL;
@@ -604,18 +602,18 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
long secs_dur;
int usecs_dur;
WalUsage walusage;
+ BufferUsage bufferusage;
StringInfoData buf;
char *msgfmt;
int32 diff;
- int64 PageHitOp = VacuumPageHit - StartPageHit,
- PageMissOp = VacuumPageMiss - StartPageMiss,
- PageDirtyOp = VacuumPageDirty - StartPageDirty;
double read_rate = 0,
write_rate = 0;
TimestampDifference(starttime, endtime, &secs_dur, &usecs_dur);
memset(&walusage, 0, sizeof(WalUsage));
WalUsageAccumDiff(&walusage, &pgWalUsage, &startwalusage);
+ memset(&bufferusage, 0, sizeof(BufferUsage));
+ BufferUsageAccumDiff(&bufferusage, &pgBufferUsage, &startbufferusage);
initStringInfo(&buf);
if (verbose)
@@ -742,18 +740,18 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
}
if (secs_dur > 0 || usecs_dur > 0)
{
- read_rate = (double) BLCKSZ * PageMissOp / (1024 * 1024) /
- (secs_dur + usecs_dur / 1000000.0);
- write_rate = (double) BLCKSZ * PageDirtyOp / (1024 * 1024) /
- (secs_dur + usecs_dur / 1000000.0);
+ read_rate = (double) BLCKSZ * (bufferusage.shared_blks_read + bufferusage.local_blks_read) /
+ (1024 * 1024) / (secs_dur + usecs_dur / 1000000.0);
+ write_rate = (double) BLCKSZ * (bufferusage.shared_blks_dirtied + bufferusage.local_blks_dirtied) /
+ (1024 * 1024) / (secs_dur + usecs_dur / 1000000.0);
}
appendStringInfo(&buf, _("avg read rate: %.3f MB/s, avg write rate: %.3f MB/s\n"),
read_rate, write_rate);
appendStringInfo(&buf,
_("buffer usage: %lld hits, %lld misses, %lld dirtied\n"),
- (long long) PageHitOp,
- (long long) PageMissOp,
- (long long) PageDirtyOp);
+ (long long) (bufferusage.shared_blks_hit + bufferusage.local_blks_hit),
+ (long long) (bufferusage.shared_blks_read + bufferusage.local_blks_read),
+ (long long) (bufferusage.shared_blks_dirtied + bufferusage.local_blks_dirtied));
appendStringInfo(&buf,
_("WAL usage: %lld records, %lld full page images, %llu bytes\n"),
(long long) walusage.wal_records,
diff --git a/src/backend/commands/analyze.c b/src/backend/commands/analyze.c
index 7d2cd24997..cab5792525 100644
--- a/src/backend/commands/analyze.c
+++ b/src/backend/commands/analyze.c
@@ -303,9 +303,8 @@ do_analyze_rel(Relation onerel, VacuumParams *params,
Oid save_userid;
int save_sec_context;
int save_nestlevel;
- int64 AnalyzePageHit = VacuumPageHit;
- int64 AnalyzePageMiss = VacuumPageMiss;
- int64 AnalyzePageDirty = VacuumPageDirty;
+ BufferUsage startbufferusage = pgBufferUsage;
+ BufferUsage bufferusage;
PgStat_Counter startreadtime = 0;
PgStat_Counter startwritetime = 0;
@@ -737,9 +736,8 @@ do_analyze_rel(Relation onerel, VacuumParams *params,
* happened as part of the analyze by subtracting out the
* pre-analyze values which we saved above.
*/
- AnalyzePageHit = VacuumPageHit - AnalyzePageHit;
- AnalyzePageMiss = VacuumPageMiss - AnalyzePageMiss;
- AnalyzePageDirty = VacuumPageDirty - AnalyzePageDirty;
+ memset(&bufferusage, 0, sizeof(BufferUsage));
+ BufferUsageAccumDiff(&bufferusage, &pgBufferUsage, &startbufferusage);
/*
* We do not expect an analyze to take > 25 days and it simplifies
@@ -765,10 +763,10 @@ do_analyze_rel(Relation onerel, VacuumParams *params,
if (delay_in_ms > 0)
{
- read_rate = (double) BLCKSZ * AnalyzePageMiss / (1024 * 1024) /
- (delay_in_ms / 1000.0);
- write_rate = (double) BLCKSZ * AnalyzePageDirty / (1024 * 1024) /
- (delay_in_ms / 1000.0);
+ read_rate = (double) BLCKSZ * (bufferusage.shared_blks_read + bufferusage.local_blks_read) /
+ (1024 * 1024) / (delay_in_ms / 1000.0);
+ write_rate = (double) BLCKSZ * (bufferusage.shared_blks_dirtied + bufferusage.local_blks_dirtied) /
+ (1024 * 1024) / (delay_in_ms / 1000.0);
}
/*
@@ -792,9 +790,9 @@ do_analyze_rel(Relation onerel, VacuumParams *params,
appendStringInfo(&buf, _("avg read rate: %.3f MB/s, avg write rate: %.3f MB/s\n"),
read_rate, write_rate);
appendStringInfo(&buf, _("buffer usage: %lld hits, %lld misses, %lld dirtied\n"),
- (long long) AnalyzePageHit,
- (long long) AnalyzePageMiss,
- (long long) AnalyzePageDirty);
+ (long long) (bufferusage.shared_blks_hit + bufferusage.local_blks_hit),
+ (long long) (bufferusage.shared_blks_read + bufferusage.local_blks_read),
+ (long long) (bufferusage.shared_blks_dirtied + bufferusage.local_blks_dirtied));
appendStringInfo(&buf, _("system usage: %s"), pg_rusage_show(&ru0));
ereport(LOG,
--
2.39.3 (Apple Git-146)
[application/octet-stream] v3-0002-Remove-unused-tracking-of-vacuum-buffer-usage.patch (2.8K, ../../CAO6_XqrJeHrPtE9z0dt3O9xqq3097-LWO9xNYbnjh_iMmurzgw@mail.gmail.com/5-v3-0002-Remove-unused-tracking-of-vacuum-buffer-usage.patch)
download | inline diff:
From d7ecf2a8b1d2d23aa21c1a2c5cc0d24e7aa4b543 Mon Sep 17 00:00:00 2001
From: Anthonin Bonnefoy <[email protected]>
Date: Mon, 22 Apr 2024 09:14:50 +0200
Subject: Remove unused tracking of vacuum buffer usage
Vacuum now relies on pgBufferUsage to track block usage making
VacuumPage{Hit,Miss,Dirty} unnecessary. Remove initialization and
increment of the vacuum specific variables used for block usage.
---
src/backend/commands/vacuum.c | 3 ---
src/backend/commands/vacuumparallel.c | 3 ---
src/backend/storage/buffer/bufmgr.c | 4 ----
3 files changed, 10 deletions(-)
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index b589279d49..192a0c74f9 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -603,9 +603,6 @@ vacuum(List *relations, VacuumParams *params, BufferAccessStrategy bstrategy,
VacuumFailsafeActive = false;
VacuumUpdateCosts();
VacuumCostBalance = 0;
- VacuumPageHit = 0;
- VacuumPageMiss = 0;
- VacuumPageDirty = 0;
VacuumCostBalanceLocal = 0;
VacuumSharedCostBalance = NULL;
VacuumActiveNWorkers = NULL;
diff --git a/src/backend/commands/vacuumparallel.c b/src/backend/commands/vacuumparallel.c
index f26070bff2..22c057fe61 100644
--- a/src/backend/commands/vacuumparallel.c
+++ b/src/backend/commands/vacuumparallel.c
@@ -1043,9 +1043,6 @@ parallel_vacuum_main(dsm_segment *seg, shm_toc *toc)
/* Set cost-based vacuum delay */
VacuumUpdateCosts();
VacuumCostBalance = 0;
- VacuumPageHit = 0;
- VacuumPageMiss = 0;
- VacuumPageDirty = 0;
VacuumCostBalanceLocal = 0;
VacuumSharedCostBalance = &(shared->cost_balance);
VacuumActiveNWorkers = &(shared->active_nworkers);
diff --git a/src/backend/storage/buffer/bufmgr.c b/src/backend/storage/buffer/bufmgr.c
index 49637284f9..996ee6fdc8 100644
--- a/src/backend/storage/buffer/bufmgr.c
+++ b/src/backend/storage/buffer/bufmgr.c
@@ -1127,7 +1127,6 @@ PinBufferForBlock(Relation rel,
}
if (*foundPtr)
{
- VacuumPageHit++;
pgstat_count_io_op(io_object, io_context, IOOP_HIT);
if (VacuumCostActive)
VacuumCostBalance += VacuumCostPageHit;
@@ -1519,7 +1518,6 @@ WaitReadBuffers(ReadBuffersOperation *operation)
false);
}
- VacuumPageMiss += io_buffers_len;
if (VacuumCostActive)
VacuumCostBalance += VacuumCostPageMiss * io_buffers_len;
}
@@ -2513,7 +2511,6 @@ MarkBufferDirty(Buffer buffer)
*/
if (!(old_buf_state & BM_DIRTY))
{
- VacuumPageDirty++;
pgBufferUsage.shared_blks_dirtied++;
if (VacuumCostActive)
VacuumCostBalance += VacuumCostPageDirty;
@@ -5036,7 +5033,6 @@ MarkBufferDirtyHint(Buffer buffer, bool buffer_std)
if (dirtied)
{
- VacuumPageDirty++;
pgBufferUsage.shared_blks_dirtied++;
if (VacuumCostActive)
VacuumCostBalance += VacuumCostPageDirty;
--
2.39.3 (Apple Git-146)
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Fix parallel vacuum buffer usage reporting
@ 2024-04-24 06:34 Masahiko Sawada <[email protected]>
parent: Anthonin Bonnefoy <[email protected]>
0 siblings, 2 replies; 21+ messages in thread
From: Masahiko Sawada @ 2024-04-24 06:34 UTC (permalink / raw)
To: Anthonin Bonnefoy <[email protected]>; +Cc: Alena Rybakina <[email protected]>; pgsql-hackers
On Mon, Apr 22, 2024 at 5:07 PM Anthonin Bonnefoy
<[email protected]> wrote:
>
> On Sat, Apr 20, 2024 at 2:00 PM Alena Rybakina <[email protected]> wrote:
>>
>> Hi, thank you for your work with this subject.
>>
>> While I was reviewing your code, I noticed that your patch conflicts with another patch [0] that been committed.
>>
>> [0] https://www.postgresql.org/message-id/flat/CA%2BhUKGJkOiOCa%2Bmag4BF%2BzHo7qo%3Do9CFheB8%3Dg6uT5TUm2...
>
>
> I've rebased the patch and also split the changes:
Thank you for updating the patch!
> 1: Use pgBufferUsage in Vacuum and Analyze block reporting
I think that if the anayze command doesn't have the same issue, we
don't need to change it. Making the vacuum and the analyze consistent
is a good point but I'd like to avoid doing unnecessary changes in
back branches. I think the patch set would contain:
(a) make lazy vacuum use BufferUsage instead of
VacuumPage{Hit,Miss,Dirty}. (backpatched down to pg13).
(b) make analyze use BufferUsage and remove VacuumPage{Hit,Miss,Dirty}
variables for consistency and simplicity (only for HEAD, if we agree).
BTW I realized that VACUUM VERBOSE running on a temp table always
shows the number of dirtied buffers being 0, which seems to be a bug.
The patch (a) will resolve it as well.
Regards,
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Fix parallel vacuum buffer usage reporting
@ 2024-04-24 09:19 Anthonin Bonnefoy <[email protected]>
parent: Masahiko Sawada <[email protected]>
1 sibling, 0 replies; 21+ messages in thread
From: Anthonin Bonnefoy @ 2024-04-24 09:19 UTC (permalink / raw)
To: Masahiko Sawada <[email protected]>; +Cc: Alena Rybakina <[email protected]>; pgsql-hackers
Thanks for the review!
> I think that if the anayze command doesn't have the same issue, we
> don't need to change it.
Good point, I've wrongly assumed that analyze was also impacted but there's
no parallel analyze so the block count is correct.
> (a) make lazy vacuum use BufferUsage instead of
> VacuumPage{Hit,Miss,Dirty}. (backpatched down to pg13).
> (b) make analyze use BufferUsage and remove VacuumPage{Hit,Miss,Dirty}
> variables for consistency and simplicity (only for HEAD, if we agree).
>
I've isolated the fix in the first patch. I've kept the analyze change and
removal of VacuumPage* variables split for clarity sake.
BTW I realized that VACUUM VERBOSE running on a temp table always
> shows the number of dirtied buffers being 0, which seems to be a bug.
> The patch (a) will resolve it as well.
>
Indeed, it visible with the following test:
SET temp_buffers='1024';
CREATE TEMPORARY TABLE vacuum_fix (aid int, bid int);
INSERT INTO vacuum_fix SELECT *, * FROM generate_series(1, 1000000);
VACUUM vacuum_fix;
UPDATE vacuum_fix SET bid = bid+1;
VACUUM (VERBOSE, INDEX_CLEANUP ON) vacuum_fix;
Pre-patch:
avg read rate: 254.751 MB/s, avg write rate: 0.029 MB/s
buffer usage: 8853 hits, 8856 misses, 1 dirtied
WAL usage: 1 records, 1 full page images, 3049 bytes
The dirtied page is from pg_class (so not a local buffer)
With the fix:
avg read rate: 250.230 MB/s, avg write rate: 250.230 MB/s
buffer usage: 8853 hits, 8856 misses, 8856 dirtied
WAL usage: 1 records, 1 full page images, 3049 bytes
Attachments:
[application/octet-stream] v4-0002-Use-pgBufferUsage-for-analyze-block-reporting.patch (3.0K, ../../CAO6_XqoVz4pBZ1H7as7CVwjUX7FLsviHW7i_BKStNNcrY+k4+g@mail.gmail.com/3-v4-0002-Use-pgBufferUsage-for-analyze-block-reporting.patch)
download | inline diff:
From 7848314c423376fc7d4403b5b9d41bc49e8ba5e5 Mon Sep 17 00:00:00 2001
From: Anthonin Bonnefoy <[email protected]>
Date: Wed, 24 Apr 2024 10:40:33 +0200
Subject: Use pgBufferUsage for analyze block reporting
pgBufferUsage is already tracking block usage, making
VacuumPage{Hit,Miss,Dirty} redundant. Replace Vacuum specific variables
by pgBufferUsage
---
src/backend/commands/analyze.c | 24 +++++++++++-------------
1 file changed, 11 insertions(+), 13 deletions(-)
diff --git a/src/backend/commands/analyze.c b/src/backend/commands/analyze.c
index 7d2cd24997..cab5792525 100644
--- a/src/backend/commands/analyze.c
+++ b/src/backend/commands/analyze.c
@@ -303,9 +303,8 @@ do_analyze_rel(Relation onerel, VacuumParams *params,
Oid save_userid;
int save_sec_context;
int save_nestlevel;
- int64 AnalyzePageHit = VacuumPageHit;
- int64 AnalyzePageMiss = VacuumPageMiss;
- int64 AnalyzePageDirty = VacuumPageDirty;
+ BufferUsage startbufferusage = pgBufferUsage;
+ BufferUsage bufferusage;
PgStat_Counter startreadtime = 0;
PgStat_Counter startwritetime = 0;
@@ -737,9 +736,8 @@ do_analyze_rel(Relation onerel, VacuumParams *params,
* happened as part of the analyze by subtracting out the
* pre-analyze values which we saved above.
*/
- AnalyzePageHit = VacuumPageHit - AnalyzePageHit;
- AnalyzePageMiss = VacuumPageMiss - AnalyzePageMiss;
- AnalyzePageDirty = VacuumPageDirty - AnalyzePageDirty;
+ memset(&bufferusage, 0, sizeof(BufferUsage));
+ BufferUsageAccumDiff(&bufferusage, &pgBufferUsage, &startbufferusage);
/*
* We do not expect an analyze to take > 25 days and it simplifies
@@ -765,10 +763,10 @@ do_analyze_rel(Relation onerel, VacuumParams *params,
if (delay_in_ms > 0)
{
- read_rate = (double) BLCKSZ * AnalyzePageMiss / (1024 * 1024) /
- (delay_in_ms / 1000.0);
- write_rate = (double) BLCKSZ * AnalyzePageDirty / (1024 * 1024) /
- (delay_in_ms / 1000.0);
+ read_rate = (double) BLCKSZ * (bufferusage.shared_blks_read + bufferusage.local_blks_read) /
+ (1024 * 1024) / (delay_in_ms / 1000.0);
+ write_rate = (double) BLCKSZ * (bufferusage.shared_blks_dirtied + bufferusage.local_blks_dirtied) /
+ (1024 * 1024) / (delay_in_ms / 1000.0);
}
/*
@@ -792,9 +790,9 @@ do_analyze_rel(Relation onerel, VacuumParams *params,
appendStringInfo(&buf, _("avg read rate: %.3f MB/s, avg write rate: %.3f MB/s\n"),
read_rate, write_rate);
appendStringInfo(&buf, _("buffer usage: %lld hits, %lld misses, %lld dirtied\n"),
- (long long) AnalyzePageHit,
- (long long) AnalyzePageMiss,
- (long long) AnalyzePageDirty);
+ (long long) (bufferusage.shared_blks_hit + bufferusage.local_blks_hit),
+ (long long) (bufferusage.shared_blks_read + bufferusage.local_blks_read),
+ (long long) (bufferusage.shared_blks_dirtied + bufferusage.local_blks_dirtied));
appendStringInfo(&buf, _("system usage: %s"), pg_rusage_show(&ru0));
ereport(LOG,
--
2.39.3 (Apple Git-146)
[application/octet-stream] v4-0001-Fix-parallel-and-temp-vacuum-buffer-usage-reporti.patch (3.7K, ../../CAO6_XqoVz4pBZ1H7as7CVwjUX7FLsviHW7i_BKStNNcrY+k4+g@mail.gmail.com/4-v4-0001-Fix-parallel-and-temp-vacuum-buffer-usage-reporti.patch)
download | inline diff:
From defd75d87a4a1785345b3ef810578b99c81d07fc Mon Sep 17 00:00:00 2001
From: Anthonin Bonnefoy <[email protected]>
Date: Mon, 22 Apr 2024 09:13:11 +0200
Subject: Fix parallel and temp vacuum buffer usage reporting
Vacuum uses dedicated VacuumPage{Hit,Miss,Dirty} globals to track buffer
usage. However, those values are not collected at the end of parallel
vacuum workers, leading to incorrect buffer count.
Buffer tracking is already done in pgBufferUsage
shared_blks_{hit,read,dirtied}, making the vacuum specific variables
redundant. Moreover, pgBufferUsage is already collected at the end of
parallel workers, leading to a correct result of buffer used.
This also fixes the reported number of dirtied buffers for temporary
tables. Buffer dirtied by MarkLocalBufferDirty was not tracked by the
VacuumPageDirty variable.
---
src/backend/access/heap/vacuumlazy.c | 24 +++++++++++-------------
1 file changed, 11 insertions(+), 13 deletions(-)
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index de109acc89..b674719db2 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -309,9 +309,7 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
PgStat_Counter startreadtime = 0,
startwritetime = 0;
WalUsage startwalusage = pgWalUsage;
- int64 StartPageHit = VacuumPageHit,
- StartPageMiss = VacuumPageMiss,
- StartPageDirty = VacuumPageDirty;
+ BufferUsage startbufferusage = pgBufferUsage;
ErrorContextCallback errcallback;
char **indnames = NULL;
@@ -604,18 +602,18 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
long secs_dur;
int usecs_dur;
WalUsage walusage;
+ BufferUsage bufferusage;
StringInfoData buf;
char *msgfmt;
int32 diff;
- int64 PageHitOp = VacuumPageHit - StartPageHit,
- PageMissOp = VacuumPageMiss - StartPageMiss,
- PageDirtyOp = VacuumPageDirty - StartPageDirty;
double read_rate = 0,
write_rate = 0;
TimestampDifference(starttime, endtime, &secs_dur, &usecs_dur);
memset(&walusage, 0, sizeof(WalUsage));
WalUsageAccumDiff(&walusage, &pgWalUsage, &startwalusage);
+ memset(&bufferusage, 0, sizeof(BufferUsage));
+ BufferUsageAccumDiff(&bufferusage, &pgBufferUsage, &startbufferusage);
initStringInfo(&buf);
if (verbose)
@@ -742,18 +740,18 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
}
if (secs_dur > 0 || usecs_dur > 0)
{
- read_rate = (double) BLCKSZ * PageMissOp / (1024 * 1024) /
- (secs_dur + usecs_dur / 1000000.0);
- write_rate = (double) BLCKSZ * PageDirtyOp / (1024 * 1024) /
- (secs_dur + usecs_dur / 1000000.0);
+ read_rate = (double) BLCKSZ * (bufferusage.shared_blks_read + bufferusage.local_blks_read) /
+ (1024 * 1024) / (secs_dur + usecs_dur / 1000000.0);
+ write_rate = (double) BLCKSZ * (bufferusage.shared_blks_dirtied + bufferusage.local_blks_dirtied) /
+ (1024 * 1024) / (secs_dur + usecs_dur / 1000000.0);
}
appendStringInfo(&buf, _("avg read rate: %.3f MB/s, avg write rate: %.3f MB/s\n"),
read_rate, write_rate);
appendStringInfo(&buf,
_("buffer usage: %lld hits, %lld misses, %lld dirtied\n"),
- (long long) PageHitOp,
- (long long) PageMissOp,
- (long long) PageDirtyOp);
+ (long long) (bufferusage.shared_blks_hit + bufferusage.local_blks_hit),
+ (long long) (bufferusage.shared_blks_read + bufferusage.local_blks_read),
+ (long long) (bufferusage.shared_blks_dirtied + bufferusage.local_blks_dirtied));
appendStringInfo(&buf,
_("WAL usage: %lld records, %lld full page images, %llu bytes\n"),
(long long) walusage.wal_records,
--
2.39.3 (Apple Git-146)
[application/octet-stream] v4-0003-Remove-unused-variables-for-vacuum-buffer-usage.patch (3.9K, ../../CAO6_XqoVz4pBZ1H7as7CVwjUX7FLsviHW7i_BKStNNcrY+k4+g@mail.gmail.com/5-v4-0003-Remove-unused-variables-for-vacuum-buffer-usage.patch)
download | inline diff:
From 8608ef254c8143191a0e623b2e654f7811915be3 Mon Sep 17 00:00:00 2001
From: Anthonin Bonnefoy <[email protected]>
Date: Wed, 24 Apr 2024 10:40:57 +0200
Subject: Remove unused variables for vacuum buffer usage
Vacuum and analyze now relies on pgBufferUsage to track block usage making
VacuumPage{Hit,Miss,Dirty} unused. This patch removes all references
to the Vacuum{Hit,Miss,Dirty} variables.
---
src/backend/commands/vacuum.c | 3 ---
src/backend/commands/vacuumparallel.c | 3 ---
src/backend/storage/buffer/bufmgr.c | 4 ----
src/backend/utils/init/globals.c | 4 ----
src/include/miscadmin.h | 4 ----
5 files changed, 18 deletions(-)
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index b589279d49..192a0c74f9 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -603,9 +603,6 @@ vacuum(List *relations, VacuumParams *params, BufferAccessStrategy bstrategy,
VacuumFailsafeActive = false;
VacuumUpdateCosts();
VacuumCostBalance = 0;
- VacuumPageHit = 0;
- VacuumPageMiss = 0;
- VacuumPageDirty = 0;
VacuumCostBalanceLocal = 0;
VacuumSharedCostBalance = NULL;
VacuumActiveNWorkers = NULL;
diff --git a/src/backend/commands/vacuumparallel.c b/src/backend/commands/vacuumparallel.c
index f26070bff2..22c057fe61 100644
--- a/src/backend/commands/vacuumparallel.c
+++ b/src/backend/commands/vacuumparallel.c
@@ -1043,9 +1043,6 @@ parallel_vacuum_main(dsm_segment *seg, shm_toc *toc)
/* Set cost-based vacuum delay */
VacuumUpdateCosts();
VacuumCostBalance = 0;
- VacuumPageHit = 0;
- VacuumPageMiss = 0;
- VacuumPageDirty = 0;
VacuumCostBalanceLocal = 0;
VacuumSharedCostBalance = &(shared->cost_balance);
VacuumActiveNWorkers = &(shared->active_nworkers);
diff --git a/src/backend/storage/buffer/bufmgr.c b/src/backend/storage/buffer/bufmgr.c
index 49637284f9..996ee6fdc8 100644
--- a/src/backend/storage/buffer/bufmgr.c
+++ b/src/backend/storage/buffer/bufmgr.c
@@ -1127,7 +1127,6 @@ PinBufferForBlock(Relation rel,
}
if (*foundPtr)
{
- VacuumPageHit++;
pgstat_count_io_op(io_object, io_context, IOOP_HIT);
if (VacuumCostActive)
VacuumCostBalance += VacuumCostPageHit;
@@ -1519,7 +1518,6 @@ WaitReadBuffers(ReadBuffersOperation *operation)
false);
}
- VacuumPageMiss += io_buffers_len;
if (VacuumCostActive)
VacuumCostBalance += VacuumCostPageMiss * io_buffers_len;
}
@@ -2513,7 +2511,6 @@ MarkBufferDirty(Buffer buffer)
*/
if (!(old_buf_state & BM_DIRTY))
{
- VacuumPageDirty++;
pgBufferUsage.shared_blks_dirtied++;
if (VacuumCostActive)
VacuumCostBalance += VacuumCostPageDirty;
@@ -5036,7 +5033,6 @@ MarkBufferDirtyHint(Buffer buffer, bool buffer_std)
if (dirtied)
{
- VacuumPageDirty++;
pgBufferUsage.shared_blks_dirtied++;
if (VacuumCostActive)
VacuumCostBalance += VacuumCostPageDirty;
diff --git a/src/backend/utils/init/globals.c b/src/backend/utils/init/globals.c
index cc61937eef..ba7f45e8a2 100644
--- a/src/backend/utils/init/globals.c
+++ b/src/backend/utils/init/globals.c
@@ -151,10 +151,6 @@ int VacuumCostPageDirty = 20;
int VacuumCostLimit = 200;
double VacuumCostDelay = 0;
-int64 VacuumPageHit = 0;
-int64 VacuumPageMiss = 0;
-int64 VacuumPageDirty = 0;
-
int VacuumCostBalance = 0; /* working state for vacuum */
bool VacuumCostActive = false;
diff --git a/src/include/miscadmin.h b/src/include/miscadmin.h
index 90f9b21b25..6d07005d39 100644
--- a/src/include/miscadmin.h
+++ b/src/include/miscadmin.h
@@ -283,10 +283,6 @@ extern PGDLLIMPORT int VacuumCostPageDirty;
extern PGDLLIMPORT int VacuumCostLimit;
extern PGDLLIMPORT double VacuumCostDelay;
-extern PGDLLIMPORT int64 VacuumPageHit;
-extern PGDLLIMPORT int64 VacuumPageMiss;
-extern PGDLLIMPORT int64 VacuumPageDirty;
-
extern PGDLLIMPORT int VacuumCostBalance;
extern PGDLLIMPORT bool VacuumCostActive;
--
2.39.3 (Apple Git-146)
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Fix parallel vacuum buffer usage reporting
@ 2024-04-24 14:01 Alena Rybakina <[email protected]>
parent: Masahiko Sawada <[email protected]>
1 sibling, 1 reply; 21+ messages in thread
From: Alena Rybakina @ 2024-04-24 14:01 UTC (permalink / raw)
To: Anthonin Bonnefoy <[email protected]>; +Cc: Masahiko Sawada <[email protected]>; pgsql-hackers
On 22.04.2024 11:07, Anthonin Bonnefoy wrote:
> On Sat, Apr 20, 2024 at 2:00 PM Alena Rybakina
> <[email protected]> wrote:
>
> Hi, thank you for your work with this subject.
>
> While I was reviewing your code, I noticed that your patch
> conflicts with another patch [0] that been committed.
>
> [0]
> https://www.postgresql.org/message-id/flat/CA%2BhUKGJkOiOCa%2Bmag4BF%2BzHo7qo%3Do9CFheB8%3Dg6uT5TUm2...
>
>
> I've rebased the patch and also split the changes:
> 1: Use pgBufferUsage in Vacuum and Analyze block reporting
> 2: Stop tracking buffer usage in the now unused Vacuum{Hit,Miss,Dirty}
> variables
> 3: Remove declarations of Vacuum{Hit,Miss,Dirty}
Hi! Thank you for your work, and I have reviewed your corrections.
I tested the main postgres branch with and without your fix using a
script that was written by me. It consists of five scenarios and I made
a comparison in the logs between the original version of the master
branch and the master branch with your patch:
1. I added 1 million data to the table and deleted 10% of them. I ran
vacuum verbose and didn't see any differences.
buffer usage: 12585 hits, 0 misses, 4 dirtied
2. I opened another connection with a repeatable read transaction
through the dblink extension and launched a query updating the records
in the table under test. Later, I ran vacuum verbose again and also
didn't see any differences.
buffer usage: 19424 hits, 0 misses, 6 dirtied
3. I commited transaction and ran vacuum verbose again. Everything is
fine in the logs.
buffer usage: 22960 hits, 0 misses, 11456 dirtied
4. I deleted all the data from the table and later started vacuum
verbose again. The number of pages in the buffer matched with your patch
too.
5.I inserted 1 million data into the table and updated it, and I found
the difference between the original master version and the version with
your patch:
with your patch: buffer usage: 32315 hits, 606 misses, 1547 dirtied
original version: buffer usage: 32348 hits, 573 misses, 1456 dirtied
I suppose, something wasn't calculated.
The same script was run, but using vacuum verbose analyze, and I saw the
difference again in the fifth step:
with your patch: buffer usage: 32312 hits, 607 misses, 1566 dirtied
master: buffer usage: 32346 hits, 573 misses, 1360 dirtied
I have attached a test file (vacuum_check_logs.sql) and four log files:
two with the vacuum verbose command (vacuum_log and
vacuum_log_with_patch files) and two others with the vacuum verbose
analyze command (vacuum_analyze_test_with_patch and vacuum_analyze_test
files). Both test approaches show logs with and without your changes.
>> 1: Use pgBufferUsage in Vacuum and Analyze block reporting
> I think that if the anayze command doesn't have the same issue, we
> don't need to change it. Making the vacuum and the analyze consistent
> is a good point but I'd like to avoid doing unnecessary changes in
> back branches. I think the patch set would contain:
>
> (a) make lazy vacuum use BufferUsage instead of
> VacuumPage{Hit,Miss,Dirty}. (backpatched down to pg13).
> (b) make analyze use BufferUsage and remove VacuumPage{Hit,Miss,Dirty}
> variables for consistency and simplicity (only for HEAD, if we agree).
>
> BTW I realized that VACUUM VERBOSE running on a temp table always
> shows the number of dirtied buffers being 0, which seems to be a bug.
> The patch (a) will resolve it as well.
>
I agree with that. I think we can leave these changes to the analysis
command for master, but I also doubt the need to backport his changes to
back versions.
--
Regards,
Alena Rybakina
Postgres Professional:http://www.postgrespro.com
The Russian Postgres Company
postgres=# \i ~/vacuum_check_logs.sql
psql:/home/alena/vacuum_check_logs.sql:2: ERROR: extension "dblink" does not exist
CREATE EXTENSION
dblink_connect
----------------
OK
(1 row)
psql:/home/alena/vacuum_check_logs.sql:5: ERROR: table "vestat" does not exist
SET
SET
CREATE TABLE
CREATE INDEX
psql:/home/alena/vacuum_check_logs.sql:15: INFO: vacuuming "postgres.public.vestat"
psql:/home/alena/vacuum_check_logs.sql:15: INFO: finished vacuuming "postgres.public.vestat": index scans: 0
pages: 0 removed, 0 remain, 0 scanned (100.00% of total)
tuples: 0 removed, 0 remain, 0 are dead but not yet removable
removable cutoff: 741, which was 0 XIDs old when operation ended
new relfrozenxid: 741, which is 2 XIDs ahead of previous value
frozen: 0 pages from table (100.00% of total) had 0 tuples frozen
index scan not needed: 0 pages from table (100.00% of total) had 0 dead item identifiers removed
I/O timings: read: 0.100 ms, write: 0.000 ms
avg read rate: 29.260 MB/s, avg write rate: 0.000 MB/s
buffer usage: 13 hits, 1 misses, 0 dirtied
WAL usage: 1 records, 0 full page images, 237 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM
pg_sleep
----------
(1 row)
INSERT 0 1000000
DELETE 100000
psql:/home/alena/vacuum_check_logs.sql:24: INFO: vacuuming "postgres.public.vestat"
psql:/home/alena/vacuum_check_logs.sql:24: INFO: table "vestat": truncated 3922 to 3530 pages
psql:/home/alena/vacuum_check_logs.sql:24: INFO: finished vacuuming "postgres.public.vestat": index scans: 1
pages: 392 removed, 3530 remain, 3922 scanned (100.00% of total)
tuples: 100000 removed, 900000 remain, 0 are dead but not yet removable
removable cutoff: 743, which was 1 XIDs old when operation ended
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan needed: 393 pages from table (10.02% of total) had 100000 dead item identifiers removed
index "vestat_idx": pages: 2197 in total, 218 newly deleted, 218 currently deleted, 0 reusable
I/O timings: read: 0.000 ms, write: 0.029 ms
avg read rate: 0.000 MB/s, avg write rate: 0.062 MB/s
buffer usage: 12585 hits, 0 misses, 4 dirtied
WAL usage: 5369 records, 1 full page images, 928676 bytes
system usage: CPU: user: 0.37 s, system: 0.00 s, elapsed: 0.50 s
VACUUM
pg_sleep
----------
(1 row)
CHECKPOINT
dblink_exec
-------------
BEGIN
(1 row)
a
---
(1 row)
UPDATE 900000
psql:/home/alena/vacuum_check_logs.sql:37: INFO: vacuuming "postgres.public.vestat"
psql:/home/alena/vacuum_check_logs.sql:37: INFO: finished vacuuming "postgres.public.vestat": index scans: 0
pages: 0 removed, 7512 remain, 7512 scanned (100.00% of total)
tuples: 0 removed, 1800000 remain, 900000 are dead but not yet removable
removable cutoff: 744, which was 1 XIDs old when operation ended
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
index "vestat_idx": pages: 4171 in total, 0 newly deleted, 218 currently deleted, 218 reusable
I/O timings: read: 0.000 ms, write: 0.058 ms
avg read rate: 0.000 MB/s, avg write rate: 0.066 MB/s
buffer usage: 19424 hits, 0 misses, 6 dirtied
WAL usage: 2 records, 1 full page images, 1529 bytes
system usage: CPU: user: 0.50 s, system: 0.00 s, elapsed: 0.71 s
VACUUM
pg_sleep
----------
(1 row)
CHECKPOINT
dblink_exec
-------------
COMMIT
(1 row)
psql:/home/alena/vacuum_check_logs.sql:44: INFO: vacuuming "postgres.public.vestat"
psql:/home/alena/vacuum_check_logs.sql:44: INFO: finished vacuuming "postgres.public.vestat": index scans: 1
pages: 0 removed, 7512 remain, 7512 scanned (100.00% of total)
tuples: 900000 removed, 900000 remain, 0 are dead but not yet removable
removable cutoff: 745, which was 0 XIDs old when operation ended
new relfrozenxid: 744, which is 3 XIDs ahead of previous value
frozen: 1 pages from table (0.01% of total) had 133 tuples frozen
index scan needed: 3530 pages from table (46.99% of total) had 900000 dead item identifiers removed
index "vestat_idx": pages: 4171 in total, 0 newly deleted, 218 currently deleted, 218 reusable
I/O timings: read: 0.000 ms, write: 0.000 ms
avg read rate: 0.000 MB/s, avg write rate: 18.227 MB/s
buffer usage: 22960 hits, 0 misses, 11456 dirtied
WAL usage: 18513 records, 7471 full page images, 21157546 bytes
system usage: CPU: user: 2.28 s, system: 0.00 s, elapsed: 4.91 s
VACUUM
pg_sleep
----------
(1 row)
CHECKPOINT
DELETE 900000
psql:/home/alena/vacuum_check_logs.sql:51: INFO: vacuuming "postgres.public.vestat"
psql:/home/alena/vacuum_check_logs.sql:51: INFO: table "vestat": truncated 7512 to 0 pages
psql:/home/alena/vacuum_check_logs.sql:51: INFO: finished vacuuming "postgres.public.vestat": index scans: 1
pages: 7512 removed, 0 remain, 3983 scanned (53.02% of total)
tuples: 900000 removed, 422804 remain, 0 are dead but not yet removable
removable cutoff: 746, which was 1 XIDs old when operation ended
new relfrozenxid: 746, which is 2 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan needed: 3983 pages from table (53.02% of total) had 900000 dead item identifiers removed
index "vestat_idx": pages: 4171 in total, 3949 newly deleted, 4167 currently deleted, 218 reusable
I/O timings: read: 0.000 ms, write: 0.000 ms
avg read rate: 0.000 MB/s, avg write rate: 16.897 MB/s
buffer usage: 51482 hits, 0 misses, 3958 dirtied
WAL usage: 23781 records, 3954 full page images, 14444904 bytes
system usage: CPU: user: 0.53 s, system: 0.01 s, elapsed: 1.83 s
VACUUM
pg_sleep
----------
(1 row)
CHECKPOINT
INSERT 0 1000000
UPDATE 1000000
psql:/home/alena/vacuum_check_logs.sql:60: INFO: vacuuming "postgres.public.vestat"
psql:/home/alena/vacuum_check_logs.sql:60: INFO: finished vacuuming "postgres.public.vestat": index scans: 1
pages: 0 removed, 8347 remain, 8347 scanned (100.00% of total)
tuples: 1000000 removed, 1000000 remain, 0 are dead but not yet removable
removable cutoff: 749, which was 0 XIDs old when operation ended
new relfrozenxid: 748, which is 2 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan needed: 3922 pages from table (46.99% of total) had 1000000 dead item identifiers removed
index "vestat_idx": pages: 8341 in total, 0 newly deleted, 3949 currently deleted, 3949 reusable
I/O timings: read: 3.803 ms, write: 1.315 ms
avg read rate: 1.365 MB/s, avg write rate: 3.484 MB/s
buffer usage: 32315 hits, 606 misses, 1547 dirtied
WAL usage: 20571 records, 2 full page images, 9938566 bytes
system usage: CPU: user: 2.78 s, system: 0.01 s, elapsed: 3.46 s
VACUUM
pg_sleep
----------
(1 row)
CHECKPOINT
postgres=# \i ~/vacuum_check_logs.sql
psql:/home/alena/vacuum_check_logs.sql:2: ERROR: extension "dblink" does not exist
CREATE EXTENSION
dblink_connect
----------------
OK
(1 row)
psql:/home/alena/vacuum_check_logs.sql:5: ERROR: table "vestat" does not exist
SET
SET
CREATE TABLE
CREATE INDEX
psql:/home/alena/vacuum_check_logs.sql:15: INFO: vacuuming "postgres.public.vestat"
psql:/home/alena/vacuum_check_logs.sql:15: INFO: finished vacuuming "postgres.public.vestat": index scans: 0
pages: 0 removed, 0 remain, 0 scanned (100.00% of total)
tuples: 0 removed, 0 remain, 0 are dead but not yet removable
removable cutoff: 741, which was 0 XIDs old when operation ended
new relfrozenxid: 741, which is 2 XIDs ahead of previous value
frozen: 0 pages from table (100.00% of total) had 0 tuples frozen
index scan not needed: 0 pages from table (100.00% of total) had 0 dead item identifiers removed
I/O timings: read: 0.092 ms, write: 0.000 ms
avg read rate: 32.964 MB/s, avg write rate: 0.000 MB/s
buffer usage: 13 hits, 1 misses, 0 dirtied
WAL usage: 1 records, 0 full page images, 237 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM
pg_sleep
----------
(1 row)
INSERT 0 1000000
DELETE 100000
psql:/home/alena/vacuum_check_logs.sql:24: INFO: vacuuming "postgres.public.vestat"
psql:/home/alena/vacuum_check_logs.sql:24: INFO: table "vestat": truncated 3922 to 3530 pages
psql:/home/alena/vacuum_check_logs.sql:24: INFO: finished vacuuming "postgres.public.vestat": index scans: 1
pages: 392 removed, 3530 remain, 3922 scanned (100.00% of total)
tuples: 100000 removed, 900000 remain, 0 are dead but not yet removable
removable cutoff: 743, which was 1 XIDs old when operation ended
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan needed: 393 pages from table (10.02% of total) had 100000 dead item identifiers removed
index "vestat_idx": pages: 2197 in total, 218 newly deleted, 218 currently deleted, 0 reusable
I/O timings: read: 0.000 ms, write: 0.033 ms
avg read rate: 0.000 MB/s, avg write rate: 0.057 MB/s
buffer usage: 12585 hits, 0 misses, 4 dirtied
WAL usage: 5369 records, 1 full page images, 928676 bytes
system usage: CPU: user: 0.42 s, system: 0.00 s, elapsed: 0.54 s
VACUUM
pg_sleep
----------
(1 row)
CHECKPOINT
dblink_exec
-------------
BEGIN
(1 row)
a
---
(1 row)
UPDATE 900000
psql:/home/alena/vacuum_check_logs.sql:37: INFO: vacuuming "postgres.public.vestat"
psql:/home/alena/vacuum_check_logs.sql:37: INFO: finished vacuuming "postgres.public.vestat": index scans: 0
pages: 0 removed, 7512 remain, 7512 scanned (100.00% of total)
tuples: 0 removed, 1800000 remain, 900000 are dead but not yet removable
removable cutoff: 744, which was 1 XIDs old when operation ended
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
index "vestat_idx": pages: 4171 in total, 0 newly deleted, 218 currently deleted, 218 reusable
I/O timings: read: 0.000 ms, write: 0.043 ms
avg read rate: 0.000 MB/s, avg write rate: 0.062 MB/s
buffer usage: 19424 hits, 0 misses, 6 dirtied
WAL usage: 2 records, 1 full page images, 1529 bytes
system usage: CPU: user: 0.55 s, system: 0.00 s, elapsed: 0.75 s
VACUUM
pg_sleep
----------
(1 row)
CHECKPOINT
dblink_exec
-------------
COMMIT
(1 row)
psql:/home/alena/vacuum_check_logs.sql:44: INFO: vacuuming "postgres.public.vestat"
psql:/home/alena/vacuum_check_logs.sql:44: INFO: finished vacuuming "postgres.public.vestat": index scans: 1
pages: 0 removed, 7512 remain, 7512 scanned (100.00% of total)
tuples: 900000 removed, 900000 remain, 0 are dead but not yet removable
removable cutoff: 745, which was 0 XIDs old when operation ended
new relfrozenxid: 744, which is 3 XIDs ahead of previous value
frozen: 1 pages from table (0.01% of total) had 133 tuples frozen
index scan needed: 3530 pages from table (46.99% of total) had 900000 dead item identifiers removed
index "vestat_idx": pages: 4171 in total, 0 newly deleted, 218 currently deleted, 218 reusable
I/O timings: read: 0.000 ms, write: 0.000 ms
avg read rate: 0.000 MB/s, avg write rate: 18.654 MB/s
buffer usage: 22960 hits, 0 misses, 11456 dirtied
WAL usage: 18513 records, 7471 full page images, 21157546 bytes
system usage: CPU: user: 2.19 s, system: 0.00 s, elapsed: 4.79 s
VACUUM
pg_sleep
----------
(1 row)
CHECKPOINT
DELETE 900000
psql:/home/alena/vacuum_check_logs.sql:51: INFO: vacuuming "postgres.public.vestat"
psql:/home/alena/vacuum_check_logs.sql:51: INFO: table "vestat": truncated 7512 to 0 pages
psql:/home/alena/vacuum_check_logs.sql:51: INFO: finished vacuuming "postgres.public.vestat": index scans: 1
pages: 7512 removed, 0 remain, 3983 scanned (53.02% of total)
tuples: 900000 removed, 422804 remain, 0 are dead but not yet removable
removable cutoff: 746, which was 1 XIDs old when operation ended
new relfrozenxid: 746, which is 2 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan needed: 3983 pages from table (53.02% of total) had 900000 dead item identifiers removed
index "vestat_idx": pages: 4171 in total, 3949 newly deleted, 4167 currently deleted, 218 reusable
I/O timings: read: 0.000 ms, write: 0.000 ms
avg read rate: 0.000 MB/s, avg write rate: 16.985 MB/s
buffer usage: 51482 hits, 0 misses, 3958 dirtied
WAL usage: 23781 records, 3954 full page images, 14444904 bytes
system usage: CPU: user: 0.52 s, system: 0.01 s, elapsed: 1.82 s
VACUUM
pg_sleep
----------
(1 row)
CHECKPOINT
INSERT 0 1000000
UPDATE 1000000
psql:/home/alena/vacuum_check_logs.sql:60: INFO: vacuuming "postgres.public.vestat"
psql:/home/alena/vacuum_check_logs.sql:60: INFO: finished vacuuming "postgres.public.vestat": index scans: 1
pages: 0 removed, 8347 remain, 8347 scanned (100.00% of total)
tuples: 1000000 removed, 1000000 remain, 0 are dead but not yet removable
removable cutoff: 749, which was 0 XIDs old when operation ended
new relfrozenxid: 748, which is 2 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan needed: 3922 pages from table (46.99% of total) had 1000000 dead item identifiers removed
index "vestat_idx": pages: 8341 in total, 0 newly deleted, 3949 currently deleted, 3949 reusable
I/O timings: read: 3.922 ms, write: 1.230 ms
avg read rate: 1.336 MB/s, avg write rate: 3.396 MB/s
buffer usage: 32348 hits, 573 misses, 1456 dirtied
WAL usage: 20571 records, 2 full page images, 9938566 bytes
system usage: CPU: user: 2.69 s, system: 0.01 s, elapsed: 3.34 s
VACUUM
pg_sleep
----------
(1 row)
CHECKPOINT
postgres=# \i vacuum_test_analyze.sql
psql:vacuum_test_analyze.sql:2: ERROR: extension "dblink" does not exist
CREATE EXTENSION
dblink_connect
----------------
OK
(1 row)
psql:vacuum_test_analyze.sql:5: ERROR: table "vestat" does not exist
SET
SET
CREATE TABLE
CREATE INDEX
psql:vacuum_test_analyze.sql:15: INFO: vacuuming "postgres.public.vestat"
psql:vacuum_test_analyze.sql:15: INFO: finished vacuuming "postgres.public.vestat": index scans: 0
pages: 0 removed, 0 remain, 0 scanned (100.00% of total)
tuples: 0 removed, 0 remain, 0 are dead but not yet removable
removable cutoff: 741, which was 0 XIDs old when operation ended
new relfrozenxid: 741, which is 2 XIDs ahead of previous value
frozen: 0 pages from table (100.00% of total) had 0 tuples frozen
index scan not needed: 0 pages from table (100.00% of total) had 0 dead item identifiers removed
I/O timings: read: 0.111 ms, write: 0.000 ms
avg read rate: 28.617 MB/s, avg write rate: 0.000 MB/s
buffer usage: 13 hits, 1 misses, 0 dirtied
WAL usage: 1 records, 0 full page images, 237 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
psql:vacuum_test_analyze.sql:15: INFO: analyzing "public.vestat"
psql:vacuum_test_analyze.sql:15: INFO: "vestat": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
VACUUM
pg_sleep
----------
(1 row)
INSERT 0 1000000
DELETE 100000
psql:vacuum_test_analyze.sql:24: INFO: vacuuming "postgres.public.vestat"
psql:vacuum_test_analyze.sql:24: INFO: table "vestat": truncated 3922 to 3530 pages
psql:vacuum_test_analyze.sql:24: INFO: finished vacuuming "postgres.public.vestat": index scans: 1
pages: 392 removed, 3530 remain, 3922 scanned (100.00% of total)
tuples: 100000 removed, 900000 remain, 0 are dead but not yet removable
removable cutoff: 743, which was 1 XIDs old when operation ended
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan needed: 393 pages from table (10.02% of total) had 100000 dead item identifiers removed
index "vestat_idx": pages: 2197 in total, 218 newly deleted, 218 currently deleted, 0 reusable
I/O timings: read: 0.000 ms, write: 0.029 ms
avg read rate: 0.000 MB/s, avg write rate: 0.060 MB/s
buffer usage: 12583 hits, 0 misses, 4 dirtied
WAL usage: 5369 records, 1 full page images, 928676 bytes
system usage: CPU: user: 0.39 s, system: 0.00 s, elapsed: 0.52 s
psql:vacuum_test_analyze.sql:24: INFO: analyzing "public.vestat"
psql:vacuum_test_analyze.sql:24: INFO: "vestat": scanned 3530 of 3530 pages, containing 900000 live rows and 0 dead rows; 30000 rows in sample, 900000 estimated total rows
VACUUM
pg_sleep
----------
(1 row)
CHECKPOINT
dblink_exec
-------------
BEGIN
(1 row)
a
---
(1 row)
UPDATE 900000
psql:vacuum_test_analyze.sql:37: INFO: vacuuming "postgres.public.vestat"
psql:vacuum_test_analyze.sql:37: INFO: finished vacuuming "postgres.public.vestat": index scans: 0
pages: 0 removed, 7512 remain, 7512 scanned (100.00% of total)
tuples: 0 removed, 1800000 remain, 900000 are dead but not yet removable
removable cutoff: 745, which was 1 XIDs old when operation ended
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
index "vestat_idx": pages: 4171 in total, 0 newly deleted, 218 currently deleted, 218 reusable
I/O timings: read: 0.000 ms, write: 0.055 ms
avg read rate: 0.000 MB/s, avg write rate: 0.063 MB/s
buffer usage: 19424 hits, 0 misses, 6 dirtied
WAL usage: 2 records, 1 full page images, 1529 bytes
system usage: CPU: user: 0.53 s, system: 0.00 s, elapsed: 0.73 s
psql:vacuum_test_analyze.sql:37: INFO: analyzing "public.vestat"
psql:vacuum_test_analyze.sql:37: INFO: "vestat": scanned 7512 of 7512 pages, containing 900000 live rows and 900000 dead rows; 30000 rows in sample, 900000 estimated total rows
VACUUM
pg_sleep
----------
(1 row)
CHECKPOINT
dblink_exec
-------------
COMMIT
(1 row)
psql:vacuum_test_analyze.sql:44: INFO: vacuuming "postgres.public.vestat"
psql:vacuum_test_analyze.sql:44: INFO: finished vacuuming "postgres.public.vestat": index scans: 1
pages: 0 removed, 7512 remain, 7512 scanned (100.00% of total)
tuples: 900000 removed, 900000 remain, 0 are dead but not yet removable
removable cutoff: 747, which was 0 XIDs old when operation ended
new relfrozenxid: 745, which is 4 XIDs ahead of previous value
frozen: 1 pages from table (0.01% of total) had 133 tuples frozen
index scan needed: 3530 pages from table (46.99% of total) had 900000 dead item identifiers removed
index "vestat_idx": pages: 4171 in total, 0 newly deleted, 218 currently deleted, 218 reusable
I/O timings: read: 0.000 ms, write: 0.000 ms
avg read rate: 0.000 MB/s, avg write rate: 18.429 MB/s
buffer usage: 22962 hits, 0 misses, 11456 dirtied
WAL usage: 18512 records, 7471 full page images, 21157309 bytes
system usage: CPU: user: 2.24 s, system: 0.00 s, elapsed: 4.85 s
psql:vacuum_test_analyze.sql:44: INFO: analyzing "public.vestat"
psql:vacuum_test_analyze.sql:44: INFO: "vestat": scanned 7512 of 7512 pages, containing 900000 live rows and 0 dead rows; 30000 rows in sample, 900000 estimated total rows
VACUUM
pg_sleep
----------
(1 row)
CHECKPOINT
DELETE 900000
psql:vacuum_test_analyze.sql:51: INFO: vacuuming "postgres.public.vestat"
psql:vacuum_test_analyze.sql:51: INFO: table "vestat": truncated 7512 to 0 pages
psql:vacuum_test_analyze.sql:51: INFO: finished vacuuming "postgres.public.vestat": index scans: 1
pages: 7512 removed, 0 remain, 3983 scanned (53.02% of total)
tuples: 900000 removed, 422804 remain, 0 are dead but not yet removable
removable cutoff: 749, which was 1 XIDs old when operation ended
new relfrozenxid: 749, which is 4 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan needed: 3983 pages from table (53.02% of total) had 900000 dead item identifiers removed
index "vestat_idx": pages: 4171 in total, 3949 newly deleted, 4167 currently deleted, 218 reusable
I/O timings: read: 0.000 ms, write: 0.000 ms
avg read rate: 0.000 MB/s, avg write rate: 17.193 MB/s
buffer usage: 51480 hits, 0 misses, 3958 dirtied
WAL usage: 23781 records, 3954 full page images, 14444904 bytes
system usage: CPU: user: 0.51 s, system: 0.01 s, elapsed: 1.79 s
psql:vacuum_test_analyze.sql:51: INFO: analyzing "public.vestat"
psql:vacuum_test_analyze.sql:51: INFO: "vestat": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
VACUUM
pg_sleep
----------
(1 row)
CHECKPOINT
INSERT 0 1000000
UPDATE 1000000
psql:vacuum_test_analyze.sql:60: INFO: vacuuming "postgres.public.vestat"
psql:vacuum_test_analyze.sql:60: INFO: finished vacuuming "postgres.public.vestat": index scans: 1
pages: 0 removed, 8347 remain, 8347 scanned (100.00% of total)
tuples: 1000000 removed, 1000000 remain, 0 are dead but not yet removable
removable cutoff: 752, which was 0 XIDs old when operation ended
new relfrozenxid: 751, which is 2 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan needed: 3922 pages from table (46.99% of total) had 1000000 dead item identifiers removed
index "vestat_idx": pages: 8341 in total, 0 newly deleted, 3949 currently deleted, 3949 reusable
I/O timings: read: 5.693 ms, write: 1.319 ms
avg read rate: 1.421 MB/s, avg write rate: 3.666 MB/s
buffer usage: 32312 hits, 607 misses, 1566 dirtied
WAL usage: 20571 records, 2 full page images, 9938566 bytes
system usage: CPU: user: 2.65 s, system: 0.00 s, elapsed: 3.33 s
psql:vacuum_test_analyze.sql:60: INFO: analyzing "public.vestat"
psql:vacuum_test_analyze.sql:60: INFO: "vestat": scanned 8347 of 8347 pages, containing 1000000 live rows and 0 dead rows; 30000 rows in sample, 1000000 estimated total rows
VACUUM
pg_sleep
----------
(1 row)
CHECKPOINT
postgres=# \i vacuum_test_analyze.sql
psql:vacuum_test_analyze.sql:2: ERROR: extension "dblink" does not exist
CREATE EXTENSION
dblink_connect
----------------
OK
(1 row)
psql:vacuum_test_analyze.sql:5: ERROR: table "vestat" does not exist
SET
SET
CREATE TABLE
CREATE INDEX
psql:vacuum_test_analyze.sql:15: INFO: vacuuming "postgres.public.vestat"
psql:vacuum_test_analyze.sql:15: INFO: finished vacuuming "postgres.public.vestat": index scans: 0
pages: 0 removed, 0 remain, 0 scanned (100.00% of total)
tuples: 0 removed, 0 remain, 0 are dead but not yet removable
removable cutoff: 741, which was 0 XIDs old when operation ended
new relfrozenxid: 741, which is 2 XIDs ahead of previous value
frozen: 0 pages from table (100.00% of total) had 0 tuples frozen
index scan not needed: 0 pages from table (100.00% of total) had 0 dead item identifiers removed
I/O timings: read: 0.107 ms, write: 0.000 ms
avg read rate: 28.617 MB/s, avg write rate: 0.000 MB/s
buffer usage: 13 hits, 1 misses, 0 dirtied
WAL usage: 1 records, 0 full page images, 237 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
psql:vacuum_test_analyze.sql:15: INFO: analyzing "public.vestat"
psql:vacuum_test_analyze.sql:15: INFO: "vestat": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
VACUUM
pg_sleep
----------
(1 row)
INSERT 0 1000000
DELETE 100000
psql:vacuum_test_analyze.sql:24: INFO: vacuuming "postgres.public.vestat"
psql:vacuum_test_analyze.sql:24: INFO: table "vestat": truncated 3922 to 3530 pages
psql:vacuum_test_analyze.sql:24: INFO: finished vacuuming "postgres.public.vestat": index scans: 1
pages: 392 removed, 3530 remain, 3922 scanned (100.00% of total)
tuples: 100000 removed, 900000 remain, 0 are dead but not yet removable
removable cutoff: 743, which was 1 XIDs old when operation ended
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan needed: 393 pages from table (10.02% of total) had 100000 dead item identifiers removed
index "vestat_idx": pages: 2197 in total, 218 newly deleted, 218 currently deleted, 0 reusable
I/O timings: read: 0.000 ms, write: 0.058 ms
avg read rate: 0.000 MB/s, avg write rate: 0.054 MB/s
buffer usage: 12583 hits, 0 misses, 4 dirtied
WAL usage: 5369 records, 1 full page images, 928676 bytes
system usage: CPU: user: 0.44 s, system: 0.00 s, elapsed: 0.57 s
psql:vacuum_test_analyze.sql:24: INFO: analyzing "public.vestat"
psql:vacuum_test_analyze.sql:24: INFO: "vestat": scanned 3530 of 3530 pages, containing 900000 live rows and 0 dead rows; 30000 rows in sample, 900000 estimated total rows
VACUUM
pg_sleep
----------
(1 row)
CHECKPOINT
dblink_exec
-------------
BEGIN
(1 row)
a
---
(1 row)
UPDATE 900000
psql:vacuum_test_analyze.sql:37: INFO: vacuuming "postgres.public.vestat"
psql:vacuum_test_analyze.sql:37: INFO: finished vacuuming "postgres.public.vestat": index scans: 0
pages: 0 removed, 7512 remain, 7512 scanned (100.00% of total)
tuples: 0 removed, 1800000 remain, 900000 are dead but not yet removable
removable cutoff: 745, which was 1 XIDs old when operation ended
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
index "vestat_idx": pages: 4171 in total, 0 newly deleted, 218 currently deleted, 218 reusable
I/O timings: read: 0.000 ms, write: 0.053 ms
avg read rate: 0.000 MB/s, avg write rate: 0.062 MB/s
buffer usage: 19424 hits, 0 misses, 6 dirtied
WAL usage: 2 records, 1 full page images, 1529 bytes
system usage: CPU: user: 0.54 s, system: 0.00 s, elapsed: 0.75 s
psql:vacuum_test_analyze.sql:37: INFO: analyzing "public.vestat"
psql:vacuum_test_analyze.sql:37: INFO: "vestat": scanned 7512 of 7512 pages, containing 900000 live rows and 900000 dead rows; 30000 rows in sample, 900000 estimated total rows
VACUUM
pg_sleep
----------
(1 row)
CHECKPOINT
dblink_exec
-------------
COMMIT
(1 row)
psql:vacuum_test_analyze.sql:44: INFO: vacuuming "postgres.public.vestat"
psql:vacuum_test_analyze.sql:44: INFO: finished vacuuming "postgres.public.vestat": index scans: 1
pages: 0 removed, 7512 remain, 7512 scanned (100.00% of total)
tuples: 900000 removed, 900000 remain, 0 are dead but not yet removable
removable cutoff: 747, which was 0 XIDs old when operation ended
new relfrozenxid: 745, which is 4 XIDs ahead of previous value
frozen: 1 pages from table (0.01% of total) had 133 tuples frozen
index scan needed: 3530 pages from table (46.99% of total) had 900000 dead item identifiers removed
index "vestat_idx": pages: 4171 in total, 0 newly deleted, 218 currently deleted, 218 reusable
I/O timings: read: 0.000 ms, write: 0.000 ms
avg read rate: 0.000 MB/s, avg write rate: 18.508 MB/s
buffer usage: 22962 hits, 0 misses, 11456 dirtied
WAL usage: 18512 records, 7471 full page images, 21157309 bytes
system usage: CPU: user: 2.24 s, system: 0.00 s, elapsed: 4.83 s
psql:vacuum_test_analyze.sql:44: INFO: analyzing "public.vestat"
psql:vacuum_test_analyze.sql:44: INFO: "vestat": scanned 7512 of 7512 pages, containing 900000 live rows and 0 dead rows; 30000 rows in sample, 900000 estimated total rows
VACUUM
pg_sleep
----------
(1 row)
CHECKPOINT
DELETE 900000
psql:vacuum_test_analyze.sql:51: INFO: vacuuming "postgres.public.vestat"
psql:vacuum_test_analyze.sql:51: INFO: table "vestat": truncated 7512 to 0 pages
psql:vacuum_test_analyze.sql:51: INFO: finished vacuuming "postgres.public.vestat": index scans: 1
pages: 7512 removed, 0 remain, 3983 scanned (53.02% of total)
tuples: 900000 removed, 422804 remain, 0 are dead but not yet removable
removable cutoff: 749, which was 1 XIDs old when operation ended
new relfrozenxid: 749, which is 4 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan needed: 3983 pages from table (53.02% of total) had 900000 dead item identifiers removed
index "vestat_idx": pages: 4171 in total, 3949 newly deleted, 4167 currently deleted, 218 reusable
I/O timings: read: 0.000 ms, write: 0.000 ms
avg read rate: 0.000 MB/s, avg write rate: 16.836 MB/s
buffer usage: 51480 hits, 0 misses, 3958 dirtied
WAL usage: 23781 records, 3954 full page images, 14444904 bytes
system usage: CPU: user: 0.55 s, system: 0.00 s, elapsed: 1.83 s
psql:vacuum_test_analyze.sql:51: INFO: analyzing "public.vestat"
psql:vacuum_test_analyze.sql:51: INFO: "vestat": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
VACUUM
pg_sleep
----------
(1 row)
CHECKPOINT
INSERT 0 1000000
UPDATE 1000000
psql:vacuum_test_analyze.sql:60: INFO: vacuuming "postgres.public.vestat"
psql:vacuum_test_analyze.sql:60: INFO: finished vacuuming "postgres.public.vestat": index scans: 1
pages: 0 removed, 8347 remain, 8347 scanned (100.00% of total)
tuples: 1000000 removed, 1000000 remain, 0 are dead but not yet removable
removable cutoff: 752, which was 0 XIDs old when operation ended
new relfrozenxid: 751, which is 2 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan needed: 3922 pages from table (46.99% of total) had 1000000 dead item identifiers removed
index "vestat_idx": pages: 8341 in total, 0 newly deleted, 3949 currently deleted, 3949 reusable
I/O timings: read: 3.803 ms, write: 1.236 ms
avg read rate: 1.351 MB/s, avg write rate: 3.208 MB/s
buffer usage: 32346 hits, 573 misses, 1360 dirtied
WAL usage: 20571 records, 2 full page images, 9938566 bytes
system usage: CPU: user: 2.67 s, system: 0.01 s, elapsed: 3.31 s
psql:vacuum_test_analyze.sql:60: INFO: analyzing "public.vestat"
psql:vacuum_test_analyze.sql:60: INFO: "vestat": scanned 8347 of 8347 pages, containing 1000000 live rows and 0 dead rows; 30000 rows in sample, 1000000 estimated total rows
VACUUM
pg_sleep
----------
(1 row)
CHECKPOINT
Attachments:
[text/plain] vacuum_log_with_patch (6.5K, ../../[email protected]/3-vacuum_log_with_patch)
download | inline:
postgres=# \i ~/vacuum_check_logs.sql
psql:/home/alena/vacuum_check_logs.sql:2: ERROR: extension "dblink" does not exist
CREATE EXTENSION
dblink_connect
----------------
OK
(1 row)
psql:/home/alena/vacuum_check_logs.sql:5: ERROR: table "vestat" does not exist
SET
SET
CREATE TABLE
CREATE INDEX
psql:/home/alena/vacuum_check_logs.sql:15: INFO: vacuuming "postgres.public.vestat"
psql:/home/alena/vacuum_check_logs.sql:15: INFO: finished vacuuming "postgres.public.vestat": index scans: 0
pages: 0 removed, 0 remain, 0 scanned (100.00% of total)
tuples: 0 removed, 0 remain, 0 are dead but not yet removable
removable cutoff: 741, which was 0 XIDs old when operation ended
new relfrozenxid: 741, which is 2 XIDs ahead of previous value
frozen: 0 pages from table (100.00% of total) had 0 tuples frozen
index scan not needed: 0 pages from table (100.00% of total) had 0 dead item identifiers removed
I/O timings: read: 0.100 ms, write: 0.000 ms
avg read rate: 29.260 MB/s, avg write rate: 0.000 MB/s
buffer usage: 13 hits, 1 misses, 0 dirtied
WAL usage: 1 records, 0 full page images, 237 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM
pg_sleep
----------
(1 row)
INSERT 0 1000000
DELETE 100000
psql:/home/alena/vacuum_check_logs.sql:24: INFO: vacuuming "postgres.public.vestat"
psql:/home/alena/vacuum_check_logs.sql:24: INFO: table "vestat": truncated 3922 to 3530 pages
psql:/home/alena/vacuum_check_logs.sql:24: INFO: finished vacuuming "postgres.public.vestat": index scans: 1
pages: 392 removed, 3530 remain, 3922 scanned (100.00% of total)
tuples: 100000 removed, 900000 remain, 0 are dead but not yet removable
removable cutoff: 743, which was 1 XIDs old when operation ended
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan needed: 393 pages from table (10.02% of total) had 100000 dead item identifiers removed
index "vestat_idx": pages: 2197 in total, 218 newly deleted, 218 currently deleted, 0 reusable
I/O timings: read: 0.000 ms, write: 0.029 ms
avg read rate: 0.000 MB/s, avg write rate: 0.062 MB/s
buffer usage: 12585 hits, 0 misses, 4 dirtied
WAL usage: 5369 records, 1 full page images, 928676 bytes
system usage: CPU: user: 0.37 s, system: 0.00 s, elapsed: 0.50 s
VACUUM
pg_sleep
----------
(1 row)
CHECKPOINT
dblink_exec
-------------
BEGIN
(1 row)
a
---
(1 row)
UPDATE 900000
psql:/home/alena/vacuum_check_logs.sql:37: INFO: vacuuming "postgres.public.vestat"
psql:/home/alena/vacuum_check_logs.sql:37: INFO: finished vacuuming "postgres.public.vestat": index scans: 0
pages: 0 removed, 7512 remain, 7512 scanned (100.00% of total)
tuples: 0 removed, 1800000 remain, 900000 are dead but not yet removable
removable cutoff: 744, which was 1 XIDs old when operation ended
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
index "vestat_idx": pages: 4171 in total, 0 newly deleted, 218 currently deleted, 218 reusable
I/O timings: read: 0.000 ms, write: 0.058 ms
avg read rate: 0.000 MB/s, avg write rate: 0.066 MB/s
buffer usage: 19424 hits, 0 misses, 6 dirtied
WAL usage: 2 records, 1 full page images, 1529 bytes
system usage: CPU: user: 0.50 s, system: 0.00 s, elapsed: 0.71 s
VACUUM
pg_sleep
----------
(1 row)
CHECKPOINT
dblink_exec
-------------
COMMIT
(1 row)
psql:/home/alena/vacuum_check_logs.sql:44: INFO: vacuuming "postgres.public.vestat"
psql:/home/alena/vacuum_check_logs.sql:44: INFO: finished vacuuming "postgres.public.vestat": index scans: 1
pages: 0 removed, 7512 remain, 7512 scanned (100.00% of total)
tuples: 900000 removed, 900000 remain, 0 are dead but not yet removable
removable cutoff: 745, which was 0 XIDs old when operation ended
new relfrozenxid: 744, which is 3 XIDs ahead of previous value
frozen: 1 pages from table (0.01% of total) had 133 tuples frozen
index scan needed: 3530 pages from table (46.99% of total) had 900000 dead item identifiers removed
index "vestat_idx": pages: 4171 in total, 0 newly deleted, 218 currently deleted, 218 reusable
I/O timings: read: 0.000 ms, write: 0.000 ms
avg read rate: 0.000 MB/s, avg write rate: 18.227 MB/s
buffer usage: 22960 hits, 0 misses, 11456 dirtied
WAL usage: 18513 records, 7471 full page images, 21157546 bytes
system usage: CPU: user: 2.28 s, system: 0.00 s, elapsed: 4.91 s
VACUUM
pg_sleep
----------
(1 row)
CHECKPOINT
DELETE 900000
psql:/home/alena/vacuum_check_logs.sql:51: INFO: vacuuming "postgres.public.vestat"
psql:/home/alena/vacuum_check_logs.sql:51: INFO: table "vestat": truncated 7512 to 0 pages
psql:/home/alena/vacuum_check_logs.sql:51: INFO: finished vacuuming "postgres.public.vestat": index scans: 1
pages: 7512 removed, 0 remain, 3983 scanned (53.02% of total)
tuples: 900000 removed, 422804 remain, 0 are dead but not yet removable
removable cutoff: 746, which was 1 XIDs old when operation ended
new relfrozenxid: 746, which is 2 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan needed: 3983 pages from table (53.02% of total) had 900000 dead item identifiers removed
index "vestat_idx": pages: 4171 in total, 3949 newly deleted, 4167 currently deleted, 218 reusable
I/O timings: read: 0.000 ms, write: 0.000 ms
avg read rate: 0.000 MB/s, avg write rate: 16.897 MB/s
buffer usage: 51482 hits, 0 misses, 3958 dirtied
WAL usage: 23781 records, 3954 full page images, 14444904 bytes
system usage: CPU: user: 0.53 s, system: 0.01 s, elapsed: 1.83 s
VACUUM
pg_sleep
----------
(1 row)
CHECKPOINT
INSERT 0 1000000
UPDATE 1000000
psql:/home/alena/vacuum_check_logs.sql:60: INFO: vacuuming "postgres.public.vestat"
psql:/home/alena/vacuum_check_logs.sql:60: INFO: finished vacuuming "postgres.public.vestat": index scans: 1
pages: 0 removed, 8347 remain, 8347 scanned (100.00% of total)
tuples: 1000000 removed, 1000000 remain, 0 are dead but not yet removable
removable cutoff: 749, which was 0 XIDs old when operation ended
new relfrozenxid: 748, which is 2 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan needed: 3922 pages from table (46.99% of total) had 1000000 dead item identifiers removed
index "vestat_idx": pages: 8341 in total, 0 newly deleted, 3949 currently deleted, 3949 reusable
I/O timings: read: 3.803 ms, write: 1.315 ms
avg read rate: 1.365 MB/s, avg write rate: 3.484 MB/s
buffer usage: 32315 hits, 606 misses, 1547 dirtied
WAL usage: 20571 records, 2 full page images, 9938566 bytes
system usage: CPU: user: 2.78 s, system: 0.01 s, elapsed: 3.46 s
VACUUM
pg_sleep
----------
(1 row)
CHECKPOINT
[text/plain] vacuum_log (6.5K, ../../[email protected]/4-vacuum_log)
download | inline:
postgres=# \i ~/vacuum_check_logs.sql
psql:/home/alena/vacuum_check_logs.sql:2: ERROR: extension "dblink" does not exist
CREATE EXTENSION
dblink_connect
----------------
OK
(1 row)
psql:/home/alena/vacuum_check_logs.sql:5: ERROR: table "vestat" does not exist
SET
SET
CREATE TABLE
CREATE INDEX
psql:/home/alena/vacuum_check_logs.sql:15: INFO: vacuuming "postgres.public.vestat"
psql:/home/alena/vacuum_check_logs.sql:15: INFO: finished vacuuming "postgres.public.vestat": index scans: 0
pages: 0 removed, 0 remain, 0 scanned (100.00% of total)
tuples: 0 removed, 0 remain, 0 are dead but not yet removable
removable cutoff: 741, which was 0 XIDs old when operation ended
new relfrozenxid: 741, which is 2 XIDs ahead of previous value
frozen: 0 pages from table (100.00% of total) had 0 tuples frozen
index scan not needed: 0 pages from table (100.00% of total) had 0 dead item identifiers removed
I/O timings: read: 0.092 ms, write: 0.000 ms
avg read rate: 32.964 MB/s, avg write rate: 0.000 MB/s
buffer usage: 13 hits, 1 misses, 0 dirtied
WAL usage: 1 records, 0 full page images, 237 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM
pg_sleep
----------
(1 row)
INSERT 0 1000000
DELETE 100000
psql:/home/alena/vacuum_check_logs.sql:24: INFO: vacuuming "postgres.public.vestat"
psql:/home/alena/vacuum_check_logs.sql:24: INFO: table "vestat": truncated 3922 to 3530 pages
psql:/home/alena/vacuum_check_logs.sql:24: INFO: finished vacuuming "postgres.public.vestat": index scans: 1
pages: 392 removed, 3530 remain, 3922 scanned (100.00% of total)
tuples: 100000 removed, 900000 remain, 0 are dead but not yet removable
removable cutoff: 743, which was 1 XIDs old when operation ended
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan needed: 393 pages from table (10.02% of total) had 100000 dead item identifiers removed
index "vestat_idx": pages: 2197 in total, 218 newly deleted, 218 currently deleted, 0 reusable
I/O timings: read: 0.000 ms, write: 0.033 ms
avg read rate: 0.000 MB/s, avg write rate: 0.057 MB/s
buffer usage: 12585 hits, 0 misses, 4 dirtied
WAL usage: 5369 records, 1 full page images, 928676 bytes
system usage: CPU: user: 0.42 s, system: 0.00 s, elapsed: 0.54 s
VACUUM
pg_sleep
----------
(1 row)
CHECKPOINT
dblink_exec
-------------
BEGIN
(1 row)
a
---
(1 row)
UPDATE 900000
psql:/home/alena/vacuum_check_logs.sql:37: INFO: vacuuming "postgres.public.vestat"
psql:/home/alena/vacuum_check_logs.sql:37: INFO: finished vacuuming "postgres.public.vestat": index scans: 0
pages: 0 removed, 7512 remain, 7512 scanned (100.00% of total)
tuples: 0 removed, 1800000 remain, 900000 are dead but not yet removable
removable cutoff: 744, which was 1 XIDs old when operation ended
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
index "vestat_idx": pages: 4171 in total, 0 newly deleted, 218 currently deleted, 218 reusable
I/O timings: read: 0.000 ms, write: 0.043 ms
avg read rate: 0.000 MB/s, avg write rate: 0.062 MB/s
buffer usage: 19424 hits, 0 misses, 6 dirtied
WAL usage: 2 records, 1 full page images, 1529 bytes
system usage: CPU: user: 0.55 s, system: 0.00 s, elapsed: 0.75 s
VACUUM
pg_sleep
----------
(1 row)
CHECKPOINT
dblink_exec
-------------
COMMIT
(1 row)
psql:/home/alena/vacuum_check_logs.sql:44: INFO: vacuuming "postgres.public.vestat"
psql:/home/alena/vacuum_check_logs.sql:44: INFO: finished vacuuming "postgres.public.vestat": index scans: 1
pages: 0 removed, 7512 remain, 7512 scanned (100.00% of total)
tuples: 900000 removed, 900000 remain, 0 are dead but not yet removable
removable cutoff: 745, which was 0 XIDs old when operation ended
new relfrozenxid: 744, which is 3 XIDs ahead of previous value
frozen: 1 pages from table (0.01% of total) had 133 tuples frozen
index scan needed: 3530 pages from table (46.99% of total) had 900000 dead item identifiers removed
index "vestat_idx": pages: 4171 in total, 0 newly deleted, 218 currently deleted, 218 reusable
I/O timings: read: 0.000 ms, write: 0.000 ms
avg read rate: 0.000 MB/s, avg write rate: 18.654 MB/s
buffer usage: 22960 hits, 0 misses, 11456 dirtied
WAL usage: 18513 records, 7471 full page images, 21157546 bytes
system usage: CPU: user: 2.19 s, system: 0.00 s, elapsed: 4.79 s
VACUUM
pg_sleep
----------
(1 row)
CHECKPOINT
DELETE 900000
psql:/home/alena/vacuum_check_logs.sql:51: INFO: vacuuming "postgres.public.vestat"
psql:/home/alena/vacuum_check_logs.sql:51: INFO: table "vestat": truncated 7512 to 0 pages
psql:/home/alena/vacuum_check_logs.sql:51: INFO: finished vacuuming "postgres.public.vestat": index scans: 1
pages: 7512 removed, 0 remain, 3983 scanned (53.02% of total)
tuples: 900000 removed, 422804 remain, 0 are dead but not yet removable
removable cutoff: 746, which was 1 XIDs old when operation ended
new relfrozenxid: 746, which is 2 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan needed: 3983 pages from table (53.02% of total) had 900000 dead item identifiers removed
index "vestat_idx": pages: 4171 in total, 3949 newly deleted, 4167 currently deleted, 218 reusable
I/O timings: read: 0.000 ms, write: 0.000 ms
avg read rate: 0.000 MB/s, avg write rate: 16.985 MB/s
buffer usage: 51482 hits, 0 misses, 3958 dirtied
WAL usage: 23781 records, 3954 full page images, 14444904 bytes
system usage: CPU: user: 0.52 s, system: 0.01 s, elapsed: 1.82 s
VACUUM
pg_sleep
----------
(1 row)
CHECKPOINT
INSERT 0 1000000
UPDATE 1000000
psql:/home/alena/vacuum_check_logs.sql:60: INFO: vacuuming "postgres.public.vestat"
psql:/home/alena/vacuum_check_logs.sql:60: INFO: finished vacuuming "postgres.public.vestat": index scans: 1
pages: 0 removed, 8347 remain, 8347 scanned (100.00% of total)
tuples: 1000000 removed, 1000000 remain, 0 are dead but not yet removable
removable cutoff: 749, which was 0 XIDs old when operation ended
new relfrozenxid: 748, which is 2 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan needed: 3922 pages from table (46.99% of total) had 1000000 dead item identifiers removed
index "vestat_idx": pages: 8341 in total, 0 newly deleted, 3949 currently deleted, 3949 reusable
I/O timings: read: 3.922 ms, write: 1.230 ms
avg read rate: 1.336 MB/s, avg write rate: 3.396 MB/s
buffer usage: 32348 hits, 573 misses, 1456 dirtied
WAL usage: 20571 records, 2 full page images, 9938566 bytes
system usage: CPU: user: 2.69 s, system: 0.01 s, elapsed: 3.34 s
VACUUM
pg_sleep
----------
(1 row)
CHECKPOINT
[text/plain] vacuum_analyze_test_with_patch (7.7K, ../../[email protected]/5-vacuum_analyze_test_with_patch)
download | inline:
postgres=# \i vacuum_test_analyze.sql
psql:vacuum_test_analyze.sql:2: ERROR: extension "dblink" does not exist
CREATE EXTENSION
dblink_connect
----------------
OK
(1 row)
psql:vacuum_test_analyze.sql:5: ERROR: table "vestat" does not exist
SET
SET
CREATE TABLE
CREATE INDEX
psql:vacuum_test_analyze.sql:15: INFO: vacuuming "postgres.public.vestat"
psql:vacuum_test_analyze.sql:15: INFO: finished vacuuming "postgres.public.vestat": index scans: 0
pages: 0 removed, 0 remain, 0 scanned (100.00% of total)
tuples: 0 removed, 0 remain, 0 are dead but not yet removable
removable cutoff: 741, which was 0 XIDs old when operation ended
new relfrozenxid: 741, which is 2 XIDs ahead of previous value
frozen: 0 pages from table (100.00% of total) had 0 tuples frozen
index scan not needed: 0 pages from table (100.00% of total) had 0 dead item identifiers removed
I/O timings: read: 0.111 ms, write: 0.000 ms
avg read rate: 28.617 MB/s, avg write rate: 0.000 MB/s
buffer usage: 13 hits, 1 misses, 0 dirtied
WAL usage: 1 records, 0 full page images, 237 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
psql:vacuum_test_analyze.sql:15: INFO: analyzing "public.vestat"
psql:vacuum_test_analyze.sql:15: INFO: "vestat": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
VACUUM
pg_sleep
----------
(1 row)
INSERT 0 1000000
DELETE 100000
psql:vacuum_test_analyze.sql:24: INFO: vacuuming "postgres.public.vestat"
psql:vacuum_test_analyze.sql:24: INFO: table "vestat": truncated 3922 to 3530 pages
psql:vacuum_test_analyze.sql:24: INFO: finished vacuuming "postgres.public.vestat": index scans: 1
pages: 392 removed, 3530 remain, 3922 scanned (100.00% of total)
tuples: 100000 removed, 900000 remain, 0 are dead but not yet removable
removable cutoff: 743, which was 1 XIDs old when operation ended
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan needed: 393 pages from table (10.02% of total) had 100000 dead item identifiers removed
index "vestat_idx": pages: 2197 in total, 218 newly deleted, 218 currently deleted, 0 reusable
I/O timings: read: 0.000 ms, write: 0.029 ms
avg read rate: 0.000 MB/s, avg write rate: 0.060 MB/s
buffer usage: 12583 hits, 0 misses, 4 dirtied
WAL usage: 5369 records, 1 full page images, 928676 bytes
system usage: CPU: user: 0.39 s, system: 0.00 s, elapsed: 0.52 s
psql:vacuum_test_analyze.sql:24: INFO: analyzing "public.vestat"
psql:vacuum_test_analyze.sql:24: INFO: "vestat": scanned 3530 of 3530 pages, containing 900000 live rows and 0 dead rows; 30000 rows in sample, 900000 estimated total rows
VACUUM
pg_sleep
----------
(1 row)
CHECKPOINT
dblink_exec
-------------
BEGIN
(1 row)
a
---
(1 row)
UPDATE 900000
psql:vacuum_test_analyze.sql:37: INFO: vacuuming "postgres.public.vestat"
psql:vacuum_test_analyze.sql:37: INFO: finished vacuuming "postgres.public.vestat": index scans: 0
pages: 0 removed, 7512 remain, 7512 scanned (100.00% of total)
tuples: 0 removed, 1800000 remain, 900000 are dead but not yet removable
removable cutoff: 745, which was 1 XIDs old when operation ended
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
index "vestat_idx": pages: 4171 in total, 0 newly deleted, 218 currently deleted, 218 reusable
I/O timings: read: 0.000 ms, write: 0.055 ms
avg read rate: 0.000 MB/s, avg write rate: 0.063 MB/s
buffer usage: 19424 hits, 0 misses, 6 dirtied
WAL usage: 2 records, 1 full page images, 1529 bytes
system usage: CPU: user: 0.53 s, system: 0.00 s, elapsed: 0.73 s
psql:vacuum_test_analyze.sql:37: INFO: analyzing "public.vestat"
psql:vacuum_test_analyze.sql:37: INFO: "vestat": scanned 7512 of 7512 pages, containing 900000 live rows and 900000 dead rows; 30000 rows in sample, 900000 estimated total rows
VACUUM
pg_sleep
----------
(1 row)
CHECKPOINT
dblink_exec
-------------
COMMIT
(1 row)
psql:vacuum_test_analyze.sql:44: INFO: vacuuming "postgres.public.vestat"
psql:vacuum_test_analyze.sql:44: INFO: finished vacuuming "postgres.public.vestat": index scans: 1
pages: 0 removed, 7512 remain, 7512 scanned (100.00% of total)
tuples: 900000 removed, 900000 remain, 0 are dead but not yet removable
removable cutoff: 747, which was 0 XIDs old when operation ended
new relfrozenxid: 745, which is 4 XIDs ahead of previous value
frozen: 1 pages from table (0.01% of total) had 133 tuples frozen
index scan needed: 3530 pages from table (46.99% of total) had 900000 dead item identifiers removed
index "vestat_idx": pages: 4171 in total, 0 newly deleted, 218 currently deleted, 218 reusable
I/O timings: read: 0.000 ms, write: 0.000 ms
avg read rate: 0.000 MB/s, avg write rate: 18.429 MB/s
buffer usage: 22962 hits, 0 misses, 11456 dirtied
WAL usage: 18512 records, 7471 full page images, 21157309 bytes
system usage: CPU: user: 2.24 s, system: 0.00 s, elapsed: 4.85 s
psql:vacuum_test_analyze.sql:44: INFO: analyzing "public.vestat"
psql:vacuum_test_analyze.sql:44: INFO: "vestat": scanned 7512 of 7512 pages, containing 900000 live rows and 0 dead rows; 30000 rows in sample, 900000 estimated total rows
VACUUM
pg_sleep
----------
(1 row)
CHECKPOINT
DELETE 900000
psql:vacuum_test_analyze.sql:51: INFO: vacuuming "postgres.public.vestat"
psql:vacuum_test_analyze.sql:51: INFO: table "vestat": truncated 7512 to 0 pages
psql:vacuum_test_analyze.sql:51: INFO: finished vacuuming "postgres.public.vestat": index scans: 1
pages: 7512 removed, 0 remain, 3983 scanned (53.02% of total)
tuples: 900000 removed, 422804 remain, 0 are dead but not yet removable
removable cutoff: 749, which was 1 XIDs old when operation ended
new relfrozenxid: 749, which is 4 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan needed: 3983 pages from table (53.02% of total) had 900000 dead item identifiers removed
index "vestat_idx": pages: 4171 in total, 3949 newly deleted, 4167 currently deleted, 218 reusable
I/O timings: read: 0.000 ms, write: 0.000 ms
avg read rate: 0.000 MB/s, avg write rate: 17.193 MB/s
buffer usage: 51480 hits, 0 misses, 3958 dirtied
WAL usage: 23781 records, 3954 full page images, 14444904 bytes
system usage: CPU: user: 0.51 s, system: 0.01 s, elapsed: 1.79 s
psql:vacuum_test_analyze.sql:51: INFO: analyzing "public.vestat"
psql:vacuum_test_analyze.sql:51: INFO: "vestat": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
VACUUM
pg_sleep
----------
(1 row)
CHECKPOINT
INSERT 0 1000000
UPDATE 1000000
psql:vacuum_test_analyze.sql:60: INFO: vacuuming "postgres.public.vestat"
psql:vacuum_test_analyze.sql:60: INFO: finished vacuuming "postgres.public.vestat": index scans: 1
pages: 0 removed, 8347 remain, 8347 scanned (100.00% of total)
tuples: 1000000 removed, 1000000 remain, 0 are dead but not yet removable
removable cutoff: 752, which was 0 XIDs old when operation ended
new relfrozenxid: 751, which is 2 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan needed: 3922 pages from table (46.99% of total) had 1000000 dead item identifiers removed
index "vestat_idx": pages: 8341 in total, 0 newly deleted, 3949 currently deleted, 3949 reusable
I/O timings: read: 5.693 ms, write: 1.319 ms
avg read rate: 1.421 MB/s, avg write rate: 3.666 MB/s
buffer usage: 32312 hits, 607 misses, 1566 dirtied
WAL usage: 20571 records, 2 full page images, 9938566 bytes
system usage: CPU: user: 2.65 s, system: 0.00 s, elapsed: 3.33 s
psql:vacuum_test_analyze.sql:60: INFO: analyzing "public.vestat"
psql:vacuum_test_analyze.sql:60: INFO: "vestat": scanned 8347 of 8347 pages, containing 1000000 live rows and 0 dead rows; 30000 rows in sample, 1000000 estimated total rows
VACUUM
pg_sleep
----------
(1 row)
CHECKPOINT
[text/plain] vacuum_analyze_test (7.7K, ../../[email protected]/6-vacuum_analyze_test)
download | inline:
postgres=# \i vacuum_test_analyze.sql
psql:vacuum_test_analyze.sql:2: ERROR: extension "dblink" does not exist
CREATE EXTENSION
dblink_connect
----------------
OK
(1 row)
psql:vacuum_test_analyze.sql:5: ERROR: table "vestat" does not exist
SET
SET
CREATE TABLE
CREATE INDEX
psql:vacuum_test_analyze.sql:15: INFO: vacuuming "postgres.public.vestat"
psql:vacuum_test_analyze.sql:15: INFO: finished vacuuming "postgres.public.vestat": index scans: 0
pages: 0 removed, 0 remain, 0 scanned (100.00% of total)
tuples: 0 removed, 0 remain, 0 are dead but not yet removable
removable cutoff: 741, which was 0 XIDs old when operation ended
new relfrozenxid: 741, which is 2 XIDs ahead of previous value
frozen: 0 pages from table (100.00% of total) had 0 tuples frozen
index scan not needed: 0 pages from table (100.00% of total) had 0 dead item identifiers removed
I/O timings: read: 0.107 ms, write: 0.000 ms
avg read rate: 28.617 MB/s, avg write rate: 0.000 MB/s
buffer usage: 13 hits, 1 misses, 0 dirtied
WAL usage: 1 records, 0 full page images, 237 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
psql:vacuum_test_analyze.sql:15: INFO: analyzing "public.vestat"
psql:vacuum_test_analyze.sql:15: INFO: "vestat": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
VACUUM
pg_sleep
----------
(1 row)
INSERT 0 1000000
DELETE 100000
psql:vacuum_test_analyze.sql:24: INFO: vacuuming "postgres.public.vestat"
psql:vacuum_test_analyze.sql:24: INFO: table "vestat": truncated 3922 to 3530 pages
psql:vacuum_test_analyze.sql:24: INFO: finished vacuuming "postgres.public.vestat": index scans: 1
pages: 392 removed, 3530 remain, 3922 scanned (100.00% of total)
tuples: 100000 removed, 900000 remain, 0 are dead but not yet removable
removable cutoff: 743, which was 1 XIDs old when operation ended
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan needed: 393 pages from table (10.02% of total) had 100000 dead item identifiers removed
index "vestat_idx": pages: 2197 in total, 218 newly deleted, 218 currently deleted, 0 reusable
I/O timings: read: 0.000 ms, write: 0.058 ms
avg read rate: 0.000 MB/s, avg write rate: 0.054 MB/s
buffer usage: 12583 hits, 0 misses, 4 dirtied
WAL usage: 5369 records, 1 full page images, 928676 bytes
system usage: CPU: user: 0.44 s, system: 0.00 s, elapsed: 0.57 s
psql:vacuum_test_analyze.sql:24: INFO: analyzing "public.vestat"
psql:vacuum_test_analyze.sql:24: INFO: "vestat": scanned 3530 of 3530 pages, containing 900000 live rows and 0 dead rows; 30000 rows in sample, 900000 estimated total rows
VACUUM
pg_sleep
----------
(1 row)
CHECKPOINT
dblink_exec
-------------
BEGIN
(1 row)
a
---
(1 row)
UPDATE 900000
psql:vacuum_test_analyze.sql:37: INFO: vacuuming "postgres.public.vestat"
psql:vacuum_test_analyze.sql:37: INFO: finished vacuuming "postgres.public.vestat": index scans: 0
pages: 0 removed, 7512 remain, 7512 scanned (100.00% of total)
tuples: 0 removed, 1800000 remain, 900000 are dead but not yet removable
removable cutoff: 745, which was 1 XIDs old when operation ended
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
index "vestat_idx": pages: 4171 in total, 0 newly deleted, 218 currently deleted, 218 reusable
I/O timings: read: 0.000 ms, write: 0.053 ms
avg read rate: 0.000 MB/s, avg write rate: 0.062 MB/s
buffer usage: 19424 hits, 0 misses, 6 dirtied
WAL usage: 2 records, 1 full page images, 1529 bytes
system usage: CPU: user: 0.54 s, system: 0.00 s, elapsed: 0.75 s
psql:vacuum_test_analyze.sql:37: INFO: analyzing "public.vestat"
psql:vacuum_test_analyze.sql:37: INFO: "vestat": scanned 7512 of 7512 pages, containing 900000 live rows and 900000 dead rows; 30000 rows in sample, 900000 estimated total rows
VACUUM
pg_sleep
----------
(1 row)
CHECKPOINT
dblink_exec
-------------
COMMIT
(1 row)
psql:vacuum_test_analyze.sql:44: INFO: vacuuming "postgres.public.vestat"
psql:vacuum_test_analyze.sql:44: INFO: finished vacuuming "postgres.public.vestat": index scans: 1
pages: 0 removed, 7512 remain, 7512 scanned (100.00% of total)
tuples: 900000 removed, 900000 remain, 0 are dead but not yet removable
removable cutoff: 747, which was 0 XIDs old when operation ended
new relfrozenxid: 745, which is 4 XIDs ahead of previous value
frozen: 1 pages from table (0.01% of total) had 133 tuples frozen
index scan needed: 3530 pages from table (46.99% of total) had 900000 dead item identifiers removed
index "vestat_idx": pages: 4171 in total, 0 newly deleted, 218 currently deleted, 218 reusable
I/O timings: read: 0.000 ms, write: 0.000 ms
avg read rate: 0.000 MB/s, avg write rate: 18.508 MB/s
buffer usage: 22962 hits, 0 misses, 11456 dirtied
WAL usage: 18512 records, 7471 full page images, 21157309 bytes
system usage: CPU: user: 2.24 s, system: 0.00 s, elapsed: 4.83 s
psql:vacuum_test_analyze.sql:44: INFO: analyzing "public.vestat"
psql:vacuum_test_analyze.sql:44: INFO: "vestat": scanned 7512 of 7512 pages, containing 900000 live rows and 0 dead rows; 30000 rows in sample, 900000 estimated total rows
VACUUM
pg_sleep
----------
(1 row)
CHECKPOINT
DELETE 900000
psql:vacuum_test_analyze.sql:51: INFO: vacuuming "postgres.public.vestat"
psql:vacuum_test_analyze.sql:51: INFO: table "vestat": truncated 7512 to 0 pages
psql:vacuum_test_analyze.sql:51: INFO: finished vacuuming "postgres.public.vestat": index scans: 1
pages: 7512 removed, 0 remain, 3983 scanned (53.02% of total)
tuples: 900000 removed, 422804 remain, 0 are dead but not yet removable
removable cutoff: 749, which was 1 XIDs old when operation ended
new relfrozenxid: 749, which is 4 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan needed: 3983 pages from table (53.02% of total) had 900000 dead item identifiers removed
index "vestat_idx": pages: 4171 in total, 3949 newly deleted, 4167 currently deleted, 218 reusable
I/O timings: read: 0.000 ms, write: 0.000 ms
avg read rate: 0.000 MB/s, avg write rate: 16.836 MB/s
buffer usage: 51480 hits, 0 misses, 3958 dirtied
WAL usage: 23781 records, 3954 full page images, 14444904 bytes
system usage: CPU: user: 0.55 s, system: 0.00 s, elapsed: 1.83 s
psql:vacuum_test_analyze.sql:51: INFO: analyzing "public.vestat"
psql:vacuum_test_analyze.sql:51: INFO: "vestat": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
VACUUM
pg_sleep
----------
(1 row)
CHECKPOINT
INSERT 0 1000000
UPDATE 1000000
psql:vacuum_test_analyze.sql:60: INFO: vacuuming "postgres.public.vestat"
psql:vacuum_test_analyze.sql:60: INFO: finished vacuuming "postgres.public.vestat": index scans: 1
pages: 0 removed, 8347 remain, 8347 scanned (100.00% of total)
tuples: 1000000 removed, 1000000 remain, 0 are dead but not yet removable
removable cutoff: 752, which was 0 XIDs old when operation ended
new relfrozenxid: 751, which is 2 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan needed: 3922 pages from table (46.99% of total) had 1000000 dead item identifiers removed
index "vestat_idx": pages: 8341 in total, 0 newly deleted, 3949 currently deleted, 3949 reusable
I/O timings: read: 3.803 ms, write: 1.236 ms
avg read rate: 1.351 MB/s, avg write rate: 3.208 MB/s
buffer usage: 32346 hits, 573 misses, 1360 dirtied
WAL usage: 20571 records, 2 full page images, 9938566 bytes
system usage: CPU: user: 2.67 s, system: 0.01 s, elapsed: 3.31 s
psql:vacuum_test_analyze.sql:60: INFO: analyzing "public.vestat"
psql:vacuum_test_analyze.sql:60: INFO: "vestat": scanned 8347 of 8347 pages, containing 1000000 live rows and 0 dead rows; 30000 rows in sample, 1000000 estimated total rows
VACUUM
pg_sleep
----------
(1 row)
CHECKPOINT
[application/sql] vacuum_check_logs.sql (1.8K, ../../[email protected]/7-vacuum_check_logs.sql)
download
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Fix parallel vacuum buffer usage reporting
@ 2024-04-25 07:17 Anthonin Bonnefoy <[email protected]>
parent: Alena Rybakina <[email protected]>
0 siblings, 1 reply; 21+ messages in thread
From: Anthonin Bonnefoy @ 2024-04-25 07:17 UTC (permalink / raw)
To: Alena Rybakina <[email protected]>; +Cc: Masahiko Sawada <[email protected]>; pgsql-hackers
On Wed, Apr 24, 2024 at 4:01 PM Alena Rybakina <[email protected]>
wrote:
> I tested the main postgres branch with and without your fix using a script
> that was written by me. It consists of five scenarios and I made a
> comparison in the logs between the original version of the master branch
> and the master branch with your patch:
>
Hi! Thanks for the tests.
I have attached a test file (vacuum_check_logs.sql)
>
Looking at the script, you won't trigger the problem. The reporting issue
will only happen if there's a parallel index vacuum and it will only happen
if there's at least 2 indexes [0]. You will need to create an additional
index.
The same script was run, but using vacuum verbose analyze, and I saw the
> difference again in the fifth step:
> with your patch: buffer usage: 32312 hits, 607 misses, 1566 dirtied
> master: buffer usage: 32346 hits, 573 misses, 1360 dirtied
>
Isn't there a chance for the checkpointer to run during this time? That
could make the conditions between the two runs slightly different and
explain the change in buffer report.
[0]
https://github.com/postgres/postgres/blob/8a1b31e6e59631807a08a4e9465134c343bbdf5e/src/backend/acces...
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Fix parallel vacuum buffer usage reporting
@ 2024-04-26 12:12 Alena Rybakina <[email protected]>
parent: Anthonin Bonnefoy <[email protected]>
0 siblings, 1 reply; 21+ messages in thread
From: Alena Rybakina @ 2024-04-26 12:12 UTC (permalink / raw)
To: Anthonin Bonnefoy <[email protected]>; +Cc: Masahiko Sawada <[email protected]>; pgsql-hackers
Hi!
> The same script was run, but using vacuum verbose analyze, and I
> saw the difference again in the fifth step:
> with your patch: buffer usage: 32312 hits, 607 misses, 1566 dirtied
> master: buffer usage: 32346 hits, 573 misses, 1360 dirtied
>
> Isn't there a chance for the checkpointer to run during this time?
> That could make the conditions between the two runs slightly different
> and explain the change in buffer report.
>
> [0]
> https://github.com/postgres/postgres/blob/8a1b31e6e59631807a08a4e9465134c343bbdf5e/src/backend/acces...
>
> Looking at the script, you won't trigger the problem.
Thank you for the link I accounted it in my next experiments.
I repeated the test without processing checkpoints with a single index,
and the number of pages in the buffer used almost matched:
master branch: buffer usage: 32315 hits, 606 misses, 4486 dirtied
with applied patch v4 version: buffer usage: 32315 hits, 606 misses,
4489 dirtied
I think you are right - the problem was interfering with the checkpoint
process, by the way I checked the first version patch. To cut a long
story short, everything is fine now with one index.
Just in case, I'll explain: I considered this case because your patch
could have impact influenced it too.
On 25.04.2024 10:17, Anthonin Bonnefoy wrote:
>
> On Wed, Apr 24, 2024 at 4:01 PM Alena Rybakina
> <[email protected]> wrote:
>
> I tested the main postgres branch with and without your fix using
> a script that was written by me. It consists of five scenarios and
> I made a comparison in the logs between the original version of
> the master branch and the master branch with your patch:
>
> Hi! Thanks for the tests.
>
> I have attached a test file (vacuum_check_logs.sql)
>
> The reporting issue will only happen if there's a parallel index
> vacuum and it will only happen if there's at least 2 indexes [0]. You
> will need to create an additional index.
Speaking of the problem, I added another index and repeated the test and
found a significant difference:
* I found it when I commited the transaction (3):
master: 2964hits, 0misses, 0dirtied
with applied patch v4 version: buffer usage: 33013hits, 0misses, 3dirtied
* When I deleted all the data from the table and later started vacuum
verbose again (4):
master: buffer usage: 51486hits, 0misses, 0dirtied
with applied patch v4 version:buffer usage: 77924hits, 0misses, 0dirtied
* when I inserted 1 million data into the table and updated it (5):
master:buffer usage: 27904hits, 5021misses, 1777dirtied
with applied patch v4 version:buffer usage: 41051hits, 9973misses,
2564dirtied
As I see, the number of pages is significantly more than it was in the
master branch and ,frankly, I couldn't fully figure out if it was a
mistake or not.
I attached a test script (vacuum_checks_logs.sql) with two indexes and
no checkpoints, I also attached log files: the first one (vacuum_test)
is the result of testing on the master branch, the second file with your
applied patch (vacuum_test_v4).
--
Regards,
Alena Rybakina
Postgres Professional:http://www.postgrespro.com
The Russian Postgres Company
postgres=# \i ~/vacuum_check_logs.sql
psql:/home/alena/vacuum_check_logs.sql:2: ERROR: extension "dblink" does not exist
CREATE EXTENSION
dblink_connect
----------------
OK
(1 row)
psql:/home/alena/vacuum_check_logs.sql:5: ERROR: table "vestat" does not exist
SET
SET
CREATE TABLE
CREATE INDEX
CREATE INDEX
psql:/home/alena/vacuum_check_logs.sql:16: INFO: vacuuming "postgres.public.vestat"
psql:/home/alena/vacuum_check_logs.sql:16: INFO: finished vacuuming "postgres.public.vestat": index scans: 0
pages: 0 removed, 0 remain, 0 scanned (100.00% of total)
tuples: 0 removed, 0 remain, 0 are dead but not yet removable
removable cutoff: 742, which was 0 XIDs old when operation ended
new relfrozenxid: 742, which is 3 XIDs ahead of previous value
frozen: 0 pages from table (100.00% of total) had 0 tuples frozen
index scan not needed: 0 pages from table (100.00% of total) had 0 dead item identifiers removed
I/O timings: read: 0.049 ms, write: 0.000 ms
avg read rate: 40.584 MB/s, avg write rate: 0.000 MB/s
buffer usage: 23 hits, 2 misses, 0 dirtied
WAL usage: 1 records, 0 full page images, 237 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM
pg_sleep
----------
(1 row)
INSERT 0 1000000
DELETE 100000
psql:/home/alena/vacuum_check_logs.sql:25: INFO: vacuuming "postgres.public.vestat"
psql:/home/alena/vacuum_check_logs.sql:25: INFO: launched 1 parallel vacuum worker for index vacuuming (planned: 1)
psql:/home/alena/vacuum_check_logs.sql:25: INFO: table "vestat": truncated 3922 to 3530 pages
psql:/home/alena/vacuum_check_logs.sql:25: INFO: finished vacuuming "postgres.public.vestat": index scans: 1
pages: 392 removed, 3530 remain, 3922 scanned (100.00% of total)
tuples: 100000 removed, 900000 remain, 0 are dead but not yet removable
removable cutoff: 744, which was 1 XIDs old when operation ended
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan needed: 393 pages from table (10.02% of total) had 100000 dead item identifiers removed
index "vestat_idx": pages: 2197 in total, 218 newly deleted, 218 currently deleted, 0 reusable
index "vestat_idx1": pages: 785 in total, 77 newly deleted, 77 currently deleted, 0 reusable
I/O timings: read: 0.000 ms, write: 0.046 ms
avg read rate: 0.000 MB/s, avg write rate: 0.054 MB/s
buffer usage: 13990 hits, 0 misses, 4 dirtied
WAL usage: 5604 records, 1 full page images, 946934 bytes
system usage: CPU: user: 0.44 s, system: 0.00 s, elapsed: 0.57 s
VACUUM
pg_sleep
----------
(1 row)
dblink_exec
-------------
BEGIN
(1 row)
a
---
(1 row)
UPDATE 900000
psql:/home/alena/vacuum_check_logs.sql:38: INFO: vacuuming "postgres.public.vestat"
psql:/home/alena/vacuum_check_logs.sql:38: INFO: launched 1 parallel vacuum worker for index cleanup (planned: 1)
psql:/home/alena/vacuum_check_logs.sql:38: INFO: finished vacuuming "postgres.public.vestat": index scans: 0
pages: 0 removed, 7512 remain, 7512 scanned (100.00% of total)
tuples: 0 removed, 1800000 remain, 900000 are dead but not yet removable
removable cutoff: 745, which was 1 XIDs old when operation ended
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
index "vestat_idx": pages: 4171 in total, 0 newly deleted, 218 currently deleted, 218 reusable
I/O timings: read: 0.000 ms, write: 0.056 ms
avg read rate: 0.000 MB/s, avg write rate: 0.041 MB/s
buffer usage: 19425 hits, 0 misses, 4 dirtied
WAL usage: 2 records, 0 full page images, 309 bytes
system usage: CPU: user: 0.55 s, system: 0.00 s, elapsed: 0.76 s
VACUUM
pg_sleep
----------
(1 row)
dblink_exec
-------------
COMMIT
(1 row)
psql:/home/alena/vacuum_check_logs.sql:45: INFO: vacuuming "postgres.public.vestat"
psql:/home/alena/vacuum_check_logs.sql:45: INFO: launched 1 parallel vacuum worker for index vacuuming (planned: 1)
psql:/home/alena/vacuum_check_logs.sql:45: INFO: finished vacuuming "postgres.public.vestat": index scans: 1
pages: 0 removed, 7512 remain, 7512 scanned (100.00% of total)
tuples: 900000 removed, 900000 remain, 0 are dead but not yet removable
removable cutoff: 746, which was 0 XIDs old when operation ended
new relfrozenxid: 745, which is 3 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan needed: 3530 pages from table (46.99% of total) had 900000 dead item identifiers removed
index "vestat_idx": pages: 4171 in total, 0 newly deleted, 218 currently deleted, 218 reusable
index "vestat_idx1": pages: 4342 in total, 703 newly deleted, 780 currently deleted, 77 reusable
I/O timings: read: 0.000 ms, write: 0.000 ms
avg read rate: 0.000 MB/s, avg write rate: 0.010 MB/s
buffer usage: 33013 hits, 0 misses, 3 dirtied
WAL usage: 20621 records, 0 full page images, 9097649 bytes
system usage: CPU: user: 2.19 s, system: 0.00 s, elapsed: 2.43 s
VACUUM
pg_sleep
----------
(1 row)
DELETE 900000
psql:/home/alena/vacuum_check_logs.sql:52: INFO: vacuuming "postgres.public.vestat"
psql:/home/alena/vacuum_check_logs.sql:52: INFO: launched 1 parallel vacuum worker for index vacuuming (planned: 1)
psql:/home/alena/vacuum_check_logs.sql:52: INFO: table "vestat": truncated 7512 to 0 pages
psql:/home/alena/vacuum_check_logs.sql:52: INFO: finished vacuuming "postgres.public.vestat": index scans: 1
pages: 7512 removed, 0 remain, 3983 scanned (53.02% of total)
tuples: 900000 removed, 422804 remain, 0 are dead but not yet removable
removable cutoff: 747, which was 1 XIDs old when operation ended
new relfrozenxid: 747, which is 2 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan needed: 3983 pages from table (53.02% of total) had 900000 dead item identifiers removed
index "vestat_idx": pages: 4171 in total, 3949 newly deleted, 4167 currently deleted, 218 reusable
index "vestat_idx1": pages: 4342 in total, 3558 newly deleted, 4338 currently deleted, 780 reusable
I/O timings: read: 0.000 ms, write: 0.000 ms
avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
buffer usage: 77924 hits, 0 misses, 0 dirtied
WAL usage: 34428 records, 0 full page images, 9439566 bytes
system usage: CPU: user: 0.56 s, system: 0.00 s, elapsed: 1.05 s
VACUUM
pg_sleep
----------
(1 row)
INSERT 0 1000000
UPDATE 1000000
psql:/home/alena/vacuum_check_logs.sql:61: INFO: vacuuming "postgres.public.vestat"
psql:/home/alena/vacuum_check_logs.sql:61: INFO: launched 1 parallel vacuum worker for index vacuuming (planned: 1)
psql:/home/alena/vacuum_check_logs.sql:61: INFO: finished vacuuming "postgres.public.vestat": index scans: 1
pages: 0 removed, 8347 remain, 8347 scanned (100.00% of total)
tuples: 1000000 removed, 1000000 remain, 0 are dead but not yet removable
removable cutoff: 750, which was 0 XIDs old when operation ended
new relfrozenxid: 749, which is 2 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan needed: 3922 pages from table (46.99% of total) had 1000000 dead item identifiers removed
index "vestat_idx": pages: 8341 in total, 0 newly deleted, 3949 currently deleted, 3949 reusable
index "vestat_idx1": pages: 8295 in total, 780 newly deleted, 4338 currently deleted, 3558 reusable
I/O timings: read: 39.594 ms, write: 8.168 ms
avg read rate: 22.641 MB/s, avg write rate: 5.821 MB/s
buffer usage: 41051 hits, 9973 misses, 2564 dirtied
WAL usage: 22911 records, 157 full page images, 11330823 bytes
system usage: CPU: user: 2.63 s, system: 0.03 s, elapsed: 3.44 s
VACUUM
pg_sleep
----------
(1 row)
postgres=# \i ~/vacuum_check_logs.sql
psql:/home/alena/vacuum_check_logs.sql:2: ERROR: extension "dblink" does not exist
CREATE EXTENSION
dblink_connect
----------------
OK
(1 row)
psql:/home/alena/vacuum_check_logs.sql:5: ERROR: table "vestat" does not exist
SET
SET
CREATE TABLE
CREATE INDEX
CREATE INDEX
psql:/home/alena/vacuum_check_logs.sql:16: INFO: vacuuming "postgres.public.vestat"
psql:/home/alena/vacuum_check_logs.sql:16: INFO: finished vacuuming "postgres.public.vestat": index scans: 0
pages: 0 removed, 0 remain, 0 scanned (100.00% of total)
tuples: 0 removed, 0 remain, 0 are dead but not yet removable
removable cutoff: 742, which was 0 XIDs old when operation ended
new relfrozenxid: 742, which is 3 XIDs ahead of previous value
frozen: 0 pages from table (100.00% of total) had 0 tuples frozen
index scan not needed: 0 pages from table (100.00% of total) had 0 dead item identifiers removed
I/O timings: read: 0.039 ms, write: 0.000 ms
avg read rate: 40.271 MB/s, avg write rate: 0.000 MB/s
buffer usage: 23 hits, 2 misses, 0 dirtied
WAL usage: 1 records, 0 full page images, 237 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM
pg_sleep
----------
(1 row)
INSERT 0 1000000
DELETE 100000
psql:/home/alena/vacuum_check_logs.sql:25: INFO: vacuuming "postgres.public.vestat"
psql:/home/alena/vacuum_check_logs.sql:25: INFO: launched 1 parallel vacuum worker for index vacuuming (planned: 1)
psql:/home/alena/vacuum_check_logs.sql:25: INFO: table "vestat": truncated 3922 to 3530 pages
psql:/home/alena/vacuum_check_logs.sql:25: INFO: finished vacuuming "postgres.public.vestat": index scans: 1
pages: 392 removed, 3530 remain, 3922 scanned (100.00% of total)
tuples: 100000 removed, 900000 remain, 0 are dead but not yet removable
removable cutoff: 744, which was 1 XIDs old when operation ended
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan needed: 393 pages from table (10.02% of total) had 100000 dead item identifiers removed
index "vestat_idx": pages: 2197 in total, 218 newly deleted, 218 currently deleted, 0 reusable
index "vestat_idx1": pages: 785 in total, 77 newly deleted, 77 currently deleted, 0 reusable
I/O timings: read: 0.000 ms, write: 0.041 ms
avg read rate: 0.000 MB/s, avg write rate: 0.053 MB/s
buffer usage: 12589 hits, 0 misses, 4 dirtied
WAL usage: 5604 records, 1 full page images, 946934 bytes
system usage: CPU: user: 0.45 s, system: 0.00 s, elapsed: 0.58 s
VACUUM
pg_sleep
----------
(1 row)
dblink_exec
-------------
BEGIN
(1 row)
a
---
(1 row)
UPDATE 900000
psql:/home/alena/vacuum_check_logs.sql:38: INFO: vacuuming "postgres.public.vestat"
psql:/home/alena/vacuum_check_logs.sql:38: INFO: launched 1 parallel vacuum worker for index cleanup (planned: 1)
psql:/home/alena/vacuum_check_logs.sql:38: INFO: finished vacuuming "postgres.public.vestat": index scans: 0
pages: 0 removed, 7512 remain, 7512 scanned (100.00% of total)
tuples: 0 removed, 1800000 remain, 900000 are dead but not yet removable
removable cutoff: 745, which was 1 XIDs old when operation ended
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
index "vestat_idx": pages: 4171 in total, 0 newly deleted, 218 currently deleted, 218 reusable
I/O timings: read: 0.000 ms, write: 0.056 ms
avg read rate: 0.000 MB/s, avg write rate: 0.041 MB/s
buffer usage: 19424 hits, 0 misses, 4 dirtied
WAL usage: 2 records, 0 full page images, 309 bytes
system usage: CPU: user: 0.55 s, system: 0.00 s, elapsed: 0.75 s
VACUUM
pg_sleep
----------
(1 row)
dblink_exec
-------------
COMMIT
(1 row)
psql:/home/alena/vacuum_check_logs.sql:45: INFO: vacuuming "postgres.public.vestat"
psql:/home/alena/vacuum_check_logs.sql:45: INFO: launched 1 parallel vacuum worker for index vacuuming (planned: 1)
psql:/home/alena/vacuum_check_logs.sql:45: INFO: finished vacuuming "postgres.public.vestat": index scans: 1
pages: 0 removed, 7512 remain, 7512 scanned (100.00% of total)
tuples: 900000 removed, 900000 remain, 0 are dead but not yet removable
removable cutoff: 746, which was 0 XIDs old when operation ended
new relfrozenxid: 745, which is 3 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan needed: 3530 pages from table (46.99% of total) had 900000 dead item identifiers removed
index "vestat_idx": pages: 4171 in total, 0 newly deleted, 218 currently deleted, 218 reusable
index "vestat_idx1": pages: 4342 in total, 703 newly deleted, 780 currently deleted, 77 reusable
I/O timings: read: 0.000 ms, write: 0.000 ms
avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
buffer usage: 22964 hits, 0 misses, 0 dirtied
WAL usage: 20621 records, 0 full page images, 9097649 bytes
system usage: CPU: user: 2.35 s, system: 0.00 s, elapsed: 2.59 s
VACUUM
pg_sleep
----------
(1 row)
DELETE 900000
psql:/home/alena/vacuum_check_logs.sql:52: INFO: vacuuming "postgres.public.vestat"
psql:/home/alena/vacuum_check_logs.sql:52: INFO: launched 1 parallel vacuum worker for index vacuuming (planned: 1)
psql:/home/alena/vacuum_check_logs.sql:52: INFO: table "vestat": truncated 7512 to 0 pages
psql:/home/alena/vacuum_check_logs.sql:52: INFO: finished vacuuming "postgres.public.vestat": index scans: 1
pages: 7512 removed, 0 remain, 3983 scanned (53.02% of total)
tuples: 900000 removed, 422804 remain, 0 are dead but not yet removable
removable cutoff: 747, which was 1 XIDs old when operation ended
new relfrozenxid: 747, which is 2 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan needed: 3983 pages from table (53.02% of total) had 900000 dead item identifiers removed
index "vestat_idx": pages: 4171 in total, 3949 newly deleted, 4167 currently deleted, 218 reusable
index "vestat_idx1": pages: 4342 in total, 3558 newly deleted, 4338 currently deleted, 780 reusable
I/O timings: read: 0.000 ms, write: 0.000 ms
avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
buffer usage: 51486 hits, 0 misses, 0 dirtied
WAL usage: 34428 records, 0 full page images, 9439566 bytes
system usage: CPU: user: 0.55 s, system: 0.01 s, elapsed: 1.04 s
VACUUM
pg_sleep
----------
(1 row)
INSERT 0 1000000
UPDATE 1000000
psql:/home/alena/vacuum_check_logs.sql:61: INFO: vacuuming "postgres.public.vestat"
psql:/home/alena/vacuum_check_logs.sql:61: INFO: launched 1 parallel vacuum worker for index vacuuming (planned: 1)
psql:/home/alena/vacuum_check_logs.sql:61: INFO: finished vacuuming "postgres.public.vestat": index scans: 1
pages: 0 removed, 8347 remain, 8347 scanned (100.00% of total)
tuples: 1000000 removed, 1000000 remain, 0 are dead but not yet removable
removable cutoff: 750, which was 0 XIDs old when operation ended
new relfrozenxid: 749, which is 2 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan needed: 3922 pages from table (46.99% of total) had 1000000 dead item identifiers removed
index "vestat_idx": pages: 8341 in total, 0 newly deleted, 3949 currently deleted, 3949 reusable
index "vestat_idx1": pages: 8295 in total, 780 newly deleted, 4338 currently deleted, 3558 reusable
I/O timings: read: 29.939 ms, write: 8.233 ms
avg read rate: 10.658 MB/s, avg write rate: 3.772 MB/s
buffer usage: 27904 hits, 5021 misses, 1777 dirtied
WAL usage: 22911 records, 151 full page images, 11283585 bytes
system usage: CPU: user: 2.88 s, system: 0.02 s, elapsed: 3.68 s
VACUUM
pg_sleep
----------
(1 row)
Attachments:
[application/sql] vacuum_check_logs.sql (1.8K, ../../[email protected]/3-vacuum_check_logs.sql)
download
[text/plain] vacuum_test_v4 (7.4K, ../../[email protected]/4-vacuum_test_v4)
download | inline:
postgres=# \i ~/vacuum_check_logs.sql
psql:/home/alena/vacuum_check_logs.sql:2: ERROR: extension "dblink" does not exist
CREATE EXTENSION
dblink_connect
----------------
OK
(1 row)
psql:/home/alena/vacuum_check_logs.sql:5: ERROR: table "vestat" does not exist
SET
SET
CREATE TABLE
CREATE INDEX
CREATE INDEX
psql:/home/alena/vacuum_check_logs.sql:16: INFO: vacuuming "postgres.public.vestat"
psql:/home/alena/vacuum_check_logs.sql:16: INFO: finished vacuuming "postgres.public.vestat": index scans: 0
pages: 0 removed, 0 remain, 0 scanned (100.00% of total)
tuples: 0 removed, 0 remain, 0 are dead but not yet removable
removable cutoff: 742, which was 0 XIDs old when operation ended
new relfrozenxid: 742, which is 3 XIDs ahead of previous value
frozen: 0 pages from table (100.00% of total) had 0 tuples frozen
index scan not needed: 0 pages from table (100.00% of total) had 0 dead item identifiers removed
I/O timings: read: 0.049 ms, write: 0.000 ms
avg read rate: 40.584 MB/s, avg write rate: 0.000 MB/s
buffer usage: 23 hits, 2 misses, 0 dirtied
WAL usage: 1 records, 0 full page images, 237 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM
pg_sleep
----------
(1 row)
INSERT 0 1000000
DELETE 100000
psql:/home/alena/vacuum_check_logs.sql:25: INFO: vacuuming "postgres.public.vestat"
psql:/home/alena/vacuum_check_logs.sql:25: INFO: launched 1 parallel vacuum worker for index vacuuming (planned: 1)
psql:/home/alena/vacuum_check_logs.sql:25: INFO: table "vestat": truncated 3922 to 3530 pages
psql:/home/alena/vacuum_check_logs.sql:25: INFO: finished vacuuming "postgres.public.vestat": index scans: 1
pages: 392 removed, 3530 remain, 3922 scanned (100.00% of total)
tuples: 100000 removed, 900000 remain, 0 are dead but not yet removable
removable cutoff: 744, which was 1 XIDs old when operation ended
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan needed: 393 pages from table (10.02% of total) had 100000 dead item identifiers removed
index "vestat_idx": pages: 2197 in total, 218 newly deleted, 218 currently deleted, 0 reusable
index "vestat_idx1": pages: 785 in total, 77 newly deleted, 77 currently deleted, 0 reusable
I/O timings: read: 0.000 ms, write: 0.046 ms
avg read rate: 0.000 MB/s, avg write rate: 0.054 MB/s
buffer usage: 13990 hits, 0 misses, 4 dirtied
WAL usage: 5604 records, 1 full page images, 946934 bytes
system usage: CPU: user: 0.44 s, system: 0.00 s, elapsed: 0.57 s
VACUUM
pg_sleep
----------
(1 row)
dblink_exec
-------------
BEGIN
(1 row)
a
---
(1 row)
UPDATE 900000
psql:/home/alena/vacuum_check_logs.sql:38: INFO: vacuuming "postgres.public.vestat"
psql:/home/alena/vacuum_check_logs.sql:38: INFO: launched 1 parallel vacuum worker for index cleanup (planned: 1)
psql:/home/alena/vacuum_check_logs.sql:38: INFO: finished vacuuming "postgres.public.vestat": index scans: 0
pages: 0 removed, 7512 remain, 7512 scanned (100.00% of total)
tuples: 0 removed, 1800000 remain, 900000 are dead but not yet removable
removable cutoff: 745, which was 1 XIDs old when operation ended
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
index "vestat_idx": pages: 4171 in total, 0 newly deleted, 218 currently deleted, 218 reusable
I/O timings: read: 0.000 ms, write: 0.056 ms
avg read rate: 0.000 MB/s, avg write rate: 0.041 MB/s
buffer usage: 19425 hits, 0 misses, 4 dirtied
WAL usage: 2 records, 0 full page images, 309 bytes
system usage: CPU: user: 0.55 s, system: 0.00 s, elapsed: 0.76 s
VACUUM
pg_sleep
----------
(1 row)
dblink_exec
-------------
COMMIT
(1 row)
psql:/home/alena/vacuum_check_logs.sql:45: INFO: vacuuming "postgres.public.vestat"
psql:/home/alena/vacuum_check_logs.sql:45: INFO: launched 1 parallel vacuum worker for index vacuuming (planned: 1)
psql:/home/alena/vacuum_check_logs.sql:45: INFO: finished vacuuming "postgres.public.vestat": index scans: 1
pages: 0 removed, 7512 remain, 7512 scanned (100.00% of total)
tuples: 900000 removed, 900000 remain, 0 are dead but not yet removable
removable cutoff: 746, which was 0 XIDs old when operation ended
new relfrozenxid: 745, which is 3 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan needed: 3530 pages from table (46.99% of total) had 900000 dead item identifiers removed
index "vestat_idx": pages: 4171 in total, 0 newly deleted, 218 currently deleted, 218 reusable
index "vestat_idx1": pages: 4342 in total, 703 newly deleted, 780 currently deleted, 77 reusable
I/O timings: read: 0.000 ms, write: 0.000 ms
avg read rate: 0.000 MB/s, avg write rate: 0.010 MB/s
buffer usage: 33013 hits, 0 misses, 3 dirtied
WAL usage: 20621 records, 0 full page images, 9097649 bytes
system usage: CPU: user: 2.19 s, system: 0.00 s, elapsed: 2.43 s
VACUUM
pg_sleep
----------
(1 row)
DELETE 900000
psql:/home/alena/vacuum_check_logs.sql:52: INFO: vacuuming "postgres.public.vestat"
psql:/home/alena/vacuum_check_logs.sql:52: INFO: launched 1 parallel vacuum worker for index vacuuming (planned: 1)
psql:/home/alena/vacuum_check_logs.sql:52: INFO: table "vestat": truncated 7512 to 0 pages
psql:/home/alena/vacuum_check_logs.sql:52: INFO: finished vacuuming "postgres.public.vestat": index scans: 1
pages: 7512 removed, 0 remain, 3983 scanned (53.02% of total)
tuples: 900000 removed, 422804 remain, 0 are dead but not yet removable
removable cutoff: 747, which was 1 XIDs old when operation ended
new relfrozenxid: 747, which is 2 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan needed: 3983 pages from table (53.02% of total) had 900000 dead item identifiers removed
index "vestat_idx": pages: 4171 in total, 3949 newly deleted, 4167 currently deleted, 218 reusable
index "vestat_idx1": pages: 4342 in total, 3558 newly deleted, 4338 currently deleted, 780 reusable
I/O timings: read: 0.000 ms, write: 0.000 ms
avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
buffer usage: 77924 hits, 0 misses, 0 dirtied
WAL usage: 34428 records, 0 full page images, 9439566 bytes
system usage: CPU: user: 0.56 s, system: 0.00 s, elapsed: 1.05 s
VACUUM
pg_sleep
----------
(1 row)
INSERT 0 1000000
UPDATE 1000000
psql:/home/alena/vacuum_check_logs.sql:61: INFO: vacuuming "postgres.public.vestat"
psql:/home/alena/vacuum_check_logs.sql:61: INFO: launched 1 parallel vacuum worker for index vacuuming (planned: 1)
psql:/home/alena/vacuum_check_logs.sql:61: INFO: finished vacuuming "postgres.public.vestat": index scans: 1
pages: 0 removed, 8347 remain, 8347 scanned (100.00% of total)
tuples: 1000000 removed, 1000000 remain, 0 are dead but not yet removable
removable cutoff: 750, which was 0 XIDs old when operation ended
new relfrozenxid: 749, which is 2 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan needed: 3922 pages from table (46.99% of total) had 1000000 dead item identifiers removed
index "vestat_idx": pages: 8341 in total, 0 newly deleted, 3949 currently deleted, 3949 reusable
index "vestat_idx1": pages: 8295 in total, 780 newly deleted, 4338 currently deleted, 3558 reusable
I/O timings: read: 39.594 ms, write: 8.168 ms
avg read rate: 22.641 MB/s, avg write rate: 5.821 MB/s
buffer usage: 41051 hits, 9973 misses, 2564 dirtied
WAL usage: 22911 records, 157 full page images, 11330823 bytes
system usage: CPU: user: 2.63 s, system: 0.03 s, elapsed: 3.44 s
VACUUM
pg_sleep
----------
(1 row)
[text/plain] vacuum_test (7.4K, ../../[email protected]/5-vacuum_test)
download | inline:
postgres=# \i ~/vacuum_check_logs.sql
psql:/home/alena/vacuum_check_logs.sql:2: ERROR: extension "dblink" does not exist
CREATE EXTENSION
dblink_connect
----------------
OK
(1 row)
psql:/home/alena/vacuum_check_logs.sql:5: ERROR: table "vestat" does not exist
SET
SET
CREATE TABLE
CREATE INDEX
CREATE INDEX
psql:/home/alena/vacuum_check_logs.sql:16: INFO: vacuuming "postgres.public.vestat"
psql:/home/alena/vacuum_check_logs.sql:16: INFO: finished vacuuming "postgres.public.vestat": index scans: 0
pages: 0 removed, 0 remain, 0 scanned (100.00% of total)
tuples: 0 removed, 0 remain, 0 are dead but not yet removable
removable cutoff: 742, which was 0 XIDs old when operation ended
new relfrozenxid: 742, which is 3 XIDs ahead of previous value
frozen: 0 pages from table (100.00% of total) had 0 tuples frozen
index scan not needed: 0 pages from table (100.00% of total) had 0 dead item identifiers removed
I/O timings: read: 0.039 ms, write: 0.000 ms
avg read rate: 40.271 MB/s, avg write rate: 0.000 MB/s
buffer usage: 23 hits, 2 misses, 0 dirtied
WAL usage: 1 records, 0 full page images, 237 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM
pg_sleep
----------
(1 row)
INSERT 0 1000000
DELETE 100000
psql:/home/alena/vacuum_check_logs.sql:25: INFO: vacuuming "postgres.public.vestat"
psql:/home/alena/vacuum_check_logs.sql:25: INFO: launched 1 parallel vacuum worker for index vacuuming (planned: 1)
psql:/home/alena/vacuum_check_logs.sql:25: INFO: table "vestat": truncated 3922 to 3530 pages
psql:/home/alena/vacuum_check_logs.sql:25: INFO: finished vacuuming "postgres.public.vestat": index scans: 1
pages: 392 removed, 3530 remain, 3922 scanned (100.00% of total)
tuples: 100000 removed, 900000 remain, 0 are dead but not yet removable
removable cutoff: 744, which was 1 XIDs old when operation ended
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan needed: 393 pages from table (10.02% of total) had 100000 dead item identifiers removed
index "vestat_idx": pages: 2197 in total, 218 newly deleted, 218 currently deleted, 0 reusable
index "vestat_idx1": pages: 785 in total, 77 newly deleted, 77 currently deleted, 0 reusable
I/O timings: read: 0.000 ms, write: 0.041 ms
avg read rate: 0.000 MB/s, avg write rate: 0.053 MB/s
buffer usage: 12589 hits, 0 misses, 4 dirtied
WAL usage: 5604 records, 1 full page images, 946934 bytes
system usage: CPU: user: 0.45 s, system: 0.00 s, elapsed: 0.58 s
VACUUM
pg_sleep
----------
(1 row)
dblink_exec
-------------
BEGIN
(1 row)
a
---
(1 row)
UPDATE 900000
psql:/home/alena/vacuum_check_logs.sql:38: INFO: vacuuming "postgres.public.vestat"
psql:/home/alena/vacuum_check_logs.sql:38: INFO: launched 1 parallel vacuum worker for index cleanup (planned: 1)
psql:/home/alena/vacuum_check_logs.sql:38: INFO: finished vacuuming "postgres.public.vestat": index scans: 0
pages: 0 removed, 7512 remain, 7512 scanned (100.00% of total)
tuples: 0 removed, 1800000 remain, 900000 are dead but not yet removable
removable cutoff: 745, which was 1 XIDs old when operation ended
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
index "vestat_idx": pages: 4171 in total, 0 newly deleted, 218 currently deleted, 218 reusable
I/O timings: read: 0.000 ms, write: 0.056 ms
avg read rate: 0.000 MB/s, avg write rate: 0.041 MB/s
buffer usage: 19424 hits, 0 misses, 4 dirtied
WAL usage: 2 records, 0 full page images, 309 bytes
system usage: CPU: user: 0.55 s, system: 0.00 s, elapsed: 0.75 s
VACUUM
pg_sleep
----------
(1 row)
dblink_exec
-------------
COMMIT
(1 row)
psql:/home/alena/vacuum_check_logs.sql:45: INFO: vacuuming "postgres.public.vestat"
psql:/home/alena/vacuum_check_logs.sql:45: INFO: launched 1 parallel vacuum worker for index vacuuming (planned: 1)
psql:/home/alena/vacuum_check_logs.sql:45: INFO: finished vacuuming "postgres.public.vestat": index scans: 1
pages: 0 removed, 7512 remain, 7512 scanned (100.00% of total)
tuples: 900000 removed, 900000 remain, 0 are dead but not yet removable
removable cutoff: 746, which was 0 XIDs old when operation ended
new relfrozenxid: 745, which is 3 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan needed: 3530 pages from table (46.99% of total) had 900000 dead item identifiers removed
index "vestat_idx": pages: 4171 in total, 0 newly deleted, 218 currently deleted, 218 reusable
index "vestat_idx1": pages: 4342 in total, 703 newly deleted, 780 currently deleted, 77 reusable
I/O timings: read: 0.000 ms, write: 0.000 ms
avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
buffer usage: 22964 hits, 0 misses, 0 dirtied
WAL usage: 20621 records, 0 full page images, 9097649 bytes
system usage: CPU: user: 2.35 s, system: 0.00 s, elapsed: 2.59 s
VACUUM
pg_sleep
----------
(1 row)
DELETE 900000
psql:/home/alena/vacuum_check_logs.sql:52: INFO: vacuuming "postgres.public.vestat"
psql:/home/alena/vacuum_check_logs.sql:52: INFO: launched 1 parallel vacuum worker for index vacuuming (planned: 1)
psql:/home/alena/vacuum_check_logs.sql:52: INFO: table "vestat": truncated 7512 to 0 pages
psql:/home/alena/vacuum_check_logs.sql:52: INFO: finished vacuuming "postgres.public.vestat": index scans: 1
pages: 7512 removed, 0 remain, 3983 scanned (53.02% of total)
tuples: 900000 removed, 422804 remain, 0 are dead but not yet removable
removable cutoff: 747, which was 1 XIDs old when operation ended
new relfrozenxid: 747, which is 2 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan needed: 3983 pages from table (53.02% of total) had 900000 dead item identifiers removed
index "vestat_idx": pages: 4171 in total, 3949 newly deleted, 4167 currently deleted, 218 reusable
index "vestat_idx1": pages: 4342 in total, 3558 newly deleted, 4338 currently deleted, 780 reusable
I/O timings: read: 0.000 ms, write: 0.000 ms
avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
buffer usage: 51486 hits, 0 misses, 0 dirtied
WAL usage: 34428 records, 0 full page images, 9439566 bytes
system usage: CPU: user: 0.55 s, system: 0.01 s, elapsed: 1.04 s
VACUUM
pg_sleep
----------
(1 row)
INSERT 0 1000000
UPDATE 1000000
psql:/home/alena/vacuum_check_logs.sql:61: INFO: vacuuming "postgres.public.vestat"
psql:/home/alena/vacuum_check_logs.sql:61: INFO: launched 1 parallel vacuum worker for index vacuuming (planned: 1)
psql:/home/alena/vacuum_check_logs.sql:61: INFO: finished vacuuming "postgres.public.vestat": index scans: 1
pages: 0 removed, 8347 remain, 8347 scanned (100.00% of total)
tuples: 1000000 removed, 1000000 remain, 0 are dead but not yet removable
removable cutoff: 750, which was 0 XIDs old when operation ended
new relfrozenxid: 749, which is 2 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan needed: 3922 pages from table (46.99% of total) had 1000000 dead item identifiers removed
index "vestat_idx": pages: 8341 in total, 0 newly deleted, 3949 currently deleted, 3949 reusable
index "vestat_idx1": pages: 8295 in total, 780 newly deleted, 4338 currently deleted, 3558 reusable
I/O timings: read: 29.939 ms, write: 8.233 ms
avg read rate: 10.658 MB/s, avg write rate: 3.772 MB/s
buffer usage: 27904 hits, 5021 misses, 1777 dirtied
WAL usage: 22911 records, 151 full page images, 11283585 bytes
system usage: CPU: user: 2.88 s, system: 0.02 s, elapsed: 3.68 s
VACUUM
pg_sleep
----------
(1 row)
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Fix parallel vacuum buffer usage reporting
@ 2024-04-30 02:18 Masahiko Sawada <[email protected]>
parent: Alena Rybakina <[email protected]>
0 siblings, 2 replies; 21+ messages in thread
From: Masahiko Sawada @ 2024-04-30 02:18 UTC (permalink / raw)
To: Alena Rybakina <[email protected]>; +Cc: Anthonin Bonnefoy <[email protected]>; pgsql-hackers
On Fri, Apr 26, 2024 at 9:12 PM Alena Rybakina <[email protected]> wrote:
>
> Hi!
>>
>> The same script was run, but using vacuum verbose analyze, and I saw the difference again in the fifth step:
>> with your patch: buffer usage: 32312 hits, 607 misses, 1566 dirtied
>> master: buffer usage: 32346 hits, 573 misses, 1360 dirtied
>
> Isn't there a chance for the checkpointer to run during this time? That could make the conditions between the two runs slightly different and explain the change in buffer report.
>
> [0] https://github.com/postgres/postgres/blob/8a1b31e6e59631807a08a4e9465134c343bbdf5e/src/backend/acces...
>
> Looking at the script, you won't trigger the problem.
>
> Thank you for the link I accounted it in my next experiments.
>
> I repeated the test without processing checkpoints with a single index, and the number of pages in the buffer used almost matched:
>
> master branch: buffer usage: 32315 hits, 606 misses, 4486 dirtied
>
> with applied patch v4 version: buffer usage: 32315 hits, 606 misses, 4489 dirtied
>
> I think you are right - the problem was interfering with the checkpoint process, by the way I checked the first version patch. To cut a long story short, everything is fine now with one index.
>
> Just in case, I'll explain: I considered this case because your patch could have impact influenced it too.
>
> On 25.04.2024 10:17, Anthonin Bonnefoy wrote:
>
>
> On Wed, Apr 24, 2024 at 4:01 PM Alena Rybakina <[email protected]> wrote:
>>
>> I tested the main postgres branch with and without your fix using a script that was written by me. It consists of five scenarios and I made a comparison in the logs between the original version of the master branch and the master branch with your patch:
>
> Hi! Thanks for the tests.
>
>> I have attached a test file (vacuum_check_logs.sql)
>
> The reporting issue will only happen if there's a parallel index vacuum and it will only happen if there's at least 2 indexes [0]. You will need to create an additional index.
>
> Speaking of the problem, I added another index and repeated the test and found a significant difference:
>
> I found it when I commited the transaction (3):
>
> master: 2964 hits, 0 misses, 0 dirtied
>
> with applied patch v4 version: buffer usage: 33013 hits, 0 misses, 3 dirtied
>
> When I deleted all the data from the table and later started vacuum verbose again (4):
>
> master: buffer usage: 51486 hits, 0 misses, 0 dirtied
>
> with applied patch v4 version:buffer usage: 77924 hits, 0 misses, 0 dirtied
>
> when I inserted 1 million data into the table and updated it (5):
>
> master:buffer usage: 27904 hits, 5021 misses, 1777 dirtied
>
> with applied patch v4 version:buffer usage: 41051 hits, 9973 misses, 2564 dirtied
>
> As I see, the number of pages is significantly more than it was in the master branch and ,frankly, I couldn't fully figure out if it was a mistake or not.
I think that the patch fixes the problem correctly.
I've run pgindent and updated the commit message. I realized that
parallel vacuum was introduced in pg13 but buffer usage reporting in
VACUUM command was implemented in pg15. Therefore, in pg13 and pg14,
VACUUM (PARALLEL) is available but VACUUM (PARALLEL, VERBOSE) doesn't
show the buffer usage report. Autovacuum does show the buffer usage
report but parallel autovacuum is not supported. Therefore, we should
backpatch it down to 15, not 13.
I'm going to push the patch down to pg15, barring any objections.
Regards,
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
Attachments:
[application/octet-stream] v5-0001-Fix-parallel-vacuum-buffer-usage-reporting.patch (4.2K, ../../CAD21AoCX2oa_GSbmvc72ZAZ=LVK7y7tMJB8duFsTevBGMwp8rg@mail.gmail.com/2-v5-0001-Fix-parallel-vacuum-buffer-usage-reporting.patch)
download | inline diff:
From 3b733c821099c72e86bb147c1c8044c289c44665 Mon Sep 17 00:00:00 2001
From: Masahiko Sawada <[email protected]>
Date: Tue, 30 Apr 2024 11:11:23 +0900
Subject: [PATCH v5] Fix parallel vacuum buffer usage reporting.
A parallel worker's buffer usage is accumulated to its pgBufferUsage
and then is accumulated into the leader's one at the end of the
parallel vacuum. However, since the leader process used to use
dedicated VacuumPage{Hit, Miss, Dirty} globals for the buffer usage
reporting, the worker's buffer usages were not included, leading to an
incorrect buffer usage report.
To fix the problem, this commit makes vacuum uses pgBufferUsage
instruments for buffer usage reporting instead of VacuumPage{Hit,
Miss, Dirty} globals. These global variables are still used by ANALYZE
command and autoanalyze.
This also fixes the buffer usage report of vacuuming on temporary
tables, since the buffers dirtied by MarkLocalBufferDirty() were not
tracked by the VacuumPageDirty variable.
Parallel vacuum was introduced in 13, but the buffer usage reporting
for VACUUM command with the VERBOSE option was implemented in
15. Therefore, backpatch to 15.
Reported-by: Anthonin Bonnefoy
Author: Anthonin Bonnefoy
Reviewed-by: Alena Rybakina, Masahiko Sawada
Discussion: https://postgr.es/m/CAO6_XqrQk+QZQcYs_C6nk0cMfHuUWk85vT9CrcA1NffFbAVE2A@mail.gmail.com
Backpatch-through: 15
---
src/backend/access/heap/vacuumlazy.c | 24 +++++++++++-------------
1 file changed, 11 insertions(+), 13 deletions(-)
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index 0c5379666b..84cc983b6e 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -309,9 +309,7 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
PgStat_Counter startreadtime = 0,
startwritetime = 0;
WalUsage startwalusage = pgWalUsage;
- int64 StartPageHit = VacuumPageHit,
- StartPageMiss = VacuumPageMiss,
- StartPageDirty = VacuumPageDirty;
+ BufferUsage startbufferusage = pgBufferUsage;
ErrorContextCallback errcallback;
char **indnames = NULL;
@@ -604,18 +602,18 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
long secs_dur;
int usecs_dur;
WalUsage walusage;
+ BufferUsage bufferusage;
StringInfoData buf;
char *msgfmt;
int32 diff;
- int64 PageHitOp = VacuumPageHit - StartPageHit,
- PageMissOp = VacuumPageMiss - StartPageMiss,
- PageDirtyOp = VacuumPageDirty - StartPageDirty;
double read_rate = 0,
write_rate = 0;
TimestampDifference(starttime, endtime, &secs_dur, &usecs_dur);
memset(&walusage, 0, sizeof(WalUsage));
WalUsageAccumDiff(&walusage, &pgWalUsage, &startwalusage);
+ memset(&bufferusage, 0, sizeof(BufferUsage));
+ BufferUsageAccumDiff(&bufferusage, &pgBufferUsage, &startbufferusage);
initStringInfo(&buf);
if (verbose)
@@ -742,18 +740,18 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
}
if (secs_dur > 0 || usecs_dur > 0)
{
- read_rate = (double) BLCKSZ * PageMissOp / (1024 * 1024) /
- (secs_dur + usecs_dur / 1000000.0);
- write_rate = (double) BLCKSZ * PageDirtyOp / (1024 * 1024) /
- (secs_dur + usecs_dur / 1000000.0);
+ read_rate = (double) BLCKSZ * (bufferusage.shared_blks_read + bufferusage.local_blks_read) /
+ (1024 * 1024) / (secs_dur + usecs_dur / 1000000.0);
+ write_rate = (double) BLCKSZ * (bufferusage.shared_blks_dirtied + bufferusage.local_blks_dirtied) /
+ (1024 * 1024) / (secs_dur + usecs_dur / 1000000.0);
}
appendStringInfo(&buf, _("avg read rate: %.3f MB/s, avg write rate: %.3f MB/s\n"),
read_rate, write_rate);
appendStringInfo(&buf,
_("buffer usage: %lld hits, %lld misses, %lld dirtied\n"),
- (long long) PageHitOp,
- (long long) PageMissOp,
- (long long) PageDirtyOp);
+ (long long) (bufferusage.shared_blks_hit + bufferusage.local_blks_hit),
+ (long long) (bufferusage.shared_blks_read + bufferusage.local_blks_read),
+ (long long) (bufferusage.shared_blks_dirtied + bufferusage.local_blks_dirtied));
appendStringInfo(&buf,
_("WAL usage: %lld records, %lld full page images, %llu bytes\n"),
(long long) walusage.wal_records,
--
2.39.3
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Fix parallel vacuum buffer usage reporting
@ 2024-04-30 06:33 Anthonin Bonnefoy <[email protected]>
parent: Masahiko Sawada <[email protected]>
1 sibling, 1 reply; 21+ messages in thread
From: Anthonin Bonnefoy @ 2024-04-30 06:33 UTC (permalink / raw)
To: Masahiko Sawada <[email protected]>; +Cc: Alena Rybakina <[email protected]>; pgsql-hackers
I've done some additional tests to validate the reported numbers. Using
pg_statio, it's possible to get the minimum number of block hits (Full
script attached).
-- Save block hits before vacuum
SELECT pg_stat_force_next_flush();
SELECT heap_blks_hit, idx_blks_hit FROM pg_statio_all_tables where
relname='vestat' \gset
vacuum (verbose, index_cleanup on) vestat;
-- Check the difference
SELECT pg_stat_force_next_flush();
SELECT heap_blks_hit - :heap_blks_hit as delta_heap_hit,
idx_blks_hit - :idx_blks_hit as delta_idx_hit,
heap_blks_hit - :heap_blks_hit + idx_blks_hit - :idx_blks_hit as sum
FROM pg_statio_all_tables where relname='vestat';
Output:
...
buffer usage: 14676 hits, 0 misses, 667 dirtied
buffer usage (new): 16081 hits, 0 misses, 667 dirtied
...
-[ RECORD 1 ]--+------
delta_heap_hit | 9747
delta_idx_hit | 6325
sum | 16072
From pg_statio, we had 16072 blocks for the relation + indexes.
Pre-patch, we are under reporting with 14676.
Post-patch, we have 16081. The 9 additional block hits come from vacuum
accessing catalog tables like pg_class or pg_class_oid_index.
Attachments:
[application/octet-stream] vacuum_block_with_pgstatio.sql (842B, ../../CAO6_Xqr7_Yp8cJ_6HbVxaWW=owj4VJFbzcjmjgS4Y0CS7hL0TQ@mail.gmail.com/3-vacuum_block_with_pgstatio.sql)
download
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Fix parallel vacuum buffer usage reporting
@ 2024-05-01 03:36 Masahiko Sawada <[email protected]>
parent: Anthonin Bonnefoy <[email protected]>
0 siblings, 1 reply; 21+ messages in thread
From: Masahiko Sawada @ 2024-05-01 03:36 UTC (permalink / raw)
To: Anthonin Bonnefoy <[email protected]>; +Cc: Alena Rybakina <[email protected]>; pgsql-hackers
On Tue, Apr 30, 2024 at 3:34 PM Anthonin Bonnefoy
<[email protected]> wrote:
>
> I've done some additional tests to validate the reported numbers. Using pg_statio, it's possible to get the minimum number of block hits (Full script attached).
>
> -- Save block hits before vacuum
> SELECT pg_stat_force_next_flush();
> SELECT heap_blks_hit, idx_blks_hit FROM pg_statio_all_tables where relname='vestat' \gset
> vacuum (verbose, index_cleanup on) vestat;
> -- Check the difference
> SELECT pg_stat_force_next_flush();
> SELECT heap_blks_hit - :heap_blks_hit as delta_heap_hit,
> idx_blks_hit - :idx_blks_hit as delta_idx_hit,
> heap_blks_hit - :heap_blks_hit + idx_blks_hit - :idx_blks_hit as sum
> FROM pg_statio_all_tables where relname='vestat';
>
> Output:
> ...
> buffer usage: 14676 hits, 0 misses, 667 dirtied
> buffer usage (new): 16081 hits, 0 misses, 667 dirtied
> ...
> -[ RECORD 1 ]--+------
> delta_heap_hit | 9747
> delta_idx_hit | 6325
> sum | 16072
>
> From pg_statio, we had 16072 blocks for the relation + indexes.
> Pre-patch, we are under reporting with 14676.
> Post-patch, we have 16081. The 9 additional block hits come from vacuum accessing catalog tables like pg_class or pg_class_oid_index.
>
Thank you for further testing! I've pushed the patch.
Regards,
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Fix parallel vacuum buffer usage reporting
@ 2024-05-01 03:51 Alena Rybakina <[email protected]>
parent: Masahiko Sawada <[email protected]>
1 sibling, 1 reply; 21+ messages in thread
From: Alena Rybakina @ 2024-05-01 03:51 UTC (permalink / raw)
To: Masahiko Sawada <[email protected]>; +Cc: Anthonin Bonnefoy <[email protected]>; pgsql-hackers
Hi!
On 30.04.2024 05:18, Masahiko Sawada wrote:
> On Fri, Apr 26, 2024 at 9:12 PM Alena Rybakina<[email protected]> wrote:
>> Hi!
>>> The same script was run, but using vacuum verbose analyze, and I saw the difference again in the fifth step:
>>> with your patch: buffer usage: 32312 hits, 607 misses, 1566 dirtied
>>> master: buffer usage: 32346 hits, 573 misses, 1360 dirtied
>> Isn't there a chance for the checkpointer to run during this time? That could make the conditions between the two runs slightly different and explain the change in buffer report.
>>
>> [0]https://github.com/postgres/postgres/blob/8a1b31e6e59631807a08a4e9465134c343bbdf5e/src/backend/acces...
>>
>> Looking at the script, you won't trigger the problem.
>>
>> Thank you for the link I accounted it in my next experiments.
>>
>> I repeated the test without processing checkpoints with a single index, and the number of pages in the buffer used almost matched:
>>
>> master branch: buffer usage: 32315 hits, 606 misses, 4486 dirtied
>>
>> with applied patch v4 version: buffer usage: 32315 hits, 606 misses, 4489 dirtied
>>
>> I think you are right - the problem was interfering with the checkpoint process, by the way I checked the first version patch. To cut a long story short, everything is fine now with one index.
>>
>> Just in case, I'll explain: I considered this case because your patch could have impact influenced it too.
>>
>> On 25.04.2024 10:17, Anthonin Bonnefoy wrote:
>>
>>
>> On Wed, Apr 24, 2024 at 4:01 PM Alena Rybakina<[email protected]> wrote:
>>> I tested the main postgres branch with and without your fix using a script that was written by me. It consists of five scenarios and I made a comparison in the logs between the original version of the master branch and the master branch with your patch:
>> Hi! Thanks for the tests.
>>
>>> I have attached a test file (vacuum_check_logs.sql)
>> The reporting issue will only happen if there's a parallel index vacuum and it will only happen if there's at least 2 indexes [0]. You will need to create an additional index.
>>
>> Speaking of the problem, I added another index and repeated the test and found a significant difference:
>>
>> I found it when I commited the transaction (3):
>>
>> master: 2964 hits, 0 misses, 0 dirtied
>>
>> with applied patch v4 version: buffer usage: 33013 hits, 0 misses, 3 dirtied
>>
>> When I deleted all the data from the table and later started vacuum verbose again (4):
>>
>> master: buffer usage: 51486 hits, 0 misses, 0 dirtied
>>
>> with applied patch v4 version:buffer usage: 77924 hits, 0 misses, 0 dirtied
>>
>> when I inserted 1 million data into the table and updated it (5):
>>
>> master:buffer usage: 27904 hits, 5021 misses, 1777 dirtied
>>
>> with applied patch v4 version:buffer usage: 41051 hits, 9973 misses, 2564 dirtied
>>
>> As I see, the number of pages is significantly more than it was in the master branch and ,frankly, I couldn't fully figure out if it was a mistake or not.
> I think that the patch fixes the problem correctly.
>
> I've run pgindent and updated the commit message. I realized that
> parallel vacuum was introduced in pg13 but buffer usage reporting in
> VACUUM command was implemented in pg15. Therefore, in pg13 and pg14,
> VACUUM (PARALLEL) is available but VACUUM (PARALLEL, VERBOSE) doesn't
> show the buffer usage report. Autovacuum does show the buffer usage
> report but parallel autovacuum is not supported. Therefore, we should
> backpatch it down to 15, not 13.
>
> I'm going to push the patch down to pg15, barring any objections.
>
> Regards,
I agree with you about porting and I saw that the patch is working
correctly.
--
Regards,
Alena Rybakina
Postgres Professional:http://www.postgrespro.com
The Russian Postgres Company
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Fix parallel vacuum buffer usage reporting
@ 2024-05-03 06:41 Anthonin Bonnefoy <[email protected]>
parent: Alena Rybakina <[email protected]>
0 siblings, 1 reply; 21+ messages in thread
From: Anthonin Bonnefoy @ 2024-05-03 06:41 UTC (permalink / raw)
To: Alena Rybakina <[email protected]>; +Cc: Masahiko Sawada <[email protected]>; pgsql-hackers
On Wed, May 1, 2024 at 5:37 AM Masahiko Sawada <[email protected]>
wrote:
> Thank you for further testing! I've pushed the patch.
Thanks!
Here is the rebased version for the follow-up patch removing VacuumPage
variables. Though I'm not sure if I should create a dedicated mail thread
since the bug was fixed and the follow-up is more of a refactoring. What
do you think?
Attachments:
[application/octet-stream] v1-0001-Use-pgBufferUsage-for-block-reporting-in-analyze.patch (6.7K, ../../CAO6_XqpmPq1W9mG1nygKmmXHj-4_kf0zjSsoir0hsM81uNtc2A@mail.gmail.com/3-v1-0001-Use-pgBufferUsage-for-block-reporting-in-analyze.patch)
download | inline diff:
From 889828d00ce586f26335f9fcc77d68c28374da6c Mon Sep 17 00:00:00 2001
From: Anthonin Bonnefoy <[email protected]>
Date: Wed, 24 Apr 2024 10:40:33 +0200
Subject: Use pgBufferUsage for block reporting in analyze
pgBufferUsage is already providing block usage tracking. Replace
VacuumPage{Hit,Miss,Dirty} Vacuum specific variables by pgBufferUsage
in analyze command.
Those variables were only used in two places, verbose reporting
in vacuum and analyze. 5cd72cc0c5017a9d4de8b5d465a75946da5abd1d already
removed their usage in the vacuum command.
This makes VacuumPage{Hit,Miss,Dirty} completely unused and removable.
This commit removes both their calls in bufmgr and their declarations.
---
src/backend/commands/analyze.c | 24 +++++++++++-------------
src/backend/commands/vacuum.c | 3 ---
src/backend/commands/vacuumparallel.c | 3 ---
src/backend/storage/buffer/bufmgr.c | 4 ----
src/backend/utils/init/globals.c | 4 ----
src/include/miscadmin.h | 4 ----
6 files changed, 11 insertions(+), 31 deletions(-)
diff --git a/src/backend/commands/analyze.c b/src/backend/commands/analyze.c
index 7d2cd24997..cab5792525 100644
--- a/src/backend/commands/analyze.c
+++ b/src/backend/commands/analyze.c
@@ -303,9 +303,8 @@ do_analyze_rel(Relation onerel, VacuumParams *params,
Oid save_userid;
int save_sec_context;
int save_nestlevel;
- int64 AnalyzePageHit = VacuumPageHit;
- int64 AnalyzePageMiss = VacuumPageMiss;
- int64 AnalyzePageDirty = VacuumPageDirty;
+ BufferUsage startbufferusage = pgBufferUsage;
+ BufferUsage bufferusage;
PgStat_Counter startreadtime = 0;
PgStat_Counter startwritetime = 0;
@@ -737,9 +736,8 @@ do_analyze_rel(Relation onerel, VacuumParams *params,
* happened as part of the analyze by subtracting out the
* pre-analyze values which we saved above.
*/
- AnalyzePageHit = VacuumPageHit - AnalyzePageHit;
- AnalyzePageMiss = VacuumPageMiss - AnalyzePageMiss;
- AnalyzePageDirty = VacuumPageDirty - AnalyzePageDirty;
+ memset(&bufferusage, 0, sizeof(BufferUsage));
+ BufferUsageAccumDiff(&bufferusage, &pgBufferUsage, &startbufferusage);
/*
* We do not expect an analyze to take > 25 days and it simplifies
@@ -765,10 +763,10 @@ do_analyze_rel(Relation onerel, VacuumParams *params,
if (delay_in_ms > 0)
{
- read_rate = (double) BLCKSZ * AnalyzePageMiss / (1024 * 1024) /
- (delay_in_ms / 1000.0);
- write_rate = (double) BLCKSZ * AnalyzePageDirty / (1024 * 1024) /
- (delay_in_ms / 1000.0);
+ read_rate = (double) BLCKSZ * (bufferusage.shared_blks_read + bufferusage.local_blks_read) /
+ (1024 * 1024) / (delay_in_ms / 1000.0);
+ write_rate = (double) BLCKSZ * (bufferusage.shared_blks_dirtied + bufferusage.local_blks_dirtied) /
+ (1024 * 1024) / (delay_in_ms / 1000.0);
}
/*
@@ -792,9 +790,9 @@ do_analyze_rel(Relation onerel, VacuumParams *params,
appendStringInfo(&buf, _("avg read rate: %.3f MB/s, avg write rate: %.3f MB/s\n"),
read_rate, write_rate);
appendStringInfo(&buf, _("buffer usage: %lld hits, %lld misses, %lld dirtied\n"),
- (long long) AnalyzePageHit,
- (long long) AnalyzePageMiss,
- (long long) AnalyzePageDirty);
+ (long long) (bufferusage.shared_blks_hit + bufferusage.local_blks_hit),
+ (long long) (bufferusage.shared_blks_read + bufferusage.local_blks_read),
+ (long long) (bufferusage.shared_blks_dirtied + bufferusage.local_blks_dirtied));
appendStringInfo(&buf, _("system usage: %s"), pg_rusage_show(&ru0));
ereport(LOG,
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 521ee74586..4d8c0e511d 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -603,9 +603,6 @@ vacuum(List *relations, VacuumParams *params, BufferAccessStrategy bstrategy,
VacuumFailsafeActive = false;
VacuumUpdateCosts();
VacuumCostBalance = 0;
- VacuumPageHit = 0;
- VacuumPageMiss = 0;
- VacuumPageDirty = 0;
VacuumCostBalanceLocal = 0;
VacuumSharedCostBalance = NULL;
VacuumActiveNWorkers = NULL;
diff --git a/src/backend/commands/vacuumparallel.c b/src/backend/commands/vacuumparallel.c
index f26070bff2..22c057fe61 100644
--- a/src/backend/commands/vacuumparallel.c
+++ b/src/backend/commands/vacuumparallel.c
@@ -1043,9 +1043,6 @@ parallel_vacuum_main(dsm_segment *seg, shm_toc *toc)
/* Set cost-based vacuum delay */
VacuumUpdateCosts();
VacuumCostBalance = 0;
- VacuumPageHit = 0;
- VacuumPageMiss = 0;
- VacuumPageDirty = 0;
VacuumCostBalanceLocal = 0;
VacuumSharedCostBalance = &(shared->cost_balance);
VacuumActiveNWorkers = &(shared->active_nworkers);
diff --git a/src/backend/storage/buffer/bufmgr.c b/src/backend/storage/buffer/bufmgr.c
index 49637284f9..996ee6fdc8 100644
--- a/src/backend/storage/buffer/bufmgr.c
+++ b/src/backend/storage/buffer/bufmgr.c
@@ -1127,7 +1127,6 @@ PinBufferForBlock(Relation rel,
}
if (*foundPtr)
{
- VacuumPageHit++;
pgstat_count_io_op(io_object, io_context, IOOP_HIT);
if (VacuumCostActive)
VacuumCostBalance += VacuumCostPageHit;
@@ -1519,7 +1518,6 @@ WaitReadBuffers(ReadBuffersOperation *operation)
false);
}
- VacuumPageMiss += io_buffers_len;
if (VacuumCostActive)
VacuumCostBalance += VacuumCostPageMiss * io_buffers_len;
}
@@ -2513,7 +2511,6 @@ MarkBufferDirty(Buffer buffer)
*/
if (!(old_buf_state & BM_DIRTY))
{
- VacuumPageDirty++;
pgBufferUsage.shared_blks_dirtied++;
if (VacuumCostActive)
VacuumCostBalance += VacuumCostPageDirty;
@@ -5036,7 +5033,6 @@ MarkBufferDirtyHint(Buffer buffer, bool buffer_std)
if (dirtied)
{
- VacuumPageDirty++;
pgBufferUsage.shared_blks_dirtied++;
if (VacuumCostActive)
VacuumCostBalance += VacuumCostPageDirty;
diff --git a/src/backend/utils/init/globals.c b/src/backend/utils/init/globals.c
index cc61937eef..ba7f45e8a2 100644
--- a/src/backend/utils/init/globals.c
+++ b/src/backend/utils/init/globals.c
@@ -151,10 +151,6 @@ int VacuumCostPageDirty = 20;
int VacuumCostLimit = 200;
double VacuumCostDelay = 0;
-int64 VacuumPageHit = 0;
-int64 VacuumPageMiss = 0;
-int64 VacuumPageDirty = 0;
-
int VacuumCostBalance = 0; /* working state for vacuum */
bool VacuumCostActive = false;
diff --git a/src/include/miscadmin.h b/src/include/miscadmin.h
index 90f9b21b25..6d07005d39 100644
--- a/src/include/miscadmin.h
+++ b/src/include/miscadmin.h
@@ -283,10 +283,6 @@ extern PGDLLIMPORT int VacuumCostPageDirty;
extern PGDLLIMPORT int VacuumCostLimit;
extern PGDLLIMPORT double VacuumCostDelay;
-extern PGDLLIMPORT int64 VacuumPageHit;
-extern PGDLLIMPORT int64 VacuumPageMiss;
-extern PGDLLIMPORT int64 VacuumPageDirty;
-
extern PGDLLIMPORT int VacuumCostBalance;
extern PGDLLIMPORT bool VacuumCostActive;
--
2.39.3 (Apple Git-146)
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Fix parallel vacuum buffer usage reporting
@ 2024-05-08 13:46 Masahiko Sawada <[email protected]>
parent: Anthonin Bonnefoy <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Masahiko Sawada @ 2024-05-08 13:46 UTC (permalink / raw)
To: Anthonin Bonnefoy <[email protected]>; +Cc: Alena Rybakina <[email protected]>; pgsql-hackers
On Fri, May 3, 2024 at 3:41 PM Anthonin Bonnefoy
<[email protected]> wrote:
>
> On Wed, May 1, 2024 at 5:37 AM Masahiko Sawada <[email protected]> wrote:
>>
>> Thank you for further testing! I've pushed the patch.
>
> Thanks!
>
> Here is the rebased version for the follow-up patch removing VacuumPage variables. Though I'm not sure if I should create a dedicated mail thread since the bug was fixed and the follow-up is more of a refactoring. What do you think?
I'd suggest starting a new thread or changing the subject as the
current subject no longer matches what we're discussing.
Regards,
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Fix parallel vacuum buffer usage reporting
@ 2024-05-10 10:25 Nazir Bilal Yavuz <[email protected]>
parent: Masahiko Sawada <[email protected]>
0 siblings, 2 replies; 21+ messages in thread
From: Nazir Bilal Yavuz @ 2024-05-10 10:25 UTC (permalink / raw)
To: Masahiko Sawada <[email protected]>; +Cc: Anthonin Bonnefoy <[email protected]>; Alena Rybakina <[email protected]>; pgsql-hackers
Hi,
Thank you for working on this!
On Wed, 1 May 2024 at 06:37, Masahiko Sawada <[email protected]> wrote:
>
> Thank you for further testing! I've pushed the patch.
I realized a behaviour change while looking at 'Use pgBufferUsage for
block reporting in analyze' thread [1]. Since that change applies here
as well, I thought it is better to mention it here.
Before this commit, VacuumPageMiss did not count the blocks if its
read was already completed by other backends [2]. Now,
'pgBufferUsage.local_blks_read + pgBufferUsage.shared_blks_read'
counts every block attempted to be read; possibly double counting if
someone else has already completed the read. I do not know which
behaviour is correct but I wanted to mention this.
[1] https://postgr.es/m/CAO6_Xqr__kTTCLkftqS0qSCm-J7_xbRG3Ge2rWhucxQJMJhcRA%40mail.gmail.com
[2] In the WaitReadBuffers() function, see comment:
/*
* Skip this block if someone else has already completed it. If an
* I/O is already in progress in another backend, this will wait for
* the outcome: either done, or something went wrong and we will
* retry.
*/
--
Regards,
Nazir Bilal Yavuz
Microsoft
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Fix parallel vacuum buffer usage reporting
@ 2024-05-10 11:49 Alena Rybakina <[email protected]>
parent: Nazir Bilal Yavuz <[email protected]>
1 sibling, 1 reply; 21+ messages in thread
From: Alena Rybakina @ 2024-05-10 11:49 UTC (permalink / raw)
To: Nazir Bilal Yavuz <[email protected]>; +Cc: Masahiko Sawada <[email protected]>; Anthonin Bonnefoy <[email protected]>; pgsql-hackers
Hi! I could try to check it with the test, but I want to ask you about
details, because I'm not sure that I completely understand the test case.
You mean that we need to have two backends and on one of them we deleted
the tuples before vacuum called the other, do you?
On 10.05.2024 13:25, Nazir Bilal Yavuz wrote:
> Hi,
>
> Thank you for working on this!
>
> On Wed, 1 May 2024 at 06:37, Masahiko Sawada <[email protected]> wrote:
>> Thank you for further testing! I've pushed the patch.
> I realized a behaviour change while looking at 'Use pgBufferUsage for
> block reporting in analyze' thread [1]. Since that change applies here
> as well, I thought it is better to mention it here.
>
> Before this commit, VacuumPageMiss did not count the blocks if its
> read was already completed by other backends [2]. Now,
> 'pgBufferUsage.local_blks_read + pgBufferUsage.shared_blks_read'
> counts every block attempted to be read; possibly double counting if
> someone else has already completed the read. I do not know which
> behaviour is correct but I wanted to mention this.
>
> [1] https://postgr.es/m/CAO6_Xqr__kTTCLkftqS0qSCm-J7_xbRG3Ge2rWhucxQJMJhcRA%40mail.gmail.com
>
> [2] In the WaitReadBuffers() function, see comment:
> /*
> * Skip this block if someone else has already completed it. If an
> * I/O is already in progress in another backend, this will wait for
> * the outcome: either done, or something went wrong and we will
> * retry.
> */
>
--
Regards,
Alena Rybakina
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Fix parallel vacuum buffer usage reporting
@ 2024-05-10 13:21 Nazir Bilal Yavuz <[email protected]>
parent: Alena Rybakina <[email protected]>
0 siblings, 1 reply; 21+ messages in thread
From: Nazir Bilal Yavuz @ 2024-05-10 13:21 UTC (permalink / raw)
To: Alena Rybakina <[email protected]>; +Cc: Masahiko Sawada <[email protected]>; Anthonin Bonnefoy <[email protected]>; pgsql-hackers
Hi,
On Fri, 10 May 2024 at 14:49, Alena Rybakina <[email protected]> wrote:
>
> Hi! I could try to check it with the test, but I want to ask you about
> details, because I'm not sure that I completely understand the test case.
>
> You mean that we need to have two backends and on one of them we deleted
> the tuples before vacuum called the other, do you?
>
I think triggering a parallel vacuum is enough. I am able to see the
differences with the following:
You can apply the attached diff file to see the differences between
the previous version and the patched version. Then, run this query:
CREATE TABLE vacuum_fix (aid int, bid int, cid int) with
(autovacuum_enabled=false);
INSERT INTO vacuum_fix SELECT *, *, * FROM generate_series(1, 1000000);
CREATE INDEX a_idx on vacuum_fix (aid);
CREATE INDEX b_idx on vacuum_fix (bid);
CREATE INDEX c_idx on vacuum_fix (cid);
VACUUM vacuum_fix;
UPDATE vacuum_fix SET aid = aid + 1;
VACUUM (VERBOSE, PARALLEL 2) vacuum_fix ;
After that I saw:
INFO: vacuuming "test.public.vacuum_fix"
INFO: launched 2 parallel vacuum workers for index vacuuming (planned: 2)
INFO: finished vacuuming "test.public.vacuum_fix": index scans: 1
...
...
buffer usage: 29343 hits, 9580 misses in the previous version, 14165
misses in the patched version, 14262 dirtied
Patched version counts 14165 misses but the previous version counts
9580 misses in this specific example.
--
Regards,
Nazir Bilal Yavuz
Microsoft
Attachments:
[text/x-patch] see_previous_output.diff (1.5K, ../../CAN55FZ15kDMp-aPALoFVp2ANnG8Hx8GKr+a7XzK5zSV06Hgggg@mail.gmail.com/2-see_previous_output.diff)
download | inline diff:
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index 84cc983b6e6..582973d575b 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -309,6 +309,7 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
PgStat_Counter startreadtime = 0,
startwritetime = 0;
WalUsage startwalusage = pgWalUsage;
+ int64 StartPageMiss = VacuumPageMiss;
BufferUsage startbufferusage = pgBufferUsage;
ErrorContextCallback errcallback;
char **indnames = NULL;
@@ -606,6 +607,7 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
StringInfoData buf;
char *msgfmt;
int32 diff;
+ int64 PageMissOp = VacuumPageMiss - StartPageMiss;
double read_rate = 0,
write_rate = 0;
@@ -748,8 +750,9 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
appendStringInfo(&buf, _("avg read rate: %.3f MB/s, avg write rate: %.3f MB/s\n"),
read_rate, write_rate);
appendStringInfo(&buf,
- _("buffer usage: %lld hits, %lld misses, %lld dirtied\n"),
+ _("buffer usage: %lld hits, %lld misses in the previous version, %lld misses in the patched version, %lld dirtied\n"),
(long long) (bufferusage.shared_blks_hit + bufferusage.local_blks_hit),
+ (long long) (PageMissOp),
(long long) (bufferusage.shared_blks_read + bufferusage.local_blks_read),
(long long) (bufferusage.shared_blks_dirtied + bufferusage.local_blks_dirtied));
appendStringInfo(&buf,
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Fix parallel vacuum buffer usage reporting
@ 2024-05-10 13:55 Nazir Bilal Yavuz <[email protected]>
parent: Nazir Bilal Yavuz <[email protected]>
0 siblings, 1 reply; 21+ messages in thread
From: Nazir Bilal Yavuz @ 2024-05-10 13:55 UTC (permalink / raw)
To: Alena Rybakina <[email protected]>; +Cc: Masahiko Sawada <[email protected]>; Anthonin Bonnefoy <[email protected]>; pgsql-hackers
Hi,
On Fri, 10 May 2024 at 16:21, Nazir Bilal Yavuz <[email protected]> wrote:
>
> Hi,
>
> On Fri, 10 May 2024 at 14:49, Alena Rybakina <[email protected]> wrote:
> >
> > Hi! I could try to check it with the test, but I want to ask you about
> > details, because I'm not sure that I completely understand the test case.
> >
> > You mean that we need to have two backends and on one of them we deleted
> > the tuples before vacuum called the other, do you?
> >
>
> I think triggering a parallel vacuum is enough. I am able to see the
> differences with the following:
>
> You can apply the attached diff file to see the differences between
> the previous version and the patched version. Then, run this query:
>
> CREATE TABLE vacuum_fix (aid int, bid int, cid int) with
> (autovacuum_enabled=false);
> INSERT INTO vacuum_fix SELECT *, *, * FROM generate_series(1, 1000000);
> CREATE INDEX a_idx on vacuum_fix (aid);
> CREATE INDEX b_idx on vacuum_fix (bid);
> CREATE INDEX c_idx on vacuum_fix (cid);
> VACUUM vacuum_fix;
> UPDATE vacuum_fix SET aid = aid + 1;
> VACUUM (VERBOSE, PARALLEL 2) vacuum_fix ;
>
> After that I saw:
>
> INFO: vacuuming "test.public.vacuum_fix"
> INFO: launched 2 parallel vacuum workers for index vacuuming (planned: 2)
> INFO: finished vacuuming "test.public.vacuum_fix": index scans: 1
> ...
> ...
> buffer usage: 29343 hits, 9580 misses in the previous version, 14165
> misses in the patched version, 14262 dirtied
>
> Patched version counts 14165 misses but the previous version counts
> 9580 misses in this specific example.
I am sorry that I showed the wrong thing, this is exactly what is
fixed in this patch. Actually, I do not know how to trigger it;
currently I am looking for it. I will share if anything comes to my
mind.
--
Regards,
Nazir Bilal Yavuz
Microsoft
Attachments:
[text/x-patch] see_double_counts.diff (936B, ../../CAN55FZ2CLoT3Dz1+TBq5s2ibJas4eDBK5X1c-2xwQoUd4ki-SQ@mail.gmail.com/2-see_double_counts.diff)
download | inline diff:
diff --git a/src/backend/storage/buffer/bufmgr.c b/src/backend/storage/buffer/bufmgr.c
index 49637284f91..a6f1df11066 100644
--- a/src/backend/storage/buffer/bufmgr.c
+++ b/src/backend/storage/buffer/bufmgr.c
@@ -1355,6 +1355,7 @@ WaitReadBuffers(ReadBuffersOperation *operation)
IOContext io_context;
IOObject io_object;
char persistence;
+ static int double_counts = 0;
/*
* Currently operations are only allowed to include a read of some range,
@@ -1426,6 +1427,7 @@ WaitReadBuffers(ReadBuffersOperation *operation)
operation->smgr->smgr_rlocator.locator.relNumber,
operation->smgr->smgr_rlocator.backend,
true);
+ double_counts++;
continue;
}
@@ -1523,6 +1525,7 @@ WaitReadBuffers(ReadBuffersOperation *operation)
if (VacuumCostActive)
VacuumCostBalance += VacuumCostPageMiss * io_buffers_len;
}
+ elog(LOG, "Double counts = %d", double_counts);
}
/*
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Fix parallel vacuum buffer usage reporting
@ 2024-05-10 15:55 Nazir Bilal Yavuz <[email protected]>
parent: Nazir Bilal Yavuz <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Nazir Bilal Yavuz @ 2024-05-10 15:55 UTC (permalink / raw)
To: Alena Rybakina <[email protected]>; +Cc: Masahiko Sawada <[email protected]>; Anthonin Bonnefoy <[email protected]>; pgsql-hackers
Hi,
On Fri, 10 May 2024 at 16:55, Nazir Bilal Yavuz <[email protected]> wrote:
>
> Hi,
>
> On Fri, 10 May 2024 at 16:21, Nazir Bilal Yavuz <[email protected]> wrote:
> >
> > Hi,
> >
> > On Fri, 10 May 2024 at 14:49, Alena Rybakina <[email protected]> wrote:
> > >
> > > Hi! I could try to check it with the test, but I want to ask you about
> > > details, because I'm not sure that I completely understand the test case.
> > >
> > > You mean that we need to have two backends and on one of them we deleted
> > > the tuples before vacuum called the other, do you?
> > >
There should be some other backend(s) which will try to read the same
buffer with the ongoing VACUUM operation. I think it works now but the
reproduction steps are a bit racy. See:
1- Build Postgres with attached diff, it is the same
see_previous_output.diff that I shared two mails ago.
2- Run Postgres, all settings are default.
3- Use two client backends, let's name them as A and B client backends.
4- On A client backend, run:
CREATE TABLE vacuum_fix (aid int, bid int) with (autovacuum_enabled=false);
INSERT INTO vacuum_fix SELECT *, * FROM generate_series(1, 20000000);
VACUUM vacuum_fix;
UPDATE vacuum_fix SET aid = aid + 1, bid = bid + 1;
5- Now it will be a bit racy, SQL commands below need to be run at the
same time. The aim is for VACUUM on A client backend and SELECT on B
client backend to read the same buffers at the same time. So, some of
the buffers will be double counted.
Firstly, run VACUUM on A client backend; immediately after running
VACUUM, run SELECT on B backend.
A client backend:
VACUUM VERBOSE vacuum_fix;
B client backend:
SELECT * from vacuum_fix WHERE aid = -1;
This is the output of the VACUUM VERBOSE on my end:
INFO: vacuuming "test.public.vacuum_fix"
INFO: finished vacuuming "test.public.vacuum_fix": index scans: 0
pages: 0 removed, 176992 remain, 176992 scanned (100.00% of total)
...
...
buffer usage: 254181 hits, 99030 misses in the previous version, 99865
misses in the patched version, 106830 dirtied
...
VACUUM
Time: 2578.217 ms (00:02.578)
VACUUM does not run parallel, so this test case does not trigger what
is fixed in this thread. As it can be seen, there is ~1000 buffers
difference.
I am not sure if there is an easier way to reproduce this but I hope this helps.
--
Regards,
Nazir Bilal Yavuz
Microsoft
Attachments:
[text/x-patch] see_previous_output.diff (1.5K, ../../CAN55FZ24cArjPhm-53T-SAF=wiBM82oxp=fCjRJUynFKd74sGg@mail.gmail.com/2-see_previous_output.diff)
download | inline diff:
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index 84cc983b6e6..582973d575b 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -309,6 +309,7 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
PgStat_Counter startreadtime = 0,
startwritetime = 0;
WalUsage startwalusage = pgWalUsage;
+ int64 StartPageMiss = VacuumPageMiss;
BufferUsage startbufferusage = pgBufferUsage;
ErrorContextCallback errcallback;
char **indnames = NULL;
@@ -606,6 +607,7 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
StringInfoData buf;
char *msgfmt;
int32 diff;
+ int64 PageMissOp = VacuumPageMiss - StartPageMiss;
double read_rate = 0,
write_rate = 0;
@@ -748,8 +750,9 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
appendStringInfo(&buf, _("avg read rate: %.3f MB/s, avg write rate: %.3f MB/s\n"),
read_rate, write_rate);
appendStringInfo(&buf,
- _("buffer usage: %lld hits, %lld misses, %lld dirtied\n"),
+ _("buffer usage: %lld hits, %lld misses in the previous version, %lld misses in the patched version, %lld dirtied\n"),
(long long) (bufferusage.shared_blks_hit + bufferusage.local_blks_hit),
+ (long long) (PageMissOp),
(long long) (bufferusage.shared_blks_read + bufferusage.local_blks_read),
(long long) (bufferusage.shared_blks_dirtied + bufferusage.local_blks_dirtied));
appendStringInfo(&buf,
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Fix parallel vacuum buffer usage reporting
@ 2024-05-10 16:09 Masahiko Sawada <[email protected]>
parent: Nazir Bilal Yavuz <[email protected]>
1 sibling, 1 reply; 21+ messages in thread
From: Masahiko Sawada @ 2024-05-10 16:09 UTC (permalink / raw)
To: Nazir Bilal Yavuz <[email protected]>; +Cc: Anthonin Bonnefoy <[email protected]>; Alena Rybakina <[email protected]>; pgsql-hackers
On Fri, May 10, 2024 at 7:26 PM Nazir Bilal Yavuz <[email protected]> wrote:
>
> Hi,
>
> Thank you for working on this!
>
> On Wed, 1 May 2024 at 06:37, Masahiko Sawada <[email protected]> wrote:
> >
> > Thank you for further testing! I've pushed the patch.
>
> I realized a behaviour change while looking at 'Use pgBufferUsage for
> block reporting in analyze' thread [1]. Since that change applies here
> as well, I thought it is better to mention it here.
>
> Before this commit, VacuumPageMiss did not count the blocks if its
> read was already completed by other backends [2]. Now,
> 'pgBufferUsage.local_blks_read + pgBufferUsage.shared_blks_read'
> counts every block attempted to be read; possibly double counting if
> someone else has already completed the read.
True. IIUC there is such a difference only in HEAD but not in v15 and
v16. The following comment in WaitReadBuffers() says that it's a
traditional behavior that we count blocks as read even if someone else
has already completed its I/O:
/*
* We count all these blocks as read by this backend. This is traditional
* behavior, but might turn out to be not true if we find that someone
* else has beaten us and completed the read of some of these blocks. In
* that case the system globally double-counts, but we traditionally don't
* count this as a "hit", and we don't have a separate counter for "miss,
* but another backend completed the read".
*/
So I think using pgBufferUsage for (parallel) vacuum is a legitimate
usage and makes it more consistent with other parallel operations.
Regards,
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Fix parallel vacuum buffer usage reporting
@ 2024-05-13 13:10 Nazir Bilal Yavuz <[email protected]>
parent: Masahiko Sawada <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Nazir Bilal Yavuz @ 2024-05-13 13:10 UTC (permalink / raw)
To: Masahiko Sawada <[email protected]>; +Cc: Anthonin Bonnefoy <[email protected]>; Alena Rybakina <[email protected]>; pgsql-hackers
Hi,
On Fri, 10 May 2024 at 19:09, Masahiko Sawada <[email protected]> wrote:
>
> On Fri, May 10, 2024 at 7:26 PM Nazir Bilal Yavuz <[email protected]> wrote:
> >
> > Hi,
> >
> > Thank you for working on this!
> >
> > On Wed, 1 May 2024 at 06:37, Masahiko Sawada <[email protected]> wrote:
> > >
> > > Thank you for further testing! I've pushed the patch.
> >
> > I realized a behaviour change while looking at 'Use pgBufferUsage for
> > block reporting in analyze' thread [1]. Since that change applies here
> > as well, I thought it is better to mention it here.
> >
> > Before this commit, VacuumPageMiss did not count the blocks if its
> > read was already completed by other backends [2]. Now,
> > 'pgBufferUsage.local_blks_read + pgBufferUsage.shared_blks_read'
> > counts every block attempted to be read; possibly double counting if
> > someone else has already completed the read.
>
> True. IIUC there is such a difference only in HEAD but not in v15 and
> v16. The following comment in WaitReadBuffers() says that it's a
> traditional behavior that we count blocks as read even if someone else
> has already completed its I/O:
>
> /*
> * We count all these blocks as read by this backend. This is traditional
> * behavior, but might turn out to be not true if we find that someone
> * else has beaten us and completed the read of some of these blocks. In
> * that case the system globally double-counts, but we traditionally don't
> * count this as a "hit", and we don't have a separate counter for "miss,
> * but another backend completed the read".
> */
>
> So I think using pgBufferUsage for (parallel) vacuum is a legitimate
> usage and makes it more consistent with other parallel operations.
That sounds logical. Thank you for the clarification.
--
Regards,
Nazir Bilal Yavuz
Microsoft
^ permalink raw reply [nested|flat] 21+ messages in thread
end of thread, other threads:[~2024-05-13 13:10 UTC | newest]
Thread overview: 21+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-05-31 09:59 [PATCH v31 06/11] Add Incremental View Maintenance support Yugo Nagata <[email protected]>
2024-04-20 12:00 Re: Fix parallel vacuum buffer usage reporting Alena Rybakina <[email protected]>
2024-04-22 08:07 ` Re: Fix parallel vacuum buffer usage reporting Anthonin Bonnefoy <[email protected]>
2024-04-24 06:34 ` Re: Fix parallel vacuum buffer usage reporting Masahiko Sawada <[email protected]>
2024-04-24 09:19 ` Re: Fix parallel vacuum buffer usage reporting Anthonin Bonnefoy <[email protected]>
2024-04-24 14:01 ` Re: Fix parallel vacuum buffer usage reporting Alena Rybakina <[email protected]>
2024-04-25 07:17 ` Re: Fix parallel vacuum buffer usage reporting Anthonin Bonnefoy <[email protected]>
2024-04-26 12:12 ` Re: Fix parallel vacuum buffer usage reporting Alena Rybakina <[email protected]>
2024-04-30 02:18 ` Re: Fix parallel vacuum buffer usage reporting Masahiko Sawada <[email protected]>
2024-04-30 06:33 ` Re: Fix parallel vacuum buffer usage reporting Anthonin Bonnefoy <[email protected]>
2024-05-01 03:36 ` Re: Fix parallel vacuum buffer usage reporting Masahiko Sawada <[email protected]>
2024-05-10 10:25 ` Re: Fix parallel vacuum buffer usage reporting Nazir Bilal Yavuz <[email protected]>
2024-05-10 11:49 ` Re: Fix parallel vacuum buffer usage reporting Alena Rybakina <[email protected]>
2024-05-10 13:21 ` Re: Fix parallel vacuum buffer usage reporting Nazir Bilal Yavuz <[email protected]>
2024-05-10 13:55 ` Re: Fix parallel vacuum buffer usage reporting Nazir Bilal Yavuz <[email protected]>
2024-05-10 15:55 ` Re: Fix parallel vacuum buffer usage reporting Nazir Bilal Yavuz <[email protected]>
2024-05-10 16:09 ` Re: Fix parallel vacuum buffer usage reporting Masahiko Sawada <[email protected]>
2024-05-13 13:10 ` Re: Fix parallel vacuum buffer usage reporting Nazir Bilal Yavuz <[email protected]>
2024-05-01 03:51 ` Re: Fix parallel vacuum buffer usage reporting Alena Rybakina <[email protected]>
2024-05-03 06:41 ` Re: Fix parallel vacuum buffer usage reporting Anthonin Bonnefoy <[email protected]>
2024-05-08 13:46 ` Re: Fix parallel vacuum buffer usage reporting Masahiko Sawada <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox