| 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(-)
| 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. |