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
