Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 13.4 \(3608.120.23.2.4\))
Subject: Re: Understanding bad estimate (related to FKs?)
From: Philip Semanchuk <philip@americanefficient.com>
In-Reply-To: <20201026170405.GT9241@telsasoft.com>
Date: Mon, 26 Oct 2020 13:14:12 -0400
Cc: pgsql-performance@postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <DF40D416-6F79-4912-9306-F012D0BED09A@americanefficient.com>
References: <7AC7104E-E233-4CCA-B99E-12197D61A4DD@americanefficient.com>
 <20201026170405.GT9241@telsasoft.com>
To: Justin Pryzby <pryzby@telsasoft.com>
Precedence: bulk


> On Oct 26, 2020, at 1:04 PM, Justin Pryzby <pryzby@telsasoft.com> =
wrote:
>=20
> 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
>=20
> What postgres version ?
> Since 9.6(?) FKs affect estimates.

We=E2=80=99re using 11.6 (under AWS Aurora).


>=20
>> 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.
>>=20
>> The first is Postgres' estimate. The condition for this index scan =
contains three expressions --
>>=20
>> (five_uniform =3D zulu_five.five_uniform) AND
>> (whiskey_mike =3D juliet_india.whiskey_mike) AND
>> (bravo =3D 'mike'::text)
>>=20
>> 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.
>>=20
>> For the third expression (bravo =3D '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%).
>>=20
>> =46rom 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?
>>=20
>> 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 =3D 'mike'. EXPLAIN reports the actual number of rows =
returned as 896*1062 ~=3D 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?