Re: BUG #16280: dead tuples (probably) effect plan and query performance

Hello,
Asking again just in case someone can help to find out why is that
happening in my case and if it is a bug.

пт, 28 февр. 2020 г. в 12:25, Serbin, Ilya <iserbin(at)bostonsd(dot)ru>:

> Hello, Tom!
> Thanks for your answer. My concern is that plan changes after a relatively
> small number of dead tuples. Bad plan is being generated when table1
> contain 300-400 dead tuples. It is only 0.07%-0.08% of the whole table
> (409k+ entries).
> In addition, table is growing and currently there are 425k of entries.
> However even on 425k size table plan stays good until number of dead tuples
> reaches 300-400, аfter that plan changes to the bad one.
> As I said, I tried analyzing table with various default_statistics_target
> (100-1000 with step of 100) - plan stays bad. Tried setting
> random_page_cost=0.1 and seq_page_cost=1 (2, 3, 4, etc). Plan changed to
> good one only starting from random_page_cost=0.1 and seq_page_cost=8.
> However, once I ran vacuum - plan changed to good one and stayed the same
> even when I set random_page_cost=30;set seq_page_cost=1;
> I realize that I can set autovacuum thresholds for this table to trigger
> it once dead tuples reach 300, but it doesn't seem right to me (this number
> of changes happens in something like 5 minutes and tables is ~2GB size as
> of now).
>
> Why does such a small amount (0.07%) of dead tuples changes cost
> estimations so dramatically? Or am I missing something and dead tuples has
> nothing to do with it?
>
>
>