Re: More efficient RI checks - take 2

Justin Pryzby <pryzby(at)telsasoft(dot)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 next
version of the patch. But right now I don't know how to implement the feature
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, and
> 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=4 indicates this is using bulk validation.
>
> postgres=# 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=0 loops=1)
> -> Index Scan using t_pkey on public.t (actual rows=998 loops=1)
> Output: ctid
> Index Cond: (t.i < 999)
> Trigger RI_ConstraintTrigger_a_16399 for constraint t_i_fkey: calls=4

> I started thinking about this 1+ years ago wondering if a BRIN index could 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=on; SET client_min_messages=debug; SET auto_explain.log_min_duration=0;
> 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 the
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%2BU5nMLM1DaHBC6JXtUMfcG6f7FgV5mPSpufO7GRnbFKkF2f7g%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