Обсуждение: pgsql: Disable run condition optimization for some WindowFuncs
Disable run condition optimization for some WindowFuncs 94985c210 added code to detect when WindowFuncs were monotonic and allowed additional quals to be "pushed down" into the subquery to be used as WindowClause runConditions in order to short-circuit execution in nodeWindowAgg.c. The Node representation of runConditions wasn't well selected and because we do qual pushdown before planning the subquery, the planning of the subquery could perform subquery pull-up of nested subqueries. For WindowFuncs with args, the arguments could be changed after pushing the qual down to the subquery. This was made more difficult by the fact that the code duplicated the WindowFunc inside an OpExpr to include in the WindowClauses runCondition field. This could result in duplication of subqueries and a pull-up of such a subquery could result in another initplan parameter being issued for the 2nd version of the subplan. This could result in errors such as: ERROR: WindowFunc not found in subplan target lists Here in the backbranches, we don't have the flexibility to improve the Node representation to resolve this, so instead we just disable the runCondition optimization for ntile() unless the argument is a Const, (v16 only) and likewise for count(expr) (both v15 and v16). count(*) is unaffected. All other window functions which support this optimization all take zero arguments and therefore are unaffected. Bug: #18170 Reported-by: Zuming Jiang Discussion: https://postgr.es/m/18170-f1d17bf9a0d58b24@postgresql.org Backpatch-through 15 (master will be fixed independently) Branch ------ REL_16_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/9d36b883bfaaeee04f09101c5e7cde96906a256e Modified Files -------------- src/backend/utils/adt/int8.c | 15 ++++++ src/backend/utils/adt/windowfuncs.c | 29 ++++++++--- src/test/regress/expected/window.out | 94 ++++++++++++++++++++---------------- src/test/regress/sql/window.sql | 43 ++++++++++------- 4 files changed, 116 insertions(+), 65 deletions(-)
> Disable run condition optimization for some WindowFuncs > > 94985c210 added code to detect when WindowFuncs were monotonic and > allowed additional quals to be "pushed down" into the subquery to be > used as WindowClause runConditions in order to short-circuit execution > in nodeWindowAgg.c. > > The Node representation of runConditions wasn't well selected and > because we do qual pushdown before planning the subquery, the planning > of the subquery could perform subquery pull-up of nested subqueries. > For WindowFuncs with args, the arguments could be changed after pushing > the qual down to the subquery. > > This was made more difficult by the fact that the code duplicated the > WindowFunc inside an OpExpr to include in the WindowClauses runCondition > field. This could result in duplication of subqueries and a pull-up of > such a subquery could result in another initplan parameter being issued > for the 2nd version of the subplan. This could result in errors such as: > > ERROR: WindowFunc not found in subplan target lists > > Here in the backbranches, we don't have the flexibility to improve the > Node representation to resolve this, so instead we just disable the > runCondition optimization for ntile() unless the argument is a Const, > (v16 only) and likewise for count(expr) (both v15 and v16). count(*) is > unaffected. All other window functions which support this optimization > all take zero arguments and therefore are unaffected. > > Bug: #18170 Maybe you are talking about BUG #18305: Unexpected error: "WindowFunc not found in subplan target lists" ? > Reported-by: Zuming Jiang > Discussion: https://postgr.es/m/18170-f1d17bf9a0d58b24@postgresql.org > Backpatch-through 15 (master will be fixed independently) > > Branch > ------ > REL_16_STABLE > > Details > ------- > https://git.postgresql.org/pg/commitdiff/9d36b883bfaaeee04f09101c5e7cde96906a256e > > Modified Files > -------------- > src/backend/utils/adt/int8.c | 15 ++++++ > src/backend/utils/adt/windowfuncs.c | 29 ++++++++--- > src/test/regress/expected/window.out | 94 ++++++++++++++++++++---------------- > src/test/regress/sql/window.sql | 43 ++++++++++------- > 4 files changed, 116 insertions(+), 65 deletions(-) >
On Wed, 1 May 2024 at 17:02, Tatsuo Ishii <ishii@sraoss.co.jp> wrote: > > Bug: #18170 > > Maybe you are talking about BUG #18305: > Unexpected error: "WindowFunc not found in subplan target lists" ? Unsure what happened there, but yes, you're right. That should be #18305 and [1]. I believe I just copied and pasted "Bug reference: 18305" from my emails to search the archives but trying that again, I don't see #18170 come up in the matches, so I dunno where #18170 came from. Thanks for pointing it out. There's not much I can do to correct the commit message now. David [1] https://www.postgresql.org/message-id/18305-33c49b4c830b37b3@postgresql.org
David Rowley <dgrowleyml@gmail.com> writes:
> On Wed, 1 May 2024 at 17:02, Tatsuo Ishii <ishii@sraoss.co.jp> wrote:
>> Maybe you are talking about BUG #18305:
> Thanks for pointing it out. There's not much I can do to correct the
> commit message now.
Yeah, once pushed commit messages are pretty much graven on stone
tablets. But we do have a history of posting corrections in this
mailing list, just in case future hackers want to track something
down.
regards, tom lane
> Thanks for pointing it out. There's not much I can do to correct the > commit message now. Never mind. Thanks for the fix. I am looking forward to seeing the fix for the master branch. Best reagards, -- Tatsuo Ishii SRA OSS LLC English: http://www.sraoss.co.jp/index_en/ Japanese:http://www.sraoss.co.jp