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
Date: Fri, 16 Dec 2022 10:06:51 -0500
Message-ID: <3675066.1671203211@sss.pgh.pa.us> (raw)
In-Reply-To: <B9DBE179-BF2D-4D41-A94D-CC9520E2EEBA@render.es>
References: <B9DBE179-BF2D-4D41-A94D-CC9520E2EEBA@render.es>
"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
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: tgl@sss.pgh.pa.us, alex@render.es, pgsql-performance@lists.postgresql.org
Subject: Re: JSON down performacen when id:1
In-Reply-To: <3675066.1671203211@sss.pgh.pa.us>
* 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