public inbox for [email protected]
help / color / mirror / Atom feedFrom: Richard Guo <[email protected]>
To: Tom Lane <[email protected]>
Cc: David Rowley <[email protected]>
Cc: nikhil raj <[email protected]>
Cc: [email protected]
Subject: Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables.
Date: Wed, 28 Aug 2024 15:08:16 +0800
Message-ID: <CAMbWs4-9dYrF44pkpkFrPoLXc_YH15DL8xT8J-oHggp_WvsLLA@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CALNXOfqDr5jVRZHThyXyNftDuezjxOJB1mqw=4x1T8YBibi6uw@mail.gmail.com>
<CAG1ps1w61XD1LxUf3EBCZ=VR3S_orRj_dk6cxmO5KusBVyrgMg@mail.gmail.com>
<CAAvG1pChk3W9MWkK-_JPyUdThSoJhv4MueH5i8cZS2T_HUP5dA@mail.gmail.com>
<CAG1ps1xvnTZceKK24OUfMKLPvDP2vjT-d+F2AOCWbw_v3KeEgg@mail.gmail.com>
<[email protected]>
<CAApHDvpRxyw1G2HVkA=PM5Uqh59moOFqPXyMTsEDUadKo2fQTA@mail.gmail.com>
<[email protected]>
<CAApHDvoQRoKHNfTaw+Sn=bZGKVmn8W-5yBPGP7+98mWJz5A9uQ@mail.gmail.com>
<[email protected]>
On Wed, Aug 28, 2024 at 12:15 AM Tom Lane <[email protected]> wrote:
> If we
> are willing to accept a HEAD-only fix, it'd likely be better to
> attack the other end and make it possible to remove no-op PHVs.
> I think that'd require marking PHVs that need to be kept because
> they are serving to isolate subexpressions.
I think it's always desirable to remove no-op PHVs, even if we end up
with a different approach to fix the issue discussed here. Doing that
could potentially open up opportunities for optimization in other
cases. For example:
explain (costs off)
select * from t t1 left join
lateral (select t1.a as x, * from t t2) s on true
where t1.a = s.a;
QUERY PLAN
----------------------------
Nested Loop
-> Seq Scan on t t1
-> Seq Scan on t t2
Filter: (t1.a = a)
(4 rows)
The target entry s.x is wrapped in a PHV that contains lateral
reference to t1, which forces us to resort to nestloop join. However,
since the left join has been reduced to an inner join, and it is
removed from the PHV's nullingrels, leaving the nullingrels being
empty, we should be able to remove this PHV and use merge or hash
joins, depending on which is cheaper.
I think there may be more cases where no-op PHVs constrain
optimization opportunities.
In [1] when working on the fix-grouping-sets patch, I included a
mechanism in 0003 to remove no-op PHVs by including a flag in
PlaceHolderVar to indicate whether it is safe to remove the PHV when
its phnullingrels becomes empty. In that patch this flag is only set
in cases where the PHV is used to carry the nullingrel bit that
represents the grouping step. Maybe we can extend its use to remove
all no-op PHVs, except those that are serving to isolate
subexpressions.
Any thoughts on this?
[1] https://postgr.es/m/CAMbWs4_2t2pqqCFdS3NYJLwMMkAzYQKBOhKweFt-wE3YOi7rGg@mail.gmail.com
Thanks
Richard
view thread (22+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables.
In-Reply-To: <CAMbWs4-9dYrF44pkpkFrPoLXc_YH15DL8xT8J-oHggp_WvsLLA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox