[Info-ingres] Bad tuple estimate on left join

[Karl and Betty Schendel]

> On Feb 1, 2018, at 8:31 AM, Martin Bowes <martin.bowes at ndph.ox.ac.uk> wrote:
> 
> 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)?

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