2000 times performance drop after pg14 upgrade when JIT = 1

From: Sergey Mirvoda <sergey(at)mirvoda(dot)com>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: 2000 times performance drop after pg14 upgrade when JIT = 1
Date: 2022-03-09 12:20:23
Message-ID: CALkWArhhtz=S9G6kOQdM=db0aXqH2MgyQqyLr91FoFYvk6AeXg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

After upgrading from pg10.20 to latest (14.2)
we noticed huge performance degradation.
The very interesting part is each of our queries takes exactly 14s of
difference from 10.20.

After investigation we founded that performance hit was in our query
procedure which takes json object and turns it into SQL query

Steps to reproduce
0. create database
1. set jit = 1
2. execute attached script
3. note performance result
4. set jit = 0
5. execute attached script
6. notice perf difference

Here is our results from different PG versions
Version Execution Time (ms)
9.6.11 18.616
10.2 19.909
10.20 5.771
13.4 (jit=on) 139.296
13.4 (jit=off) 147.461
14.2 (jit=on) 15114.222
14.2 (jit=off) 40.851

here is out jit = 1 explain analyze
Planning Time: 17.425 ms
JIT:
Functions: 895
Options: Inlining true, Optimization true, Expressions true, Deforming
true
Timing: Generation 122.892 ms, Inlining 48.502 ms, Optimization 8207.274
ms, Emission 5647.010 ms, Total 14025.678 ms
Execution Time: 14065.000 ms

Env info
PostgreSQL 14.2 (Ubuntu 14.2-1.pgdg20.04+1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit

--
--Regards, Sergey Mirvoda

Attachment Content-Type Size
jit_pg14_slow_query.sql application/octet-stream 44.9 KB

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2022-03-09 15:11:47 Re: BUG #17385: "RESET transaction_isolation" inside serializable transaction causes Assert at the transaction end
Previous Message Masahiko Sawada 2022-03-09 02:26:42 Re: BUG #17385: "RESET transaction_isolation" inside serializable transaction causes Assert at the transaction end