Horton

RE: speeding up planning with partitions

Tsunakawa, Takayuki <tsunakawa.takay@jp.fujitsu.com>

View thread View on postgr.es
From: "Tsunakawa, Takayuki" <tsunakawa.takay@jp.fujitsu.com>
To: "Imai, Yoshikazu" <imai.yoshikazu@jp.fujitsu.com>
Cc: 'Amit Langote' <Langote_Amit_f8@lab.ntt.co.jp>, David Rowley <david.rowley@2ndquadrant.com>, Alvaro Herrera <alvherre@2ndquadrant.com>, "Pg Hackers" <pgsql-hackers@postgresql.org>
Date: 2019-01-18T05:12:32Z
Lists: pgsql-hackers 
Imai-san,

From: Amit Langote [mailto:Langote_Amit_f8@lab.ntt.co.jp]
> On 2019/01/09 11:08, Imai, Yoshikazu wrote:
> > I wonder why force_custom_plan is faster than auto after applied the patch.
> >
> > When we use PREPARE-EXECUTE, a generic plan is created and used if its
> cost is
> > cheaper than creating and using a custom plan with plan_cache_mode='auto',
> > while a custom plan is always created and used with
> plan_cache_mode='force_custom_plan'.
> > So one can think the difference in above results is because of creating
> or
> > using a generic plan.
> >
> > I checked how many times a generic plan is created during executing pgbench
> and
> > found a generic plan is created only once and custom plans are created
> at other
> > times with plan_cache_mode='auto'. I also checked the time of creating
> a
> > generic plan, but it didn't take so much(250ms or so with 4096 partitions).
> So
> > the time of creating a generic plan does not affect the performance.
> >
> > Currently, a generic plan is created at sixth time of executing EXECUTE
> query.
> > I changed it to more later (ex. at 400,000th time of executing EXECUTE
> query on
> > master with 4096 partitions, because 7000TPS x 60sec=420,0000
> transactions are
> > run while executing pgbench.), then there are almost no difference between
> auto
> > and force_custom_plan. I think that creation of a generic plan affects
> the time
> > of executing queries which are ordered after creating generic plan.
> >
> > If my assumption is right, we can expect some additional process is
> occurred at
> > executing queries ordered after creating a generic plan, which results
> in auto is
> > slower than force_custom_plan because of additional process. But looking
> at
> > above results, on master with 4096 partitions, auto is faster than
> force_custom_plan.
> > So now I am confused.
> >
> > Do you have any ideas what does affect the performance?
> 
> Are you saying that, when using auto mode, all executions of the query
> starting from 7th are slower than the first 5 executions?  That is, the
> latency of creating and using a custom plan increases *after* a generic
> plan is created and discarded on the 6th execution of the query?  If so,
> that is inexplicable to me.

Isn't CheckCachedPlan() (and AcquireExecutorLocks() therein) called in every EXECUTE after 6th one due to some unknow issue?
Does choose_custom_plan() always return true after 6th EXECUTE?

Regards
Takayuki Tsunakawa

Commits

  1. Clean up handling of constraint_exclusion and enable_partition_pruning.

  2. Add test case exercising formerly-unreached code in inheritance_planner.

  3. Speed up planning when partitions can be pruned at plan time.

  4. Avoid crash in partitionwise join planning under GEQO.

  5. Avoid passing query tlist around separately from root->processed_tlist.

  6. Build "other rels" of appendrel baserels in a separate step.

  7. Get rid of duplicate child RTE for a partitioned table.

  8. Rearrange make_partitionedrel_pruneinfo to avoid work when we can't prune.

  9. Don't copy PartitionBoundInfo in set_relation_partition_info.

  10. Move building of child base quals out into a new function

  11. Reorganize planner code moved in b60c39759908

  12. Move inheritance expansion code into its own file

  13. Fix inherited UPDATE/DELETE with UNION ALL subqueries.

  14. Rearrange planner to save the whole PlannerInfo (subroot) for a subquery.