Re: GIN JSONB path index is not always used

On Mon, Oct 23, 2023 at 6:33 AM Tomasz Szymański <lime129(at)gmail(dot)com> wrote:

> Limit (cost=0.00..1184.30 rows=21 width=4) (actual
> time=1567.136..1619.956 rows=1 loops=1)
> -> Seq Scan on account_user (cost=0.00..256768.27 rows=4553 width=4)
> (actual time=1567.135..1619.953 rows=1 loops=1)
>

It thinks the seq scan will stop 99.5% early, after finding 21 out of 4553
qualifying tuples. But instead it has to read the entire table to actually
find only 1.

The selectivity estimate of the @> operator has been substantially improved
in v13. It is still far from perfect, but should be good enough to solve
this problem for this case and most similar cases. Turning off fastupdate
on the index would probably also solve the problem, for a different reason.

Cheers,

Jeff