Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
2  
Autovacuum comes to mind. What happens if you run vacuum analyze manually on the affected tables? –  a_horse_with_no_name Jan 23 '14 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 '14 at 18:36
    
Out of interest, if you force a CHECKPOINT; immediately after running the drops, does your query then perform at normal speed? –  Craig Ringer Jan 24 '14 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 '14 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 '14 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.

So I think you can improve the performance with this

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.

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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