Re: Query with high planning time at version 11.1 compared versions 10.5 and 11.0

[Alvaro Herrera <alvherre@xxxxxxxxxxxxxxx>]

So, the slowness in this test seems to come from
add_child_rel_equivalences() and bms_overlap() therein, according to
perf (mine and Justin's) ...  apparently we end up with a lot of
equivalence class members.  I added a debugging block to spit out the
number of ECs as well as the number of members in each (after creating
table "precio" and about a thousand partitions), and I got progressively
slower lines the last of which says
WARNING:  4 classes: 2000, 1999, 1999, 999001, 

so for some reason we produced quadratic number of EC members, and we
bms_overlap all that stuff over and over a number of times.

This code seems to come from partitionwise join.

Now, the query is a bit silly; it puts table "precio" four times in the
range table. (thanks http://sqlformat.darold.net/)

CREATE TABLE precio(fecha timestamp, pluid int, loccd int, plusalesprice int) PARTITION BY RANGE (fecha); 
SELECT format('CREATE TABLE public.precio_%s PARTITION OF public.precio (PRIMARY KEY (fecha, pluid, loccd) ) FOR VALUES FROM (''%s'')TO(''%s'')', i, a, b) FROM (SELECT '1990-01-01'::timestam p+(i||'days')::interval a, '1990-01-02'::timestamp+(i||'days')::interval b, i FROM generate_series(1,999) i)x \gexec

EXPLAIN SELECT
    l_variacao.fecha,
    l_variacao.loccd,
    l_variacao.pant,
    l_variacao.patual,
    max_variacao.var_max
FROM (
    SELECT
        p.fecha,
        p.loccd,
        p.plusalesprice patual,
        da.plusalesprice pant,
        a bs (p.plusalesprice - da.plusalesprice) AS var
    FROM
        precio p,
        (
            SELECT
                p.fecha,
                p.plusalesprice,
                p.loccd
            FROM
                precio p
            WHERE
                p.fecha BETWEEN '2017-03-01' AND '2017-03-02'
                AND p.pluid = 2) da
        WHERE
            p.fecha BETWEEN '2017-03-01' AND '2017-03-02'
            AND p.pluid = 2
            AND p.loccd = da.loccd
            AND p.fecha = da.fecha) l_variacao, (
        SELECT
            max(abs(p.plusalesprice - da.plusalesprice)) AS var_max
        FROM
            precio p, (
                SELECT
                    p.fecha, p.plusalesprice, p.loccd
                FROM
                    precio p
                WHERE
                    p.fecha BETWEEN '2017-03-01' AND '2017-03-02'
                    AND p.pluid = 2) da
            WHERE
                p.fecha BETWEEN '2017-03-01'
                AND '2017-03-02'
                AND p.pluid = 2
                AND p.loccd = da.loccd
                AND p.fecha = da.fecha) max_variacao
WHERE
    max_variacao.var_max = l_variacao.var;

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services