From: Andrew Dunstan <andrew@dunslane.net>
Subject: Re: time taking deletion on large tables
To: Tom Lane <tgl@sss.pgh.pa.us>, Justin Pryzby <pryzby@telsasoft.com>
Cc: Ravikumar Reddy <urravikumarreddy@gmail.com>,
 Atul Kumar <akumar14871@gmail.com>,
 pgsql-performance <pgsql-performance@lists.postgresql.org>
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>
Message-ID: <ff79272c-eaf0-23dc-ae35-086995bc56a0@dunslane.net>
Date: Thu, 3 Dec 2020 12:00:44 -0500
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101
 Thunderbird/78.3.1
MIME-Version: 1.0
In-Reply-To: <1735185.1607012176@sss.pgh.pa.us>
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
Content-Language: en-US
Precedence: bulk


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