Thread: Turn jit off for slow subquery in Postgres 12

Hi!

Last year I posted testcase [1] which shows that using jit makes execution
magnitude slower ( 2.5s vs 0.4 sec) in typical shopping cart application product search in Postgres 12.

There are also other reports on this [2,3].

I tried to turn jit off for subquery using

select ...  from
(
set jit to off;
select ... from cartdata, ...  where ....
set jit to on
)

But this cause syntax error.

How to turn off jit for specific subquery in Postgres 12 ?

Subquery is generated by EF Core and splitting it to multiple statements is not possible.

Postgres upgrade is not planned.

Andrus.

[1] https://www.postgresql.org/message-id/A2E2572094D4434CAEB57C80085B22C7@dell2
[2] https://www.postgresql.org/message-id/CAHOFxGo5xJt02RmwAWrtv2K0jcqqxG-cDiR8FQbvb0WxdKhcgw%40mail.gmail.com
[3]https://www.postgresql.org/message-id/629715.1595630222%40sss.pgh.pa.us

Andrus schrieb am 16.02.2021 um 07:48:
> Last year I posted testcase [1] which shows that using jit makes execution
> magnitude slower ( 2.5s vs 0.4 sec) in typical shopping cart application product search in Postgres 12.
>
> There are also other reports on this [2,3].
>
> I tried to turn jit off for subquery using
>
> select ...  from
> (
> set jit to off;
> select ... from cartdata, ...  where ....
> set jit to on
> )
>
> But this cause syntax error.
>
> How to turn off jit for specific subquery in Postgres 12?

You can't.

You can only turn off JIT for the whole query (or the session).

In my experience, in OLTP type environments, JIT never improves a query.
We have turned it off globally.