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
a...@render.es
657661974 · Denia 50, bajo izquierda · 46006 · Valencia

> On 16 Dec 2022, at 16:06, Tom Lane <t...@sss.pgh.pa.us> wrote:
>
> "Render Comunicacion S.L." <a...@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
>
>
