[Info-ingres] query plan issues

[Martin Bowes]

Hi All,

I have a view:
create view mb_field_view AS
SELECT
        m.app_id AS app_id, m.basket_id AS basket_id, x.item_id AS field_id,
        p.available AS available, p.label_main AS label_main, p.title AS title,
        p.orda AS orda, x.filter_id
                ,m.res_ins_id -- add this
FROM basket_main m
    JOIN basket_item x ON m.basket_id = x.basket_id
    JOIN field_precis p ON x.item_id = p.field_id
WHERE x.item_type = 1
AND x.status = 0;

So pretty clearly stats on basket_item columns item_type and status look like a good idea. Even more so as the data is heavily skewed with these two conditions accounting for 99% of the table.
*** statistics for database bowtest version: S0001
*** table basket_item rows:1637249 pages:25609 overflow pages:0
*** column status of type integer (length:1, scale:0, not_nullable)
date:2018_06_08 13:31:08 GMT  unique values:1.000
repetition factor:1637249.000000 unique flag:N complete flag:0
domain:0 histogram cells:2 null count:0.000000       value length:1
cell:    0    count:0.000000          repf:0.000000          value:    -1
cell:    1    count:1.000000          repf:1637249.000000    value:     0

*** statistics for database bowtest version: S0001
*** table basket_item rows:1637249 pages:25609 overflow pages:0
*** column item_type of type integer (length:1, scale:0, not_nullable)
date:2018_06_08 13:11:35 GMT  unique values:3.000
repetition factor:545749.6875000 unique flag:N complete flag:0
domain:0 histogram cells:4 null count:0.000000       value length:1
cell:    0    count:0.000000          repf:0.000000          value:     0
cell:    1    count:0.999519          repf:1636462.000000    value:     1
cell:    2    count:0.000464          repf:760.000000        value:     2
cell:    3    count:0.000016          repf:27.000000         value:     3


So when I run a query:

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.planetingres.org/pipermail/info-ingres/attachments/20180608/3de8ce3e/attachment.html>