From: Tobias Gierke <tobias.gierke@code-sourcery.de>
To: pgsql-performance@lists.postgresql.org
Subject: Re: JIT performance question
Date: Wed, 6 Mar 2019 19:21:33 +0100
Message-ID: <cd19a3f0-7c73-34dc-71e2-be2f69266537@code-sourcery.de> (raw)
In-Reply-To: <20190306174258.f23avt6k3vtgqsqr@alap3.anarazel.de>
References: <6191e1ee-d548-2a8c-d87d-dab63c05ad2e@code-sourcery.de>
<20190306174258.f23avt6k3vtgqsqr@alap3.anarazel.de>
On 06.03.19 18:42, Andres Freund wrote:
>
> 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 :(
Thanks for the quick response ! I think you're onto something with the
numeric type. I replaced it with bigint and repeated my test and now I
get a nice 40% speedup (I'm again intentionally ignoring the costs for
JIT'ting here as I assume a future PostgreSQL version will have some
kind of caching for the generated code):
Without JIT:
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1395000.49..1395000.50 rows=1 width=6240) (actual
time=6023.436..6023.436 rows=1 loops=1)
Buffers: shared hit=256 read=399744
I/O Timings: read=475.135
-> Seq Scan on test (cost=0.00..420000.00 rows=2000000 width=1560)
(actual time=0.035..862.424 rows=2000000 loops=1)
Buffers: shared hit=256 read=399744
I/O Timings: read=475.135
Planning Time: 0.574 ms
Execution Time: 6024.298 ms
(8 rows)
With JIT:
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1395000.49..1395000.50 rows=1 width=6240) (actual
time=4840.064..4840.064 rows=1 loops=1)
Buffers: shared hit=320 read=399680
I/O Timings: read=493.679
-> Seq Scan on test (cost=0.00..420000.00 rows=2000000 width=1560)
(actual time=0.090..847.458 rows=2000000 loops=1)
Buffers: shared hit=320 read=399680
I/O Timings: read=493.679
Planning Time: 1.414 ms
JIT:
Functions: 3
Options: Inlining true, Optimization true, Expressions true,
Deforming true
Timing: Generation 19.747 ms, Inlining 10.281 ms, Optimization
222.619 ms, Emission 362.862 ms, Total 615.509 ms
Execution Time: 4862.113 ms
(12 rows)
Cheers,
Tobias
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tobias.gierke@code-sourcery.de, pgsql-performance@lists.postgresql.org
Subject: Re: JIT performance question
In-Reply-To: <cd19a3f0-7c73-34dc-71e2-be2f69266537@code-sourcery.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox