Re: JSON down performacen when id:1

["Render Comunicacion S.L." <alex@xxxxxxxxx>]

Hi Tom

Thanks for your quick answer.

I did not mention that the index for all tables is:

CREATE INDEX IF NOT EXISTS matrix_relations_idx

    ON public.matrix USING gin

    ((datos #> '{relations}') jsonb_path_ops)  TABLESPACE pg_default;

And we try with and without jsonb_path_ops option with similar results.

My question is about, what is the difference between the first 3 searches and the > 4 search? 

We don't know why in the first 3 cases seems that PostgreSQL doesn't use the index, and the result takes the same time with or without index, and the > 4, every number higher of 3, it works perfectly...

We are really desperate about this... 

Thanks in avance.

Best
Alex
alex@xxxxxxxxx


657661974 · Denia 50, bajo izquierda · 46006 · Valencia

On 16 Dec 2022, at 16:06, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:

"Render Comunicacion S.L." <alex@xxxxxxxxx> 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