Justin Pryzby <pryzby@xxxxxxxxxxxxx> 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
