Re: Foreign keys and slow insert

On Wed, 2005-06-08 at 12:39, Dan Black wrote:
> I've observed that inserts into slave table became slower when I use
> foreign key than without one.
> Can it be related to foreign key?
> And I am interested how much performance of database with foreign
> keys can be different from performance of database without foreign
> keys? In other words, how much performance decrease on using foreign
> keys?

The problem you're seeing is usually caused by adding records to a table
set that starts out empty, and the planner uses seq scans, and as it
grows, should switch to random seeks, but doesn't know to, because no
one has bothered to analyze said tables.

Set up the pg_autovacuum daemon or cron vacuumdb -az to run every so
often to help that.

On the other hand, foreign keys are never zero cost, so even the most
efficient implementation is gonna be slower than not using them. Data
coherency costs, either up front (i.e. in the database doing it) or in
the back (i.e. hiring 20 summer interns to go through your data and find
the parts that are bad...) :)