[Info-ingres] Bad join estimate

[Martin Bowes]

Hi All,

In II 11.0.0 (a64.lnx/100) + p15255

I have a query which is causing lock escalation messages in my log because its estimate for a key join is way wrong.

The relevant part of the query plan reads:
                         /
                        K Join(basket_id,
                         item_type)
                        Sorted(basket_id)
                        Tups 1 Pages 1
                        D6 C2
             /                      \
            Proj-rest               basket_item
            Sorted(basket_id)       (x)
            Tups 1 Pages 1          B-Tree(basket_id,
            D3 C0                    item_type,
                                     item_id)
                                    Tups 1596419 Pages 24957
/
basket_main
(bm)
B-Tree(basket_id)
Tups 4067 Pages 91

So it says that it is getting a single row from basket_main (correct) and it expects a single row out of the K Join to basket_item...but in reality it gets 1686.

The stats for table basket_item are:
                                                                  Key  Avg Count
Column Name              Type               Length Nulls Defaults Seq  Per Value
basket_id                integer                 4   no      no     1      433.0
item_type                integer                 1   no      no     2   529435.0
item_id                  integer                 4   no      no     3      362.2
filter_id                integer                 4   no     yes           1737.9
status                   integer                 1   no     yes        1588305.0
res_upd_id               integer                 4   no      no
tupdate                  ingresdate                  no   value

So given a single basket_id from basket_main we should on average see 433 entries in basket_item.
The query has restrictions item_type = 1 AND status = 0. I suppose those restrictions have caused it to lower the join tuple estimate.

But we have stats on the fields involved and as can be seen above the item_type and status are not particularly restrictive settings in a table with 1596419 rows.

I've attempted to build composite statistics on the table with:
optimizedb -zcpk -rbasket_item -abasket_id -aitem_type -astatus

But the QEP still insists on a single row resulting from the K Join.

Anyone got any ideas?

Martin Bowes

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.planetingres.org/pipermail/info-ingres/attachments/20180511/2a56589e/attachment.html>