From: Antonin Houska <ah@cybertec.at>
To: Justin Pryzby <pryzby@telsasoft.com>
cc: pgsql-hackers@postgresql.org, Andres Freund <andres@anarazel.de>
Subject: Re: More efficient RI checks - take 2
In-reply-to: <20200927025917.GB13816@telsasoft.com>
References: <1813.1586363881@antos> <65467.1591370203@antos>
 <20200927025917.GB13816@telsasoft.com>
Comments: In-reply-to Justin Pryzby <pryzby@telsasoft.com>
   message dated "Sat, 26 Sep 2020 21:59:17 -0500."
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <57553.1602652953.1@antos>
Content-Transfer-Encoding: quoted-printable
Date: Wed, 14 Oct 2020 07:22:33 +0200
Message-ID: <57554.1602652953@antos>
Precedence: bulk

Justin Pryzby <pryzby@telsasoft.com> wrote:

> I'm interested in testing this patch, however there's a lot of internals=
 to
> digest.
> =

> Are there any documentation updates or regression tests to add ?

I'm not sure if user documentation should be changed unless a new GUC or
statistics information is added. As for regression tests, perhaps in the n=
ext
version of the patch. But right now I don't know how to implement the feat=
ure
in a less invasive way (see the complaint by Andres in [1]), nor do I have
enough time to work on the patch.

> If FKs support "bulk" validation, users should know when that applies, a=
nd
> be able to check that it's working as intended.  Even if the test cases =
are
> overly verbose or not stable, and not intended for commit, that would be=
 a
> useful temporary addition.
> =

> I think that calls=3D4 indicates this is using bulk validation.
> =

> postgres=3D# begin; explain(analyze, timing off, costs off, summary off,=
 verbose) DELETE FROM t WHERE i<999; rollback;
> BEGIN
>                               QUERY PLAN                               =

> -----------------------------------------------------------------------
>  Delete on public.t (actual rows=3D0 loops=3D1)
>    ->  Index Scan using t_pkey on public.t (actual rows=3D998 loops=3D1)
>          Output: ctid
>          Index Cond: (t.i < 999)
>  Trigger RI_ConstraintTrigger_a_16399 for constraint t_i_fkey: calls=3D4

> I started thinking about this 1+ years ago wondering if a BRIN index cou=
ld be
> used for (bulk) FK validation.
> =

> So I would like to be able to see the *plan* for the query.

> I was able to show the plan and see that BRIN can be used like so:
> |SET auto_explain.log_nested_statements=3Don; SET client_min_messages=3D=
debug; SET auto_explain.log_min_duration=3D0;
> Should the plan be visible in explain (not auto-explain) ?

For development purposes, I thin I could get the plan this way:

SET debug_print_plan TO on;
SET client_min_messages TO debug;

(The plan is cached, so I think the query will only be displayed during th=
e
first execution in the session).

Do you think that the documentation should advise the user to create BRIN
index on the FK table?

> BTW did you see this older thread ?
> https://www.postgresql.org/message-id/flat/CA%2BU5nMLM1DaHBC6JXtUMfcG6f7=
FgV5mPSpufO7GRnbFKkF2f7g%40mail.gmail.com

Not yet. Thanks.

[1] https://www.postgresql.org/message-id/20200630011729.mr25bmmbvsattxe2%=
40alap3.anarazel.de

-- =

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