0001-Reduce-pg_class-scans-in-GRANT-REVOKE-ON-ALL-TABLES-.patch

From 32e86de0e36a9b9544426ab91c6af57d029d078c Mon Sep 17 00:00:00 2001
From: charsyam <charsyam@naver.com>
Date: Sun, 12 Apr 2026 16:20:35 +0900
Subject: [PATCH] Reduce pg_class scans in GRANT/REVOKE ON ALL TABLES IN SCHEMA

When processing GRANT/REVOKE ... ON ALL TABLES IN SCHEMA,
objectsInSchemaToOids() called getRelationsInNamespace() five times,
once per relkind (RELATION, VIEW, MATVIEW, FOREIGN_TABLE,
PARTITIONED_TABLE).  pg_class does have an index on
(relname, relnamespace), but there is no index matching
(relnamespace, relkind), so each of those per-relkind calls falls
back to a full heap scan via table_beginscan_catalog() -- i.e. the
catalog is scanned five times in total.

Introduce getRelationsInNamespaceMulti(), which performs a single
heap scan filtered by relnamespace and distributes matching tuples
into per-relkind buckets supplied by the caller.  Relkind filtering
is done in C after each tuple is read, which is trivially cheap.
The OBJECT_TABLE case uses the helper; OBJECT_SEQUENCE and
OBJECT_PROPGRAPH keep calling getRelationsInNamespace() unchanged
because they only need a single relkind and benefit from the second
ScanKey.

Behavior is preserved:

* Result order is identical.  The underlying pg_class heap (and
  therefore its physical scan order) is the same regardless of how
  we filter, so each bucket ends up holding exactly the OIDs that
  the corresponding per-relkind heap scan would have produced, in
  the same order.  Concatenating the buckets in the original
  relkind order reproduces the previous list tuple-for-tuple.
  This was verified empirically by comparing, on a schema with
  mixed relkinds, the OID sequence produced by the old UNION-ALL
  pattern against the new single-scan + bucketed pattern; the
  sequences are identical element by element.

* MVCC semantics are, if anything, a bit stricter: all relkinds
  are now collected under a single catalog snapshot rather than
  five.

* Locking is unchanged in kind -- AccessShareLock on pg_class is
  still taken, just once instead of five times.

A simple benchmark (10,000 tables in one schema, pg_class ~10,452
rows) shows a consistent ~15% reduction in end-to-end time of
GRANT/REVOKE SELECT ON ALL TABLES IN SCHEMA:

  GRANT : 88.2 ms -> 75.9 ms
  REVOKE: 134.9 ms -> 115.7 ms

The absolute savings are small because the bulk of the time in
these commands is spent updating per-relation ACL tuples, not
scanning pg_class.  For schemas with only a handful of relations
the effect is not measurable.  The change is therefore a targeted
improvement for deployments with very large catalogs
(multi-tenant / partition-heavy systems) that frequently run ALL
TABLES IN SCHEMA grants.
---
 src/backend/catalog/aclchk.c | 90 ++++++++++++++++++++++++++++++++----
 1 file changed, 80 insertions(+), 10 deletions(-)

diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.c
index 67424fe3b0c..a68f0a989d4 100644
--- a/src/backend/catalog/aclchk.c
+++ b/src/backend/catalog/aclchk.c
@@ -125,6 +125,11 @@ static List *objectNamesToOids(ObjectType objtype, List *objnames,
 							   bool is_grant);
 static List *objectsInSchemaToOids(ObjectType objtype, List *nspnames);
 static List *getRelationsInNamespace(Oid namespaceId, char relkind);
+/* Single-scan helper over pg_class for multiple relkinds in one namespace */
+static void getRelationsInNamespaceMulti(Oid namespaceId,
+										 const char *relkinds,
+										 int nkinds,
+										 List **buckets);
 static void expand_col_privileges(List *colnames, Oid table_oid,
 								  AclMode this_privileges,
 								  AclMode *col_privileges,
@@ -797,16 +802,25 @@ objectsInSchemaToOids(ObjectType objtype, List *nspnames)
 		switch (objtype)
 		{
 			case OBJECT_TABLE:
-				objs = getRelationsInNamespace(namespaceId, RELKIND_RELATION);
-				objects = list_concat(objects, objs);
-				objs = getRelationsInNamespace(namespaceId, RELKIND_VIEW);
-				objects = list_concat(objects, objs);
-				objs = getRelationsInNamespace(namespaceId, RELKIND_MATVIEW);
-				objects = list_concat(objects, objs);
-				objs = getRelationsInNamespace(namespaceId, RELKIND_FOREIGN_TABLE);
-				objects = list_concat(objects, objs);
-				objs = getRelationsInNamespace(namespaceId, RELKIND_PARTITIONED_TABLE);
-				objects = list_concat(objects, objs);
+				{
+					const char kinds[] = {
+						RELKIND_RELATION,
+						RELKIND_VIEW,
+						RELKIND_MATVIEW,
+						RELKIND_FOREIGN_TABLE,
+						RELKIND_PARTITIONED_TABLE
+					};
+					List *buckets[lengthof(kinds)];
+					int i;
+
+					for (i = 0; i < (int) lengthof(kinds); i++)
+						buckets[i] = NIL;
+
+					getRelationsInNamespaceMulti(namespaceId, kinds, lengthof(kinds), buckets);
+
+					for (i = 0; i < (int) lengthof(kinds); i++)
+						objects = list_concat(objects, buckets[i]);
+				}
 				break;
 			case OBJECT_SEQUENCE:
 				objs = getRelationsInNamespace(namespaceId, RELKIND_SEQUENCE);
@@ -907,6 +921,62 @@ getRelationsInNamespace(Oid namespaceId, char relkind)
 	return relations;
 }
 
+/*
+ * getRelationsInNamespaceMulti
+ *
+ * Perform a single heap scan over pg_class for the given namespace, and
+ * distribute matching tuples into per-relkind buckets provided by the
+ * caller.  There is no pg_class index matching (relnamespace, relkind),
+ * so the previous per-relkind variant also resorted to a full heap scan;
+ * this helper simply collapses N such scans into one.
+ *
+ * Order preservation: within each bucket, entries appear in the order
+ * they were encountered during the heap scan.  Because the underlying
+ * heap (and thus its physical scan order) is the same regardless of
+ * how we filter, each bucket ends up holding the same OIDs in the same
+ * relative order as a separate per-relkind heap scan would have
+ * produced.  Concatenating the buckets in the caller's requested
+ * relkind order therefore reproduces the list that the previous code
+ * built from N separate getRelationsInNamespace() calls, tuple for
+ * tuple.
+ */
+static void
+getRelationsInNamespaceMulti(Oid namespaceId, const char *relkinds, int nkinds, List **buckets)
+{
+	ScanKeyData key;
+	Relation	rel;
+	TableScanDesc scan;
+	HeapTuple	tuple;
+	int			i;
+
+	/* Open pg_class once and scan by namespace; filter relkind in-code */
+	ScanKeyInit(&key,
+				Anum_pg_class_relnamespace,
+				BTEqualStrategyNumber, F_OIDEQ,
+				ObjectIdGetDatum(namespaceId));
+
+	rel = table_open(RelationRelationId, AccessShareLock);
+	scan = table_beginscan_catalog(rel, 1, &key);
+
+	while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL)
+	{
+		Form_pg_class cform = (Form_pg_class) GETSTRUCT(tuple);
+		char		rk = cform->relkind;
+
+		for (i = 0; i < nkinds; i++)
+		{
+			if (rk == relkinds[i])
+			{
+				buckets[i] = lappend_oid(buckets[i], cform->oid);
+				break;
+			}
+		}
+	}
+
+	table_endscan(scan);
+	table_close(rel, AccessShareLock);
+}
+
 
 /*
  * ALTER DEFAULT PRIVILEGES statement
-- 
2.50.1 (Apple Git-155)