CODEWITHSUNDEEP
postgresql
David Tinker
David Tinker

Reputation: 9644

Why does SET CONSTRAINTS ALL DEFERRED on Postgresql take a long time?

Our Postgres 9.2.4 server is taking 0.69s on average (15000 statements) to execute 

SET CONSTRAINTS ALL DEFERRED

Why should this be?

Would using INITIALLY DEFERRED in the table definition and not using SET CONSTRAINTS .. eliminate this time or just hide it somewhere else?

Upvotes: 5

Views: 1817

Answers (2)

Peter Eisentraut
Peter Eisentraut

Reputation: 36739

I looked through the source code. Essentially, SET CONSTRAINTS ALL DEFERRED does nothing except set a global variable noting "all appropriate constraints are now deferred". The only nontrivial work that is done is when there are subtransactions in play. If that applies to your case, try it without. (Note that subtransactions include PL/pgSQL exception blocks.)

Upvotes: 4

Denis de Bernardy
Denis de Bernardy

Reputation: 78523

Imho, this is probably because each time you run this statement, what happens in the background is a big update statement on table definitions located in the pg_catalog. In other words, you add dead rows to the pg_catalog, whichg results in increasingly larger amounts of disc space being visited during the appropriate seq scans.

If you're really running the statement 15k times per day, perhaps you need to give a cold hard look to your schema or workflow?

Upvotes: 2

Related Questions