Re: JIT doing duplicative optimization? - Mailing list pgsql-hackers

From Andres Freund
Subject Re: JIT doing duplicative optimization?
Date
Msg-id 20211115171346.7tqpsmqhprt2calb@alap3.anarazel.de
Whole thread Raw
In response to JIT doing duplicative optimization?  (Alvaro Herrera <alvherre@alvh.no-ip.org>)
List pgsql-hackers
Hi,

On 2021-11-11 17:02:09 -0300, Alvaro Herrera wrote:
> Recently I noticed extremely suspicious behavior from queries under JIT.
> A query (details for which sadly I cannot disclose) had an EXPLAIN
> looking like this:
> 
>  JIT:
>    Functions: 743
>    Options: Inlining true, Optimization true, Expressions true, Deforming true
>    Timing: Generation 47.013 ms, Inlining 20.277 ms, Optimization 3269.381 ms, Emission 2392.115 ms, Total 5728.786
ms
>  Execution Time: 5735.552 ms
> 
> Disabling JIT, the query takes a few tens of milliseconds, so of course
> the typical recommendation is to disable JIT.  But I noted that the
> average time to optimize, per function, is 4.7ms.  I didn't know what to
> make of this -- is it a reasonable to to take to optimize a single
> function?

No, that's pretty insane. Any chance you share the generated bitcode for this
- it'll have some details about the query / table structure though. Perhaps
just the contained function names?


> I put that thought aside.  But then I spotted a couple of minor things
> in the query that could be made better; so I did some of that, and
> noticed that the number of functions was reduced from 743 to 671, and
> the JIT details in the explain looked like this:

> But what really surprised me is that the the average time to optimize
> per function is now 2.06ms ... less than half of the previous
> measurement.  It emits 10% less functions than before, but the time to
> both optimize and emit is reduced by 50%.  How does that make sense?

Without knowing the concrete query I don't know if it it's that. But


> I don't know how to approach an investigation into this, but I thought
> that maybe we're doing quadratic effort in the optimization / emission
> phase or something like that.

Yea. One way to investigate it is to enable jit_dump_bitcode and then use
llvm's 'opt' tool to see details about optimization times.

opt -time-passes -O3 -o /dev/null < /srv/dev/pgdev-dev/3990041.77.bc |less

Greetings,

Andres Freund



pgsql-hackers by date:

Previous
From: Bharath Rupireddy
Date:
Subject: Re: enhance pg_log_backend_memory_contexts() to log memory contexts of auxiliary processes
Next
From: Andres Freund
Date:
Subject: Re: JIT doing duplicative optimization?