pg.ddx.io  pgsql-committers@postgresql.org mailing list archive  
help / color / mirror / Atom feed
pgsql: Fix incorrect row estimates used for Memoize costing
2+ messages / 1 participants
[nested] [flat]

* pgsql: Fix incorrect row estimates used for Memoize costing
@ 2022-05-16 04:08 David Rowley <drowley@postgresql.org>
  0 siblings, 0 replies; 2+ messages in thread

From: David Rowley @ 2022-05-16 04:08 UTC (permalink / raw)
  To: pgsql-committers@lists.postgresql.org

Fix incorrect row estimates used for Memoize costing

In order to estimate the cache hit ratio of a Memoize node, one of the
inputs we require is the estimated number of times the Memoize node will
be rescanned.  The higher this number, the large the cache hit ratio is
likely to become.  Unfortunately, the value being passed as the number of
"calls" to the Memoize was incorrectly using the Nested Loop's
outer_path->parent->rows instead of outer_path->rows.  This failed to
account for the fact that the outer_path might be parameterized by some
upper-level Nested Loop.

This problem could lead to Memoize plans appearing more favorable than
they might actually be.  It could also lead to extended executor startup
times when work_mem values were large due to the planner setting overly
large MemoizePath->est_entries resulting in the Memoize hash table being
initially made much larger than might be required.

Fix this simply by passing outer_path->rows rather than
outer_path->parent->rows.  Also, adjust the expected regression test
output for a plan change.

Reported-by: Pavel Stehule
Author: David Rowley
Discussion: https://postgr.es/m/CAFj8pRAMp%3DQsMi6sPQJ4W3hczoFJRvyXHJV3AZAZaMyTVM312Q%40mail.gmail.com
Backpatch-through: 14, where Memoize was introduced

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/1e731ed12aac3f2ed99cf13244f5a1571a6eb6e6

Modified Files
--------------
src/backend/optimizer/path/joinpath.c |  2 +-
src/test/regress/expected/join.out    | 26 ++++++++++----------------
2 files changed, 11 insertions(+), 17 deletions(-)



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* pgsql: Fix incorrect row estimates used for Memoize costing
@ 2022-05-16 04:09 David Rowley <drowley@postgresql.org>
  0 siblings, 0 replies; 2+ messages in thread

From: David Rowley @ 2022-05-16 04:09 UTC (permalink / raw)
  To: pgsql-committers@lists.postgresql.org

Fix incorrect row estimates used for Memoize costing

In order to estimate the cache hit ratio of a Memoize node, one of the
inputs we require is the estimated number of times the Memoize node will
be rescanned.  The higher this number, the large the cache hit ratio is
likely to become.  Unfortunately, the value being passed as the number of
"calls" to the Memoize was incorrectly using the Nested Loop's
outer_path->parent->rows instead of outer_path->rows.  This failed to
account for the fact that the outer_path might be parameterized by some
upper-level Nested Loop.

This problem could lead to Memoize plans appearing more favorable than
they might actually be.  It could also lead to extended executor startup
times when work_mem values were large due to the planner setting overly
large MemoizePath->est_entries resulting in the Memoize hash table being
initially made much larger than might be required.

Fix this simply by passing outer_path->rows rather than
outer_path->parent->rows.  Also, adjust the expected regression test
output for a plan change.

Reported-by: Pavel Stehule
Author: David Rowley
Discussion: https://postgr.es/m/CAFj8pRAMp%3DQsMi6sPQJ4W3hczoFJRvyXHJV3AZAZaMyTVM312Q%40mail.gmail.com
Backpatch-through: 14, where Memoize was introduced

Branch
------
REL_14_STABLE

Details
-------
https://git.postgresql.org/pg/commitdiff/23c2b76a8322233ce0b2c135c10ad52d48a4a22b

Modified Files
--------------
src/backend/optimizer/path/joinpath.c |  2 +-
src/test/regress/expected/join.out    | 26 ++++++++++----------------
2 files changed, 11 insertions(+), 17 deletions(-)



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2022-05-16 04:09 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-05-16 04:08 pgsql: Fix incorrect row estimates used for Memoize costing David Rowley <drowley@postgresql.org>
2022-05-16 04:09 pgsql: Fix incorrect row estimates used for Memoize costing David Rowley <drowley@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