[Info-ingres] Bad tuple estimate on left join

[Martin Bowes]

Hi All,

In 10.2.

I have a query:
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
WHERE c.active=1 AND c.comm_type=2

The part of the qep which handles the left join between comms and researcher looks like this:
                        /
                        left join
                        K Join(launch_id)
                        Sort on(tidp)
                        Pages 1 Tups 11
                        D203 C539
             /                      \
            Proj-rest               researcher_id_idx
            Heap                    I(r)
            Pages 4 Tups 745        B-Tree(researcher_id)
            D170 C525               Pages 36 Tups 6117
/
comms
(c)
B-Tree(NU)
Pages 680 Tups 52520

How can a left join have fewer result rows (11) than the left table (745)?

The fun part is that this only occurs if:

1.        the left join between comms and researcher has the ON cond1 AND cond2

2.       The query has multiple left joins.

If you remove either of those then the tuple estimate returns to 745. So it looks like the it has treated the second join condition as a row restriction just like it had been in the where clause of the query.

Martin Bowes
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.planetingres.org/pipermail/info-ingres/attachments/20180201/946fb1db/attachment.html>