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
