[Info-ingres] Bad tuple estimate on left join
Martin Bowes
martin.bowes at ndph.ox.ac.uk
Thu Feb 1 14:00:00 UTC 2018
Hi Karl,
The Proj-rest node is indeed handling the WHERE clause.
If I simply count the number of rows in comms that satisfy WHERE c.active=1 AND c.comm_type=2 I get 745 as the QEP showed.
Nonetheless I ran the query without the where clause. This changed the plan to a right join and the tuple counts are still weird.
See below.
SELECT count(*)
FROM comms c
LEFT JOIN researcher r ON c.launch_id = r.researcher_id AND c.launch_type in (0,2)
LEFT JOIN user_details ud ON ud.user_id = r.user_id
Executing . . .
********************************************************************
QUERY PLAN 2,2, no timeout, of simple aggregate
aggregate expression -> count(*)
left join
K Join(user_id)
Heap
Pages 2 Tups 786
D161 C1279
/ \
right join user_details
Hash Join(researcher_id)(ud)
Sort on(user_id) B-Tree(user_id)
Pages 4 Tups 786 Pages 29 Tups 6200
D109 C1248
/ \
Proj-rest Proj-rest
Sorted(NU) Sorted(NU)
Pages 28 Tups 6117 Pages 221 Tups 52520
D33 C61 D76 C525
/ /
researcher comms_launch_indx
(r) I(c)
B-Tree(NU) B-Tree(NU)
Pages 33 Tups 6117 Pages 305 Tups 52520
I wonder if that Kjoin tuple estimate is including the where clause as well, since the where restriction has to be applied somewhere, and the cost tree (QEP) doesn't normally show a separate proj-rest above joins. (Maybe it should!) Do you still get 11 if you take the where clause off?
(although in this instance, the where restrictions can apply at the orig level, so maybe they're included at the proj-rest.)
Karl
_______________________________________________
Info-ingres mailing list
Info-ingres at lists.planetingres.org<mailto:Info-ingres at lists.planetingres.org>
http://lists.planetingres.org/mailman/listinfo/info-ingres
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.planetingres.org/pipermail/info-ingres/attachments/20180201/0add4527/attachment.html>
More information about the Info-ingres
mailing list