On Mon, 5 Apr 2004, Rosser Schwarz wrote:
> while you weren't looking, Kevin Barnard wrote:
>
> > Have you added indexes for the custid column for tables
> > account.acct accunt.orgacct and note?
>
> They were indexed in the original case, yes. There was no
> need to index them in today's test case, as that was done
> purely in attempt to rule in or out foreign key validation
> as the cause of the performance hit. No foreign keys that
> might be validated, no need to index the foreign key columns.
>
> > I haven't followed the entire thread but it you have
> > cascading FK on those tables without an index on the
> > column that could cause your delay.
>
> The issue is that the foreign keys are being validated at
> all, when the column being referenced by those foreign keys
> (account.cust.custid) is never touched.
>
> Regardless of whether or not the referencing columns are
> indexed, validating them at all--in this specific case--is
> broken. The column they refer to is never touched; they
> should remain utterly ignorant of whatever happens to other
> columns in the same row.
It shouldn't be checking the other table if the values of the key column
hadn't changed. The ri_KeysEqual check should be causing it to return just
before actually doing the check on the other table (it still does a few
things before then but nothing that should be particularly expensive). In
some simple tests on my 7.4.2 machine, this appears to work for me on pk
cascade updates. It would be interesting to know if it's actually doing
any checks for you, you might be able to poke around the triggers
(backend/utils/adt/ri_triggers.c).
