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
Date: Thu, 14 Nov 2019 16:28:45 -0600
Message-ID: <20191114222845.GW2923@telsasoft.com> (raw)
In-Reply-To: <CAFwQ8rcV_qsODMuY4C1jpiw+q17VLqnoZ1fmRQVTDSRGaJ5KsA@mail.gmail.com>
References: <CAFwQ8rcV_qsODMuY4C1jpiw+q17VLqnoZ1fmRQVTDSRGaJ5KsA@mail.gmail.com>
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
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: pryzby@telsasoft.com, cjames@emolecules.com, pgsql-performance@lists.postgresql.org
Subject: Re: Simple DELETE on modest-size table runs 100% CPU forever
In-Reply-To: <20191114222845.GW2923@telsasoft.com>
* 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