On Wed, Nov 18, 2026 at 11:35 PM Chao Li <[email protected]> wrote:
>
>
>
> > On Nov 19, 2026, at 07:28, Masahiko Sawada <[email protected]> wrote:
> >
> > On Mon, Nov 16, 2026 at 9:32 PM Manu <[email protected]> wrote:
> >>
> >> Hi,
> >>
> >> On a subscriber, every dropped relation scans all of
> >> pg_subscription_rel: RemoveSubscriptionRel() uses a heap scan although
> >> the (srrelid, srsubid) primary key serves the lookup by relid.  A table
> >> with a text column and an identity key pays it three times (the table,
> >> its TOAST table and its sequence).  The attached patch uses the index
> >> when relid is given; DROP SUBSCRIPTION keeps the heap scan.
> >>
> >> With 100,000 rows in pg_subscription_rel, dropping a schema of 1,000
> >> such tables takes 2.8 s on master and 0.23 s patched, and a transaction
> >> with an ON COMMIT DROP temporary table runs 45M instructions instead of
> >> 2.5M.  With an empty catalog the index costs 0.6% more instructions.
> >> If several subscriptions have the table in a non-ready state, the error
> >> now names the lowest OID rather than the first one in heap order.  The
> >> setup is synthetic; measurements.txt and the script are attached.
> >
> > Thank you for the patch. +1 for the idea. Here are some comments:
> >
> > ---
> > + /* Put srrelid first: index scan keys must be in index column order. */
> > + if (OidIsValid(relid))
> >  {
> >
> > The reordering is harmless but it's not actually required.
> > systable_beginscan() maps each scan key to its index column by
> > attribute number, so the order in the array doesn't matter. I'm fine
> > with keeping it for readability, but I think the comment should be
> > reworded (or just dropped).
>
> The reordering is required.

You're right. I misunderstood the code.

> Other than the nitpicks about the code comments, the patch overall looks good 
> to me.

Agreed.

I've attached the updated patch that includes the minor fix. I'm going
to push it, barring any objections.

Regards,

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
From 685a0ae18b545dff29e5ef29fac341cbbc0c0e16 Mon Sep 17 00:00:00 2001
From: Manuel Reyes Bravo <[email protected]>
Date: Mon, 16 Nov 2026 19:41:27 -0300
Subject: [PATCH v2] Use an index scan in RemoveSubscriptionRel() when
 possible.

Previously this function scanned all of pg_subscription_rel with a
heap scan, even when given a relation OID that the (srrelid, srsubid)
primary key index could serve.  It's called for every dropped
relation, so on a subscriber with a large pg_subscription_rel every
dropped table paid for a full scan.

Use the index when a relation OID is given.  DROP SUBSCRIPTION passes
only a subscription OID, so that path keeps scanning the heap.

Author: Manu <[email protected]>
Reviewed-by: Masahiko Sawada <[email protected]>
Reviewed-by: Chao Li <[email protected]>
Discussion: https://postgr.es/m/[email protected]
---
 src/backend/catalog/pg_subscription.c | 27 ++++++++++++++++-----------
 1 file changed, 16 insertions(+), 11 deletions(-)

diff --git a/src/backend/catalog/pg_subscription.c b/src/backend/catalog/pg_subscription.c
index 60c15b22194..2ae57c056b4 100644
--- a/src/backend/catalog/pg_subscription.c
+++ b/src/backend/catalog/pg_subscription.c
@@ -520,34 +520,39 @@ void
 RemoveSubscriptionRel(Oid subid, Oid relid)
 {
 	Relation	rel;
-	TableScanDesc scan;
+	SysScanDesc scan;
 	ScanKeyData skey[2];
 	HeapTuple	tup;
 	int			nkeys = 0;
 
 	rel = table_open(SubscriptionRelRelationId, RowExclusiveLock);
 
-	if (OidIsValid(subid))
+	if (OidIsValid(relid))
 	{
 		ScanKeyInit(&skey[nkeys++],
-					Anum_pg_subscription_rel_srsubid,
+					Anum_pg_subscription_rel_srrelid,
 					BTEqualStrategyNumber,
 					F_OIDEQ,
-					ObjectIdGetDatum(subid));
+					ObjectIdGetDatum(relid));
 	}
 
-	if (OidIsValid(relid))
+	if (OidIsValid(subid))
 	{
 		ScanKeyInit(&skey[nkeys++],
-					Anum_pg_subscription_rel_srrelid,
+					Anum_pg_subscription_rel_srsubid,
 					BTEqualStrategyNumber,
 					F_OIDEQ,
-					ObjectIdGetDatum(relid));
+					ObjectIdGetDatum(subid));
 	}
 
-	/* Do the search and delete what we found. */
-	scan = table_beginscan_catalog(rel, nkeys, skey);
-	while (HeapTupleIsValid(tup = heap_getnext(scan, ForwardScanDirection)))
+	/*
+	 * Do the search and delete what we found.  Use the (srrelid, srsubid)
+	 * index when relid is given; otherwise (DROP SUBSCRIPTION) there is no
+	 * suitable index, so fall back to a heap scan.
+	 */
+	scan = systable_beginscan(rel, SubscriptionRelSrrelidSrsubidIndexId,
+							  OidIsValid(relid), NULL, nkeys, skey);
+	while (HeapTupleIsValid(tup = systable_getnext(scan)))
 	{
 		Form_pg_subscription_rel subrel;
 
@@ -584,7 +589,7 @@ RemoveSubscriptionRel(Oid subid, Oid relid)
 
 		CatalogTupleDelete(rel, &tup->t_self);
 	}
-	table_endscan(scan);
+	systable_endscan(scan);
 
 	table_close(rel, RowExclusiveLock);
 }
-- 
2.56.0

Reply via email to