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...) :)
