Re: REVIEW: Optimize referential integrity checks (todo item) - Mailing list pgsql-hackers

From Tom Lane
Subject Re: REVIEW: Optimize referential integrity checks (todo item)
Date
Msg-id 11672.1340151598@sss.pgh.pa.us
Whole thread Raw
In response to REVIEW: Optimize referential integrity checks (todo item)  (Dean Rasheed <dean.a.rasheed@gmail.com>)
Responses Re: REVIEW: Optimize referential integrity checks (todo item)
List pgsql-hackers
Dean Rasheed <dean.a.rasheed@gmail.com> writes:
> On 12 February 2012 02:06, Vik Reykja <vikreykja@gmail.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


pgsql-hackers by date:

Previous
From: Josh Berkus
Date:
Subject: Re: Backport of fsync queue compaction
Next
From: Robert Haas
Date:
Subject: Re: [PATCH 10/16] Introduce the concept that wal has a 'origin' node