Re: Foreign keys

On Sun, Sep 10, 2006 at 09:40:51AM -0700, Joshua D. Drake wrote:
>
> >In any case the same logic that leads to it being desirable to report all
> >the
> >errors to the user in a UI and not just report them one by one also
> >applies to
> >the database. I'm not sure it's the most important issue in the world, but
> >it
> >does seem like a "it would be nice" feature if it reported all the errors
> >in
> >the statement, not just the first one it finds.
> >
>
> Seems kind of extraneous to me. I am guessing it would cause yet further
> overhead with our foreign key checks.
>
> My testing shows that the use of foreign keys on high velocity single
> transaction loads, can cause easily a 50% reduction in performance. Why
> add to that? What we need to be doing is finding a way to decrease the
> impact of foreign key checks.

IIRC, a big chunk of that overhead is simply having triggers on the
table. I tested it once and found something like a 30% overhead for
having a trigger that did nothing on insert. Granted, that was a simple
test on a single machine, but still...

Obviously one place to look is in the trigger code to see if there's
performance gains to be had there. But something else to consider is
moving away from using a general-purpose trigger framework to impliment
RI. I suspect a dedicate code path for RI could be a lot leaner than the
general-purpose trigger code is.
--
Jim Nasby jim(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)