From: Render Comunicacion S.L. <alex@render.es>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: JSON down performacen when id:1
Date: Fri, 16 Dec 2022 18:01:47 +0100
Message-ID: <16B10D64-A1BF-4522-9A6B-EB31F0622713@render.es> (raw)
In-Reply-To: <3675066.1671203211@sss.pgh.pa.us>
References: <B9DBE179-BF2D-4D41-A94D-CC9520E2EEBA@render.es>
<3675066.1671203211@sss.pgh.pa.us>
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@render.es
657661974 · Denia 50, bajo izquierda · 46006 · Valencia

> 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
>
>
Attachments:
[image/tiff] pastedGraphic.tiff (6.6K, ../16B10D64-A1BF-4522-9A6B-EB31F0622713@render.es/3-pastedGraphic.tiff)
download | view image
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: alex@render.es, tgl@sss.pgh.pa.us, pgsql-performance@lists.postgresql.org
Subject: Re: JSON down performacen when id:1
In-Reply-To: <16B10D64-A1BF-4522-9A6B-EB31F0622713@render.es>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox