From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Rowley <dgrowleyml@gmail.com>
cc: "Ehrenreich, Sigrid" <Ehrenreich@consist.de>,
        "pgsql-performance@lists.postgresql.org"
 <pgsql-performance@lists.postgresql.org>
Subject: Re: Postgres Optimizer ignores information about foreign key
 relationship, severly misestimating number of returned rows in join
In-reply-to: 
 <CAApHDvqh4wSDOD+UVZ2xdaymu_-6rvkxjdcTQHOVAT-RTCdzog@mail.gmail.com>
References: 
 <AM6PR02MB5287A0ADD936C1FA80973E72AB190@AM6PR02MB5287.eurprd02.prod.outlook.com>
 <CAApHDvqh4wSDOD+UVZ2xdaymu_-6rvkxjdcTQHOVAT-RTCdzog@mail.gmail.com>
Comments: In-reply-to David Rowley <dgrowleyml@gmail.com>
	message dated "Tue, 27 Oct 2020 11:25:45 +1300"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <807003.1603752841.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Mon, 26 Oct 2020 18:54:01 -0400
Message-ID: <807004.1603752841@sss.pgh.pa.us>
Precedence: bulk

David Rowley <dgrowleyml@gmail.com> writes:
> On Tue, 27 Oct 2020 at 06:54, Ehrenreich, Sigrid <Ehrenreich@consist.de>=
 wrote:
>> ->  Hash Join  (cost=3D226.27..423.82 rows=3D115 width=3D0) (actual tim=
e=3D3.150..7.511 rows=3D3344 loops=3D1)   <=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
 With the FK, the estimation should be 3344, but it is 115 rows

> I'd have expected this to find the foreign key and have the join
> selectivity of 1.0, but I see it does not due to the fact that one of
> the EquivalenceClass has a constant due to the fact.low_card =3D 1 qual.

Right.

> I feel like we could probably do better there and perhaps somehow
> count ECs with ec_has_const as matched, but there seems to be some
> assumptions later in get_foreign_key_join_selectivity() where we
> determine the selectivity based on the base rel's tuple count.  We'd
> need to account for how many rows remainder after filtering the ECs
> with ec_has_const =3D=3D true, else we'd be doing the wrong thing.  That
> needs more thought than I have time for right now.

Yeah, I'm fooling with a patch for that now.  The basic problem is
that the selectivity of the x =3D constant clauses has already been
factored into the sizes of both join input relations, so we're
double-counting it if we just apply the existing FK-based
selectivity estimate.  I think though that we can recover the
selectivity associated with that qual on the FK side (or should
it be the PK side?) and cancel it out of the FK selectivity.

			regards, tom lane