Thread: Unexpected performance degradation when calling jsonb_path_query() function in PostgreSQL 13.x and 14 beta

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