Turn jit off for slow subquery in Postgres 12

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Turn jit off for slow subquery in Postgres 12

Andrus Moor

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

Reply | Threaded
Open this post in threaded view
|

Re: Turn jit off for slow subquery in Postgres 12

Thomas Kellerer-4
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.



Reply | Threaded
Open this post in threaded view
|

Re: Turn jit off for slow subquery in Postgres 12

Michael Lewis
Either turn it off, or increase jit_above_cost, jit_inline_above_cost, and/or jit_optimize_above_cost.