pg.ddx.io  pgsql-committers@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: David Rowley <drowley@postgresql.org>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Fix incorrect row estimates used for Memoize costing
Date: Mon, 16 May 2022 04:08:26 +0000
Message-ID: <E1nqS1x-000waC-Pa@gemulon.postgresql.org> (raw)

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(-)



view thread (2+ messages)  latest in thread

Message-ID: <E1nqS1x-000waC-Pa@gemulon.postgresql.org>
Permalink:  ../E1nqS1x-000waC-Pa@gemulon.postgresql.org/
Also on:    postgresql.org/message-id/E1nqS1x-000waC-Pa@gemulon.postgresql.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-committers@postgresql.org
  Cc: drowley@postgresql.org
  Subject: Re: pgsql: Fix incorrect row estimates used for Memoize costing
  In-Reply-To: <E1nqS1x-000waC-Pa@gemulon.postgresql.org>
  References: <E1nqS1x-000waC-Pa@gemulon.postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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