Simple DELETE on modest-size table runs 100% CPU forever

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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 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:
  7997 items in table registry.category_staging_15
228292 items in table registry.categories
309398 items in table registry.smiles
  7997 items in joined registry.category_staging_15 / registry.categories

What on Earth could be causing this simple query to be running 100% CPU for hours?

Postgres: 10.10
Ubuntu 16.04
This is a VirtualBox virtual machine running on a Mac host.

Everything else seems to work as expected; just this one query does this.

Thanks,
Craig


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux