Re: More efficient RI checks - take 2

Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:

>> st 8. 4. 2020 v 18:36 odesílatel Antonin Houska <ah(at)cybertec(dot)at> napsal:
>
>> Some performance comparisons are below. (Besides the execution time, please
>> note the difference in the number of trigger function executions.) In general,
>> 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 sure 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).
>
> 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 you
> can choose better strategy.
>
> 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 hash
eventually contains a single row.

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