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")'));
