From: Antonin Houska <ah@cybertec.at>
To: Pavel Stehule <pavel.stehule@gmail.com>
cc: Tom Lane <tgl@sss.pgh.pa.us>, Robert Haas <robertmhaas@gmail.com>,
    Andres Freund <andres@anarazel.de>,
    Alvaro Herrera <alvherre@2ndquadrant.com>,
    Corey Huinker <corey.huinker@gmail.com>,
    PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Subject: Re: More efficient RI checks - take 2
In-reply-to: 
 <CAFj8pRBNKrTYZLQt6qd09ikm4ZF+XQB4R+bFHHag7cuCXrOL0A@mail.gmail.com>
References: <20200422154231.6shz4kdor4yb5w5b@alap3.anarazel.de>
 <20200422171806.GA12435@alvherre.pgsql>
 <CA+TgmoZF_fkSYTcY7=yiRr7MxC=yt1VoZPMcYfwsRYw6TusEmA@mail.gmail.com>
 <20200422183600.tpl5745dfbnozi6t@alap3.anarazel.de>
 <CA+Tgmobb2_sm8PZ16_dqevUktcjT8OnZ5xvv=AFtxWWjf28VxA@mail.gmail.com>
 <6442.1587595207@sss.pgh.pa.us> <28826.1587618482@antos>
 <CAFj8pRBNKrTYZLQt6qd09ikm4ZF+XQB4R+bFHHag7cuCXrOL0A@mail.gmail.com>
Comments: In-reply-to Pavel Stehule <pavel.stehule@gmail.com>
   message dated "Thu, 23 Apr 2020 07:13:40 +0200."
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Date: Thu, 23 Apr 2020 08:29:33 +0200
Message-ID: <29560.1587623373@antos>
Precedence: bulk

Pavel Stehule <pavel.stehule@gmail.com> wrote:

> =C4=8Dt 23. 4. 2020 v 7:06 odes=C3=ADlatel Antonin Houska <ah@cybertec.at=
> napsal:
>=20
>  Tom Lane <tgl@sss.pgh.pa.us> wrote:
>=20
>  > But it's not entirely clear to me that we know the best plan for a
>  > statement-level RI action with sufficient certainty to go that way.
>  > Is it really the case that the plan would not vary based on how
>  > many tuples there are to check, for example?
>=20
>  I'm concerned about that too. With my patch the checks become a bit slow=
er if
>  only a single row is processed. The problem seems to be that the planner=
 is
>  not entirely convinced about that the number of input rows, so it can st=
ill
>  build a plan that expects many rows. For example (as I mentioned elsewhe=
re in
>  the thread), a hash join where the hash table only contains one tuple. Or
>  similarly a sort node for a single input tuple.
>=20
> without statistics the planner expect about 2000 rows table , no?

I think that at some point it estimates the number of rows from the number =
of
table pages, but I don't remember details.

I wanted to say that if we constructed the plan "manually", we'd need at le=
ast
two substantially different variants: one to check many rows and the other =
to
check a single row.

--=20
Antonin Houska
Web: https://www.cybertec-postgresql.com