On 19 March 2018 at 23:03, Amit Langote <langote_amit...@lab.ntt.co.jp> wrote: > Just recently, I replied to a pgsql-bugs report by someone who had OOM > kill a backend running `delete from > partitioned_table_with_7202_partitions` on their test system [1]. That'd > be because running inheritance_planner on a partitioned table doesn't cope > very well beyond a few hundred partitions, as we've also written in our > partitioning/inheritance documentation.
hmm, yeah that's unfortunate. I'd not done much study of the inheritance planner before, but I see how that could happen now that I understand a bit more about it. nparts^2 RelOptInfos will be created on such problems. My patch should help with that providing that some pruning will actually take place, but make the problem very slightly worse if none can be pruned. > On 2018/03/19 16:18, David Rowley wrote: >> Likely there's more than could be squeezed out of this if we could get >> the grouping_planner() to somehow skip creating paths and performing >> the join search. But that patch is not nearly as simple as the >> attached. > > Yeah, that'd be nice. Do you think that we cannot fix update/delete on > partitioned tables until we have such a patch though? IOW, did you intend > the patch you posted to just be a PoC to demonstrate that we can save tons > just by not doing grouping_planner() on pruned partitions? The patch was meant as a PoC. I think the performance of the patch is acceptable without any additional optimisation work. It would be nice, but any more code that's added would need more reviewer and committer time, both of which are finite, especially so before PG11 code cut off. I think it would be a shame to tell people partition is usable now for a decent number of partitions, providing you don't need to perform any OLTP UPDATE/DELETE operations on the partitions. I think for the few lines of code that the proposed patch takes it's worth considering for PG11, but only once your work has gone in. I certainly wouldn't want this to hold your work back. > BTW, maybe you know, but if we want this to prune same partitions as are > pruned during select (due to the new pruning facility), we'd need to teach > get_relation_constraints() to not fetch the partition constraint > (RelationGetPartitionQual) at all. My patch currently teaches it to avoid > fetching the partition constraint only for select. If we include the > partition constraint in the list of constraints returned by > get_relation_constraints, we'd still be redundantly executing the > constraint exclusion logic for the selected partitions via the > grouping_planner() call on those partitions. I'd not thought of that. It seems more like a performance optimisation than something that's required for correctness. Removing that would probably make constraint_exclusion = 'partition' pretty useless -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
