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

On 2/27/20 17:28, PG Bug reporting form wrote:
> I can confirm that issue exists on 11.4 and 11.6 (updated to 11.6 since I
> thought it was a bu that may have been fixed in 11.5 "Fix possible failure
> of planner's index endpoint probes (Tom Lane)"), it is always reproducible,
> and can be reproducted on prod and all lower environments.
> I can't understand why it happens. As far as I understand there is something
> to do with visibility map, but as per documention it should effect
> index-only scans, not my case.
> Main two questions are:
> 1) Is it a bug?
> 2) If it is expected behaviour - can someone please explain why it happens
> and if there is any way  to keep the good plan (without installing
> extensions to force pin plans)
>
> Best regards,
> Ilya
>
Hello,

I don't think that's a bug, comparing these lines from both plans

->  Bitmap Index Scan on content_idx  (cost=0.00..155.07 rows=409 
width=0) (actual time=4.932..4.932 rows=21952 loops=1)
       Index Cond: (content @> '{"anotherjsonkey": {"values": 
["13"]}}'::jsonb)
       Buffers: shared hit=48

->  Bitmap Index Scan on content_idx  (cost=0.00..27.11 rows=414 
width=0) (actual time=4.287..4.287 rows=21952 loops=1)
       Index Cond: (content @> '{"anotherjsonkey": {"values": 
["13"]}}'::jsonb)
       Buffers: shared hit=16


we can see that cost of bitmapscan is more in bad plan but actual time 
is about the same.
I think that could be fixed by changing seq_page_cost/random_page cost
to something like 1/10th of their current value (0.1/0.11) if your database
is on SSD (or fits in memory). Change in cost possibly could be 
explained due
to change in table size (if table size changed after vacuum).

It's also possible to trigger autovacuum on that table more often by 
changing
autovacuum_vacuum_scale_factor/autovacuum_vacuum_threshold
(it's possible to do so on selected table only if necessary).

In given case main reason of problem I think is that planner underestimate
cost of rechecking condition on large jsonb value, AFAIK planner doesn't
account width of the column but I might be wrong.

--
Alexey Ermakov