On 12/3/20 11:16 AM, Tom Lane wrote: > 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. > > Yeah. IIRC some other RDBMS systems actually create such an index if it doesn't already exist. Maybe we should have a warning when setting up an FK constraint if the referencing fields aren't usefully indexed. cheers andrew -- Andrew Dunstan EDB: https://www.enterprisedb.com
