Re: JSON down performacen when id:1 - Mailing list pgsql-performance

On 16 Dec 2022, at 16:06, Tom Lane <tgl@sss.pgh.pa.us> wrote:

"Render Comunicacion S.L." <alex@render.es> writes:

The issue:
When we search our locator with section_id: 1 (or any number < 4), PostgreSQL takes around 40000, 5000, 8000ms or more.
When we search our locator with section_id: 4 (or any other bigger number), PostgreSQL takes around 100 ms. ( ~ expected time)

Your index is providing pretty awful performance:

       ->  Bitmap Heap Scan on matrix  (cost=92.21..199.36 rows=27 width=1144) (actual time=415.708..8325.296 rows=11 loops=1)
             Recheck Cond: ((datos #> '{relations}'::text[]) @> '[{"section_id": "1", "section_tipo": "numisdata3"}]'::jsonb)
             Rows Removed by Index Recheck: 272037
             Heap Blocks: exact=34164 lossy=33104
             ->  Bitmap Index Scan on matrix_relations_idx  (cost=0.00..92.20 rows=27 width=0) (actual time=61.462..61.462 rows=155031 loops=1)
                   Index Cond: ((datos #> '{relations}'::text[]) @> '[{"section_id": "1", "section_tipo": "numisdata3"}]'::jsonb)

I read that as 155K hits delivered by the index, of which only 11 were
real matches.  To make matters worse, with so many hits the bitmap was
allowed to become "lossy" (ie track some hits at page-level not
tuple-level) to conserve memory, so that the executor actually had to
check even more than 155K rows.

You need a better index.  It might be that switching to a jsonb_path_ops
index would be enough to fix it, or you might need to build an expression
index matched specifically to this type of query.  See

https://www.postgresql.org/docs/current/datatype-json.html#JSON-INDEXING

Also, if any of the terminology there doesn't make sense, read

https://www.postgresql.org/docs/current/indexes.html

regards, tom lane