From: Tom Lane <tgl@sss.pgh.pa.us>
To: Justin Pryzby <pryzby@telsasoft.com>
cc: Ravikumar Reddy <urravikumarreddy@gmail.com>,
        Atul Kumar <akumar14871@gmail.com>,
        pgsql-performance <pgsql-performance@lists.postgresql.org>
Subject: Re: time taking deletion on large tables
In-reply-to: <20201203153612.GM24052@telsasoft.com>
References: 
 <CA+ONtZ541OzW3dzrUKcXscTYAK8G_3DEtUhFz+i2+HsD3-5ffQ@mail.gmail.com>
 <CA+ONtZ7rNziZKMjgvocR9EHH=L8V93C-K4E20qP8JwEfJpW5Zg@mail.gmail.com>
 <CANMO9LBud=vzUc7Pa4B7Ebe3QvMPJi=bQtqtwTPCNFVEitsH+Q@mail.gmail.com>
 <20201203153612.GM24052@telsasoft.com>
Comments: In-reply-to Justin Pryzby <pryzby@telsasoft.com>
	message dated "Thu, 03 Dec 2020 09:36:12 -0600"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1735184.1607012176.1@sss.pgh.pa.us>
Date: Thu, 03 Dec 2020 11:16:16 -0500
Message-ID: <1735185.1607012176@sss.pgh.pa.us>
Precedence: bulk

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