From: | Aliaxei Voitsik <aliaxei(dot)voitsik(at)jnet(dot)nl> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Subject: | Unexpected performance degradation when calling jsonb_path_query() function in PostgreSQL 13.x and 14 beta |
Date: | 2021-08-30 12:05:58 |
Message-ID: | 5dbad7fa-da8f-0778-891d-66d494984726@jnet.nl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Hi,
I've noticed an unexpected performance degradation when calling
jsonb_path_query() function in PostgreSQL 13.x and 14beta3:
select * from jsonb_path_query((select data from params), '$[*] ? (@.** like_regex "1")');
The same query works ~150 times faster in PostgreSQL 12.7.
*How to reproduce*
The attached archive contains a dump and shell script with automation.
It recreates test database, loads dump, executes queries and creates logs.
You would need a Linux with Bash shell and access to PostgreSQL
instances with different versions (12, 13, 14).
*Data model*
SQL data model:
CREATE TABLE public.params (
id numeric(18,0) NOT NULL,
data jsonb
);
JSON data model:
[
{"key": "12345678"},
...
]
*Steps*
1. Configure access to the target PostgreSQL instance in
bin/pg_jsonb_issue.sh
2. Run shell script (it may take few minutes):
cd pg_jsonb_issue_report
bin/pg_jsonb_issue.sh
3. Find results in logs/
*Results*
Tested with the following PostgreSQL versions:
12.7 (performance is fine)
13.0, 13.1, 13.2, 13.3, 13.4, 14beta3 (performance degraded)
Please find the complete set of logs in the attached archive, under logs/.
Reports with _wa_ in name demonstrate that suggested workaround works
(see below).
*Example query plans*
PostgreSQL 12.7 (performance is fine)
version
---------------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 12.7 (Ubuntu 12.7-1.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.12) 5.4.0 20160609, 64-bit
(1 row)
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Function Scan on jsonb_path_query x (cost=1.01..11.01 rows=1000 width=32) (actual time=1101.885..1162.167 rows=779800 loops=1)
Buffers: shared hit=1078, temp read=3237 written=3237
InitPlan 1 (returns $0)
-> Seq Scan on params (cost=0.00..1.01 rows=1 width=32) (actual time=0.006..0.006 rows=1 loops=1)
Buffers: shared hit=1
Planning Time: 0.152 ms
Execution Time: 1200.545 ms
(7 rows)
PostgreSQL 13.4 (performance degraded)
version
--------------------------------------------------------------------------------------------------------------
PostgreSQL 13.4 on x86_64-pc-linux-musl, compiled by gcc (Alpine 10.3.1_git20210424) 10.3.1 20210424, 64-bit
(1 row)
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Function Scan on jsonb_path_query (cost=1.01..11.01 rows=1000 width=32) (actual time=54030.833..54096.154 rows=779800 loops=1)
Buffers: shared hit=1084, temp read=3237 written=3237
InitPlan 1 (returns $0)
-> Seq Scan on params (cost=0.00..1.01 rows=1 width=32) (actual time=0.025..0.026 rows=1 loops=1)
Buffers: shared hit=1
Planning:
Buffers: shared hit=48 read=1
Planning Time: 1.099 ms
Execution Time: 54117.129 ms
*
*
*Suggested workaround*
Rewrite the original query with jsonb_array_elements() and
jsonb_path_query_array() functions:
select * from jsonb_array_elements(jsonb_path_query_array((select data from params), '$[*] ? (@.** like_regex "1")'));
Attachment | Content-Type | Size |
---|---|---|
pg_jsonb_issue_report.tar.gz | application/gzip | 4.7 MB |
From | Date | Subject | |
---|---|---|---|
Next Message | Jeremy Spray | 2021-08-30 17:20:34 | Postgres bug report |
Previous Message | Alexander Lakhin | 2021-08-30 12:00:51 | Re: BUG #17167: UndefinedBehaviorSanitizer: invalid-shift-exponent while running int4shr/int4shl |