From: Tom Lane <tgl@sss.pgh.pa.us>
To: Justin Pryzby <pryzby@telsasoft.com>
Cc: Ravikumar Reddy <urravikumarreddy@gmail.com>
Cc: Atul Kumar <akumar14871@gmail.com>
Cc: pgsql-performance <pgsql-performance@lists.postgresql.org>
Subject: Re: time taking deletion on large tables
Date: Thu, 03 Dec 2020 11:16:16 -0500
Message-ID: <1735185.1607012176@sss.pgh.pa.us> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, pryzby@telsasoft.com, urravikumarreddy@gmail.com, akumar14871@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: time taking deletion on large tables
In-Reply-To: <1735185.1607012176@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox