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
