Обсуждение: Planning time of Generic plan for a table partitioned into a lot

Hi,
I found that making a generic plan of SELECT/UPDATE/DELETE for a table partitioned into thousands is slow.
Especially, UPDATE/DELETE statement is too slow.

I'm afraid that I could not come up with a good idea, but how can I shorten the creation time of a generic plan?

The results are as follows.

*setup*

postgres=# create table t(id int) partition by range(id);
CREATE TABLE
postgres=# \o /dev/null
postgres=# select 'create table t_' || x || ' partition of t for values from (' || x || ') to (' || x+1 || ')'from
generate_series(1,8192) x;
 
postgres=# \gexec
postgres-# analyze;
ANALYZE

*explain analyze results*

postgres=# set plan_cache_mode = force_generic_plan;
SET
postgres=# prepare select_stmt(int) as select * from t where id = $1;
PREPARE
postgres=# explain analyze execute select_stmt(8192);
                                               QUERY PLAN                                               
--------------------------------------------------------------------------------------------------------
 Append  (cost=0.00..343572.48 rows=106496 width=4) (actual time=0.015..0.015 rows=0 loops=1)
   Subplans Removed: 8191
   ->  Seq Scan on t_8192  (cost=0.00..41.88 rows=13 width=4) (actual time=0.013..0.013 rows=0 loops=1)
         Filter: (id = $1)
 Planning Time: 206.415 ms
 Execution Time: 0.742 ms
(6 rows)

postgres=# prepare update_stmt(int) as update t set id = id + 1 where id = $1;
PREPARE
postgres=# explain analyze execute update_stmt(8192);
                                               QUERY PLAN
---------------------------------------------------------------------------------------------------------
 Update on t  (cost=0.00..343306.24 rows=106496 width=10) (actual time=39.502..39.503 rows=0 loops=1)
   Update on t_1
   Update on t_2
   ...
   ->  Seq Scan on t_1  (cost=0.00..41.91 rows=13 width=10) (actual time=0.025..0.026 rows=0 loops=1)
         Filter: (id = $1)
   ->  Seq Scan on t_2  (cost=0.00..41.91 rows=13 width=10) (actual time=0.004..0.005 rows=0 loops=1)
         Filter: (id = $1)
   ...
 Planning Time: 14357.504 ms
 Execution Time: 397.652 ms
(24579 rows)

postgres=# prepare delete_stmt(int) as delete from t where id = $1;
PREPARE
postgres=# explain analyze execute delete_stmt(8192);
                                               QUERY PLAN
--------------------------------------------------------------------------------------------------------
 Delete on t  (cost=0.00..343040.00 rows=106496 width=6) (actual time=51.628..51.628 rows=0 loops=1)
   Delete on t_1
   Delete on t_2
   ...
   ->  Seq Scan on t_1  (cost=0.00..41.88 rows=13 width=6) (actual time=0.025..0.026 rows=0 loops=1)
         Filter: (id = $1)
   ->  Seq Scan on t_2  (cost=0.00..41.88 rows=13 width=6) (actual time=0.014..0.015 rows=0 loops=1)
         Filter: (id = $1)
   ...
 Planning Time: 14225.908 ms
 Execution Time: 405.605 ms
(24579 rows)

Of course, in case of plan_cache_mode = force_custom_plan, it is not problem because unnecessary paths are pruned by
speedingup planning with partitions patch[1].
 

However, if plan_cachemode is force_generic_plan, generic plan is made at the first execution of prepared statement.
If plan_cache_mode is auto(default), generic plan is made at the sixth execution.
So, with default setting, performance get lower at the sixth execution.
Even if you do not improve creation of generic plan, if the number of partition is large, it is better to recommend
force_custom_plan.
Thoughts?

[1]: https://www.postgresql.org/message-id/9d7c5112-cb99-6a47-d3be-cf1ee6862a1d@lab.ntt.co.jp

Regards,
Sho Kato

On Tue, 27 Nov 2018 at 23:05, Kato, Sho <kato-sho@jp.fujitsu.com> wrote:
> I found that making a generic plan of SELECT/UPDATE/DELETE for a table partitioned into thousands is slow.
> Especially, UPDATE/DELETE statement is too slow.

It's quite well known and also documented [1] that this is slow. The
manual reads:

"Currently, pruning of partitions during the planning of an UPDATE or
DELETE command is implemented using the constraint exclusion method
(however, it is controlled by the enable_partition_pruning rather than
constraint_exclusion) — see the following section for details and
caveats that apply."

and later on the same page:

"All constraints on all children of the parent table are examined
during constraint exclusion, so large numbers of children are likely
to increase query planning time considerably. So the legacy
inheritance based partitioning will work well with up to perhaps a
hundred child tables; don't try to use many thousands of children."

That documentation should be getting adjusted by [2] as that patch
aims to improve the performance of UPDATE/DELETE planning, and also
improve planning performance for when partitions are pruned. Although
I'm not sure this will do much for you SELECT case, since you're not
pruning any partitions during planning.  There's been a discussion in
[3] about improving the performance of determining the relation's
size, which is known to be quite a bottleneck when generating a plan
which includes a partitioned table with a large number of partitions.

> I'm afraid that I could not come up with a good idea, but how can I shorten the creation time of a generic plan?

Since you mentioned the plan_cache_mode GUC, then I assume you're not
talking about any version of PostgreSQL that's been released, so if
you're looking for a way to make it faster in master then I'd suggest
helping with the review of [2]. If that patch does not meet your needs
then also help Thomas with [3]. If that's still not good enough then
you might need to do some research yourself. perf is your friend
there.

[1] https://www.postgresql.org/docs/devel/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE
[2] https://commitfest.postgresql.org/20/1778/
[3]
https://www.postgresql.org/message-id/flat/CAMyN-kCPin_stCMoXCVCq5J557e9-WEFPZTqdpO3j8wzoNVwNQ%40mail.gmail.com#e085c43b597b2775326afd9f3a2b6591

--
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Hi Kato-san,

On 2018/11/27 19:05, Kato, Sho wrote:
> Of course, in case of plan_cache_mode = force_custom_plan, it is not problem because unnecessary paths are pruned by
speedingup planning with partitions patch[1].
 
> 
> However, if plan_cachemode is force_generic_plan, generic plan is made at the first execution of prepared statement.
> If plan_cache_mode is auto(default), generic plan is made at the sixth execution.
> So, with default setting, performance get lower at the sixth execution.

Keeping aside the fact that making a generic plan gets increasing more
expensive as the number of partitions increases, I'm a bit surprised that
you get a generic plan with plan_cache_mode = auto.  Isn't a generic plan
way too expensive in this case?

When I try your example, I always get a custom plan, because its cost is
pretty low and obviously so because it will contain only 1 partition and
even adding the cost of planning doesn't make it grow beyond a generic
plan's cost which contains 8192 partitions.  The following formula is used
to calculate the planning cost:

planning time = 1000.0 * cpu_operator_cost * (nrelations + 1)

where nrelations is the number of relations in the range table.

Here's what I get with various settings of plan caching.

--- force generic plan to see its cost

set plan_cache_mode = 'force_generic_plan';
set max_parallel_workers_per_gather = 0;
explain (timing off, analyze) execute select_stmt(8192);
                                      QUERY PLAN

──────────────────────────────────────
 Append  (cost=0.00..343572.48 rows=106496 width=4) (actual rows=0 loops=1)
   Subplans Removed: 8191
   ->  Seq Scan on t_8192  (cost=0.00..41.88 rows=13 width=4) (actual
rows=0 loops=1)
         Filter: (id = $1)
 Planning Time: 1217.543 ms
 Execution Time: 1.340 ms
(6 rows)

-- now look at the custom plan's cost

reset plan_cache_mode; -- resets to 'auto'

explain (timing off, analyze) execute select_stmt(8192);
                                      QUERY PLAN

──────────────────────────────────────
 Append  (cost=0.00..41.94 rows=13 width=4) (actual rows=0 loops=1)
   ->  Seq Scan on t_8192  (cost=0.00..41.88 rows=13 width=4) (actual
rows=0 loops=1)
         Filter: (id = 8192)
 Planning Time: 525.501 ms
 Execution Time: 1.104 ms
(5 rows)

So, the cost of custom plan is 41.94 + 1000 * 0.0025 * 8195 = 20529.44,
which is way less than 343572 (the generic plan cost).

-- force it to generic plan again to use the cached plan (no re-planning!)

set plan_cache_mode = 'force_generic_plan';

explain (timing off, analyze) execute select_stmt(8192);
                                      QUERY PLAN

──────────────────────────────────────
 Append  (cost=0.00..343572.48 rows=106496 width=4) (actual rows=0 loops=1)
   Subplans Removed: 8191
   ->  Seq Scan on t_8192  (cost=0.00..41.88 rows=13 width=4) (actual
rows=0 loops=1)
         Filter: (id = $1)
 Planning Time: 14.202 ms
 Execution Time: 1.841 ms
(6 rows)

You can see that the total time is the least when a cached plan is used,
which is only possible if a generic plan can be used (even if creating it
for the first time is very expensive.).  But its cost prevents it from
being automatically selected (plan_cache_mode = 'auto').  That may be one
thing we could fix in the future by considering run-time pruning in the
equation of Append costing, so that its cost is more or less the same as
the custom plan's cost.


Just as one more data point, if you apply the patch that you mentioned
[1], you can see that custom planning costs even less than that.

reset plan_cache_mode;

explain (timing off, analyze) execute select_stmt(8192);
                                      QUERY PLAN

───────────────────────────────────────
 Append  (cost=0.00..41.94 rows=13 width=4) (actual rows=0 loops=1)
   ->  Seq Scan on t_8192  (cost=0.00..41.88 rows=13 width=4) (actual
rows=0 loops=1)
         Filter: (id = 8192)
 Planning Time: 0.438 ms
 Execution Time: 0.121 ms
(5 rows)

It's cheaper than using a cached generic plan (without re-planning),
because the latter has to pay the cost of AcquireExecutorLocks which takes
longer as the number of partitions increases.  Perhaps something to try
fix fixing too.  Not planning should cost less than planning! :)

Thanks,
Amit

[1]
https://www.postgresql.org/message-id/9d7c5112-cb99-6a47-d3be-cf1ee6862a1d@lab.ntt.co.jp

On 2018/11/28 13:46, Amit Langote wrote:
> It's cheaper than using a cached generic plan (without re-planning),
> because the latter has to pay the cost of AcquireExecutorLocks which takes
> longer as the number of partitions increases.  Perhaps something to try
> fix fixing too.  Not planning should cost less than planning! :)

Ah, I see that David has already thought about this issue.

(last paragraph of this email)
https://www.postgresql.org/message-id/CAKJS1f-ibmyn1W_UsdSmygjKOL6YgPyX0Mz54V_iD0HWWL_h%3Dg%40mail.gmail.com

Thanks,
Amit

Hi, David

On Wednesday, November 28, 2018 1:23 PM David Rowley wrote:
> > I found that making a generic plan of SELECT/UPDATE/DELETE for a table
> partitioned into thousands is slow.
> > Especially, UPDATE/DELETE statement is too slow.
> 
> It's quite well known and also documented [1] that this is slow. The manual
> reads:

Thanks for the detailed explanation and sorry for my lack of explanation.
What I am worried about is that if users don't know the flow of PREPARE EXECUTE, query execution seems to be suddenly
slowand they will be in trouble.
 
Query execution seems no problem because choose_custom_plan() compare the costs of custom plans and generic plans and
selecta lower cost plan.
 
Actually, selected custom plan is faster than generic plan.
However, since a generic plan is made at sixth execution, it looks slow as a whole.

postgres=# prepare update_stmt(int) as update t set id = id + 1 where id = $1;
PREPARE
postgres=# \timing
Timing is on.
postgres=# execute update_stmt(8192);                                       
UPDATE 0
Time: 25.702 ms
postgres=# execute update_stmt(8192);
UPDATE 0
Time: 13.641 ms
postgres=# execute update_stmt(8192);
UPDATE 0
Time: 13.380 ms
postgres=# execute update_stmt(8192);
UPDATE 0
Time: 8.508 ms
postgres=# execute update_stmt(8192);
UPDATE 0
Time: 12.162 ms
postgres=# execute update_stmt(8192);
UPDATE 0
Time: 26517.487 ms (00:26.517)
postgres=# execute update_stmt(8192);
UPDATE 0
Time: 13.208 ms

>Although I'm not
> sure this will do much for you SELECT case, since you're not pruning any
> partitions during planning.

Umm. enable_partition_pruning is on(default). Is there any settings?

> Since you mentioned the plan_cache_mode GUC, then I assume you're not
> talking about any version of PostgreSQL that's been released, so if you're
> looking for a way to make it faster in master then I'd suggest helping
> with the review of [2]. If that patch does not meet your needs then also
> help Thomas with [3]. If that's still not good enough then you might need
> to do some research yourself. perf is your friend there.

Yes, I look for a way to make it faster in master.
Thanks for your advice. I'll take a look.
I understand that since there is no run-time partition pruning of UPDATE/DELETE yet, planner creates plans for all
partitionswith force_generic_plan, which takes time to plan.
 

Regards,
Sho Kato
> -----Original Message-----
> From: David Rowley [mailto:david.rowley@2ndquadrant.com]
> Sent: Wednesday, November 28, 2018 1:23 PM
> To: Kato, Sho/加藤 翔 <kato-sho@jp.fujitsu.com>
> Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
> Subject: Re: Planning time of Generic plan for a table partitioned into
> a lot
> 
> On Tue, 27 Nov 2018 at 23:05, Kato, Sho <kato-sho@jp.fujitsu.com> wrote:
> > I found that making a generic plan of SELECT/UPDATE/DELETE for a table
> partitioned into thousands is slow.
> > Especially, UPDATE/DELETE statement is too slow.
> 
> It's quite well known and also documented [1] that this is slow. The manual
> reads:
> 
> "Currently, pruning of partitions during the planning of an UPDATE or
> DELETE command is implemented using the constraint exclusion method
> (however, it is controlled by the enable_partition_pruning rather than
> constraint_exclusion) — see the following section for details and caveats
> that apply."
> 
> and later on the same page:
> 
> "All constraints on all children of the parent table are examined during
> constraint exclusion, so large numbers of children are likely to increase
> query planning time considerably. So the legacy inheritance based
> partitioning will work well with up to perhaps a hundred child tables;
> don't try to use many thousands of children."
> 
> That documentation should be getting adjusted by [2] as that patch aims
> to improve the performance of UPDATE/DELETE planning, and also improve
> planning performance for when partitions are pruned. Although I'm not
> sure this will do much for you SELECT case, since you're not pruning any
> partitions during planning.  There's been a discussion in [3] about
> improving the performance of determining the relation's size, which is
> known to be quite a bottleneck when generating a plan which includes a
> partitioned table with a large number of partitions.
> 
> > I'm afraid that I could not come up with a good idea, but how can I
> shorten the creation time of a generic plan?
> 
> Since you mentioned the plan_cache_mode GUC, then I assume you're not
> talking about any version of PostgreSQL that's been released, so if you're
> looking for a way to make it faster in master then I'd suggest helping
> with the review of [2]. If that patch does not meet your needs then also
> help Thomas with [3]. If that's still not good enough then you might need
> to do some research yourself. perf is your friend there.
> 
> [1]
> https://www.postgresql.org/docs/devel/ddl-partitioning.html#DDL-PART
> ITIONING-DECLARATIVE
> [2] https://commitfest.postgresql.org/20/1778/
> [3]
> https://www.postgresql.org/message-id/flat/CAMyN-kCPin_stCMoXCVCq5J5
> 57e9-WEFPZTqdpO3j8wzoNVwNQ%40mail.gmail.com#e085c43b597b2775326afd9f
> 3a2b6591
> 
> --
>  David Rowley                   http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services

Hi, Amit

Thanks for your reply.
Your explanation is very easy to understand :)

On Wednesday, November 28, 2018 2:52 PM, Amit Langote wrote: 
>Keeping aside the fact that making a generic plan gets increasing more expensive as the number of partitions
increases,I'm a bit surprised that you get a generic plan with plan_cache_mode = auto.  Isn't a generic plan way too
expensivein this case?
 

Sorry for my lack of explanation. I didn't get a generic plan with plan_cache_mode = auto.
What I am worried about is that if users don't know the flow of PREPARE EXECUTE, query execution seems to be suddenly
slowand they will be in trouble.
 
Just as you said, generic plan is only made, and a custom plan is chosen.
But, as the time to make a general plan is added, it becomes slow as a whole.

>Ah, I see that David has already thought about this issue.
>
>(last paragraph of this email)
>https://www.postgresql.org/message-id/CAKJS1f-ibmyn1W_UsdSmygjKOL6YgPyX0Mz54V_iD0HWWL_h%3Dg%40mail.gmail.com

Oh great! I am also concerned about AcquireExecutorLocks.
Ok, I'll take a look.

regards,
Sho Kato
> -----Original Message-----
> From: Amit Langote [mailto:Langote_Amit_f8@lab.ntt.co.jp]
> Sent: Wednesday, November 28, 2018 2:52 PM
> To: Kato, Sho/加藤 翔 <kato-sho@jp.fujitsu.com>;
> pgsql-hackers@lists.postgresql.org
> Cc: David Rowley <dgrowleyml@gmail.com>
> Subject: Re: Planning time of Generic plan for a table partitioned into
> a lot
> 
> On 2018/11/28 13:46, Amit Langote wrote:
> > It's cheaper than using a cached generic plan (without re-planning),
> > because the latter has to pay the cost of AcquireExecutorLocks which
> > takes longer as the number of partitions increases.  Perhaps something
> > to try fix fixing too.  Not planning should cost less than planning!
> > :)
> 
> Ah, I see that David has already thought about this issue.
> 
> (last paragraph of this email)
> https://www.postgresql.org/message-id/CAKJS1f-ibmyn1W_UsdSmygjKOL6Yg
> PyX0Mz54V_iD0HWWL_h%3Dg%40mail.gmail.com
> 
> Thanks,
> Amit
> 

On Thu, 29 Nov 2018 at 20:40, Kato, Sho <kato-sho@jp.fujitsu.com> wrote:
> Sorry for my lack of explanation. I didn't get a generic plan with plan_cache_mode = auto.
> What I am worried about is that if users don't know the flow of PREPARE EXECUTE, query execution seems to be suddenly
slowand they will be in trouble.
 
> Just as you said, generic plan is only made, and a custom plan is chosen.
> But, as the time to make a general plan is added, it becomes slow as a whole.

Like Amit, I also had in mind that you'd never get a generic plan due
to the cost appearing much higher, but of course, the planner must
actually attempt to build a generic plan before it realises that the
cost of it is unfavourable, which would only occur on the 6th
execution, any subsequent executions would realise the generic plan is
no good.  I don't quite see any way around that other than ensuring
you have plan_cache_mode as force_custom_plan, but then you're still
going against the manual's recommendations about not having thousands
of partitions.  The problem is only made worse in PG11 from PG10
because generating the custom plan has become faster than it
previously was due to the new partition pruning code which might make
it appear we can handle more partitions than we could previously, but
generating a generic plan for that many partitions being slow kinda
proves that's not the case.  The fact that we still have the warning
in the manual about not having thousands of partitions makes me not so
worried about this.

-- 
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

On 2018/11/29 19:54, David Rowley wrote:
> The problem is only made worse in PG11 from PG10
> because generating the custom plan has become faster than it
> previously was due to the new partition pruning code which might make
> it appear we can handle more partitions than we could previously,
Actually, PG 11's pruning improvements don't change plancache.c's equation
of custom plan cost, that is, even if pruning may have gotten faster it
doesn't change the value cached_plan_cost comes up with.

Although, you're certainly right that users are well advised to trust the
documentation to not go beyond hundreds of partitions, even if they may
not care about all the internal details that make partitioning slow.

Thanks,
Amit

On Thursday, November 29, 2018 7:55 PM　 David Rowley wrote:
>I don't quite see any way around that other than ensuring you have plan_cache_mode as >force_custom_plan, but then
you'restill going against the manual's recommendations about not >having thousands of partitions.
 

Ok, I got it.

Regards,
Sho Kato
> -----Original Message-----
> From: David Rowley [mailto:david.rowley@2ndquadrant.com]
> Sent: Thursday, November 29, 2018 7:55 PM
> To: Kato, Sho/加藤 翔 <kato-sho@jp.fujitsu.com>
> Cc: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>; PostgreSQL Hackers
> <pgsql-hackers@lists.postgresql.org>
> Subject: Re: Planning time of Generic plan for a table partitioned into
> a lot
> 
> On Thu, 29 Nov 2018 at 20:40, Kato, Sho <kato-sho@jp.fujitsu.com> wrote:
> > Sorry for my lack of explanation. I didn't get a generic plan with
> plan_cache_mode = auto.
> > What I am worried about is that if users don't know the flow of PREPARE
> EXECUTE, query execution seems to be suddenly slow and they will be in
> trouble.
> > Just as you said, generic plan is only made, and a custom plan is chosen.
> > But, as the time to make a general plan is added, it becomes slow as
> a whole.
> 
> Like Amit, I also had in mind that you'd never get a generic plan due
> to the cost appearing much higher, but of course, the planner must actually
> attempt to build a generic plan before it realises that the cost of it
> is unfavourable, which would only occur on the 6th execution, any
> subsequent executions would realise the generic plan is no good.  I don't
> quite see any way around that other than ensuring you have plan_cache_mode
> as force_custom_plan, but then you're still going against the manual's
> recommendations about not having thousands of partitions.  The problem
> is only made worse in PG11 from PG10 because generating the custom plan
> has become faster than it previously was due to the new partition pruning
> code which might make it appear we can handle more partitions than we
> could previously, but generating a generic plan for that many partitions
> being slow kinda proves that's not the case.  The fact that we still have
> the warning in the manual about not having thousands of partitions makes
> me not so worried about this.
> 
> --
>  David Rowley                   http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services

On Fri, 30 Nov 2018 at 15:04, Amit Langote
<Langote_Amit_f8@lab.ntt.co.jp> wrote:
>
> On 2018/11/29 19:54, David Rowley wrote:
> > The problem is only made worse in PG11 from PG10
> > because generating the custom plan has become faster than it
> > previously was due to the new partition pruning code which might make
> > it appear we can handle more partitions than we could previously,
>
> Actually, PG 11's pruning improvements don't change plancache.c's equation
> of custom plan cost, that is, even if pruning may have gotten faster it
> doesn't change the value cached_plan_cost comes up with.

Unsure why you think I was implying that the plancache code had
changed. What I meant was, the faster pruning code means that PG11
appears more capable of handling more partitions than PG10 could
handle, but this really only goes as far as custom plans where many
partitions get pruned. When no pruning takes place, say, in a generic
plan where the partition key is being compared to some parameter, then
we've done nothing to improve the performance of planning for that.
This may result in someone doing some light testing and thinking PG11
can handle a higher number of partitions that we might advise them to
use, only to find themselves stumble later when trying to build a
generic plan for that number of partitions.   It appears to me that
this is what's happened in this case.

-- 
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

On 2018/11/30 14:58, David Rowley wrote:
> On Fri, 30 Nov 2018 at 15:04, Amit Langote
> <Langote_Amit_f8@lab.ntt.co.jp> wrote:
>>
>> On 2018/11/29 19:54, David Rowley wrote:
>>> The problem is only made worse in PG11 from PG10
>>> because generating the custom plan has become faster than it
>>> previously was due to the new partition pruning code which might make
>>> it appear we can handle more partitions than we could previously,
>>
>> Actually, PG 11's pruning improvements don't change plancache.c's equation
>> of custom plan cost, that is, even if pruning may have gotten faster it
>> doesn't change the value cached_plan_cost comes up with.
> 
> Unsure why you think I was implying that the plancache code had
> changed.

Sorry I misinterpreted your sentence "...which might make it appear we can
handle more partitions than we could previously".  I thought you're saying
that *plancache* now thinks custom plans are better because they're
sightly faster.

> What I meant was, the faster pruning code means that PG11
> appears more capable of handling more partitions than PG10 could
> handle, but this really only goes as far as custom plans where many
> partitions get pruned.

Right.

> When no pruning takes place, say, in a generic
> plan where the partition key is being compared to some parameter, then
> we've done nothing to improve the performance of planning for that.

Yeah.  Even with patches for PG 12, this case will be only slightly faster.

> This may result in someone doing some light testing and thinking PG11
> can handle a higher number of partitions that we might advise them to
> use, only to find themselves stumble later when trying to build a
> generic plan for that number of partitions.   It appears to me that
> this is what's happened in this case.

Yeah, maybe we haven't explained in the documentation where generic plans
are described that making them for partitioned table is an expensive
affair.  Although, by definition, they are built once for a given query
and PG 11 with it's execution-time pruning can execute these plans pretty
quickly, which is an overall improvement.  But you'd obviously know that
much. :)

Thanks,
Amit

On Fri, 30 Nov 2018 at 19:44, Amit Langote
<Langote_Amit_f8@lab.ntt.co.jp> wrote:
> Yeah, maybe we haven't explained in the documentation where generic plans
> are described that making them for partitioned table is an expensive
> affair.  Although, by definition, they are built once for a given query
> and PG 11 with it's execution-time pruning can execute these plans pretty
> quickly, which is an overall improvement.  But you'd obviously know that
> much. :)

Maybe a documents patch is a good idea. Your planner patches for
partitioning are only going to widen the performance gap between
generating a plan where many partitions are pruned vs generating a
generic plan where no partition could be pruned.  I can only imagine
this will cause problems for more people as that gap widens as it will
likely lead to more and more people with partitioned tables with high
numbers of partitions.

-- 
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

On Friday, November 30, 2018 3:44 PM, Amit Langote wrote:
> Yeah, maybe we haven't explained in the documentation where generic plans
> are described that making them for partitioned table is an expensive
> affair.

Will we improve creating the generic plan in the future?
For example, if there is UPDATE / DELETE run-time partition pruning, creating the generic plan will also be faster.

Although it may not be possible with PG 12, I think that it is necessary to improve it in the future.

Regards,
Sho Kato
> -----Original Message-----
> From: Amit Langote [mailto:Langote_Amit_f8@lab.ntt.co.jp]
> Sent: Friday, November 30, 2018 3:44 PM
> To: David Rowley <david.rowley@2ndquadrant.com>
> Cc: Kato, Sho/加藤 翔 <kato-sho@jp.fujitsu.com>; PostgreSQL Hackers
> <pgsql-hackers@lists.postgresql.org>
> Subject: Re: Planning time of Generic plan for a table partitioned into
> a lot
> 
> On 2018/11/30 14:58, David Rowley wrote:
> > On Fri, 30 Nov 2018 at 15:04, Amit Langote
> > <Langote_Amit_f8@lab.ntt.co.jp> wrote:
> >>
> >> On 2018/11/29 19:54, David Rowley wrote:
> >>> The problem is only made worse in PG11 from PG10 because generating
> >>> the custom plan has become faster than it previously was due to the
> >>> new partition pruning code which might make it appear we can handle
> >>> more partitions than we could previously,
> >>
> >> Actually, PG 11's pruning improvements don't change plancache.c's
> >> equation of custom plan cost, that is, even if pruning may have
> >> gotten faster it doesn't change the value cached_plan_cost comes up
> with.
> >
> > Unsure why you think I was implying that the plancache code had
> > changed.
> 
> Sorry I misinterpreted your sentence "...which might make it appear we
> can handle more partitions than we could previously".  I thought you're
> saying that *plancache* now thinks custom plans are better because they're
> sightly faster.
> 
> > What I meant was, the faster pruning code means that PG11 appears more
> > capable of handling more partitions than PG10 could handle, but this
> > really only goes as far as custom plans where many partitions get
> > pruned.
> 
> Right.
> 
> > When no pruning takes place, say, in a generic plan where the
> > partition key is being compared to some parameter, then we've done
> > nothing to improve the performance of planning for that.
> 
> Yeah.  Even with patches for PG 12, this case will be only slightly faster.
> 
> > This may result in someone doing some light testing and thinking PG11
> > can handle a higher number of partitions that we might advise them to
> > use, only to find themselves stumble later when trying to build a
> > generic plan for that number of partitions.   It appears to me that
> > this is what's happened in this case.
> 
> Yeah, maybe we haven't explained in the documentation where generic plans
> are described that making them for partitioned table is an expensive
> affair.  Although, by definition, they are built once for a given query
> and PG 11 with it's execution-time pruning can execute these plans pretty
> quickly, which is an overall improvement.  But you'd obviously know that
> much. :)
> 
> Thanks,
> Amit
> 

On Fri, 30 Nov 2018 at 20:15, Kato, Sho <kato-sho@jp.fujitsu.com> wrote:
> Will we improve creating the generic plan in the future?

I think Amit's patch will improve the UPDATE/DELETE planning
performance even when nothing can be pruned.  Currently planning those
are quite inefficient as a separate grouping planner call is made for
each partition.

> For example, if there is UPDATE / DELETE run-time partition pruning, creating the generic plan will also be faster.

Adding run-time pruning code for that won't make the planner any
faster. There will be a very slight overhead to build the required
PartitionPruneInfo struct to attach to the plan in order to allow the
run-time pruning to work. This would mean that there'd only be a hope
of reducing the execution time of such a plan, not the time to build
the plan in the first place.

-- 
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services