Обсуждение: [COMMITTERS] pgsql: Don't scan partitioned tables.
Don't scan partitioned tables. Partitioned tables do not contain any data; only their unpartitioned descendents need to be scanned. However, the partitioned tables still need to be locked, even though they're not scanned. To make that work, Append and MergeAppend relations now need to carry a list of (unscanned) partitioned relations that must be locked, and InitPlan must lock all partitioned result relations. Aside from the obvious advantage of avoiding some work at execution time, this has two other advantages. First, it may improve the planner's decision-making in some cases since the empty relation might throw things off. Second, it paves the way to getting rid of the storage for partitioned tables altogether. Amit Langote, reviewed by me. Discussion: http://postgr.es/m/6837c359-45c4-8044-34d1-736756335a15@lab.ntt.co.jp Branch ------ master Details ------- http://git.postgresql.org/pg/commitdiff/d3cc37f1d801a6b5cad9bf179274a8d767f1ee50 Modified Files -------------- src/backend/executor/execMain.c | 22 ++++- src/backend/executor/execParallel.c | 1 + src/backend/executor/execUtils.c | 56 ++++++++++++ src/backend/executor/nodeAppend.c | 6 ++ src/backend/executor/nodeMergeAppend.c | 6 ++ src/backend/executor/nodeModifyTable.c | 19 +++- src/backend/nodes/copyfuncs.c | 21 +++++ src/backend/nodes/equalfuncs.c | 12 +++ src/backend/nodes/outfuncs.c | 21 +++++ src/backend/nodes/readfuncs.c | 4 + src/backend/optimizer/path/allpaths.c | 43 +++++++-- src/backend/optimizer/path/joinrels.c | 2 +- src/backend/optimizer/plan/createplan.c | 14 ++- src/backend/optimizer/plan/planner.c | 89 +++++++++++++++--- src/backend/optimizer/plan/setrefs.c | 21 +++++ src/backend/optimizer/prep/prepunion.c | 103 ++++++++++++++------- src/backend/optimizer/util/pathnode.c | 10 +- src/backend/utils/cache/plancache.c | 3 +- src/include/executor/executor.h | 1 + src/include/nodes/nodes.h | 1 + src/include/nodes/plannodes.h | 20 +++- src/include/nodes/relation.h | 37 +++++++- src/include/optimizer/pathnode.h | 8 +- src/include/optimizer/planner.h | 2 + src/test/regress/expected/inherit.out | 147 +++++++++++++----------------- src/test/regress/expected/tablesample.out | 4 +- src/test/regress/sql/inherit.sql | 28 ++++++ 27 files changed, 537 insertions(+), 164 deletions(-)
On 21 March 2017 at 13:48, Robert Haas <rhaas@postgresql.org> wrote: > Don't scan partitioned tables. Sounds good. > Aside from the obvious advantage of avoiding some work at execution > time, this has two other advantages. First, it may improve the > planner's decision-making in some cases since the empty relation > might throw things off. I was surprised to see that an Append node still exists when there is only one child plan to be appended. I thought removing that was the whole point of the patch? > Second, it paves the way to getting rid of > the storage for partitioned tables altogether. I thought we already discussed that. Seems strange to mention something not very important that might happen in the future. We could save much more space by optimising FSM. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Tue, Mar 21, 2017 at 10:01 AM, Simon Riggs <simon@2ndquadrant.com> wrote: > On 21 March 2017 at 13:48, Robert Haas <rhaas@postgresql.org> wrote: >> Don't scan partitioned tables. > > Sounds good. > >> Aside from the obvious advantage of avoiding some work at execution >> time, this has two other advantages. First, it may improve the >> planner's decision-making in some cases since the empty relation >> might throw things off. > > I was surprised to see that an Append node still exists when there is > only one child plan to be appended. I thought removing that was the > whole point of the patch? No, that was discussed on-thread. http://postgr.es/m/b7f2d81d-9c9b-ba4e-5e00-edf626567256@lab.ntt.co.jp It's not a bad idea, but it would require further work on top of what this patch already does. >> Second, it paves the way to getting rid of >> the storage for partitioned tables altogether. > > I thought we already discussed that. Seems strange to mention > something not very important that might happen in the future. We could > save much more space by optimising FSM. Sure, that's possible. I didn't think it was strange to mention it, but you're welcome to have a different opinion. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company