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