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
