From: Antonin Houska <ah@cybertec.at>
To: Pavel Stehule <pavel.stehule@gmail.com>
cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Subject: Re: More efficient RI checks - take 2
In-reply-to: 
 <CAFj8pRAGtHrJ4YKUpC5YNWrLyyTsxjfucDp5f7tx6CS7po1xDA@mail.gmail.com>
References: <1813.1586363881@antos>
 <CAFj8pRAGtHrJ4YKUpC5YNWrLyyTsxjfucDp5f7tx6CS7po1xDA@mail.gmail.com>
Comments: In-reply-to Pavel Stehule <pavel.stehule@gmail.com>
   message dated "Wed, 08 Apr 2020 19:05:45 +0200."
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Date: Mon, 20 Apr 2020 15:56:35 +0200
Message-ID: <8011.1587390995@antos>
Precedence: bulk

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

>> st 8. 4. 2020 v 18:36 odes=C3=ADlatel Antonin Houska <ah@cybertec.at> na=
psal:
>=20
>>  Some performance comparisons are below. (Besides the execution time, pl=
ease
>>  note the difference in the number of trigger function executions.) In g=
eneral,
>>  the checks are significantly faster if there are many rows to process, =
and a
>>  bit slower when we only need to check a single row. However I'm not sur=
e about
>>  the accuracy if only a single row is measured (if a single row check is
>>  performed several times, the execution time appears to fluctuate).
>=20
> It is hard task to choose good strategy for immediate constraints, but for
> deferred constraints you know how much rows should be checked, and then y=
ou
> can choose better strategy.
>=20
> Is possible to use estimation for choosing method of RI checks?

The exact number of rows ("batch size") is always known before the query is
executed. So one problem to solve is that, when only one row is affected, we
need to convince the planner that the "transient table" really contains a
single row. Otherwise it can, for example, produce a hash join where the ha=
sh
eventually contains a single row.

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