From: Andrew Dunstan <andrew@dunslane.net>
To: 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, 3 Dec 2020 12:00:44 -0500
Message-ID: <ff79272c-eaf0-23dc-ae35-086995bc56a0@dunslane.net> (raw)
In-Reply-To: <1735185.1607012176@sss.pgh.pa.us>
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>
<1735185.1607012176@sss.pgh.pa.us>
On 12/3/20 11:16 AM, Tom Lane wrote:
> 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.
>
>
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
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: andrew@dunslane.net, 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: <ff79272c-eaf0-23dc-ae35-086995bc56a0@dunslane.net>
* 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