Re: GIN JSONB path index is not always used - Mailing list pgsql-performance

From Jeff Janes
Subject Re: GIN JSONB path index is not always used
Date
Msg-id CAMkU=1ymeq5bdJD7d0jDr+tALSXTuvtDR9Jmfx6Hq7yS00qzCA@mail.gmail.com
Whole thread Raw
In response to Re: GIN JSONB path index is not always used  (Tomasz Szymański <lime129@gmail.com>)
List pgsql-performance
On Mon, Oct 23, 2023 at 6:33 AM Tomasz Szymański <lime129@gmail.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

pgsql-performance by date:

Previous
From: Tomasz Szymański
Date:
Subject: Re: GIN JSONB path index is not always used
Next
From: Alexander Okulovich
Date:
Subject: Re: Postgres 15 SELECT query doesn't use index under RLS