Search Postgresql Archives

Re: Foreign keys and slow insert

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux