| From: | Manu <manuelreyesbravo(at)gmail(dot)com> |
|---|---|
| To: | Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> |
| Cc: | Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com>, pgsql-hackers(at)lists(dot)postgresql(dot)org |
| Subject: | Use an index scan in RemoveSubscriptionRel() |
| Date: | 2026-11-17 05:32:27 |
| Message-ID: | 179489354788.348371.16745415952616502664@gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
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.
Manu
| Attachment | Content-Type | Size |
|---|---|---|
| v1-0001-Use-an-index-scan-in-RemoveSubscriptionRel.patch | text/x-patch | 3.1 KB |
| measurements.txt | text/plain | 3.3 KB |
| subrel-measure.py.txt | text/plain | 15.6 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Chao Li | 2026-11-17 05:37:38 | Re: extension_control_path: ignore empty elements and deduplicate directories |
| Previous Message | Hayato Kuroda (Fujitsu) | 2026-11-17 05:25:53 | RE: Document prepared transaction lock waits with two_phase subscriptions |