RE: speeding up planning with partitions

Imai, Yoshikazu <imai.yoshikazu@jp.fujitsu.com>

From: "Imai, Yoshikazu" <imai.yoshikazu@jp.fujitsu.com>

To: 'Amit Langote' <Langote_Amit_f8@lab.ntt.co.jp>

Cc: 'Amit Langote' <amitlangote09@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>, David Rowley <david.rowley@2ndquadrant.com>, Alvaro Herrera <alvherre@2ndquadrant.com>, "jesper.pedersen@redhat.com" <jesper.pedersen@redhat.com>, Justin Pryzby <pryzby@telsasoft.com>, "Pg Hackers" <pgsql-hackers@postgresql.org>

Date: 2019-03-08T07:16:30Z

Lists: pgsql-hackers

Attachments

v26-0003-solving-memory-increasement-problem.diff (application/octet-stream) patch v26-0003

Amit-san,

On Wed, Mar 6, 2019 at 5:38 AM, Amit Langote wrote:
...
> > I didn't investigate that problem, but there is another memory
> > increase
> issue, which is because of 0003 patch I think. I'll try to solve the latter
> issue.
> 
> Interested in details as it seems to be a separate problem.

I solved this problem.

I think we don't need to do list_copy in the below code.

+		/*
+		 * No need to copy of the RTEs themselves, but do copy the List
+		 * structure.
+		 */
+		subroot->parse->rtable = list_copy(rtable_with_target);

Because subroot->parse->rtable will be copied again by:

 		subroot->parse = (Query *)
 			adjust_appendrel_attrs(parent_root,
-								   (Node *) parent_parse,
+								   (Node *) subroot->parse,
 								   1, &appinfo);

So I modified the code and did test to confirm memory increasement don't happen. The test and results are below.

[test]
* Create partitioned table with 1536 partitions.
* Execute "update rt set a = random();"

[results]
A backend uses below amount of memory in update transaction:

HEAD: 807MB
With v26-0001, 0002: 790MB
With v26-0001, 0002, 0003: 860MB
With v26-0003 modified: 790MB

I attached the diff of modification for v26-0003 patch which also contains some refactoring.
Please see if it is ok.
(Sorry it is modification for v26 patch though latest ones are v28.)

--
Yoshikazu Imai

Commits

Clean up handling of constraint_exclusion and enable_partition_pruning.
- e03ff739695c 12.0 landed
- 11ea45ffec9a 11.3 landed
Add test case exercising formerly-unreached code in inheritance_planner.
- 8fba397f0ca7 12.0 landed
Speed up planning when partitions can be pruned at plan time.
- 428b260f87e8 12.0 landed
Avoid crash in partitionwise join planning under GEQO.
- 7ad6498fd5a6 12.0 landed
- d70c147fa217 11.3 landed
Avoid passing query tlist around separately from root->processed_tlist.
- 333ed246c6f3 12.0 landed
Build "other rels" of appendrel baserels in a separate step.
- 53bcf5e3dbea 12.0 landed
Get rid of duplicate child RTE for a partitioned table.
- e8d5dd6be79e 12.0 landed
Rearrange make_partitionedrel_pruneinfo to avoid work when we can't prune.
- 734308a22072 12.0 landed
Don't copy PartitionBoundInfo in set_relation_partition_info.
- c8151e642368 12.0 landed
Move building of child base quals out into a new function
- 80579f9bb171 12.0 landed
Reorganize planner code moved in b60c39759908
- d723f56872a9 12.0 landed
Move inheritance expansion code into its own file
- b60c39759908 12.0 landed
Fix inherited UPDATE/DELETE with UNION ALL subqueries.
- c03ad5602f52 9.4.0 cited
Rearrange planner to save the whole PlannerInfo (subroot) for a subquery.
- b3aaf9081a1a 9.2.0 cited