| From: | Chao Li <li(dot)evan(dot)chao(at)gmail(dot)com> |
|---|---|
| To: | Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> |
| Cc: | Manu <manuelreyesbravo(at)gmail(dot)com>, 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-19 07:35:07 |
| Message-ID: | 2FDA3C06-25CA-470E-8776-F4EA4061A75A@gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
> On Nov 19, 2026, at 07:28, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
>
> 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).
The reordering is required.
I created a TAP script, see the attachment. The test passes with the reordering. But if we revert the reordering, the test fails with this error:
```
[15:08:45.242](0.000s) # psql:<stdin>:1: ERROR: btree index keys must be ordered by attribute
[15:08:45.253](0.011s) not ok 3 - refresh removes the subscription relation
[15:08:45.253](0.000s)
[15:08:45.253](0.000s) # Failed test 'refresh removes the subscription relation'
```
Then in _bt_preprocess_keys(), I found this check:
```
/* check input keys are correctly ordered */
if (i < numberOfKeys && inkey->sk_attno < attno)
elog(ERROR, "btree index keys must be ordered by attribute");
```
Then looking at systable_beginscan(), though it converts attribute numbers to index column numbers, it doesn't reorder the keys in the array. Without the reordering, the index column numbers are 2 and 1, which violates the above check in _bt_preprocess_keys().
But I still agree that the “Put srrelid first” comment is unnecessary.
> ---
> + /*
> + * 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.
>
+1
Other than the nitpicks about the code comments, the patch overall looks good to me.
BTW, I put the attached TAP script under src/test/subscription/t/.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
| Attachment | Content-Type | Size |
|---|---|---|
| 039_remove_subscription_rel.pl | text/x-perl-script | 1.9 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Chao Li | 2026-11-19 07:39:21 | Re: Fix ISSN output truncation when the issue code is nonzero |
| Previous Message | Kyotaro Horiguchi | 2026-11-19 07:22:06 | A latent pointer increment bug in pgtypes_defmt_scan() |