If I run a script which drops indexes (17 of them, CONCURRENTLY makes no difference except that without it I get the occasional deadlock), then constraints (20 constraints on 18 tables) and then run a function which deletes 100,000 records from a main table and all of its associated data from ancillary tables, the initial delete takes about an hour.

If I wait 10 minutes before running the function, it runs in about 16 - 20 seconds. I run this function 120 times in separate transactions in this script, then rebuild my constraints and indexes at the end (postgres was hanging in the middle if I try to keep them all in the same transaction and even at 500000 per transaction sometimes resulted in a performance hit).

Is there anything which postgres does following index/constraint changes that could account for the need to wait on running my deletes?

  • 2
    Autovacuum comes to mind. What happens if you run vacuum analyze manually on the affected tables?
    – user1822
    Jan 23, 2014 at 18:30
  • I ran analyze on its own and performance degraded considerably. I can try a vacuum analyze. Thanks for the suggestion.
    – adamdc78
    Jan 23, 2014 at 18:36
  • Out of interest, if you force a CHECKPOINT; immediately after running the drops, does your query then perform at normal speed? Jan 24, 2014 at 1:03
  • Unfortunately neither of these suggestions has remedied the situation. I'm going to set my logs to DEBUG5, hopefully this will yield something to help narrow down a possible cause.
    – adamdc78
    Jan 24, 2014 at 15:47
  • Had to abandon the function and go back to straight deletes, table by table. It's slower, but doesn't require manual intervention or delays which aren't allowed by our audit process.
    – adamdc78
    Feb 6, 2014 at 20:47

1 Answer 1


First, sorry if I have a bad English.

Even if you dropped the constraints from the table you are trying to delete, the constraints of any children tables will affect the performance of the parent table. This occurs because the database needs to do an select on all the foreing keys pointed to the main table each line of delete to guarantee the integrity of the deletion that you're trying to do.

Create indexes on the foreign keys of the child columns that are pointed to the main table you are trying to delete, this will solve the problem and improve performance for inner join also. Remember PostgreSQL will not create indexes for the foreign keys automatically.

  • I wish I could test this answer, however I have changed companies and no longer have access to the issue.
    – adamdc78
    Jul 30, 2015 at 15:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.