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

[Martin Bowes]

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