Re: Internal error XX000 with enable_partition_pruning=on, pg 11beta1 on Debian

On 20 June 2018 at 13:20, David Rowley  wrote:
> select count(*) from (select * from p union all select * from p) p;
>
> Unpatched:
> tps = 8.530355 (excluding connections establishing)
>
> Patched:
> tps = 7.853939 (excluding connections establishing)

I've been thinking about this and I'm not so happy about my earlier
proposed fix about not flattening the Appends for UNION ALL parents
with partitioned relation children.  Apart from the additional
overhead of pulling all tuples through an additional Append node, I
also don't like it because we one day might decide to fix it and make
it flatten these again.  It would be much nicer not to fool around
with the plan shapes like that from one release to the next.

So, today I decided to write some code to just make the UNION ALL
parents work with partitioned tables while maintaining the Append
hierarchy flattening.

I've only just completed reading back through all the code and I think
it's correct.  I ended up changing add_paths_to_append_rel() so that
instead of performing concatenation on partitioned_rels from two UNION
ALL children, it creates a List of lists.  I believe this list can
only end up with a 2-level hierarchy of partitioned rels.  I tested
this and it seems to work as I expect, although I think I need to
study the code a bit more to ensure it can't happen. I need to check
if there's some cases where nested UNION ALLs cannot be flattened to
have a single UNION ALL parent.  Supporting this did cause me to have
to check the List->type in a few places. I only saw one other place in
the code where this is done, so I figured that meant it was okay.

In the executor side, I didn't add any pre-calculation code for each
partition hierarchy. I did this previously to save having to re-prune
on individual partitions, but I figured that was at a good enough
level not to have to add it for each partition hierarchy.  I imagine
most partition hierarchies will just contain a single partitioned
table anyway, so an additional level of caching might not buy very
much, but I might just be trying to convince myself of that because
it's late here now.

Anyway... Patch attached.  This is method 3 of the 3 methods I thought
to fix this, so if this is not suitable then I'm out of ideas.

It would be great to get some feedback on this as I'd really like to
be done with it before July.

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