On 07/04/2013 06:11 PM, Antonin Houska wrote:
On 07/03/2013 08:32 PM, Tom Lane wrote:
Another possibility would be to keep the optimization, but disable it in
queries that use LATERAL. I don't much care for that though --- seems
too Rube Goldbergish, and in any case I have a lot less faith in the
whole concept now than I had before I started digging into this issue.
Thoughts?
I noticed EXPLAIN in some regression tests. So if they all pass after
removal of this optimization, it might indicate that it was really
insignificant. But alternatively it may just be a lack of focus on
this feature in the test queries. Digging for (non-LATERAL) queries or
rather patterns where the ph_may_need optimization clearly appears to
be important sounds to me like a good SQL exercise, but I'm afraid I
won't have time for it in the next few days.
I constructed a query that triggers the optimization - see attachment
with comments. (Note that the relid sets are derived from my current
knowledge of the logic. I haven't figured out how to check them easily
in gdb session.)
The intention was that the top-level OJ references LHS of the join below
rather than the RHS. That should increase the likelihood that the PHV
becomes the only obstacle for join commuting. And therefore the
ph_may_need optimization should unblock some combinations that would be
impossible otherwise.
However I could not see the condition
if (bms_is_subset(phinfo->ph_may_need, min_righthand))
continue;
met for the top-level join even though the supposed ph_may_need did not
contain tab1. Then it struck me that min_righthand can be the problem.
So I changed the join clause to reference RHS of j1, hoping that it
should make min_righthand bigger. And that really triggered the condition.
EXPLAIN shows the same plan with or without the ph_may_need
optimization, but that might be data problem (my tables are empty).
More important is the fact that I could only avoid addition of the PHV's
eval_at to min_righthand at the cost of adding the whole j1 join (i.e.
more than just eval_at).
Although the idea behind ph_may_need is clever, I can now imagine that
other techniques of the planner can substitute for it. There might be
examples showing the opposite but such are beyond my imagination.
// Antonin Houska (Tony)
SELECT tab1.i
FROM tab1
-- The ph_may_need optimization should be effective for the
-- top-level LEFT JOIN. The PHV in sub1 is only referenced below it.
LEFT JOIN
( tab2
LEFT JOIN
( tab3
LEFT JOIN
( SELECT
-- This expression should be wrapped in the PHV
-- That PHV should have eval_at = {tab4, tab5}.
--
-- Join clause of j1 is the highest reference to the PHV.
-- Thus ph_may_need should be {tab2, tab3, tab4, tab5}. Therefore
-- the ph_may_need optimization should avoid addition of eval_at
-- to min_righthand of the top-level join's SpecialJoinInfo.
COALESCE(tab4.l, tab5.m, 1) AS x
FROM tab4
LEFT JOIN
tab5
ON l = m
) AS sub1(x)
ON tab3.k = sub1.x
) AS j2(k, x)
ON tab2.j = j2.x
) AS j1(j, k)
-- This clause references j.k (RHS of the lower join) to keep min_righthand
-- of the top-level join bigger (ph_may_need needs to be its subset).
ON tab1.i = j1.k;
CREATE TABLE tab1(i int);
CREATE TABLE tab2(j int);
CREATE TABLE tab3(k int);
CREATE TABLE tab4(l int);
CREATE TABLE tab5(m int);
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
