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

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

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

Amit Langote-2
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

0001-Add-child-EC-members-for-only-the-non-dummy-children.patch (5K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

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

Amit Langote-2
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


Reply | Threaded
Open this post in threaded view
|

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

Amit Langote-2
In reply to this post by Amit Langote-2
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 like 1990 to miss the typo
maybe.)  I fixed the query to change '1999-05-07' to '1990-05-07' of the
first Append's parent relation and I get the following planning time with
the patch I posted above with 2 partitions selected under each Append as
expected.

 Planning Time: 536.947 ms
 Execution Time: 1.304 ms
(31 rows)

Even without changing 1999 to 1990, the planning time with the patch is:

 Planning Time: 4669.685 ms
 Execution Time: 110.506 ms
(1777 rows)

Thanks,
Amit


Reply | Threaded
Open this post in threaded view
|

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

Alvaro Herrera-9
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 Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Reply | Threaded
Open this post in threaded view
|

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

Ashutosh Bapat-2
In reply to this post by Amit Langote-2


On Thu, Dec 6, 2018 at 1:27 PM Alvaro Herrera <[hidden email]> wrote:
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.

As the comment says it has to do with the equivalence classes being used during merge append. EC's are used to create pathkeys used for sorting. Creating a sort node which has column on the nullable side of an OUTER join will fail if it doesn't find corresponding equivalence class. You may not notice this if both the partitions being joined are pruned for some reason. Amit's idea to make partition-wise join code do this may work, but will add a similar overhead esp. in N-way partition-wise join once those equivalence classes are added.

--
Best Wishes,
Ashutosh Bapat