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 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 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 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 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 at 20:47
add comment

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.