agora inbox for pgsql-committers@postgresql.orghelp / color / mirror / Atom feed
pgsql: Fix COUNT's logic for window run condition support 6+ messages / 1 participants [nested] [flat]
* pgsql: Fix COUNT's logic for window run condition support @ 2026-07-07 11:58 David Rowley <drowley@postgresql.org> 0 siblings, 0 replies; 6+ messages in thread From: David Rowley @ 2026-07-07 11:58 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix COUNT's logic for window run condition support 9d9c02ccd added code to allow the executor to stop early when processing WindowAgg nodes where a monotonic window function starts producing values that result in a pushed-down qual no longer matching, and will never match again due to the window function's monotonic properties. That commit requires a SupportRequestWFuncMonotonic to exist on the window function and for it to detect when the function is monotonic. For COUNT(ANY) and COUNT(*), the support function failed to consider some cases where the WindowClause used EXCLUDE to exclude certain rows from being aggregated. Some WindowClause definitions mean we aggregate rows that come after the current row, and when processing those rows later, if we EXCLUDE certain rows, the monotonic property can be broken. Wrongly treating the COUNT(*) or COUNT(ANY) aggregate as monotonic could lead to rows being filtered that should not be filtered from the result set. Another issue was that the support function for the COUNT aggregate mistakenly thought that a WindowClause without an ORDER BY meant that the results would be both monotonically increasing and decreasing, but that's only true when in RANGE mode, where all rows are peers. It is possible to support various cases that do have an EXCLUDE clause, but getting the logic correct for the exact set of cases that are valid is quite complex and would likely better be left for a future project. Here, we mostly disable run condition pushdown when there is an EXCLUDE clause unless the clause is for EXCLUDE CURRENT ROW, uses COUNT(*) (rather than COUNT(ANY)), and the window aggregate has no FILTER clause. Bug: #19533 Reported-by: Qifan Liu <imchifan@163.com> Author: Chengpeng Yan <chengpeng_yan@outlook.com> Author: David Rowley <dgrowleyml@gmail.com> Reviewed-by: Richard Guo <guofenglinux@gmail.com> Reviewed-by: John Naylor <johncnaylorls@gmail.com> Discussion: https://postgr.es/m/19533-413a1014e5d0e766@postgresql.org Backpatch-through: 15 Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/d007800f02a7b9fe3ca984a1b870405657d990ed Modified Files -------------- src/backend/utils/adt/int8.c | 34 +++++- src/test/regress/expected/window.out | 221 ++++++++++++++++++++++++++++++++++- src/test/regress/sql/window.sql | 115 +++++++++++++++++- 3 files changed, 361 insertions(+), 9 deletions(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Fix COUNT's logic for window run condition support @ 2026-07-07 11:59 David Rowley <drowley@postgresql.org> 0 siblings, 0 replies; 6+ messages in thread From: David Rowley @ 2026-07-07 11:59 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix COUNT's logic for window run condition support 9d9c02ccd added code to allow the executor to stop early when processing WindowAgg nodes where a monotonic window function starts producing values that result in a pushed-down qual no longer matching, and will never match again due to the window function's monotonic properties. That commit requires a SupportRequestWFuncMonotonic to exist on the window function and for it to detect when the function is monotonic. For COUNT(ANY) and COUNT(*), the support function failed to consider some cases where the WindowClause used EXCLUDE to exclude certain rows from being aggregated. Some WindowClause definitions mean we aggregate rows that come after the current row, and when processing those rows later, if we EXCLUDE certain rows, the monotonic property can be broken. Wrongly treating the COUNT(*) or COUNT(ANY) aggregate as monotonic could lead to rows being filtered that should not be filtered from the result set. Another issue was that the support function for the COUNT aggregate mistakenly thought that a WindowClause without an ORDER BY meant that the results would be both monotonically increasing and decreasing, but that's only true when in RANGE mode, where all rows are peers. It is possible to support various cases that do have an EXCLUDE clause, but getting the logic correct for the exact set of cases that are valid is quite complex and would likely better be left for a future project. Here, we mostly disable run condition pushdown when there is an EXCLUDE clause unless the clause is for EXCLUDE CURRENT ROW, uses COUNT(*) (rather than COUNT(ANY)), and the window aggregate has no FILTER clause. Bug: #19533 Reported-by: Qifan Liu <imchifan@163.com> Author: Chengpeng Yan <chengpeng_yan@outlook.com> Author: David Rowley <dgrowleyml@gmail.com> Reviewed-by: Richard Guo <guofenglinux@gmail.com> Reviewed-by: John Naylor <johncnaylorls@gmail.com> Discussion: https://postgr.es/m/19533-413a1014e5d0e766@postgresql.org Backpatch-through: 15 Branch ------ REL_19_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/fcd58c6d9642487eb97e9cf7699daddb3951bc9a Modified Files -------------- src/backend/utils/adt/int8.c | 34 +++++- src/test/regress/expected/window.out | 221 ++++++++++++++++++++++++++++++++++- src/test/regress/sql/window.sql | 115 +++++++++++++++++- 3 files changed, 361 insertions(+), 9 deletions(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Fix COUNT's logic for window run condition support @ 2026-07-07 11:59 David Rowley <drowley@postgresql.org> 0 siblings, 0 replies; 6+ messages in thread From: David Rowley @ 2026-07-07 11:59 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix COUNT's logic for window run condition support 9d9c02ccd added code to allow the executor to stop early when processing WindowAgg nodes where a monotonic window function starts producing values that result in a pushed-down qual no longer matching, and will never match again due to the window function's monotonic properties. That commit requires a SupportRequestWFuncMonotonic to exist on the window function and for it to detect when the function is monotonic. For COUNT(ANY) and COUNT(*), the support function failed to consider some cases where the WindowClause used EXCLUDE to exclude certain rows from being aggregated. Some WindowClause definitions mean we aggregate rows that come after the current row, and when processing those rows later, if we EXCLUDE certain rows, the monotonic property can be broken. Wrongly treating the COUNT(*) or COUNT(ANY) aggregate as monotonic could lead to rows being filtered that should not be filtered from the result set. Another issue was that the support function for the COUNT aggregate mistakenly thought that a WindowClause without an ORDER BY meant that the results would be both monotonically increasing and decreasing, but that's only true when in RANGE mode, where all rows are peers. It is possible to support various cases that do have an EXCLUDE clause, but getting the logic correct for the exact set of cases that are valid is quite complex and would likely better be left for a future project. Here, we mostly disable run condition pushdown when there is an EXCLUDE clause unless the clause is for EXCLUDE CURRENT ROW, uses COUNT(*) (rather than COUNT(ANY)), and the window aggregate has no FILTER clause. Bug: #19533 Reported-by: Qifan Liu <imchifan@163.com> Author: Chengpeng Yan <chengpeng_yan@outlook.com> Author: David Rowley <dgrowleyml@gmail.com> Reviewed-by: Richard Guo <guofenglinux@gmail.com> Reviewed-by: John Naylor <johncnaylorls@gmail.com> Discussion: https://postgr.es/m/19533-413a1014e5d0e766@postgresql.org Backpatch-through: 15 Branch ------ REL_18_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/cf184ec77a04bf164692a0c7bcd148f1e96b71f2 Modified Files -------------- src/backend/utils/adt/int8.c | 36 +++++- src/test/regress/expected/window.out | 221 ++++++++++++++++++++++++++++++++++- src/test/regress/sql/window.sql | 115 +++++++++++++++++- 3 files changed, 362 insertions(+), 10 deletions(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Fix COUNT's logic for window run condition support @ 2026-07-07 11:59 David Rowley <drowley@postgresql.org> 0 siblings, 0 replies; 6+ messages in thread From: David Rowley @ 2026-07-07 11:59 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix COUNT's logic for window run condition support 9d9c02ccd added code to allow the executor to stop early when processing WindowAgg nodes where a monotonic window function starts producing values that result in a pushed-down qual no longer matching, and will never match again due to the window function's monotonic properties. That commit requires a SupportRequestWFuncMonotonic to exist on the window function and for it to detect when the function is monotonic. For COUNT(ANY) and COUNT(*), the support function failed to consider some cases where the WindowClause used EXCLUDE to exclude certain rows from being aggregated. Some WindowClause definitions mean we aggregate rows that come after the current row, and when processing those rows later, if we EXCLUDE certain rows, the monotonic property can be broken. Wrongly treating the COUNT(*) or COUNT(ANY) aggregate as monotonic could lead to rows being filtered that should not be filtered from the result set. Another issue was that the support function for the COUNT aggregate mistakenly thought that a WindowClause without an ORDER BY meant that the results would be both monotonically increasing and decreasing, but that's only true when in RANGE mode, where all rows are peers. It is possible to support various cases that do have an EXCLUDE clause, but getting the logic correct for the exact set of cases that are valid is quite complex and would likely better be left for a future project. Here, we mostly disable run condition pushdown when there is an EXCLUDE clause unless the clause is for EXCLUDE CURRENT ROW, uses COUNT(*) (rather than COUNT(ANY)), and the window aggregate has no FILTER clause. Bug: #19533 Reported-by: Qifan Liu <imchifan@163.com> Author: Chengpeng Yan <chengpeng_yan@outlook.com> Author: David Rowley <dgrowleyml@gmail.com> Reviewed-by: Richard Guo <guofenglinux@gmail.com> Reviewed-by: John Naylor <johncnaylorls@gmail.com> Discussion: https://postgr.es/m/19533-413a1014e5d0e766@postgresql.org Backpatch-through: 15 Branch ------ REL_17_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/be63b285e5006459c1c66d986dd3da27cbcce746 Modified Files -------------- src/backend/utils/adt/int8.c | 36 ++++++- src/test/regress/expected/window.out | 204 ++++++++++++++++++++++++++++++++++- src/test/regress/sql/window.sql | 115 +++++++++++++++++++- 3 files changed, 348 insertions(+), 7 deletions(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Fix COUNT's logic for window run condition support @ 2026-07-07 12:00 David Rowley <drowley@postgresql.org> 0 siblings, 0 replies; 6+ messages in thread From: David Rowley @ 2026-07-07 12:00 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix COUNT's logic for window run condition support 9d9c02ccd added code to allow the executor to stop early when processing WindowAgg nodes where a monotonic window function starts producing values that result in a pushed-down qual no longer matching, and will never match again due to the window function's monotonic properties. That commit requires a SupportRequestWFuncMonotonic to exist on the window function and for it to detect when the function is monotonic. For COUNT(ANY) and COUNT(*), the support function failed to consider some cases where the WindowClause used EXCLUDE to exclude certain rows from being aggregated. Some WindowClause definitions mean we aggregate rows that come after the current row, and when processing those rows later, if we EXCLUDE certain rows, the monotonic property can be broken. Wrongly treating the COUNT(*) or COUNT(ANY) aggregate as monotonic could lead to rows being filtered that should not be filtered from the result set. Another issue was that the support function for the COUNT aggregate mistakenly thought that a WindowClause without an ORDER BY meant that the results would be both monotonically increasing and decreasing, but that's only true when in RANGE mode, where all rows are peers. It is possible to support various cases that do have an EXCLUDE clause, but getting the logic correct for the exact set of cases that are valid is quite complex and would likely better be left for a future project. Here, we mostly disable run condition pushdown when there is an EXCLUDE clause unless the clause is for EXCLUDE CURRENT ROW, uses COUNT(*) (rather than COUNT(ANY)), and the window aggregate has no FILTER clause. Bug: #19533 Reported-by: Qifan Liu <imchifan@163.com> Author: Chengpeng Yan <chengpeng_yan@outlook.com> Author: David Rowley <dgrowleyml@gmail.com> Reviewed-by: Richard Guo <guofenglinux@gmail.com> Reviewed-by: John Naylor <johncnaylorls@gmail.com> Discussion: https://postgr.es/m/19533-413a1014e5d0e766@postgresql.org Backpatch-through: 15 Branch ------ REL_16_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/a857321625ec9ca9ef56a3c8f20a73a7c5dec17d Modified Files -------------- src/backend/utils/adt/int8.c | 34 +++++- src/test/regress/expected/window.out | 204 ++++++++++++++++++++++++++++++++++- src/test/regress/sql/window.sql | 115 +++++++++++++++++++- 3 files changed, 346 insertions(+), 7 deletions(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Fix COUNT's logic for window run condition support @ 2026-07-07 12:01 David Rowley <drowley@postgresql.org> 0 siblings, 0 replies; 6+ messages in thread From: David Rowley @ 2026-07-07 12:01 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix COUNT's logic for window run condition support 9d9c02ccd added code to allow the executor to stop early when processing WindowAgg nodes where a monotonic window function starts producing values that result in a pushed-down qual no longer matching, and will never match again due to the window function's monotonic properties. That commit requires a SupportRequestWFuncMonotonic to exist on the window function and for it to detect when the function is monotonic. For COUNT(ANY) and COUNT(*), the support function failed to consider some cases where the WindowClause used EXCLUDE to exclude certain rows from being aggregated. Some WindowClause definitions mean we aggregate rows that come after the current row, and when processing those rows later, if we EXCLUDE certain rows, the monotonic property can be broken. Wrongly treating the COUNT(*) or COUNT(ANY) aggregate as monotonic could lead to rows being filtered that should not be filtered from the result set. Another issue was that the support function for the COUNT aggregate mistakenly thought that a WindowClause without an ORDER BY meant that the results would be both monotonically increasing and decreasing, but that's only true when in RANGE mode, where all rows are peers. It is possible to support various cases that do have an EXCLUDE clause, but getting the logic correct for the exact set of cases that are valid is quite complex and would likely better be left for a future project. Here, we mostly disable run condition pushdown when there is an EXCLUDE clause unless the clause is for EXCLUDE CURRENT ROW, uses COUNT(*) (rather than COUNT(ANY)), and the window aggregate has no FILTER clause. Bug: #19533 Reported-by: Qifan Liu <imchifan@163.com> Author: Chengpeng Yan <chengpeng_yan@outlook.com> Author: David Rowley <dgrowleyml@gmail.com> Reviewed-by: Richard Guo <guofenglinux@gmail.com> Reviewed-by: John Naylor <johncnaylorls@gmail.com> Discussion: https://postgr.es/m/19533-413a1014e5d0e766@postgresql.org Backpatch-through: 15 Branch ------ REL_15_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/842e34efa7cf851b28c0234fbad857cd8c34fdd8 Modified Files -------------- src/backend/utils/adt/int8.c | 34 +++++- src/test/regress/expected/window.out | 204 ++++++++++++++++++++++++++++++++++- src/test/regress/sql/window.sql | 115 +++++++++++++++++++- 3 files changed, 346 insertions(+), 7 deletions(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2026-07-07 12:01 UTC | newest] Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2026-07-07 11:58 pgsql: Fix COUNT's logic for window run condition support David Rowley <drowley@postgresql.org> 2026-07-07 11:59 pgsql: Fix COUNT's logic for window run condition support David Rowley <drowley@postgresql.org> 2026-07-07 11:59 pgsql: Fix COUNT's logic for window run condition support David Rowley <drowley@postgresql.org> 2026-07-07 11:59 pgsql: Fix COUNT's logic for window run condition support David Rowley <drowley@postgresql.org> 2026-07-07 12:00 pgsql: Fix COUNT's logic for window run condition support David Rowley <drowley@postgresql.org> 2026-07-07 12:01 pgsql: Fix COUNT's logic for window run condition support David Rowley <drowley@postgresql.org>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox