From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: jakub.janecek@comgate.cz
Subject: BUG #15486: PG11 jit on 50x slower than jit off
Date: Mon, 05 Nov 2018 08:10:17 +0000
Message-ID: <15486-05850f065da42931@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 15486
Logged by: Jakub Janeček
Email address: jakub.janecek@comgate.cz
PostgreSQL version: 11.0
Operating system: Debian 9
Description:
Hi team,
we have a problem with PG11 and JIT. Execution time with JIT ON is slower
than JIT OFF. PostgreSQL is running on Debian in LXC Container on Proxmox
VE.
Here is some example:
create table t(a int);
insert into t select generate_series from generate_series(1, 100000);
analyze t;
set jit to off;
explain analyze SELECT
ARRAY( SELECT DISTINCT unnest(array[]::integer[] || t.a) AS unnest) AS
c1,
ARRAY( SELECT DISTINCT unnest(((((array[]::integer[]))) || t.a)) AS
unnest) AS c2
FROM t
;
set jit to on;
explain analyze SELECT
ARRAY( SELECT DISTINCT unnest(array[]::integer[] || t.a) AS unnest) AS
c1,
ARRAY( SELECT DISTINCT unnest(((((array[]::integer[]))) || t.a)) AS
unnest) AS c2
FROM t
;
show jit_provider;
Here are results:
JIT OFF
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Seq Scan on t (cost=0.00..455443.00 rows=100000 width=64) (actual
time=0.032..1047.012 rows=100000 loops=1)
SubPlan 1
-> HashAggregate (cost=0.77..2.27 rows=100 width=4) (actual
time=0.001..0.002 rows=1 loops=100000)
Group Key: unnest(('{}'::integer[] || t.a))
-> ProjectSet (cost=0.00..0.52 rows=100 width=4) (actual
time=0.001..0.001 rows=1 loops=100000)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual
time=0.000..0.000 rows=1 loops=100000)
SubPlan 2
-> HashAggregate (cost=0.77..2.27 rows=100 width=4) (actual
time=0.001..0.002 rows=1 loops=100000)
Group Key: unnest(('{}'::integer[] || t.a))
-> ProjectSet (cost=0.00..0.52 rows=100 width=4) (actual
time=0.001..0.001 rows=1 loops=100000)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual
time=0.000..0.000 rows=1 loops=100000)
Planning Time: 0.162 ms
Execution Time: 1099.676 ms
(13 rows)
--------------------
JIT ON
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Seq Scan on t (cost=0.00..455443.00 rows=100000 width=64) (actual
time=12.979..57391.188 rows=100000 loops=1)
SubPlan 1
-> HashAggregate (cost=0.77..2.27 rows=100 width=4) (actual
time=0.004..0.004 rows=1 loops=100000)
Group Key: unnest(('{}'::integer[] || t.a))
-> ProjectSet (cost=0.00..0.52 rows=100 width=4) (actual
time=0.002..0.002 rows=1 loops=100000)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual
time=0.000..0.000 rows=1 loops=100000)
SubPlan 2
-> HashAggregate (cost=0.77..2.27 rows=100 width=4) (actual
time=0.004..0.004 rows=1 loops=100000)
Group Key: unnest(('{}'::integer[] || t.a))
-> ProjectSet (cost=0.00..0.52 rows=100 width=4) (actual
time=0.002..0.002 rows=1 loops=100000)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual
time=0.000..0.000 rows=1 loops=100000)
Planning Time: 0.092 ms
JIT:
Functions: 600016
Options: Inlining false, Optimization false, Expressions true, Deforming
true
Timing: Generation 55262.932 ms, Inlining 0.000 ms, Optimization 0.481
ms, Emission 12.218 ms, Total 55275.631 ms
Execution Time: 70522.785 ms
(17 rows)
------------------
If you need more details, do not hesitate to contact me.
Thank you,
best regards,
Jakub Janecek
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-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, jakub.janecek@comgate.cz
Subject: Re: BUG #15486: PG11 jit on 50x slower than jit off
In-Reply-To: <15486-05850f065da42931@postgresql.org>
* 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