Re: Partition-wise join for join between (declaratively) partitioned tables

PFA the zip containing all the patches rebased on
56018bf26eec1a0b4bf20303c98065a8eb1b0c5d and contain the patch to free
memory consumed by paths using a separate path context.

There are some more changes wrt earlier set of patches
1. Since we don't need a separate context for planning for each
child_join, changed code in create_partition_join_plan() to not do
that. The function collects all child_join paths into merge/append
path and calls create_plan_recurse() on that path instead of
converting each child_join path to plan one at a time.

2. Changed optimizer/README and some comments referring to temporary
memory context, since we do not use that anymore.

3. reparameterize_path_by_child() is fixed to translate the merge and
hash clause in Hash/Merge path.

On Thu, Mar 9, 2017 at 6:44 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Wed, Mar 1, 2017 at 3:56 AM, Ashutosh Bapat
> <ashutosh(dot)bapat(at)enterprisedb(dot)com> wrote:
>>> 2. If the PartitionJoinPath emerges as the best path, we create paths
>>> for each of the remaining child-joins. Then we collect paths with
>>> properties same as the given PartitionJoinPath, one from each
>>> child-join. These paths are converted into plans and a Merge/Append
>>> plan is created combing these plans. The paths and plans for
>>> child-join are created in a temporary memory context. The final plan
>>> for each child-join is copied into planner's context and the temporary
>>> memory context is reset.
>>>
>>
>> Robert and I discussed this in more detail. Path creation code may
>> allocate objects other than paths. postgres_fdw, for example,
>> allocates character array to hold the name of relation being
>> pushed-down. When the temporary context gets zapped after creating
>> paths for a given child-join, those other objects also gets thrown
>> away. Attached patch has implemented the idea that came out of the
>> discussion.
>>
>> We create a memory context for holding paths at the time of creating
>> PlannerGlobal and save it in PlannerGlobal. The patch introduces a new
>> macro makePathNode() which allocates the memory for given type of path
>> from this context. Every create_*_path function has been changed to
>> use this macro instead of makeNode(). In standard_planner(), at the
>> end of planning we destroy the memory context freeing all the paths
>> allocated. While creating a plan node, planner copies everything
>> required by the plan from the path, so the path is not needed any
>> more. So, freeing corresponding memory should not have any adverse
>> effects.
>>
>> Most of the create_*_path() functions accept root as an argument, thus
>> the temporary path context is available through root->glob everywhere.
>> An exception is create_append_path() which does not accept root as an
>> argument. The patch changes create_append_path() and its callers like
>> set_dummy_rel_pathlist(), mark_dummy_rel() to accept root as an
>> argument. Ideally paths are not required after creating plan, so we
>> should be
>> able to free the context right after the call to create_plan(). But we
>> need dummy paths while creating flat rtable in
>> set_plan_references()->add_rtes_to_flat_rtable(). We used to So free
>> the path context at the end of planning cycle. Now that we are
>> allocating all the paths in a different memory context, it doesn't
>> make sense to switch context in mark_dummy_rel().
>>
>> 0001 patch implements the idea described above.
>> 0002 patch adds instrumentation to measure memory consumed in
>> standard_planner() call.
>> 0003 patch adds a GUC zap_paths to enable/disable destroying path context.
>> The last two patches are for testing only.
>>
>> Attached also find the SQL script and its output showing the memory
>> saved. For a 5 way self-join of pg_class, the total memory consumed in
>> standard_planner() is 760K without patch and with patch it comes down
>> to 713K, saving 47K memory otherwise occupied by paths. It looks like
>> something useful even without partition-wise joins.
>
> Of course, that's not a lot, but the savings will be a lot better for
> partition-wise joins. Do you have a set of patches for that feature
> that apply on top of 0001?
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company