From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Justin Pryzby <pryzby@telsasoft.com>
Cc: Sanyo Moura <sanyo.moura@tatic.net>
Cc: jeff.janes@gmail.com, David Rowley <david.rowley@2ndquadrant.com>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Query with high planning time at version 11.1 compared versions 10.5 and 11.0
Date: Tue, 4 Dec 2018 18:43:31 -0300
Message-ID: <20181204214331.o7ljrfjja4jkkiau@alvherre.pgsql> (raw)
In-Reply-To: <20181128030129.GG30707@telsasoft.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: alvherre@2ndquadrant.com, pryzby@telsasoft.com, sanyo.moura@tatic.net, david.rowley@2ndquadrant.com, robertmhaas@gmail.com, 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
In-Reply-To: <20181204214331.o7ljrfjja4jkkiau@alvherre.pgsql>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox