Horton

Re: speeding up planning with partitions

Amit Langote <langote_amit_f8@lab.ntt.co.jp>

View thread View on postgr.es
From: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
To: "Imai, Yoshikazu" <imai.yoshikazu@jp.fujitsu.com>, "jesper.pedersen@redhat.com" <jesper.pedersen@redhat.com>
Cc: "'Amit Langote'" <amitlangote09@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>, David Rowley <david.rowley@2ndquadrant.com>, Alvaro Herrera <alvherre@2ndquadrant.com>, Justin Pryzby <pryzby@telsasoft.com>, Pg Hackers <pgsql-hackers@postgresql.org>
Date: 2019-03-05T07:16:52Z
Lists: pgsql-hackers 
Imai-san,

Thanks for checking.

On 2019/03/05 15:03, Imai, Yoshikazu wrote:
> I've also done code review of 0001 and 0002 patch so far.
> 
> [0001]
> 1. Do we need to open/close a relation in add_appendrel_other_rels()? 
> 
> +	if (rel->part_scheme)
>  	{
> -		ListCell   *l;
>     ...
> -		Assert(cnt_parts == nparts);
> +		rel->part_rels = (RelOptInfo **)
> +				palloc0(sizeof(RelOptInfo *) * rel->nparts);
> +		relation = table_open(rte->relid, NoLock);
>  	}
> 
> +	if (relation)
> +		table_close(relation, NoLock);

Ah, it should be moved to another patch.  Actually, to patch 0003, which
makes it necessary to inspect the PartitionDesc.

> 2. We can sophisticate the usage of Assert in add_appendrel_other_rels().
> 
> +	if (rel->part_scheme)
>  	{
>     ...
> +		rel->part_rels = (RelOptInfo **)
> +				palloc0(sizeof(RelOptInfo *) * rel->nparts);
> +		relation = table_open(rte->relid, NoLock);
>  	}
>   ...
> +	i  = 0;
> +	foreach(l, root->append_rel_list)
> +	{
>     ...
> +		if (rel->part_scheme != NULL)
> +		{
> +			Assert(rel->nparts > 0 && i < rel->nparts);
> +			rel->part_rels[i] = childrel;
> +		}
> +
> +		i++;
> 
> as below;
> 
> +	if (rel->part_scheme)
>  	{
>     ...
>     Assert(rel->nparts > 0);
> +		rel->part_rels = (RelOptInfo **)
> +				palloc0(sizeof(RelOptInfo *) * rel->nparts);
> +		relation = table_open(rte->relid, NoLock);
>  	}
>   ...
> +	i  = 0;
> +	foreach(l, root->append_rel_list)
> +	{
>     ...
> +		if (rel->part_scheme != NULL)
> +		{
> +			Assert(i < rel->nparts);
> +			rel->part_rels[i] = childrel;
> +		}
> +
> +		i++;

You're right.  That's what makes sense in this context.

> [0002]
> 3. If using variable like is_source_inh_expansion, the code would be easy to read. (I might mistakenly understand root->simple_rel_array_size > 0 means source inheritance expansion though.)

root->simple_rel_array_size > 0  *does* mean source inheritance expansion,
so you're not mistaken.  As you can see, expand_inherited_rtentry is
called by inheritance_planner() to expand target inheritance and by
add_appendrel_other_rels() to expand source inheritance.  Since the latter
is called by query_planner, simple_rel_array would have been initialized
and hence root->simple_rel_array_size > 0 is true.

Maybe it'd be a good idea to introduce is_source_inh_expansion variable
for clarity as you suggest and set it to (root->simple_rel_array_size > 0).

> 4. I didn't see much carefully, but should the introduction of contains_inherit_children be in 0003 patch...?

You're right.

Thanks again for the comments.  I've made changes to my local repository.

Thanks,
Amit

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.