暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / PostgreSQL查询耗时同临时表的数量呈线性增长

PostgreSQL查询耗时同临时表的数量呈线性增长

原创 小满未满、 2025-09-10
932

场景复现和简要分析

这是一个相对比较特殊的场景，想要验证的话，也蛮容易复现的。
postgres@zxm-VMware-Virtual-Platform:~/code/16$ psql -o /dev/nullpsql (16.10)Type "help" for help.
postgres=# \timingTiming is on.postgres=# select * from pg_database;Time: 0.491 mspostgres=# select * from pg_database;Time: 0.676 mspostgres=# select * from pg_database;Time: 0.573 mspostgres=# CREATE TEMP TABLE a_gtt (n numeric) ON COMMIT DELETE ROWS;Time: 5.771 mspostgres=# select * from a_gtt;Time: 1.721 mspostgres=# select * from a_gtt;Time: 1.383 mspostgres=# select * from a_gtt;Time: 1.703 mspostgres=# DO $$DECLARE  v_sql VARCHAR(100);BEGIN  FOR i IN 1..3000 LOOP    v_sql := 'CREATE TEMP TABLE a_gtt'||i||'(n numeric) ON COMMIT DELETE ROWS';    EXECUTE v_sql;  END LOOP;END;$$ LANGUAGE plpgsql;Time: 4150.059 ms (00:04.150)postgres=# select * from a_gtt;Time: 1250.611 ms (00:01.251)postgres=# select * from a_gtt;Time: 1239.305 ms (00:01.239)postgres=# select * from a_gtt;Time: 1213.000 ms (00:01.213)postgres=# select * from pg_database;Time: 1.975 mspostgres=# select * from pg_database;Time: 0.981 mspostgres=# select * from pg_database;Time: 0.758 ms
可以看看耗时增加了，对于查询临时表甚至不在一个数量级了（数千倍差距）。
粗略的判断，没有仔细研究，估计在于需要多次遍历大的list，以及还需要对临时表做一些额外的清理工作，这里主要耗时是在类似truncate的操作
// 和on_commits 息息相关static List *on_commits = NIL;
在创建临时表时，如果存在ON COMMIT的动作 就会将其动作注册到这个变量中
/* What to do at commit time for temporary relations */
typedef enum OnCommitAction
{
	ONCOMMIT_NOOP,				/* No ON COMMIT clause (do nothing) */
	ONCOMMIT_PRESERVE_ROWS,		/* ON COMMIT PRESERVE ROWS (do nothing) */
	ONCOMMIT_DELETE_ROWS,		/* ON COMMIT DELETE ROWS */
	ONCOMMIT_DROP				/* ON COMMIT DROP */
} OnCommitAction;


// heap_create_with_catalog
	 * If there's a special on-commit action, remember it
	 */
	if (oncommit != ONCOMMIT_NOOP)
		register_on_commit_action(relid, oncommit);

/*
 * Register a newly-created relation's ON COMMIT action.
 */
void
register_on_commit_action(Oid relid, OnCommitAction action)
{
	OnCommitItem *oc;
	MemoryContext oldcxt;

	/*
	 * We needn't bother registering the relation unless there is an ON COMMIT
	 * action we need to take.
	 */
	if (action == ONCOMMIT_NOOP || action == ONCOMMIT_PRESERVE_ROWS)
		return;

	oldcxt = MemoryContextSwitchTo(CacheMemoryContext);

	oc = (OnCommitItem *) palloc(sizeof(OnCommitItem));
	oc->relid = relid;
	oc->oncommit = action;
	oc->creating_subid = GetCurrentSubTransactionId();
	oc->deleting_subid = InvalidSubTransactionId;

	/*
	 * We use lcons() here so that ON COMMIT actions are processed in reverse
	 * order of registration.  That might not be essential but it seems
	 * reasonable.
	 */
	on_commits = lcons(oc, on_commits);

	MemoryContextSwitchTo(oldcxt);
}
而在事务将要完成之前，需要处理这个list中的相关内容
/*
 * Perform ON COMMIT actions.
 *
 * This is invoked just before actually committing, since it's possible
 * to encounter errors.
 */
void
PreCommit_on_commit_actions(void)
{
	ListCell   *l;
	List	   *oids_to_truncate = NIL;
	List	   *oids_to_drop = NIL;

         // 遍历
	foreach(l, on_commits)
	{
		OnCommitItem *oc = (OnCommitItem *) lfirst(l);

		/* Ignore entry if already dropped in this xact */
		if (oc->deleting_subid != InvalidSubTransactionId)
			continue;

		switch (oc->oncommit)
		{
			case ONCOMMIT_NOOP:
			case ONCOMMIT_PRESERVE_ROWS:
				/* Do nothing (there shouldn't be such entries, actually) */
				break;
			case ONCOMMIT_DELETE_ROWS:

				/*
				 * If this transaction hasn't accessed any temporary
				 * relations, we can skip truncating ON COMMIT DELETE ROWS
				 * tables, as they must still be empty.
				 */
				if ((MyXactFlags & XACT_FLAGS_ACCESSEDTEMPNAMESPACE))
					oids_to_truncate = lappend_oid(oids_to_truncate, oc->relid); // 生成新的list
				break;
			case ONCOMMIT_DROP:
				oids_to_drop = lappend_oid(oids_to_drop, oc->relid);
				break;
		}
	}

	/*
	 * Truncate relations before dropping so that all dependencies between
	 * relations are removed after they are worked on.  Doing it like this
	 * might be a waste as it is possible that a relation being truncated will
	 * be dropped anyway due to its parent being dropped, but this makes the
	 * code more robust because of not having to re-check that the relation
	 * exists at truncation time.
	 */
	if (oids_to_truncate != NIL)
		heap_truncate(oids_to_truncate); // 新的list需要处理

	if (oids_to_drop != NIL)
	{
		ObjectAddresses *targetObjects = new_object_addresses();

		foreach(l, oids_to_drop)
		{
			ObjectAddress object;

			object.classId = RelationRelationId;
			object.objectId = lfirst_oid(l);
			object.objectSubId = 0;

			Assert(!object_address_present(&object, targetObjects));

			add_exact_object_address(&object, targetObjects);
		}

		/*
		 * Object deletion might involve toast table access (to clean up
		 * toasted catalog entries), so ensure we have a valid snapshot.
		 */
		PushActiveSnapshot(GetTransactionSnapshot());

		/*
		 * Since this is an automatic drop, rather than one directly initiated
		 * by the user, we pass the PERFORM_DELETION_INTERNAL flag.
		 */
		performMultipleDeletions(targetObjects, DROP_CASCADE,
								 PERFORM_DELETION_INTERNAL | PERFORM_DELETION_QUIETLY);

		PopActiveSnapshot();

#ifdef USE_ASSERT_CHECKING

		/*
		 * Note that table deletion will call remove_on_commit_action, so the
		 * entry should get marked as deleted.
		 */
		foreach(l, on_commits)
		{
			OnCommitItem *oc = (OnCommitItem *) lfirst(l);

			if (oc->oncommit != ONCOMMIT_DROP)
				continue;

			Assert(oc->deleting_subid != InvalidSubTransactionId);
		}
#endif
	}
}
调试可以看到多个大的list
图片
其实主要耗时应该还是heap_truncate
void
heap_truncate(List *relids)
{
	List	   *relations = NIL;
	ListCell   *cell;

        // 遍历
	/* Open relations for processing, and grab exclusive access on each */
	foreach(cell, relids)
	{
		Oid			rid = lfirst_oid(cell);
		Relation	rel;

		rel = table_open(rid, AccessExclusiveLock);
		relations = lappend(relations, rel);  // 生成新的list
	}

	/* Don't allow truncate on tables that are referenced by foreign keys */
	heap_truncate_check_FKs(relations, true); // 其实这内部还有遍历

        // 再次遍历
	/* OK to do it */
	foreach(cell, relations)
	{
		Relation	rel = lfirst(cell);

		/* Truncate the relation */
		heap_truncate_one_rel(rel);  // 估计大部分的耗时在此处

		/* Close the relation, but keep exclusive lock on it until commit */
		table_close(rel, NoLock);
	}
}
调试可以看到多个大的list
图片
heap_truncate_check_FKs中其实还存在遍历，这里就不展示了。
接下来，让我们加些打印看看具体耗时。没修改什么代码，有空可以自己弄着玩玩。
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index e67ffc37b7d..d3d85cb1264 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -75,6 +75,7 @@
 #include "utils/lsyscache.h"
 #include "utils/syscache.h"
 
+#include "portability/instr_time.h"
 
 /* Potentially set by pg_upgrade_support functions */
 Oid                    binary_upgrade_next_heap_pg_class_oid = InvalidOid;
@@ -3070,6 +3071,9 @@ heap_truncate(List *relids)
 {
        List       *relations = NIL;
        ListCell   *cell;
+       instr_time  start;
+       instr_time  duration;
+       INSTR_TIME_SET_CURRENT(start);
 
        /* Open relations for processing, and grab exclusive access on each */
        foreach(cell, relids)
@@ -3095,6 +3099,10 @@ heap_truncate(List *relids)
                /* Close the relation, but keep exclusive lock on it until commit */
                table_close(rel, NoLock);
        }
+
+       INSTR_TIME_SET_CURRENT(duration);
+       INSTR_TIME_SUBTRACT(duration, start);
+       elog(INFO, "heap_truncate time: %.3f ms\n", INSTR_TIME_GET_MILLISEC(duration));
 }
 
 /*
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 016ae0a65f9..0172ec8bc5e 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -105,6 +105,8 @@
 #include "utils/typcache.h"
 #include "utils/usercontext.h"
 
+#include "portability/instr_time.h"
+
 /*
  * ON COMMIT action list
  */
@@ -17120,6 +17122,9 @@ PreCommit_on_commit_actions(void)
        ListCell   *l;
        List       *oids_to_truncate = NIL;
        List       *oids_to_drop = NIL;
+       instr_time  start;
+       instr_time  duration;
+       INSTR_TIME_SET_CURRENT(start);
 
        foreach(l, on_commits)
        {
@@ -17211,6 +17216,10 @@ PreCommit_on_commit_actions(void)
                }
 #endif
        }
+
+       INSTR_TIME_SET_CURRENT(duration);
+       INSTR_TIME_SUBTRACT(duration, start);
+       elog(INFO, "PreCommit_on_commit_actions time: %.3f ms\n", INSTR_TIME_GET_MILLISEC(duration));
 }
 
 /*
结果如下，可以看到耗时主要是在heap_truncate。
postgres@zxm-VMware-Virtual-Platform:~/code/16$ psql -o /dev/nullINFO:  PreCommit_on_commit_actions time: 0.000 mspsql (16.10)Type "help" for help.postgres=# \timingTiming is on.postgres=# select * from pg_database;INFO:  PreCommit_on_commit_actions time: 0.000 msTime: 1.724 mspostgres=# select * from pg_database;INFO:  PreCommit_on_commit_actions time: 0.000 msTime: 0.636 mspostgres=# select * from pg_database;INFO:  PreCommit_on_commit_actions time: 0.000 msTime: 0.634 mspostgres=# CREATE TEMP TABLE a_gtt (n numeric) ON COMMIT DELETE ROWS;INFO:  heap_truncate time: 0.534 msINFO:  PreCommit_on_commit_actions time: 0.618 msTime: 16.613 mspostgres=# select * from a_gtt;INFO:  heap_truncate time: 0.756 msINFO:  PreCommit_on_commit_actions time: 0.834 msTime: 2.341 mspostgres=# select * from a_gtt;INFO:  heap_truncate time: 0.727 msINFO:  PreCommit_on_commit_actions time: 0.789 msTime: 1.372 mspostgres=# select * from a_gtt;INFO:  heap_truncate time: 0.838 msINFO:  PreCommit_on_commit_actions time: 0.918 msTime: 1.746 mspostgres=# DO $$DECLARE  v_sql VARCHAR(100);BEGIN  FOR i IN 1..3000 LOOP    v_sql := 'CREATE TEMP TABLE a_gtt'||i||'(n numeric) ON COMMIT DELETE ROWS';    EXECUTE v_sql;  END LOOP;END;$$ LANGUAGE plpgsql;INFO:  heap_truncate time: 1048.421 msINFO:  PreCommit_on_commit_actions time: 1048.570 msTime: 3854.843 ms (00:03.855)postgres=# select * from a_gtt;INFO:  PreCommit_on_commit_actions time: 0.053 msINFO:  heap_truncate time: 1234.707 msINFO:  PreCommit_on_commit_actions time: 1234.880 msTime: 1242.413 ms (00:01.242)postgres=# select * from a_gtt;INFO:  PreCommit_on_commit_actions time: 0.039 msINFO:  heap_truncate time: 1199.870 msINFO:  PreCommit_on_commit_actions time: 1200.038 msTime: 1207.323 ms (00:01.207)postgres=# select * from a_gtt;INFO:  PreCommit_on_commit_actions time: 0.038 msINFO:  heap_truncate time: 1384.979 msINFO:  PreCommit_on_commit_actions time: 1385.284 msTime: 1393.626 ms (00:01.394)postgres=# select * from pg_database;INFO:  PreCommit_on_commit_actions time: 0.063 msINFO:  PreCommit_on_commit_actions time: 0.092 msTime: 2.293 mspostgres=# select * from pg_database;INFO:  PreCommit_on_commit_actions time: 0.111 msTime: 0.963 mspostgres=# select * from pg_database;INFO:  PreCommit_on_commit_actions time: 0.103 msTime: 0.933 mspostgres=# 
来自tom lane的评价，因为场景比较单一，对于优化此处要增加代码复杂度，让他并不感兴趣。
图片

声明

若文中存在错误或不当之处，敬请指出，以便我进行修正和完善。希望这篇文章能够帮助到各位。

文章转载请联系，谢谢合作~


其他文章推荐

简单解析下PostgreSQL的Toast
PostgreSQL的CREATE TABLE和文件分支
没有from是否能够执行count操作
WordPress 与Halo的爱恨情仇：无缝迁移全攻略
PostgreSQL数据回环的另一解决方案——bilogical
搭建一个多主复制数据回环场景
在PostgreSQL中，你不仅可以看到Bad Apple，还可以看故人唱跳、rap、打篮球的名场面
简单聊聊PostgreSQL中的多态伪类型
外国CTO也感兴趣的开源数据库项目——openHalo
openHalo问世，全球首款基于PostgreSQL兼容MySQL协议的国产开源数据库
玩一玩系列——玩玩login_hook（一款即将停止维护的PostgreSQL登录插件）
玩一玩系列——玩玩pg_duckdb
玩一玩系列——玩玩postgres_fdw
玩一玩系列——玩玩pg_dirtyread
惯性思维不可取 （看懂PostgreSQL where子句中表达式的先后执行顺序）
打破认知幻像：你写的SQL是否如你心意？
聊聊pg_bulkload的大概的实现逻辑
换一种角度理解PostgreSQL的search_path
PostgreSQL——关于autocommit功能的实现
PostgreSQL——关于临时表的二三事
让PostgreSQL拥抱全局临时表功能
Oracle、PostgreSQL、羲和（Halo）数据库中的的IN、OUT 和 INOUT参数模式
墨力计划postgresql
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

文章被以下合辑收录

PostgreSQL系列（共28篇）
PostgreSQL的相关知识

评论