Обсуждение: Simple DELETE on modest-size table runs 100% CPU forever

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)").

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.

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:

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)

On Fri, Nov 15, 2019 at 2:45 PM Jeff Janes <jeff.janes@gmail.com> wrote:

BTW, I'll note at this point that "analyze category_staging_8" prior to this query made no difference.

 

What do you see in `select * from pg_stats where tablename='categories' and attname='category_id' \x\g\x`?

db=> select * from pg_stats where tablename='categories' and attname='category_id' \x\g\x;
Expanded display is on.

... 

n_distinct             | 21
most_common_vals       | {4,3,2,10,11,13,12,16,9,6,7,5,15,23,14,25,24,1,26,28,27}
most_common_freqs      | {0.2397,0.159933,0.0926667,0.0556,0.0555667,0.0546333,0.0525333,0.0439,0.0426667,0.0346333,0.0331,0.0302333,0.0288333,0.0240667,0.0224,0.0122333,0.011,0.0035,0.00233333,0.000366667,0.0001}

On Fri, Nov 15, 2019 at 7:27 PM Craig James <cjames@emolecules.com> wrote:

On Fri, Nov 15, 2019 at 2:45 PM Jeff Janes <jeff.janes@gmail.com> wrote:

BTW, I'll note at this point that "analyze category_staging_8" prior to this query made no difference.

Isn't that the wrong table to have analyzed? The offender here is "categories", not  "category_staging_8".  Is this some sort of inheritance situation?

 

What do you see in `select * from pg_stats where tablename='categories' and attname='category_id' \x\g\x`?

db=> select * from pg_stats where tablename='categories' and attname='category_id' \x\g\x;
Expanded display is on.

 

... 

n_distinct             | 21
most_common_vals       | {4,3,2,10,11,13,12,16,9,6,7,5,15,23,14,25,24,1,26,28,27}
most_common_freqs      | {0.2397,0.159933,0.0926667,0.0556,0.0555667,0.0546333,0.0525333,0.0439,0.0426667,0.0346333,0.0331,0.0302333,0.0288333,0.0240667,0.0224,0.0122333,0.011,0.0035,0.00233333,0.000366667,0.0001}

There is a path in the analyze code where if the least-seen value in the sample was seen more than once (i.e. no value was seen exactly once) then it assumes that the seen values are all the values that exist.  I think the logic behind that is dubious.  I think it is pretty clear that that is kicking in here.  But why?  I think the simple answer is that you analyzed the wrong table, and the statistics shown here might be accurate for some time in the past but are no longer accurate.  It is hard to see how a value present 5000 times in a table of 274602 rows could have evaded sampling if they were present at the time the sample was done.

Cheers,

Jeff