Dear all,
I got the following problem for which I could not find a solution by searching the archives:
I have Tables Ta, Tb, Tc with primary keys as bigserials.
Ta references Tb references Tc.
Not all but most rows in Ta reference exactly one row in Tb.
Above the Tables I have views Va, Vb, Vc that gather some other information in addition to the Tables Ta,Tb,Tc.
Va outputs columns of Vb outputs columns of Vc.
also, some other nested selects form columns of Va, Vb, Vc.
my problem is the join in Va:
It basically says
SELECT Ta.PK, Ta.FK-Tb, Ta.x…, Ta.y, Vb.x, Vb.y, …
FROM Ta, Vb
WHERE Vb.TbPK = Ta.TbFK
AND <somerestrictionOnTa>
Even if I select exactly one row of Ta which also results in exacly one row of Vb (and then Vc),
the planner seems to always create all possible lines (of a subselect) in Vb and afterwards drop all
lines but one when performing the join.
When I replace Va's join of Ta and Vb by directly placing Vb's SELECT in Va
the planner finds that it needs only one row of the former Vb (which is then incorporated in Va).
Also, when I replace the above join by subselects resulting in a
SELECT Ta.PK-id, Ta.FK-Tb, Ta.x…,
(SELECT Vb.x FROM Vb WHERE Vb.TbPK = Ta.TbFK) AS x,
(SELECT Vb.y FROM Vb WHERE Vb.TbPK = Ta.TbFK) AS y
WHERE e.g Ta.PK-id = <singlevalue>
the planner is - for each subselect - able to perform Vb's operations on only the one row
that matches the FK of Ta.
That the planner repeats this for each of the above subselects of Vb is a
different story which I don't understand either.
My question is:
Is there any way to convince the planner that it makes sense for
the Vb joined with Ta into Va to first select one row of Tb and then perform the rest of Vb on this one row?
(And why is the plan for the regular join form differing from the subselects?)
Here is the explain,analyze of Va's SELECT using four fast subselects on Vb:
https://explain.depesz.com/s/2tp
Here is it for the original Va's SELECT with the slow join of Ta, Vb:
https://explain.depesz.com/s/oKS
BTW: That some expressions in Vb are slow and inefficient is understood and can be corrected by me.
That's what made this problem visible but to my understanding this should not matter for the question.
Addition information:
"PostgreSQL 9.6.1, compiled by Visual C++ build 1800, 64-bit"
The same problem existed on 9.3 before. I updated to 9.6.1 to see if it gets better which it did not.
Hardware:
VM running Windows 10 or WS2012R2 on WS2012R2 HyperV running on Xeon E5-2600. SSD buffered HDDs.
I have the impression that this problem was at least invisible on 8.1 which I used before 9.3.
Any insight is welcome.
Let me know if more information is needed to analyze the question.
Regards,
Titus