Re: Use an index scan in RemoveSubscriptionRel()

From: Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com>
To: Chao Li <li(dot)evan(dot)chao(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 18:46:04
Message-ID: CAD21AoDv2iA2ROOAkkFJC-A7jHFxKH-Ei39cmVY8KXCtO51Bzw@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Wed, Nov 18, 2026 at 11:35 PM Chao Li <li(dot)evan(dot)chao(at)gmail(dot)com> wrote:
>
>
>
> > 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.

You're right. I misunderstood the code.

> Other than the nitpicks about the code comments, the patch overall looks good to me.

Agreed.

I've attached the updated patch that includes the minor fix. I'm going
to push it, barring any objections.

Regards,

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com

Attachment Content-Type Size
v2-0001-Use-an-index-scan-in-RemoveSubscriptionRel-when-p.patch text/x-patch 2.8 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Manu 2026-11-19 19:08:57 Re: pgsql: Don't trust NOT NULL on virtual generated columns of partitioned
Previous Message Tom Lane 2026-11-19 18:45:46 Re: [PATCH] Fix encoding-dependent LIKE/regex selectivity estimates