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

Jeff Janes <[email protected]> Fri, 3 Jan 2020 09:50:33 -0500

Newsgroups	gmane.comp.db.postgresql.bugs
Message-ID	<CAMkU=1zEX+kKqV9t_ZP9a+bQ=51koTfYm1qRqBuGkcx5BEuHaw@mail.gmail.com>

On Thu, Jan 2, 2020 at 5:03 PM Christian Quest <[email protected]> wrote:

> 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[])
>
I think your estimation here is falling victim to an deficiency in how
stats are computed on array types when all values in the array (across all
rows) are rare.  See the discussion of this at
https://www.postgresql.org/message-id/flat/CAMkU%3D1x2W1gpEP3AQsrSA30uxQk1Sau5VDOLL4LkhWLwrOY8Lw%40mail.gmail.com

(My quick and dirty patch posted there still compiles and works, if you
would like to test that it fixes the problem for you.)

Because the number of rows is vastly overestimated, so is the cost.  Which
then causes JIT to kick in counter-productively, due to the deranged cost
exceeding jit_above_cost.

Cheers,

Jeff

>