[Info-ingres] Bad tuple estimate on left join

[Martin Bowes]

Sorry hit the wrong button on that last mail...

If I reduce down to a single join condition the tuple estimates become sensible. Note that the right branch shows a consistent set of tuples all the way.

SELECT count(*)
FROM comms c
    LEFT JOIN researcher r ON c.launch_id = r.researcher_id AND c.launch_type in (0,2)
Executing . . .


********************************************************************

QUERY PLAN 7,2, no timeout, of simple aggregate
    aggregate expression -> count(*)


                        right join
                        Hash Join(launch_id)
                        Heap
                        Pages 79 Tups 52520
                        D85 C1232
             /                      \
            Proj-rest               Proj-rest
            Sorted(NU)              Sorted(NU)
            Pages 25 Tups 6117      Pages 221 Tups 52520
            D9 C61                  D76 C525
/                       /
researcher_id_idx       comms_launch_indx
I(r)                    I(c)
B-Tree(NU)              B-Tree(NU)
Pages 36 Tups 6117      Pages 305 Tups 52520


From: Martin Bowes [mailto:martin.bowes at ndph.ox.ac.uk]
Sent: 01 February 2018 14:00
To: Karl and Betty Schendel; info-ingres at lists.planetingres.org
Subject: Re: [Info-ingres] Bad tuple estimate on left join


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/f0f491ec/attachment.html>