Re: JSON down performacen when id:1

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(at)render(dot)es

657661974 · Denia 50, bajo izquierda · 46006 · Valencia



> On 16 Dec 2022, at 16:06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> "Render Comunicacion S.L." <alex(at)render(dot)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
>
>