From: Christian Quest <cquest@cquest.org>
To: Jeff Janes <jeff.janes@gmail.com>
To: github@cquest.org, pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #16183: PREPARED STATEMENT slowed down by jit
Date: Thu, 2 Jan 2020 22:29:31 +0100
Message-ID: <258f228c-914e-0e34-46ca-9342153396e7@cquest.org> (raw)
In-Reply-To: <CAMkU=1yApeBH0ajaAPa35sD7t==LY=YmACcXXOGG18GwqzNung@mail.gmail.com>
References: <16183-64843a4ebc44d9d2@postgresql.org>
<CAMkU=1yApeBH0ajaAPa35sD7t==LY=YmACcXXOGG18GwqzNung@mail.gmail.com>
The queries are very simple, like:
PREPARE mark_ways_by_node(" POSTGRES_OSMID_TYPE ") AS select id from
planet_osm_ways WHERE nodes && ARRAY[$1];
They are all located here in osm2pgsql source code:
https://github.com/openstreetmap/osm2pgsql/blob/master/src/middle-pgsql.cpp#L786
Here are the EXPLAIN/ANALYZE without jit and with jit:
osm=# explain analyze execute mark_ways_by_node(1836953770);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on planet_osm_ways (cost=2468.37..305182.32
rows=301467 width=8) (actual time=0.039..0.042 rows=2 loops=1)
Recheck Cond: (nodes && '{1836953770}'::bigint[])
Heap Blocks: exact=2
-> Bitmap Index Scan on planet_osm_ways_nodes (cost=0.00..2393.00
rows=301467 width=0) (actual time=0.029..0.029 rows=2 loops=1)
Index Cond: (nodes && '{1836953770}'::bigint[])
Planning Time: 0.171 ms
Execution Time: 0.077 ms
(7 rows)
osm=# PREPARE mark_ways_by_node(bigint) AS select id from
planet_osm_ways WHERE nodes && ARRAY[$1];
PREPARE
osm=# explain analyze execute mark_ways_by_node(1836953770);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on planet_osm_ways (cost=2468.37..305182.32
rows=301467 width=8) (actual time=5.775..5.905 rows=2 loops=1)
Recheck Cond: (nodes && '{1836953770}'::bigint[])
Heap Blocks: exact=2
-> Bitmap Index Scan on planet_osm_ways_nodes (cost=0.00..2393.00
rows=301467 width=0) (actual time=0.512..0.512 rows=2 loops=1)
Index Cond: (nodes && '{1836953770}'::bigint[])
Planning Time: 3.667 ms
JIT:
Functions: 4
Options: Inlining false, Optimization false, Expressions true,
Deforming true
Timing: Generation 0.466 ms, Inlining 0.000 ms, Optimization 0.354
ms, Emission 4.634 ms, Total 5.454 ms
Execution Time: 30.393 ms
(11 rows)
Le 02/01/2020 à 19:00, Jeff Janes a écrit :
>
> The update tool (osm2pgsql) makes heavy use of PREPARED
> statements. Could
> there be a bug in jit+PREPARED causing the statement to be
> recompiled on
> each execute ?
>
>
> I don't think that that is a bug, rather it is just how JIT works at
> the moment (that it is recompiled for each execution). That might be
> improved in a future version.
>
> For the slow down to be 10x though does seem rather extreme. Could
> you isolate the query and post an execution with "EXPLAIN (ANALYZE)"?
> I suspect it is a very cheap query (e.g. a single-row update), but for
> some reason the planner thinks it is expensive, and so JIT kicks in
> when it shouldn't. Because of the high overhead, JIT is only supposed
> to activate for expensive queries, see jit_above_cost.
>
> Cheers,
>
> Jeff
>
> --
> Ce message a été vérifié par *MailScanner* <http://www.mailscanner.info/;
> pour des virus ou des polluriels et rien de
> suspect n'a été trouvé.
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-bugs@postgresql.org
Cc: cquest@cquest.org, jeff.janes@gmail.com
Subject: Re: BUG #16183: PREPARED STATEMENT slowed down by jit
In-Reply-To: <258f228c-914e-0e34-46ca-9342153396e7@cquest.org>
References: <16183-64843a4ebc44d9d2@postgresql.org> <CAMkU=1yApeBH0ajaAPa35sD7t==LY=YmACcXXOGG18GwqzNung@mail.gmail.com> <258f228c-914e-0e34-46ca-9342153396e7@cquest.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