Re: REVIEW: Optimize referential integrity checks (todo item)

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>
Cc: Vik Reykja <vikreykja(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: REVIEW: Optimize referential integrity checks (todo item)
Date: 2012-06-20 00:19:58
Message-ID: 11672.1340151598@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> writes:
> On 12 February 2012 02:06, Vik Reykja <vikreykja(at)gmail(dot)com> wrote:
>> I decided to take a crack at the todo item created from the following post:
>> http://archives.postgresql.org/pgsql-performance/2005-10/msg00458.php

> Here's my review of this patch.

I've marked this patch committed, although in the end there was nothing
left of it ;-). After teaching the trigger skip logic that old PK nulls
or new FK nulls mean the constraint needn't be checked, there is no case
where ri_KeysEqual is called on data that is not known to be
all-non-null on one side or the other. So it doesn't matter whether
we use plain equality or is-not-distinct logic. We could have applied
these changes anyway but I didn't see much value, since as previously
noted there would be some cases where the comparison got microscopically
slower. I did add a comment about the point in case anybody revisits
the code again in future.

> There's also a separate question about whether the RI trigger
> functions need to be doing these key comparisons, given that they are
> done earlier when the triggers are queued
> (http://archives.postgresql.org/pgsql-performance/2005-10/msg00459.php)
> but the savings to be made there are likely to be smaller than the
> savings this patch makes by not queuing the triggers at all.

I haven't looked into this question. It would only matter for PK
updates (since the FK-side triggers make no such comparisons), so it's
probably not going to make much difference in typical workloads.
Still, if anybody wants to investigate ...

regards, tom lane

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Robert Haas 2012-06-20 00:35:59 Re: [PATCH 10/16] Introduce the concept that wal has a 'origin' node
Previous Message Josh Berkus 2012-06-19 23:33:32 Re: Backport of fsync queue compaction