pg.ddx.io pgsql-committers@postgresql.org mailing list archivehelp / color / mirror / Atom feed
pgsql: Ignore nullingrels when looking up statistics 3+ messages / 1 participants [nested] [flat]
* pgsql: Ignore nullingrels when looking up statistics @ 2025-01-02 09:07 Richard Guo <rguo@postgresql.org> 0 siblings, 0 replies; 3+ messages in thread From: Richard Guo @ 2025-01-02 09:07 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Ignore nullingrels when looking up statistics When looking up statistical data about an expression, we do not need to concern ourselves with the outer joins that could null the Vars/PHVs contained in the expression. Accounting for nullingrels in the expression could cause estimate_num_groups to count the same Var multiple times if it's marked with different nullingrels. This is incorrect, and could lead to "ERROR: corrupt MVNDistinct entry" when searching for multivariate n-distinct. Furthermore, the nullingrels could prevent us from matching an expression to expressional index columns or to the expressions in extended statistics, leading to inaccurate estimates. To fix, strip out all the nullingrels from the expression before we look up statistical data about it. There is one ensuing plan change in the regression tests, but it looks reasonable and does not compromise its original purpose. This patch could result in plan changes, but it fixes an actual bug, so back-patch to v16 where the outer-join-aware-Var infrastructure was introduced. Author: Richard Guo Discussion: https://postgr.es/m/CAMbWs4-2Z4k+nFTiZe0Qbu5n8juUWenDAtMzi98bAZQtwHx0-w@mail.gmail.com Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/e28033fe1af8037e0fec8bb3a32fabbe18ac06b1 Modified Files -------------- src/backend/utils/adt/selfuncs.c | 29 ++++++++++++++++++++++++++--- src/test/regress/expected/join.out | 26 ++++++++++++++++++++++++-- src/test/regress/sql/join.sql | 13 +++++++++++++ 3 files changed, 63 insertions(+), 5 deletions(-) ^ permalink raw reply [nested|flat] 3+ messages in thread
* pgsql: Ignore nullingrels when looking up statistics @ 2025-01-02 09:08 Richard Guo <rguo@postgresql.org> 0 siblings, 0 replies; 3+ messages in thread From: Richard Guo @ 2025-01-02 09:08 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Ignore nullingrels when looking up statistics When looking up statistical data about an expression, we do not need to concern ourselves with the outer joins that could null the Vars/PHVs contained in the expression. Accounting for nullingrels in the expression could cause estimate_num_groups to count the same Var multiple times if it's marked with different nullingrels. This is incorrect, and could lead to "ERROR: corrupt MVNDistinct entry" when searching for multivariate n-distinct. Furthermore, the nullingrels could prevent us from matching an expression to expressional index columns or to the expressions in extended statistics, leading to inaccurate estimates. To fix, strip out all the nullingrels from the expression before we look up statistical data about it. There is one ensuing plan change in the regression tests, but it looks reasonable and does not compromise its original purpose. This patch could result in plan changes, but it fixes an actual bug, so back-patch to v16 where the outer-join-aware-Var infrastructure was introduced. Author: Richard Guo Discussion: https://postgr.es/m/CAMbWs4-2Z4k+nFTiZe0Qbu5n8juUWenDAtMzi98bAZQtwHx0-w@mail.gmail.com Branch ------ REL_17_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/297b280abd7a2e57263cf786a3dd5584cb7e0892 Modified Files -------------- src/backend/utils/adt/selfuncs.c | 29 ++++++++++++++++++++++++++--- src/test/regress/expected/join.out | 26 ++++++++++++++++++++++++-- src/test/regress/sql/join.sql | 13 +++++++++++++ 3 files changed, 63 insertions(+), 5 deletions(-) ^ permalink raw reply [nested|flat] 3+ messages in thread
* pgsql: Ignore nullingrels when looking up statistics @ 2025-01-02 09:08 Richard Guo <rguo@postgresql.org> 0 siblings, 0 replies; 3+ messages in thread From: Richard Guo @ 2025-01-02 09:08 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Ignore nullingrels when looking up statistics When looking up statistical data about an expression, we do not need to concern ourselves with the outer joins that could null the Vars/PHVs contained in the expression. Accounting for nullingrels in the expression could cause estimate_num_groups to count the same Var multiple times if it's marked with different nullingrels. This is incorrect, and could lead to "ERROR: corrupt MVNDistinct entry" when searching for multivariate n-distinct. Furthermore, the nullingrels could prevent us from matching an expression to expressional index columns or to the expressions in extended statistics, leading to inaccurate estimates. To fix, strip out all the nullingrels from the expression before we look up statistical data about it. There is one ensuing plan change in the regression tests, but it looks reasonable and does not compromise its original purpose. This patch could result in plan changes, but it fixes an actual bug, so back-patch to v16 where the outer-join-aware-Var infrastructure was introduced. Author: Richard Guo Discussion: https://postgr.es/m/CAMbWs4-2Z4k+nFTiZe0Qbu5n8juUWenDAtMzi98bAZQtwHx0-w@mail.gmail.com Branch ------ REL_16_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/a1a9120c797d689be1dafd6fac8d956e3f667ba6 Modified Files -------------- src/backend/utils/adt/selfuncs.c | 28 +++++++++++++++++++++++++--- src/test/regress/expected/join.out | 26 ++++++++++++++++++++++++-- src/test/regress/sql/join.sql | 13 +++++++++++++ 3 files changed, 62 insertions(+), 5 deletions(-) ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-01-02 09:08 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-01-02 09:07 pgsql: Ignore nullingrels when looking up statistics Richard Guo <rguo@postgresql.org> 2025-01-02 09:08 pgsql: Ignore nullingrels when looking up statistics Richard Guo <rguo@postgresql.org> 2025-01-02 09:08 pgsql: Ignore nullingrels when looking up statistics Richard Guo <rguo@postgresql.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox