[Info-ingres] Bad tuple estimate on left join

[Martin Bowes]

I've raised a bug.

I've also seen this on Ingres 11 .0.0 (a64.lnx/100) + p15266.

The consequence of this query is massive lock escalation through my poor errlog and I suspect it may be associated with deadlocking on these tables, so I'll be keen to see a fix.

Marty

-----Original Message-----
From: Karl and Betty Schendel [mailto:schendel at kbcomputer.com] 
Sent: 01 February 2018 14:04
To: Martin Bowes
Subject: Re: [Info-ingres] Bad tuple estimate on left join

We've fought this sort of thing before.  I think it's a bug.  I'm pretty sure Doug put in some code once to hold OJ counts to the outer estimate (it can be higher, but not lower, unless there's a post-join restriction) but obviously it's not working all the time.

Karl

> On Feb 1, 2018, at 9:00 AM, Martin Bowes <martin.bowes at ndph.ox.ac.uk> wrote:
> 
> 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
> http://lists.planetingres.org/mailman/listinfo/info-ingres