Re: time taking deletion on large tables

Justin Pryzby <pryzby(at)telsasoft(dot)com> writes:
> On Thu, Dec 03, 2020 at 08:43:57PM +0530, Ravikumar Reddy wrote:
>> When I try to do a delete like this: it hangs for an entire day, so I
>> need to kill it with pg_terminate_backend(pid).
>>
>> DELETE FROM feed_posts
>> WHERE feed_definition_id = 'bf33573d-936e-4e55-8607-72b685d2cbae'
>> AND created_at > '2020-05-11 00:00:00'
>> AND created_at < '2020-05-12 00:00:00';

90% of the "delete takes forever" complaints that we hear trace down to
having a foreign key reference to the deletion-target table that's not
backed by an index on the referencing column. Then you end up getting
a seqscan on the referencing table to look for rows referencing a
row-to-be-deleted. And then another one for the next row. Etc.

You could try "explain analyze" on a query deleting just a single
one of these rows and see if an RI enforcement trigger is what's
eating the time.

regards, tom lane