[Info-ingres] Adding a constraint constrained my row estimate

[Adrian Williamson]

Hi Marty,

 

We found an issue with constraints and QEP some time ago.

 

Defining constraints seemed to destroy the performance of selected selects.

 

They tracked it down to fault in the greedy optimizer.

Bug 132120 (GENERIC)

Greedy enumeration omits useful indexes for query with outer join. 

You don't seem to have the same thing but it does smell similar - if you
switch of greedy optimisation do you still get it?

 

Cheers

 

Adrian

 

 

From: info-ingres-bounces at lists.planetingres.org
<info-ingres-bounces at lists.planetingres.org> On Behalf Of Martin Bowes
Sent: 12 March 2018 11:44
To: info-ingres at lists.planetingres.org
Subject: [Info-ingres] Adding a constraint constrained my row estimate

 

Hi All,

 

On Ingres 11. 0.0 (a64.lnx/100) + p15255

 

Here is the QEP with no constraint between the tables.

QUERY PLAN 3,2, no timeout, of simple aggregate  

    aggregate expression -> count(*) 

 

                        K Join(basket_id,

                         item_type)

                        Tups 641 Pages 1

                        D9 C2

             /                      \

            Proj-rest               basket_item

            Sorted(basket_id)       (x)

            Tups 2 Pages 1          B-Tree(basket_id,

            D4 C0                    item_type)

                                    Tups 1454547 Pages 22752

/

basket_main

(m)

B-Tree(basket_id)

Tups 3596 Pages 18

 

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

 

--------------┐

│col1         │

+-------------+

 

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

 

          ----------

          | 0|

          KJOIN     

          at      330

          et      641 <--A little over, but ball park.

                    

          ad        6

          ed        9

                    

          ac        0

          ec      225

                    

          et        0

          ----------

/

 

And after adding a constraint for basket_item(basket_id) to refer to
basket_main(basket_id)

 

QUERY PLAN 3,2, no timeout, of simple aggregate  

    aggregate expression -> count(*) 

 

                        K Join(basket_id,

                         item_type)

                        Tups 1 Pages 1

                        D8 C2

             /                      \

            Proj-rest               basket_item

            Sorted(basket_id)       (x)

            Tups 2 Pages 1          B-Tree(basket_id,

            D4 C0                    item_type)

                                    Tups 1454547 Pages 22752

/

basket_main

(m)

B-Tree(basket_id)

Tups 3596 Pages 18

 

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

 

--------------┐

│col1         │

+-------------+

 

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

 

          ----------

          | 0|

          KJOIN     

          at      330

          et        1 <-- What the hell just happened?

                    

          ad        6

          ed        8

                    

          ac        0

          ec      154

                    

          et        0

          ----------

/

 

So why has a constraint altered my row estimate?

 

I tried this on 10.2 and the et stayed at the original estimate of 641.

 

Marty

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.planetingres.org/pipermail/info-ingres/attachments/20180312/237c0f74/attachment.html>