From: | Andres Freund <andres(at)anarazel(dot)de> |
---|---|
To: | Craig James <cjames(at)emolecules(dot)com> |
Cc: | pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | Re: Simple DELETE on modest-size table runs 100% CPU forever |
Date: | 2019-11-14 22:29:29 |
Message-ID: | 20191114222929.2aldgt3yuvlwrhp6@alap3.anarazel.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Hi,
On 2019-11-14 14:19:51 -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:
I assume this is intended to be an equivalent SELECT? Because you did
mention DELETE, but I'm not seeing one here? Could you actually show
that query - surely that didn't include a count() etc... You can
EPLAIN DELETEs too.
> 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
> categories_staging_N table's name changes every time; it's
> created on demand as "create table categories_staging_n(id integer)").
> Aggregate (cost=193.54..193.55 rows=1 width=8)
> -> Nested Loop Semi Join (cost=0.84..193.54 rows=1 width=0)
> Join Filter: (categories.id = c.id)
> -> Index Scan using i_categories_category_id on categories
> (cost=0.42..2.44 rows=1 width=4)
> Index Cond: (category_id = 23)
> -> Nested Loop Anti Join (cost=0.42..191.09 rows=1 width=4)
> Join Filter: (c.id = st.id)
> -> Index Scan using i_categories_category_id on categories c
> (cost=0.42..2.44 rows=1 width=4)
> Index Cond: (category_id = 23)
> -> Seq Scan on category_staging_23 st (cost=0.00..99.40
> rows=7140 width=4)
>
> The tables are small. From a debugging printout:
Is categories.category_id unique? Does the plan change if you ANALYZE
the tables?
This plan doesn't look like it'd actually take long, if the estimates
are correct.
> What on Earth could be causing this simple query to be running 100% CPU for
> hours?
Is the DELETE actually taking that long, or the query you showed the
explain for, or both?
Greetings,
Andres Freund
From | Date | Subject | |
---|---|---|---|
Next Message | Craig James | 2019-11-15 21:06:42 | Re: Simple DELETE on modest-size table runs 100% CPU forever |
Previous Message | Justin Pryzby | 2019-11-14 22:28:45 | Re: Simple DELETE on modest-size table runs 100% CPU forever |