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

Fujii Masao <masao.fujii@gmail.com> writes:

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?

This changes user-visible behavior, but we already changed that since
v18. I checked this case:

create table alt_t1(a int, b int);
create table alt_t2(a int, b int);
explain SELECT * FROM alt_t1 WHERE EXISTS (SELECT 1 FROM alt_t2 WHERE alt_t2.a = alt_t1.a) OR alt_t1.a < 0;

v18 says

Seq Scan on alt_t1 (cost=0.00..7896.89 rows=1507 width=8)
Filter: ((ANY (a = (hashed SubPlan 2).col1)) OR (a < 0))
SubPlan 2
-> Seq Scan on alt_t2 (cost=0.00..32.60 rows=2260 width=4)

HEAD says

Seq Scan on alt_t1 (cost=0.00..7896.89 rows=1507 width=8)
Filter: ((ANY (a = (hashed SubPlan exists_2).col1)) OR (a < 0))
SubPlan exists_2
-> Seq Scan on alt_t2 (cost=0.00..32.60 rows=2260 width=4)

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

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.

regards, tom lane

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

Hi,

I tested this patch on the current HEAD.
Before applying the patch, I observed that the converted subplan was
displayed with the exists_ prefix in the EXPLAIN output.
For example:
Filter: ((ANY (a = (hashed SubPlan exists_2).col1)) OR (a < 0))
SubPlan exists_2
Even though the planner had internally converted the simple EXISTS
subquery into an ANY subplan, the displayed name still used the
exists_ prefix, which could be a little confusing when reading the
execution plan.

After applying the patch, the same query produced:
Filter: ((ANY (a = (hashed SubPlan exists_to_any_1).col1)) OR (a < 0))
SubPlan exists_to_any_1
The new name makes it much clearer that this subplan is the result of
an EXISTS-to-ANY conversion.

I also verified that the query executed successfully before and after
applying the patch. I didn't notice any change in the execution plan
or query results ,the only visible difference was the improved subplan
naming in the EXPLAIN output.
Overall, the patch improves the readability of the user-visible plan
without changing planner behavior.

Regards,
Solai

On Fri, 17 Jul 2026 16:40:29 +0530
solai v <solai.cdac@gmail.com> wrote:

I tested this patch on the current HEAD.
Before applying the patch, I observed that the converted subplan was
displayed with the exists_ prefix in the EXPLAIN output.
For example:
Filter: ((ANY (a = (hashed SubPlan exists_2).col1)) OR (a < 0))
SubPlan exists_2
Even though the planner had internally converted the simple EXISTS
subquery into an ANY subplan, the displayed name still used the
exists_ prefix, which could be a little confusing when reading the
execution plan.

After applying the patch, the same query produced:
Filter: ((ANY (a = (hashed SubPlan exists_to_any_1).col1)) OR (a < 0))
SubPlan exists_to_any_1
The new name makes it much clearer that this subplan is the result of
an EXISTS-to-ANY conversion.

I also verified that the query executed successfully before and after
applying the patch. I didn't notice any change in the execution plan
or query results ,the only visible difference was the improved subplan
naming in the EXPLAIN output.
Overall, the patch improves the readability of the user-visible plan
without changing planner behavior.

Hi Solai, Robert,

Thank you, Solai, for testing the patch and confirming the results.
It is good to hear that the change improves the readability of the
EXPLAIN　output as intended.

Robert, do you have a chance to review this? As Tom and Fujii-san
suggested, we'd like to get this into v19 to keep the naming consistent.
If you don't have any objections, we'd like to move forward with this patch.

Regards,
Yugo Nagata

--
Yugo Nagata <nagata@sraoss.co.jp>

On Sat, 25 Jul 2026 02:12:56 +0900
Yugo Nagata <nagata@sraoss.co.jp> wrote:

On Fri, 17 Jul 2026 16:40:29 +0530
solai v <solai.cdac@gmail.com> wrote:

I tested this patch on the current HEAD.
Before applying the patch, I observed that the converted subplan was
displayed with the exists_ prefix in the EXPLAIN output.
For example:
Filter: ((ANY (a = (hashed SubPlan exists_2).col1)) OR (a < 0))
SubPlan exists_2
Even though the planner had internally converted the simple EXISTS
subquery into an ANY subplan, the displayed name still used the
exists_ prefix, which could be a little confusing when reading the
execution plan.

After applying the patch, the same query produced:
Filter: ((ANY (a = (hashed SubPlan exists_to_any_1).col1)) OR (a < 0))
SubPlan exists_to_any_1
The new name makes it much clearer that this subplan is the result of
an EXISTS-to-ANY conversion.

I also verified that the query executed successfully before and after
applying the patch. I didn't notice any change in the execution plan
or query results ,the only visible difference was the improved subplan
naming in the EXPLAIN output.
Overall, the patch improves the readability of the user-visible plan
without changing planner behavior.

Hi Solai, Robert,

Thank you, Solai, for testing the patch and confirming the results.
It is good to hear that the change improves the readability of the
EXPLAIN　output as intended.

Robert, do you have a chance to review this? As Tom and Fujii-san
suggested, we'd like to get this into v19 to keep the naming consistent.
If you don't have any objections, we'd like to move forward with this patch.

Hi Robert,

As we'd like to get this into v19 if possible, I'd like to check one more time
if there are any objections to this change.

Regards,
Yugo Nagata

--
Yugo Nagata <nagata@sraoss.co.jp>

On Wed, Aug 5, 2026 at 12:00 PM Yugo Nagata <nagata@sraoss.co.jp> wrote:

As we'd like to get this into v19 if possible, I'd like to check one more time
if there are any objections to this change.

I've updated the source comment in subselect.c and the commit message.
Attached is v2 of the patch.

Since there do not seem to be any objections to this change,
I will commit v2 shortly.

Regards,

--
Fujii Masao

On Thu, Aug 13, 2026 at 10:05 PM Fujii Masao <masao.fujii@gmail.com> wrote:

Since there do not seem to be any objections to this change,
I will commit v2 shortly.

I've pushed the patch. Thanks!

Regards,

--
Fujii Masao

On Sat, 15 Aug 2026 18:14:38 +0900
Fujii Masao <masao.fujii@gmail.com> wrote:

On Thu, Aug 13, 2026 at 10:05 PM Fujii Masao <masao.fujii@gmail.com> wrote:

Since there do not seem to be any objections to this change,
I will commit v2 shortly.

I've pushed the patch. Thanks!

Thank you for updating and committing the patch.

I was on summer vacation and couldn't see your updates, but I've now checked
the committed version and it looks good to me.

Regards,
Yugo Nagata

--
Yugo Nagata <nagata@sraoss.co.jp>