pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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é. 

view thread (9+ messages)  latest in thread

Message-ID: <258f228c-914e-0e34-46ca-9342153396e7@cquest.org>
Permalink:  ../258f228c-914e-0e34-46ca-9342153396e7@cquest.org/
Also on:    postgresql.org/message-id/258f228c-914e-0e34-46ca-9342153396e7@cquest.org

 · 

reply

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