Rename EXISTS-to-ANY converted subplan to exists_to_any

On Fri, Jun 5, 2026 at 4:56 PM Yugo Nagata <nagata@sraoss.co.jp> wrote:

Hi,

While looking at alternatives.out in the regression test of pg_plan_advice,
I was a bit confused because there are exists_1 and exists_2 in the generated
plan advice even though the query contains only one EXISTS.

EXPLAIN (COSTS OFF, PLAN_ADVICE)
SELECT * FROM alt_t1
WHERE EXISTS (SELECT 1 FROM alt_t2 WHERE alt_t2.a = alt_t1.a) OR alt_t1.a < 0;
QUERY PLAN
-------------------------------------------------------------------
Seq Scan on alt_t1
Filter: ((ANY (a = (hashed SubPlan exists_2).col1)) OR (a < 0))
SubPlan exists_2
-> Seq Scan on alt_t2
Generated Plan Advice:
SEQ_SCAN(alt_t1 alt_t2@exists_2)
NO_GATHER(alt_t1 alt_t2@exists_2)
DO_NOT_SCAN(alt_t2@exists_1)
(8 rows)

I realized that when an EXISTS is simple, it is converted into an additional
ANY subplan. However, it seems slightly confusing that the prefix "exists_"
is the same as the original EXISTS subplan, especially when using
pg_plan_advice to control the plan.

I am wondering whether it might be worth renaming the subplan to something
like "exists_to_any", to make it clearer that it is an ANY subplan converted
from EXISTS rather than the original EXISTS. I’ve attached a patch in this direction.

What do you think?

Agreed.

Using the same "exists_*" prefix for both the original EXISTS subplan and
the EXISTS-to-ANY converted alternative is confusing, especially for
pg_plan_advice where the subplan name is user-visible and used when
specifying advice.

Renaming the converted subplan to "exists_to_any_*" makes the EXPLAIN
output and generated advice easier to understand without changing planner
behavior, so this seems like a reasonable improvement.

Regards,

--
Fujii Masao

On Tue, Jun 9, 2026 at 3:01 PM Fujii Masao <masao.fujii@gmail.com> wrote:

On Fri, Jun 5, 2026 at 4:56 PM Yugo Nagata <nagata@sraoss.co.jp> wrote:

Hi,

While looking at alternatives.out in the regression test of pg_plan_advice,
I was a bit confused because there are exists_1 and exists_2 in the generated
plan advice even though the query contains only one EXISTS.

EXPLAIN (COSTS OFF, PLAN_ADVICE)
SELECT * FROM alt_t1
WHERE EXISTS (SELECT 1 FROM alt_t2 WHERE alt_t2.a = alt_t1.a) OR alt_t1.a < 0;
QUERY PLAN
-------------------------------------------------------------------
Seq Scan on alt_t1
Filter: ((ANY (a = (hashed SubPlan exists_2).col1)) OR (a < 0))
SubPlan exists_2
-> Seq Scan on alt_t2
Generated Plan Advice:
SEQ_SCAN(alt_t1 alt_t2@exists_2)
NO_GATHER(alt_t1 alt_t2@exists_2)
DO_NOT_SCAN(alt_t2@exists_1)
(8 rows)

I realized that when an EXISTS is simple, it is converted into an additional
ANY subplan. However, it seems slightly confusing that the prefix "exists_"
is the same as the original EXISTS subplan, especially when using
pg_plan_advice to control the plan.

I am wondering whether it might be worth renaming the subplan to something
like "exists_to_any", to make it clearer that it is an ANY subplan converted
from EXISTS rather than the original EXISTS. I’ve attached a patch in this direction.

What do you think?

Agreed.

Using the same "exists_*" prefix for both the original EXISTS subplan and
the EXISTS-to-ANY converted alternative is confusing, especially for
pg_plan_advice where the subplan name is user-visible and used when
specifying advice.

Renaming the converted subplan to "exists_to_any_*" makes the EXPLAIN
output and generated advice easier to understand without changing planner
behavior, so this seems like a reasonable improvement.

I think this is an improvement rather than a bug fix. However, since the
subplan name is user-visible in EXPLAIN/PLAN_ADVICE and can also be
referenced by pg_plan_advice in v19, it seems better to make this
adjustment before v19 is released rather than ship the current naming
and change it later in v20. Thoughts?

If we agree with this direction and there are no objections,
I'll commit the patch.

Reards,

--
Fujii Masao

On Thu, Jun 18, 2026 at 11:14 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

So if we wait till v20 then v19 will be unlike both the previous and
following releases. Probably better to change only once.

Agreed.

IIRC, Robert is responsible for the change we have now, so looping
him in to see if he's got any objections to changing it again.

Yes, looping him in sounds reasonable.

Regards,

--
Fujii Masao