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

Hi,

On 2020-01-02 22:29:31 +0100, Christian Quest wrote:
> 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)

I'm not too surprised heuristics down't work, if the row sestimate is
off by ~5 orders of magnitude... Caching could "fix" this by making the
JIT cost less noticable, but you're very liable to get bad plan
decisions as long as you're that far off with estimated plan costs.

Greetings,

Andres Freund