From: Amit Langote [mailto:langote_amit...@lab.ntt.co.jp] > The immediate one I think is to refactor the planner such that the new > pruning code, that we were able to utilize for SELECT in PG 11, can also > be used for UPDATE/DELETE. Refactoring needed to replace the pruning > algorithm was minimal for SELECT, but not so much for UPDATE/DELETE. > > Once we're able to utilize the new pruning algorithm for all the cases, > we > can move on to refactoring to avoid locking and opening of all partitions. > As long as we're relying on constraint exclusion for partition pruning, > which we still do for UPDATE/DELETE, we cannot do that because constraint > exclusion has to look at each partition individually. > > The UPDATE/DELETE planning for partitioning using huge memory and CPU is > a > pretty big issue and refactoring planner to avoid that may be what's > hardest of all the problems to be solved here.
Thank you. There seem to be many challenges to address... As a user and PG developer, I'd be happy to see some wiki page that describes the current performance characteristics in terms of # of partitions, the ideal and reachable performance, and the challenges to overcome to reach that ideal goal. > If the benchmark contains queries that will need to access just one > partition, then yes the planning part has is the biggest overhead. > > Execution-time overhead is limited to having an extra, possibly needless, > Append node, but I know David has patch for that too. That's good news, thanks. Our user will perform around a hundred single-row INSERT/SELECT/UPDATE/DELETE statements in each transaction, and those are PREPAREd. I hope PG 11 (with David's patches) will work well for that workload. I'll wait for Kato-san's pgbench -M prepared result. Regards Takayuki Tsunakawa
