Re: Performance regression with PostgreSQL 11 and partitioning

On 26 May 2018 at 09:17, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> I'm inclined to think that we should flush find_appinfos_by_relids
> altogether, along with these inefficient intermediate arrays, and instead
> have the relevant places in adjust_appendrel_attrs call some function
> defined as "gimme the AppendRelInfo for child rel A and parent rel B,
> working directly from the PlannerInfo root data". That could use a hash
> lookup when dealing with more than some-small-number of AppendRelInfos,
> comparable to what we do with join_rel_list/join_rel_hash.

For partitioned tables, a child just has a single parent, so to speed
up find_appinfos_by_relids we'd simply just need a faster way to find
the AppendRelInfo by child relid.

I've been working on a patch series which I plan to submit to PG12
aimed to speed up partitioning. Ideally, I'd have liked to just tag
the AppendRelInfo onto the child RelOptInfo, but that falls down
during inheritance planning.

In the end I just made an array to store AppendRelInfo's by their
child_relid which is created and populated during
setup_simple_rel_arrays.

Probably the patch could go a bit further and skip array allocation
when the append_rel_list is empty, but I've been busy working on
another bunch of stuff to improve planning time. I'd planned to give
this another look before submitting in the PG12 cycle, so state ==
WIP.

A quick test of the attached on Thomas' 4k part test, I get:

Unpatched
tps = 5.957508 (excluding connections establishing)

Patched:
tps = 15.368806 (excluding connections establishing)

Using that, if Thomas sees the same speedup then that puts PG11 at
55.4ms vs his measured 66ms in PG10.

The attached should apply with some fuzz to master.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services