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: 
 <CAHOFxGpcN+=Vo10=2roxOOm8UXu8zstdaggRq0vvu00wcC6YjQ@mail.gmail.com>
Date: Mon, 26 Oct 2020 14:55:46 -0400
Cc: Justin Pryzby <pryzby@telsasoft.com>,
 postgres performance list <pgsql-performance@postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <553742C5-85E6-48C1-997F-3BFE2FD81FC4@americanefficient.com>
References: <7AC7104E-E233-4CCA-B99E-12197D61A4DD@americanefficient.com>
 <20201026170405.GT9241@telsasoft.com>
 <DF40D416-6F79-4912-9306-F012D0BED09A@americanefficient.com>
 <CAHOFxGpcN+=Vo10=2roxOOm8UXu8zstdaggRq0vvu00wcC6YjQ@mail.gmail.com>
To: Michael Lewis <mlewis@entrata.com>
Precedence: bulk


> On Oct 26, 2020, at 1:20 PM, Michael Lewis <mlewis@entrata.com> wrote:
>=20
> On Mon, Oct 26, 2020 at 11:14 AM Philip Semanchuk =
<philip@americanefficient.com> wrote:
> >> 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
> Are the columns correlated? Have you tried to create extended =
statistics and see if the estimate changes? I believe that extended =
stats will not directly help with joins though, only group bys and =
perhaps choosing an index scan vs table scan when comparing the =
correlated columns to static values rather than joining up tables. =
Wouldn't be much effort to try it though.


There=E2=80=99s not a lot of correlation between whiskey_mike and bravo =
--
stxkind     stxndistinct    stxdependencies
['d', 'f']  {"7, 12": 42}   {"12 =3D> 7": 0.000274}

Those stats didn=E2=80=99t help the planner.=20

I should have mentioned that five_uniform has ~63k unique values, =
whereas whiskey_mike has only 3, and bravo only 19.

Cheers
Philip=