Re: Performance regression with PostgreSQL 11 and partitioning

On Fri, May 25, 2018 at 11:49 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Fri, May 25, 2018 at 10:30 AM, Thomas Reiss <thomas(dot)reiss(at)dalibo(dot)com> wrote:
>> Then I used the following to compare the planning time :
>> explain (analyze) SELECT * FROM t1 WHERE dt = '2018-05-25';
>>
>> With PostgreSQL 10, planning time is 66ms, in v11, planning rise to
>> 143ms. I also did a little test with more than 20k partitions, and while
>> the planning time was reasonable with PG10 (287.453 ms), it exploded
>> with v11 with 4578.054 ms.
>>
>> Perf showed that thes functions find_appinfos_by_relids and
>> bms_is_member consumes most of the CPU time with v11. With v10, this
>> functions don't appear. It seems that find_appinfos_by_relids was
>> introduced by commit 480f1f4329f.
>
> Hmm. Have you verified whether that commit is actually the one that
> caused the regression? It's certainly possible, but I wouldn't expect
> calling find_appinfos_by_relids() with 1 AppendRelInfo to be too much
> more expensive than calling find_childrel_appendrelinfo() as the
> previous code did. I wonder if some later change, perhaps related to
> pruning, just caused this code path to be hit more often.

One more possibility is that find_appinfos_by_relids being shown high
up in profiles is called from apply_scanjoin_target_to_paths that's
new in PG 11, which in turn is called (unconditionally) from
grouping_planner. Especially, the following bit in it:

/*
* If the relation is partitioned, recurseively apply the same changes to
* all partitions and generate new Append paths. Since Append is not
* projection-capable, that might save a separate Result node, and it also
* is important for partitionwise aggregate.
*/
if (rel->part_scheme && rel->part_rels)
{
int partition_idx;
List *live_children = NIL;

/* Adjust each partition. */
for (partition_idx = 0; partition_idx < rel->nparts; partition_idx++)
{
RelOptInfo *child_rel = rel->part_rels[partition_idx];
ListCell *lc;
AppendRelInfo **appinfos;
int nappinfos;
List *child_scanjoin_targets = NIL;

/* Translate scan/join targets for this child. */
appinfos = find_appinfos_by_relids(root, child_rel->relids,
&nappinfos);

Seems here that we call find_appinfos_by_relids here for *all*
partitions, even if all but one partition may have been pruned. I
haven't studied this code in detail, but I suspect it might be
unnecessary, although I might be wrong.

Fwiw, I'm not sure why the new pruning code would call here, at least
git grep find_appinfos_by_relids doesn't turn up anything interesting
in that regard.

Thanks,
Amit