Re: Query with high planning time at version 11.1 compared versions 10.5 and 11.0
Hi,
On 2018/12/05 6:55, Alvaro Herrera wrote:
> On 2018-Dec-04, Alvaro Herrera wrote:
>
>> 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
>
> Actually, the primary keys are not needed; it's just as slow without
> them.
I ran the original unmodified query at [1] (the one that produces an empty
plan due to all children being pruned) against the server built with
patches I posted on the "speeding up planning with partitions" [2] thread
and it finished in a jiffy.
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, abs(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;
QUERY PLAN
───
Result (cost=0.00..0.00 rows=0 width=24)
One-Time Filter: false
(2 rows)
Time: 50.792 ms
That's because one of the things changed by one of the patches is that
child EC members are added only for the non-dummy children. In this case,
since all the children are pruned, there should be zero child EC members,
which is what would happen in PG 10 too. The partitionwise join related
changes in PG 11 moved the add_child_rel_equivalences call in
set_append_rel_size such that child EC members would be added even before
checking if the child rel is dummy, but for a reason named in the comment
above the call:
... Even if this child is
* deemed dummy, it may fall on nullable side in a child-join, which
* in turn may participate in a MergeAppend, where we will need the
* EquivalenceClass data structures.
However, I think we can skip adding the dummy child EC members here and
instead make it a responsibility of partitionwise join code in joinrels.c
to add the needed EC members. Attached a patch to show what I mean, which
passes the tests and gives this planning time:
QUERY PLAN
───
Result (cost=0.00..0.00 rows=0 width=24) (actual rows=0 loops=1)
One-Time Filter: false
Planning Time: 512.788 ms
Execution Time: 0.162 ms
which is not as low as with the patches at [2] for obvious reasons, but as
low as we can hope to get with PG 11. Sadly, planning time is less with
PG 10.6:
QUERY PLAN
───
Result (cost=0.00..0.00 rows=0 width=24) (actual rows=0 loops=1)
One-Time Filter: false
Planning time: 254.533 ms
Execution time: 0.080 ms
(4 rows)
But I haven't looked closely at what else in PG 11 makes the planning time
twice that of 10.
> I noticed another interesting thing, which is that if I modify the query
> to actually reference some partition that I do have (as opposed to the
> above, which just takes 30s to prune everything) the plan is mighty
> curious ... if only because in one of the Append nodes, partitions have
> not been pruned as they should.
>
> So, at least two bugs here,
> 1. the equivalence-class related slowness,
> 2. the lack of pruning
I haven't reproduced 2 yet. Can you share the modified query?
Thanks,
Amit
[1]
https://www.postgresql.org/message-id/20181128004402.GC30707%40telsasoft.com
From 67d051526138b00b7429996655a403535ea6d75c Mon Sep 17 00:00:00 2001
From: amit
Date: Thu, 6 Dec 2018 10:38:25 +0900
Subject: [PATCH] Add child EC members for only the non-dummy children
In set_append_rel_size, child EC members are added before checking
if the child relation itself is dummy. That's very inefficient if
there are lots of children as the EC lists grow huge. Such EC
members would remain unused unless the child participates in
partitionwise join, so add them only if the child "actually"
participates in partitionwise join.
---
src/backend/optimizer/path/allpaths.c | 27 ---
src/backend/optimizer/path/joinrels.c | 19 +++
2 files changed, 31 insertions(+), 15 deletions(-)
diff --git
Re: Query with high planning time at version 11.1 compared versions 10.5 and 11.0
On 2018/12/06 11:14, Amit Langote wrote: > I ran the original unmodified query at [1] (the one that produces an empty > plan due to all children being pruned) against the server built with > patches I posted on the "speeding up planning with partitions" [2] thread > and it finished in a jiffy. Forgot to add the link for [2]: https://commitfest.postgresql.org/21/1778/ Thanks, Amit
Re: Query with high planning time at version 11.1 compared versions 10.5 and 11.0
Hi, On 2018/12/05 6:55, Alvaro Herrera wrote: > I noticed another interesting thing, which is that if I modify the query > to actually reference some partition that I do have (as opposed to the > above, which just takes 30s to prune everything) the plan is mighty > curious ... if only because in one of the Append nodes, partitions have > not been pruned as they should. > > So, at least two bugs here, > 1. the equivalence-class related slowness, > 2. the lack of pruning > > > QUERY PLAN > > ─ > Hash Join (cost=1159.13..25423.65 rows=1 width=24) >Hash Cond: (abs((p.plusalesprice - p_875.plusalesprice)) = > (max(abs((p_877.plusalesprice - p_879.plusalesprice) >-> Nested Loop (cost=1000.00..25264.52 rows=1 width=20) > Join Filter: ((p.loccd = p_875.loccd) AND (p.fecha = p_875.fecha)) > -> Gather (cost=1000.00..25154.38 rows=875 width=16) >Workers Planned: 2 >-> Parallel Append (cost=0.00..24066.88 rows=875 width=16) > -> Parallel Seq Scan on precio_125 p (cost=0.00..27.50 > rows=1 width=16) >Filter: ((fecha >= '1990-05-06 > 00:00:00'::timestamp without time zone) AND (fecha <= '1999-05-07 > 00:00:00'::timestamp without time zone) AND (pluid = 2)) [ Parallel SeqScan on precio_126 to precio_998 ] > -> Parallel Seq Scan on precio_999 p_874 > (cost=0.00..27.50 rows=1 width=16) >Filter: ((fecha >= '1990-05-06 > 00:00:00'::timestamp without time zone) AND (fecha <= '1999-05-07 > 00:00:00'::timestamp without time zone) AND (pluid = 2)) As you can see from the "Filter: " property above, the baserestrictinfo of this Append's parent relation is: BETWEEN '1990-05-06' AND '1999-05-07' which selects partitions for all days from '1990-05-06' (precio_125) up to '1992-09-26' (precio_999). > -> Materialize (cost=0.00..79.52 rows=2 width=16) >-> Append (cost=0.00..79.51 rows=2 width=16) > -> Seq Scan on precio_125 p_875 (cost=0.00..39.75 > rows=1 width=16) >Filter: ((fecha >= '1990-05-06 > 00:00:00'::timestamp without time zone) AND (fecha <= '1990-05-07 > 00:00:00'::timestamp without time zone) AND (pluid = 2)) > -> Seq Scan on precio_126 p_876 (cost=0.00..39.75 > rows=1 width=16) >Filter: ((fecha >= '1990-05-06 > 00:00:00'::timestamp without time zone) AND (fecha <= '1990-05-07 > 00:00:00'::timestamp without time zone) AND (pluid = 2)) Whereas for this Append, it is BETWEEN '1990-05-06' AND '1990-05-07'. >-> Hash (cost=159.12..159.12 rows=1 width=4) > -> Aggregate (cost=159.10..159.11 rows=1 width=4) >-> Nested Loop (cost=0.00..159.10 rows=1 width=8) > Join Filter: ((p_877.loccd = p_879.loccd) AND > (p_877.fecha = p_879.fecha)) > -> Append (cost=0.00..79.51 rows=2 width=16) >-> Seq Scan on precio_125 p_877 > (cost=0.00..39.75 rows=1 width=16) > Filter: ((fecha >= '1990-05-06 > 00:00:00'::timestamp without time zone) AND (fecha <= '1990-05-07 > 00:00:00'::timestamp without time zone) AND (pluid = 2)) >-> Seq Scan on precio_126 p_878 > (cost=0.00..39.75 rows=1 width=16) > Filter: ((fecha >= '1990-05-06 > 00:00:00'::timestamp without time zone) AND (fecha <= '1990-05-07 > 00:00:00'::timestamp without time zone) AND (pluid = 2)) > -> Materialize (cost=0.00..79.52 rows=2 width=16) >-> Append (cost=0.00..79.51 rows=2 width=16) > -> Seq Scan on precio_125 p_879 > (cost=0.00..39.75 rows=1 width=16) >Filter: ((fecha >= '1990-05-06 > 00:00:00'::timestamp without time zone) AND (fecha <= '1990-05-07 > 00:00:00'::timestamp without time zone) AND (pluid = 2)) > -> Seq Scan on precio_126 p_880 > (cost=0.00..39.75 rows=1 width=16) >Filter: ((fecha >= '1990-05-06 > 00:00:00'::timestamp without time zone) AND (fecha <= '1990-05-07 > 00:00:00'::timestamp without time zone) AND (pluid = 2)) And also for these two Appends. So, I don't think there's anything funny going on with pruning here, maybe just a typo in the query (1999 looks very much like 1990 to miss the typo maybe.) I fixed the query to change '1999-05-07' to '1990-05-07' of th
Re: Query with high planning time at version 11.1 compared versions 10.5 and 11.0
Hi, (Re-sending after adding -hackers, sorry for the noise to those who would receive this twice) On 2018/12/05 6:55, Alvaro Herrera wrote: > I noticed another interesting thing, which is that if I modify the query > to actually reference some partition that I do have (as opposed to the > above, which just takes 30s to prune everything) the plan is mighty > curious ... if only because in one of the Append nodes, partitions have > not been pruned as they should. > > So, at least two bugs here, > 1. the equivalence-class related slowness, > 2. the lack of pruning > > > QUERY PLAN > > ─ > Hash Join (cost=1159.13..25423.65 rows=1 width=24) >Hash Cond: (abs((p.plusalesprice - p_875.plusalesprice)) = > (max(abs((p_877.plusalesprice - p_879.plusalesprice) >-> Nested Loop (cost=1000.00..25264.52 rows=1 width=20) > Join Filter: ((p.loccd = p_875.loccd) AND (p.fecha = p_875.fecha)) > -> Gather (cost=1000.00..25154.38 rows=875 width=16) >Workers Planned: 2 >-> Parallel Append (cost=0.00..24066.88 rows=875 width=16) > -> Parallel Seq Scan on precio_125 p (cost=0.00..27.50 > rows=1 width=16) >Filter: ((fecha >= '1990-05-06 > 00:00:00'::timestamp without time zone) AND (fecha <= '1999-05-07 > 00:00:00'::timestamp without time zone) AND (pluid = 2)) [ Parallel SeqScan on precio_126 to precio_998 ] > -> Parallel Seq Scan on precio_999 p_874 > (cost=0.00..27.50 rows=1 width=16) >Filter: ((fecha >= '1990-05-06 > 00:00:00'::timestamp without time zone) AND (fecha <= '1999-05-07 > 00:00:00'::timestamp without time zone) AND (pluid = 2)) As you can see from the "Filter: " property above, the baserestrictinfo of this Append's parent relation is: BETWEEN '1990-05-06' AND '1999-05-07' which selects partitions for all days from '1990-05-06' (precio_125) up to '1992-09-26' (precio_999). > -> Materialize (cost=0.00..79.52 rows=2 width=16) >-> Append (cost=0.00..79.51 rows=2 width=16) > -> Seq Scan on precio_125 p_875 (cost=0.00..39.75 > rows=1 width=16) >Filter: ((fecha >= '1990-05-06 > 00:00:00'::timestamp without time zone) AND (fecha <= '1990-05-07 > 00:00:00'::timestamp without time zone) AND (pluid = 2)) > -> Seq Scan on precio_126 p_876 (cost=0.00..39.75 > rows=1 width=16) >Filter: ((fecha >= '1990-05-06 > 00:00:00'::timestamp without time zone) AND (fecha <= '1990-05-07 > 00:00:00'::timestamp without time zone) AND (pluid = 2)) Whereas for this Append, it is BETWEEN '1990-05-06' AND '1990-05-07'. >-> Hash (cost=159.12..159.12 rows=1 width=4) > -> Aggregate (cost=159.10..159.11 rows=1 width=4) >-> Nested Loop (cost=0.00..159.10 rows=1 width=8) > Join Filter: ((p_877.loccd = p_879.loccd) AND > (p_877.fecha = p_879.fecha)) > -> Append (cost=0.00..79.51 rows=2 width=16) >-> Seq Scan on precio_125 p_877 > (cost=0.00..39.75 rows=1 width=16) > Filter: ((fecha >= '1990-05-06 > 00:00:00'::timestamp without time zone) AND (fecha <= '1990-05-07 > 00:00:00'::timestamp without time zone) AND (pluid = 2)) >-> Seq Scan on precio_126 p_878 > (cost=0.00..39.75 rows=1 width=16) > Filter: ((fecha >= '1990-05-06 > 00:00:00'::timestamp without time zone) AND (fecha <= '1990-05-07 > 00:00:00'::timestamp without time zone) AND (pluid = 2)) > -> Materialize (cost=0.00..79.52 rows=2 width=16) >-> Append (cost=0.00..79.51 rows=2 width=16) > -> Seq Scan on precio_125 p_879 > (cost=0.00..39.75 rows=1 width=16) >Filter: ((fecha >= '1990-05-06 > 00:00:00'::timestamp without time zone) AND (fecha <= '1990-05-07 > 00:00:00'::timestamp without time zone) AND (pluid = 2)) > -> Seq Scan on precio_126 p_880 > (cost=0.00..39.75 rows=1 width=16) >Filter: ((fecha >= '1990-05-06 > 00:00:00'::timestamp without time zone) AND (fecha <= '1990-05-07 > 00:00:00'::timestamp without time zone) AND (pluid = 2)) And also for these two Appends. So, I don't think there's anything funny going on with pruning here, maybe just a typo in the query (1999 looks very much lik
Re: Query with high planning time at version 11.1 compared versions 10.5 and 11.0
On 2018-Dec-06, Amit Langote wrote: Hi > [ Parallel SeqScan on precio_126 to precio_998 ] > > > -> Parallel Seq Scan on precio_999 p_874 > > (cost=0.00..27.50 rows=1 width=16) > >Filter: ((fecha >= '1990-05-06 > > 00:00:00'::timestamp without time zone) AND (fecha <= '1999-05-07 > > 00:00:00'::timestamp without time zone) AND (pluid = 2)) > > As you can see from the "Filter: " property above, the baserestrictinfo of > this Append's parent relation is: > > BETWEEN '1990-05-06' AND '1999-05-07' > > which selects partitions for all days from '1990-05-06' (precio_125) up to > '1992-09-26' (precio_999). Looking at my .psql_history, you're right -- I typoed 1990 as 1999 in one of the clauses. Thanks, mystery solved :-) -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: Query with high planning time at version 11.1 compared versions 10.5 and 11.0
On 2018-Dec-06, Amit Langote wrote: > The partitionwise join related > changes in PG 11 moved the add_child_rel_equivalences call in > set_append_rel_size such that child EC members would be added even before > checking if the child rel is dummy, but for a reason named in the comment > above the call: > >... Even if this child is > * deemed dummy, it may fall on nullable side in a child-join, which > * in turn may participate in a MergeAppend, where we will need the > * EquivalenceClass data structures. > > However, I think we can skip adding the dummy child EC members here and > instead make it a responsibility of partitionwise join code in joinrels.c > to add the needed EC members. Attached a patch to show what I mean, which > passes the tests and gives this planning time: Robert, Ashutosh, any comments on this? I'm unfamiliar with the partitionwise join code. -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
