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

On Fri, Nov 15, 2019 at 7:27 PM Craig James <cjames(at)emolecules(dot)com> wrote:

> On Fri, Nov 15, 2019 at 2:45 PM Jeff Janes <jeff(dot)janes(at)gmail(dot)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