Re: Delete performance

adey <adey11(at)gmail(dot)com> writes:
> We are attempting many deletes in our production database for the first
> time, and we're getting nowhere fast.
> The SQL runs for more than 12 hours to delete 2 million rows, and hasn't
> finished each time we've tried it as we've had to cancel it.

The usual cause of slow deletes is that (a) the table is the target of
some foreign key references from other large tables, and (b) the
referencing columns in those tables aren't indexed, or (in older PG
versions such as 7.4) aren't exactly the same datatype as the master
column. This forces the FK actions to use inefficient sequential-scan
plans. Fix the index situation and then start a fresh session to ensure
you have fresh FK-action plans.

Please also think *hard* about running something more modern than 7.4.2.
That release series is at 7.4.12 --- you are missing nearly two years'
worth of critical bug fixes.

regards, tom lane