Re: Why CASCADE constraint takes more time when table is loaded with huge records?

On Nov 22, 2011, at 4:32, Siva Palanisamy <siva_p(at)hcl(dot)com> wrote:

> Hi ya,
>
>
>
> As I had raised a question here at ‘Please recommend the best bulk-delete option’ (http://stackoverflow.com/questions/8172101/please-recommend-the-best-bulk-delete-option), CASCADE constraint is the one that prevents me to delete all the records in all the tables when they were loaded with bulk records. Is there any reason for why CASCADE takes time when DELETE FROM table1; Or TRUNCATE table1 CASCADE is attempted? FYI, I'm using PostgreSQL 8.1.4. Though outdated, when I remove CASCADE constraint in my table structures (listed in the top link), both DELETE and TRUNCATE queries work fine and completes quickly. However, CASCADE is what I needed! I can't just remove the constraint. Please help me on this.
>
>
>
> Regards,
>
> Siva.
>
>

Cascading is a convenience, not a necessity. It takes time because you have to find and delete each related record on the other tables.

My only possible guess is your FK fields on the related tables are not indexed. Create indexes on them and see how much performance improves.

David J.