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

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