pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  

help / color / mirror / Atom feed

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
Date: Mon, 20 Apr 2020 15:56:35 +0200
Message-ID: <8011.1587390995@antos> (raw)
In-Reply-To: <CAFj8pRAGtHrJ4YKUpC5YNWrLyyTsxjfucDp5f7tx6CS7po1xDA@mail.gmail.com>
References: <1813.1586363881@antos>
	<CAFj8pRAGtHrJ4YKUpC5YNWrLyyTsxjfucDp5f7tx6CS7po1xDA@mail.gmail.com>

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

>> st 8. 4. 2020 v 18:36 odesílatel Antonin Houska <ah@cybertec.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

view thread (35+ messages)  latest in thread

Message-ID: <8011.1587390995@antos>
Permalink:  ../8011.1587390995@antos/
Also on:    postgresql.org/message-id/8011.1587390995@antos

reply

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: ah@cybertec.at, pavel.stehule@gmail.com
  Subject: Re: More efficient RI checks - take 2
  In-Reply-To: <8011.1587390995@antos>

* 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