From: "Jonathan S. Katz" <jkatz@postgresql.org>
Message-Id: <7884E7AD-2C41-496E-9829-66B79385DFC7@postgresql.org>
Content-Type: multipart/signed;
 boundary="Apple-Mail=_4B337340-6B94-48AC-8696-BCD663911D0B";
 protocol="application/pgp-signature"; micalg=pgp-sha256
Mime-Version: 1.0 (Mac OS X Mail 10.3 \(3273\))
Subject: Re: Query is over 2x slower with jit=on
Date: Wed, 22 Aug 2018 21:00:42 -0400
In-Reply-To: <20180822231301.ybjvolekzojkme7x@alap3.anarazel.de>
Cc: Andreas Joseph Krogh <andreas@visena.com>,
 Robert Haas <robertmhaas@gmail.com>,
 Alvaro Herrera <alvherre@2ndquadrant.com>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
To: Andres Freund <andres@anarazel.de>
References: <VisenaEmail.31.c08fca6668dabfb5.165630040a6@tc7-visena>
 <2519CF61-3190-4A83-A7A1-0ED3F245518A@postgresql.org>
 <20180822231301.ybjvolekzojkme7x@alap3.anarazel.de>
Precedence: bulk


--Apple-Mail=_4B337340-6B94-48AC-8696-BCD663911D0B
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=utf-8


> On Aug 22, 2018, at 7:13 PM, Andres Freund <andres@anarazel.de> wrote:
>=20
> On 2018-08-22 18:29:58 -0400, Jonathan S. Katz wrote:
>>=20
>>> On Aug 22, 2018, at 2:58 PM, Andreas Joseph Krogh =
<andreas@visena.com> wrote:
>>>=20
>>> P=C3=A5 onsdag 22. august 2018 kl. 20:52:05, skrev Andres Freund =
<andres@anarazel.de <mailto:andres@anarazel.de>>:
>>> On 2018-08-22 19:51:12 +0200, Andreas Joseph Krogh wrote:
>>>> I thought JITing of prepared queries happended once (in "prepare")
>>>=20
>>> No, it happens when the first JITed function is executed.
>>>=20
>>>=20
>>>> so it didn't have to do the JITing every time the query is
>>>> executed. Isn't the previously generated bytecode usable for
>>>> subsequent queries?
>>>=20
>>> No, not currently. There's some reasons preventing that (primarily =
that
>>> we currently rely on addresses of certain things not to change =
during
>>> execution). There's ongoing work to change that, but that's =
certainly
>>> not going to be ready for v11.
>>>=20
>>> Greetings,
>>>=20
>>> Andres Freund
>>>=20
>>>=20
>>> Ok, thanks for clarifying.
>>=20
>> Per earlier note[1] I was able to reproduce this issue with similar =
results to
>> Andreas while running 11 Beta 3.
>>=20
>> jit =3D on
>> https://explain.depesz.com/s/vgzD <https://explain.depesz.com/s/vgzD>
>>=20
>> Planning Time: 0.921 ms
>> JIT:
>>  Functions: 193
>>  Generation Time: 121.595 ms
>>  Inlining: false
>>  Inlining Time: 0.000 ms
>>  Optimization: false
>>  Optimization Time: 58.045 ms
>>  Emission Time: 1201.100 ms
>> Execution Time: 1628.017 ms
>>=20
>> jit =3D off
>> https://explain.depesz.com/s/AvZM <https://explain.depesz.com/s/AvZM>
>>=20
>> Planning Time: 1.398 ms
>> Execution Time: 256.473 ms
>>=20
>> I increased the the search range I used in the query by 3x, and got =
these numbers:
>>=20
>> jit=3Don
>> Planning Time: 0.931 ms
>> JIT:
>>  Functions: 184
>>  Generation Time: 126.587 ms
>>  Inlining: true
>>  Inlining Time: 98.865 ms
>>  Optimization: true
>>  Optimization Time: 20518.982 ms
>>  Emission Time: 7270.963 ms
>> Execution Time: 28772.576 ms
>>=20
>> jit=3Doff
>> Planning Time: 1.527 ms
>> Execution Time: 959.160 ms
>=20
> For the archives sake: This likely largely is the consequence of
> building with LLVM's expensive assertions enabled, as confirmed by
> Jonathan over IM.

I recompiled with the release version of LLVM. jit=3Don was still =
slower,
but the discrepancy was not as bad as the previously reported result:

jit =3D off
Planning Time: 0.938 ms
Execution Time: 935.599 ms

jit =3D on
Planning Time: 0.951 ms
JIT:
  Functions: 184
  Generation Time: 17.605 ms
  Inlining: true
  Inlining Time: 20.522 ms
  Optimization: true
  Optimization Time: 1001.034 ms
  Emission Time: 665.319 ms
Execution Time: 2491.560 ms

However, it was still 2x+ slower, so still +1ing for open items.

Jonathan

--Apple-Mail=_4B337340-6B94-48AC-8696-BCD663911D0B
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
	filename=signature.asc
Content-Type: application/pgp-signature;
	name=signature.asc
Content-Description: Message signed with OpenPGP

-----BEGIN PGP SIGNATURE-----

iQIzBAEBCAAdFiEE+oS2la8r95ogZD/x8QSccp8cZScFAlt+BzoACgkQ8QSccp8c
ZSe+ShAAlLUHI5GzmzjCb2jOjFNyNyT5lQBAx4YkD/FKqTGYmSmUswaSXjLwirBw
6uGbiy1rLkuwl3aoRKHPGlohdkaTryJfi7RFDL1fwXS63ni7GJAsJJY38tzEq9nq
IBmLU41BwKjMsNQLUj+pUYIAd9AQBa1a1M1557AzfL74LQUCGKwKI/4pWwvNDGVg
SSCqV/RNic2tbbnYujd54TSqPKIrdH1jx2moDAoNrPp1uYWR/HnNU8R5nkWP5PYc
GwFlEZyQuTmFsw1+wfT9VRIeg1QICAAPe4dnaAVtN8XJC854EQLfUI73kvxZxjU7
kMJXS60ACHHb5/673qmWb5tQpHk6qZEMH7UNNyM0QveyFA7UU5e4TP1PvAoTiNov
On656L/45J1GyjZScOjB+fpYRlNCmGOMkaai4fdJ6iVmpCKPrkBomyXy/2IwN56G
0bX9/6kRUESTNmw8EFHDHT9PxWkgequJmcDJMbfAjtpQp2USRg6n868wckhjCZUN
K6YUqQDOBnoy8O4xkVdx8qb/vdsMx8eCkDRjI1sv+qwvruPvNzVcaJbIQAcQLiJh
jDuIAXoXpXX/ZdNyBeo2BaxexBa9o+KS3ifPtn5GocnDb58NrtDsHTX+LXdDUuuH
+hOwR7W6gNgIt0x9+6av5HWZGRsA/fMqg8WWk/odEsxvVQ7sufE=
=ym2N
-----END PGP SIGNATURE-----

--Apple-Mail=_4B337340-6B94-48AC-8696-BCD663911D0B--