[Django]-PostgreSQL, Foreign Keys, Insert speed & Django

100.000 FK checks should take about 2-5 seconds if it doesn’t have to wait for IO reads.
Much slower than inserting into the table, but much faster than the time you got.

Check that all your foreign keys are INDEXED :

(I’m talking about an index on the referenced column, not the referencing column, got it ?)

If products.category_id REFERENCES category(id), and there is no index on category.id, every time it needs to check a FK it will have to scan the table.

To find which isn’t, do your insert with 1 FK, then 2 FKs… you’ll find which one is responsible.

And yes, if you truncate the table, it’s faster to also drop all constraints and indexes and rebuild them after bulk insertion.

This seems like normal behavior to me. When bulk inserting into a database, if the table has indexes, foreign keys or triggers, they have to be checked row-by-row. So typically you want to drop them, perform the inserts (using copy if possible), and then recreate indexes, FKs and triggers.

This page on the docs has more details about autocommit, maintenance_work_mem and checkpoint_segments that you can tune: http://www.postgresql.org/docs/8.4/interactive/populate.html

Maybe you have a trigger on your table, you do not know of or remember, that fires on every row inserted/deleted. Can you connect to a database using “psql”? If yes, then analyze the output of “\d+ table_name” for all your tables.

You can also dump your database, do import, dump a database again. Compare dumps to check if any other table contents has changed.

I had forgotten that EXPLAIN ANALYZE INSERT INTO bleh … will show you the timing of all insert triggers.