On 20 June 2018 at 13:20, David Rowley <david.row...@2ndquadrant.com> 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
v1-0001-Fix-run-time-partition-pruning-for-UNION-ALL-pare.patch
Description: Binary data
