pg.ddx.io pgsql-committers@postgresql.org mailing list archive
help / color / mirror / Atom feedpgsql: Ignore PlaceHolderVars when looking up statistics
6+ messages / 4 participants
[nested] [flat]
* pgsql: Ignore PlaceHolderVars when looking up statistics
@ 2025-12-29 02:41 Richard Guo <rguo@postgresql.org>
0 siblings, 0 replies; 6+ messages in thread
From: Richard Guo @ 2025-12-29 02:41 UTC (permalink / raw)
To: pgsql-committers@lists.postgresql.org
Ignore PlaceHolderVars when looking up statistics
When looking up statistical data about an expression, we failed to
look through PlaceHolderVar nodes, treating them as opaque. This
could prevent us from matching an expression to base columns, index
expressions, or extended statistics, as examine_variable() relies on
strict structural matching.
As a result, queries involving PlaceHolderVar nodes often fell back to
default selectivity estimates, potentially leading to poor plan
choices.
This patch updates examine_variable() to strip PlaceHolderVars before
analysis. This is safe during estimation because PlaceHolderVars are
transparent for the purpose of statistics lookup: they do not alter
the value distribution of the underlying expression.
To minimize performance overhead on this hot path, a lightweight
walker first checks for the presence of PlaceHolderVars. The more
expensive mutator is invoked only when necessary.
There is one ensuing plan change in the regression tests, which is
expected and demonstrates the fix: the rowcount estimate becomes much
more accurate with this patch.
Back-patch to v18. Although this issue exists before that, changes in
this version made it common enough to notice. Given the lack of field
reports for older versions, I am not back-patching further.
Reported-by: Haowu Ge <gehaowu@bitmoe.com>
Author: Richard Guo <guofenglinux@gmail.com>
Discussion: https://postgr.es/m/62af586c-c270-44f3-9c5e-02c81d537e3d.gehaowu@bitmoe.com
Backpatch-through: 18
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/559f9e90dbbd5d72b1da802703317913280c5080
Modified Files
--------------
src/backend/utils/adt/selfuncs.c | 93 +++++++++++++++++++++++++++++++++-----
src/test/regress/expected/join.out | 27 ++++++++++-
src/test/regress/sql/join.sql | 10 ++++
3 files changed, 116 insertions(+), 14 deletions(-)
^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Ignore PlaceHolderVars when looking up statistics
@ 2025-12-29 02:45 Richard Guo <rguo@postgresql.org>
2026-01-02 20:46 ` Re: pgsql: Ignore PlaceHolderVars when looking up statistics Robert Haas <robertmhaas@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Richard Guo @ 2025-12-29 02:45 UTC (permalink / raw)
To: pgsql-committers@lists.postgresql.org
Ignore PlaceHolderVars when looking up statistics
When looking up statistical data about an expression, we failed to
look through PlaceHolderVar nodes, treating them as opaque. This
could prevent us from matching an expression to base columns, index
expressions, or extended statistics, as examine_variable() relies on
strict structural matching.
As a result, queries involving PlaceHolderVar nodes often fell back to
default selectivity estimates, potentially leading to poor plan
choices.
This patch updates examine_variable() to strip PlaceHolderVars before
analysis. This is safe during estimation because PlaceHolderVars are
transparent for the purpose of statistics lookup: they do not alter
the value distribution of the underlying expression.
To minimize performance overhead on this hot path, a lightweight
walker first checks for the presence of PlaceHolderVars. The more
expensive mutator is invoked only when necessary.
There is one ensuing plan change in the regression tests, which is
expected and demonstrates the fix: the rowcount estimate becomes much
more accurate with this patch.
Back-patch to v18. Although this issue exists before that, changes in
this version made it common enough to notice. Given the lack of field
reports for older versions, I am not back-patching further.
Reported-by: Haowu Ge <gehaowu@bitmoe.com>
Author: Richard Guo <guofenglinux@gmail.com>
Discussion: https://postgr.es/m/62af586c-c270-44f3-9c5e-02c81d537e3d.gehaowu@bitmoe.com
Backpatch-through: 18
Branch
------
REL_18_STABLE
Details
-------
https://git.postgresql.org/pg/commitdiff/7e9f852a79fe19d4d0f18aabc32a620797fb676e
Modified Files
--------------
src/backend/utils/adt/selfuncs.c | 93 +++++++++++++++++++++++++++++++++-----
src/test/regress/expected/join.out | 27 ++++++++++-
src/test/regress/sql/join.sql | 10 ++++
3 files changed, 116 insertions(+), 14 deletions(-)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pgsql: Ignore PlaceHolderVars when looking up statistics
2025-12-29 02:45 pgsql: Ignore PlaceHolderVars when looking up statistics Richard Guo <rguo@postgresql.org>
@ 2026-01-02 20:46 ` Robert Haas <robertmhaas@gmail.com>
2026-01-02 21:20 ` Re: pgsql: Ignore PlaceHolderVars when looking up statistics Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Robert Haas @ 2026-01-02 20:46 UTC (permalink / raw)
To: Richard Guo <rguo@postgresql.org>; +Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
On Sun, Dec 28, 2025 at 9:45 PM Richard Guo <rguo@postgresql.org> wrote:
> Ignore PlaceHolderVars when looking up statistics
>
> Back-patch to v18. Although this issue exists before that, changes in
> this version made it common enough to notice. Given the lack of field
> reports for older versions, I am not back-patching further.
Generally we don't back-patch fixes that could change plans, because
it tends to produce user complaints. It's maybe more justifiable in
this case because v18 is quite new and you didn't back-patch to older
releases, but are we sure that it's warranted even there?
--
Robert Haas
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pgsql: Ignore PlaceHolderVars when looking up statistics
2025-12-29 02:45 pgsql: Ignore PlaceHolderVars when looking up statistics Richard Guo <rguo@postgresql.org>
2026-01-02 20:46 ` Re: pgsql: Ignore PlaceHolderVars when looking up statistics Robert Haas <robertmhaas@gmail.com>
@ 2026-01-02 21:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-01-02 23:32 ` Re: pgsql: Ignore PlaceHolderVars when looking up statistics Robert Haas <robertmhaas@gmail.com>
2026-01-05 00:52 ` Re: pgsql: Ignore PlaceHolderVars when looking up statistics Richard Guo <guofenglinux@gmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: Tom Lane @ 2026-01-02 21:20 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: Richard Guo <rguo@postgresql.org>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Robert Haas <robertmhaas@gmail.com> writes:
> On Sun, Dec 28, 2025 at 9:45 PM Richard Guo <rguo@postgresql.org> wrote:
>> Back-patch to v18. Although this issue exists before that, changes in
>> this version made it common enough to notice. Given the lack of field
>> reports for older versions, I am not back-patching further.
> Generally we don't back-patch fixes that could change plans, because
> it tends to produce user complaints. It's maybe more justifiable in
> this case because v18 is quite new and you didn't back-patch to older
> releases, but are we sure that it's warranted even there?
It's a regression if we don't. See nearby complaint at
https://www.postgresql.org/message-id/flat/b75866aa-bb54-456b-8f88-6b5bc52064ca%40app.fastmail.com
That case was correctly handled in v17 and before.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pgsql: Ignore PlaceHolderVars when looking up statistics
2025-12-29 02:45 pgsql: Ignore PlaceHolderVars when looking up statistics Richard Guo <rguo@postgresql.org>
2026-01-02 20:46 ` Re: pgsql: Ignore PlaceHolderVars when looking up statistics Robert Haas <robertmhaas@gmail.com>
2026-01-02 21:20 ` Re: pgsql: Ignore PlaceHolderVars when looking up statistics Tom Lane <tgl@sss.pgh.pa.us>
@ 2026-01-02 23:32 ` Robert Haas <robertmhaas@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Robert Haas @ 2026-01-02 23:32 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Richard Guo <rguo@postgresql.org>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
On Fri, Jan 2, 2026 at 4:20 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> It's a regression if we don't. See nearby complaint at
>
> https://www.postgresql.org/message-id/flat/b75866aa-bb54-456b-8f88-6b5bc52064ca%40app.fastmail.com
>
> That case was correctly handled in v17 and before.
Oh, OK. Probably makes sense then, since v18 is new.
--
Robert Haas
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pgsql: Ignore PlaceHolderVars when looking up statistics
2025-12-29 02:45 pgsql: Ignore PlaceHolderVars when looking up statistics Richard Guo <rguo@postgresql.org>
2026-01-02 20:46 ` Re: pgsql: Ignore PlaceHolderVars when looking up statistics Robert Haas <robertmhaas@gmail.com>
2026-01-02 21:20 ` Re: pgsql: Ignore PlaceHolderVars when looking up statistics Tom Lane <tgl@sss.pgh.pa.us>
@ 2026-01-05 00:52 ` Richard Guo <guofenglinux@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Richard Guo @ 2026-01-05 00:52 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Robert Haas <robertmhaas@gmail.com>; Richard Guo <rguo@postgresql.org>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
On Sat, Jan 3, 2026 at 6:20 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
> > Generally we don't back-patch fixes that could change plans, because
> > it tends to produce user complaints. It's maybe more justifiable in
> > this case because v18 is quite new and you didn't back-patch to older
> > releases, but are we sure that it's warranted even there?
> It's a regression if we don't.
That's right. I was actually of two minds for a while regarding the
back-patch, but in the end decided to proceed. You can find the
discussions and the reasoning behind this decision in the link below.
https://postgr.es/m/CAMbWs49h5F66KZwLxaeXoLwHe_9jAB7Eu44UmJhhQpLA38tKhw@mail.gmail.com
- Richard
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2026-01-05 00:52 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-12-29 02:41 pgsql: Ignore PlaceHolderVars when looking up statistics Richard Guo <rguo@postgresql.org>
2025-12-29 02:45 pgsql: Ignore PlaceHolderVars when looking up statistics Richard Guo <rguo@postgresql.org>
2026-01-02 20:46 ` Robert Haas <robertmhaas@gmail.com>
2026-01-02 21:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-01-02 23:32 ` Robert Haas <robertmhaas@gmail.com>
2026-01-05 00:52 ` Richard Guo <guofenglinux@gmail.com>
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