Re: JIT performance question

Hi,

On 2019-03-06 18:16:08 +0100, Tobias Gierke wrote:
> I was playing around with PG11.2 (i6700k with 16GB RAM, on Ubuntu 18.04,
> compiled from sources) and LLVM, trying a CPU-bound query that in my simple
> mind should benefit from JIT'ting but (almost) doesn't.
>
> 1.) Test table with 195 columns of type 'numeric':
>
> CREATE TABLE test (data0 numeric,data1 numeric,data2 numeric,data3
> numeric,...,data192 numeric,data193 numeric,data194 numeric);
>
> 2.) bulk-loaded (via COPY) 2 mio. rows of randomly generated data into this
> table (and ran vacuum & analyze afterwards)
>
> 3.) Disable parallel workers to just measure JIT performance via 'set
> max_parallel_workers = 0'

FWIW, it's better to do that via max_parallel_workers_per_gather in most
cases, because creating a parallel plan and then not using that will
have its own consequences.

> 4.) Execute query without JIT a couple of times to make sure table is in
> memory (I had iostat running in the background to verify that actually no
> disk access was taking place):

There's definitely accesses outside of PG happening here :(. Probably
cached at the IO level, but without track_io_timings that's hard to
confirm. Presumably that's caused by the sequential scan ringbuffers.
I found that forcing the pages to be read in using pg_prewarm gives more
measurable results.

> So (ignoring the time for JIT'ting itself) this yields only ~2-3%
> performance increase... is this because my query is just too simple to
> actually benefit a lot, meaning the code path for the 'un-JIT' case is
> already fairly optimal ? Or does JIT'ting actually only have a large impact
> on the filter/WHERE part of the query but not so much on aggregation / tuple
> deforming ?

It's hard to know precisely without running a profile of the
workload. My suspicion is that the bottleneck in this query is the use
of numeric, which has fairly slow operations, including aggregation. And
they're too complicated to be inlined.

Generally there's definitely advantage in JITing aggregation.

There's a lot of further improvements on the table with better JIT code
generation, I just haven't gotten around implementing those :(

Greetings,

Andres Freund