Re: BUG #16183: PREPARED STATEMENT slowed down by jit

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é.