From: Justin Pryzby <pryzby@telsasoft.com>
To: Philip Semanchuk <philip@americanefficient.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Understanding bad estimate (related to FKs?)
Date: Mon, 26 Oct 2020 12:04:05 -0500
Message-ID: <20201026170405.GT9241@telsasoft.com> (raw)
In-Reply-To: <7AC7104E-E233-4CCA-B99E-12197D61A4DD@americanefficient.com>
References: <7AC7104E-E233-4CCA-B99E-12197D61A4DD@americanefficient.com>
On Mon, Oct 26, 2020 at 12:50:38PM -0400, Philip Semanchuk wrote:
> I'm trying to understand a bad estimate by the planner, and what I can do about it. The anonymized plan is here: https://explain.depesz.com/s/0MDz
What postgres version ?
Since 9.6(?) FKs affect estimates.
> The item I'm focused on is node 23. The estimate is for 7 rows, actual is 896 (multiplied by 1062 loops). I'm confused about two things in this node.
>
> The first is Postgres' estimate. The condition for this index scan contains three expressions --
>
> (five_uniform = zulu_five.five_uniform) AND
> (whiskey_mike = juliet_india.whiskey_mike) AND
> (bravo = 'mike'::text)
>
> The columns in the first two expressions (five_uniform and whiskey_mike) are NOT NULL, and have foreign key constraints to their respective tables (zulu_five.five_uniform and juliet_india.whiskey_mike). The planner can know in advance that 100% of the rows in the table will satisfy those criteria.
>
> For the third expression (bravo = 'mike'), Postgres has excellent statistics. The estimated frequency of 'mike' is 2.228%, actual frequency is 2.242%, so Postgres' estimate is only off by a tiny amount (0.014%).
>
> From what I understand, the planner has all the information it needs to make a very accurate estimate here, but it's off by quite a lot. What information am I failing to give to the planner?
>
> My second point of confusion is related. There are 564,071 rows in the source table (xray_india, aliased as papa) that satisfy the condition bravo = 'mike'. EXPLAIN reports the actual number of rows returned as 896*1062 ~= 951,552. I understand that the number reported by EXPLAIN is often a bit bigger, but this discrepancy is much larger than I'm expecting. What am I missing here?
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pryzby@telsasoft.com, philip@americanefficient.com
Subject: Re: Understanding bad estimate (related to FKs?)
In-Reply-To: <20201026170405.GT9241@telsasoft.com>
* 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