From: Justin Pryzby <pryzby@telsasoft.com>
To: Antonin Houska <ah@cybertec.at>
Cc: pgsql-hackers@postgresql.org, Andres Freund <andres@anarazel.de>
Subject: Re: More efficient RI checks - take 2
Date: Sat, 26 Sep 2020 21:59:17 -0500
Message-ID: <20200927025917.GB13816@telsasoft.com> (raw)
In-Reply-To: <65467.1591370203@antos>
References: <1813.1586363881@antos>
<65467.1591370203@antos>
On Fri, Jun 05, 2020 at 05:16:43PM +0200, Antonin Houska wrote:
> Antonin Houska <ah@cybertec.at> wrote:
>
> > 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.
>
> Attached is a new version that uses the existing simple queries if there's
> only one row to check. SPI is used for both single-row and bulk checks - as
> discussed in this thread, it can perhaps be replaced with a different approach
> if appears to be beneficial, at least for the single-row checks.
>
> I think using a separate query for the single-row check is more practicable
> than convincing the planner that the bulk-check query should only check a
> single row. So this patch version tries to show what it'd look like.
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 ? 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) ?
BTW did you see this older thread ?
https://www.postgresql.org/message-id/flat/CA%2BU5nMLM1DaHBC6JXtUMfcG6f7FgV5mPSpufO7GRnbFKkF2f7g%40m...
--
Justin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: pryzby@telsasoft.com, ah@cybertec.at, andres@anarazel.de
Subject: Re: More efficient RI checks - take 2
In-Reply-To: <20200927025917.GB13816@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox