From: Tom Lane <tgl@sss.pgh.pa.us>
To: "Render Comunicacion S.L." <alex@render.es>
cc: pgsql-performance@lists.postgresql.org
Subject: Re: JSON down performacen when id:1
In-reply-to: <B9DBE179-BF2D-4D41-A94D-CC9520E2EEBA@render.es>
References: <B9DBE179-BF2D-4D41-A94D-CC9520E2EEBA@render.es>
Comments: In-reply-to "Render Comunicacion S.L." <alex@render.es>
	message dated "Fri, 16 Dec 2022 14:30:42 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3675065.1671203211.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Fri, 16 Dec 2022 10:06:51 -0500
Message-ID: <3675066.1671203211@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/3675066.1671203211%40sss.pgh.pa.us>
Precedence: bulk

"Render Comunicacion S.L." <alex@render.es> writes:
> The issue:
> When we search our locator with section_id: 1 (or any number < 4), Postg=
reSQL takes around 40000, 5000, 8000ms or more.
> When we search our locator with section_id: 4 (or any other bigger numbe=
r), PostgreSQL takes around 100 ms. ( ~ expected time)

Your index is providing pretty awful performance:

>         ->  Bitmap Heap Scan on matrix  (cost=3D92.21..199.36 rows=3D27 =
width=3D1144) (actual time=3D415.708..8325.296 rows=3D11 loops=3D1)
>               Recheck Cond: ((datos #> '{relations}'::text[]) @> '[{"sec=
tion_id": "1", "section_tipo": "numisdata3"}]'::jsonb)
>               Rows Removed by Index Recheck: 272037
>               Heap Blocks: exact=3D34164 lossy=3D33104
>               ->  Bitmap Index Scan on matrix_relations_idx  (cost=3D0.0=
0..92.20 rows=3D27 width=3D0) (actual time=3D61.462..61.462 rows=3D155031 =
loops=3D1)
>                     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