Date: Thu, 14 Nov 2019 16:28:45 -0600
From: Justin Pryzby <pryzby@telsasoft.com>
To: Craig James <cjames@emolecules.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Simple DELETE on modest-size table runs 100% CPU forever
Message-ID: <20191114222845.GW2923@telsasoft.com>
References: 
 <CAFwQ8rcV_qsODMuY4C1jpiw+q17VLqnoZ1fmRQVTDSRGaJ5KsA@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAFwQ8rcV_qsODMuY4C1jpiw+q17VLqnoZ1fmRQVTDSRGaJ5KsA@mail.gmail.com>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk

On Thu, Nov 14, 2019 at 02:19:51PM -0800, Craig James wrote:
> I'm completely baffled by this problem: I'm doing a delete that joins three
> modest-sized tables, and it gets completely stuck: 100% CPU use forever.
> Here's the query:
> 
> explain analyze
>  select count(1) from registry.categories
>   where category_id = 15 and id in
>     (select c.id from registry.categories c
>      left join registry.category_staging_15 st on (c.id = st.id)  where
> c.category_id = 15 and st.id is null);
> 
> If I leave out the "analyze", here's what I get (note that the

Do you mean that you're doing DELETE..USING, and that's an explain for SELECT
COUNT() with same join conditions ?  Can you show explain for the DELETE, and
\d for the tables ?  

If there's FKs, do the other tables have indices on their referencING columns ?

https://www.postgresql.org/docs/devel/static/ddl-constraints.html#DDL-CONSTRAINTS-FK
"Since a DELETE of a row from the referenced table [...] will require a scan of
the referencing table for rows matching the old value, it is often a good idea
to index the referencing columns too."

Justin