pgsql: Use an index scan in RemoveSubscriptionRel() when possible.

From: Masahiko Sawada <msawada(at)postgresql(dot)org>
To: pgsql-committers(at)lists(dot)postgresql(dot)org
Subject: pgsql: Use an index scan in RemoveSubscriptionRel() when possible.
Date: 2026-11-20 18:22:00
Message-ID: E1xUTFH-00000000Xvx-3tl1@gemulon.postgresql.org
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-committers

Use an index scan in RemoveSubscriptionRel() when possible.

Previously, RemoveSubscriptionRel() 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. Since it's called for every
dropped relation, dropping tables on a subscriber with a large
pg_subscription_rel could be slow.

Use the index when a relation OID is given. DROP SUBSCRIPTION passes
only a subscription OID (the second index column), so it keeps using a
heap scan. In a performance test with 100,000 entries in
pg_subscription_rel, dropping 1,000 tables became 10x faster.

Author: Manu <manuelreyesbravo(at)gmail(dot)com>
Reviewed-by: Chao Li <li(dot)evan(dot)chao(at)gmail(dot)com>
Reviewed-by: Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com>
Discussion: https://postgr.es/m/179489354788.348371.16745415952616502664@gmail.com

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/72146c7db15773ba0660c7c7e0284e3b1116ae2f

Modified Files
--------------
src/backend/catalog/pg_subscription.c | 27 ++++++++++++++++-----------
1 file changed, 16 insertions(+), 11 deletions(-)

Browse pgsql-committers by date

  From Date Subject
Next Message Tom Lane 2026-11-20 19:53:16 pgsql: Fix SEARCH/CYCLE when the recursive term is SELECT DISTINCT.
Previous Message Tom Lane 2026-11-20 16:52:33 pgsql: Fix SEARCH/CYCLE when the recursive term has resjunk columns.