| From: | Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> |
|---|---|
| To: | Manu <manuelreyesbravo(at)gmail(dot)com> |
| Cc: | Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>, pgsql-hackers(at)lists(dot)postgresql(dot)org |
| Subject: | Re: Use an index scan in RemoveSubscriptionRel() |
| Date: | 2026-11-18 23:28:25 |
| Message-ID: | CAD21AoCvcdyxTM72+C=0aLpGtJ+hWP92_kiaOqz60RY6TkTtJQ@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
On Mon, Nov 16, 2026 at 9:32 PM Manu <manuelreyesbravo(at)gmail(dot)com> 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).
---
+ /*
+ * 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);
The comments seem too detailed to me. The first half is clear from the
OidIsValid(relid) check, but I think the fallback part is worth
keeping. It's not obvious that DROP SUBSCRIPTION can't use this index
(srsubid is the second column), so it has to fall back to a heap scan.
Regards,
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Masahiko Sawada | 2026-11-18 23:38:05 | Re: reindexdb: Change quoting rule of --schema? |
| Previous Message | Tom Lane | 2026-11-18 23:08:25 | Re: [PATCH] Fix encoding-dependent LIKE/regex selectivity estimates |