Re: Planning time of Generic plan for a table partitioned into a lot

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 speeding up 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