Date: Tue, 4 Dec 2018 18:43:31 -0300
From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Justin Pryzby <pryzby@telsasoft.com>
Cc: Sanyo Moura <sanyo.moura@tatic.net>, jeff.janes@gmail.com,
	David Rowley <david.rowley@2ndquadrant.com>,
	Robert Haas <robertmhaas@gmail.com>,
	Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>,
	pgsql-performance@lists.postgresql.org
Subject: Re: Query with high planning time at version 11.1 compared versions
 10.5 and 11.0
Message-ID: <20181204214331.o7ljrfjja4jkkiau@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <20181128030129.GG30707@telsasoft.com>
User-Agent: NeoMutt/20180716
Precedence: bulk

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