Remove no-op PlaceHolderVars

From: Richard Guo <guofenglinux(at)gmail(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Remove no-op PlaceHolderVars
Date: 2024-09-03 02:53:21
Message-ID: CAMbWs48biJp-vof82PNP_LzzFkURh0W+RKt4phoML-MyYavgdg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

In [1] there was a short discussion about removing no-op
PlaceHolderVars. This thread is for continuing that discussion.

We may insert PlaceHolderVars when pulling up a subquery that is
within the nullable side of an outer join: if subquery pullup needs to
replace a subquery-referencing Var that has nonempty varnullingrels
with an expression that is not simply a Var, we may need to wrap the
replacement expression into a PlaceHolderVar. However, if the outer
join above is later reduced to an inner join, the PHVs would become
no-ops with no phnullingrels bits.

I think it's always desirable to remove these no-op PlaceHolderVars
because they can constrain optimization opportunities. The issue
reported in [1] shows that they can block subexpression folding, which
can prevent an expression from being matched to an index. I provided
another example in that thread which shows that no-op PlaceHolderVars
can force join orders, potentially forcing us to resort to nestloop
join in some cases.

As explained in the comment of remove_nulling_relids_mutator, PHVs are
used in some cases to enforce separate identity of subexpressions. In
other cases, however, it should be safe to remove a PHV if its
phnullingrels becomes empty.

Attached is a WIP patch that marks PHVs that need to be kept because
they are serving to isolate subexpressions, and removes all other PHVs
in remove_nulling_relids_mutator if their phnullingrels bits become
empty. One problem with it is that a PHV's contained expression may
not have been fully preprocessed. For example if the PHV is used as a
qual clause, its contained expression is not processed for AND/OR
flattening, which could trigger the Assert in make_restrictinfo that
the clause shouldn't be an AND clause.

For example:

create table t (a boolean, b boolean);

select * from t t1 left join
lateral (select (t1.a and t1.b) as x, * from t t2) s on true
where s.x;

The other problem with this is that it breaks 17 test cases. We need
to modify them one by one to ensure that they still test what they are
supposed to, which is not a trivial task.

Before delving into these two problems, I'd like to know whether this
optimization is worthwhile, and whether I'm going in the right
direction.

[1] https://postgr.es/m/CAMbWs4-9dYrF44pkpkFrPoLXc_YH15DL8xT8J-oHggp_WvsLLA@mail.gmail.com

Thanks
Richard

Attachment Content-Type Size
v1-0001-Remove-no-op-PlaceHolderVars.patch application/octet-stream 7.2 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2024-09-03 03:31:20 Re: Remove no-op PlaceHolderVars
Previous Message Tender Wang 2024-09-03 02:16:44 Re: [BUG] Fix DETACH with FK pointing to a partitioned table fails