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

On Fri, Jan 3, 2020 at 7:50 AM Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:

> On Thu, Jan 2, 2020 at 5:03 PM Christian Quest <cquest(at)cquest(dot)org> 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
>

Hi Jeff,

I tried applying your original patch and the later patch from that thread
to REL_12_STABLE, unfortunately no luck on either. The original patch
errors with:

$ git apply -p1 < array_type_analyze_MCE_V001.patch
error: src/backend/utils/adt/array_typanalyze.c: already exists in working
directory

The other with:

$ git apply -p1 < array_typanalyze_0_mce.patch
error: src/backend/utils/adt/array_typanalyze.c: already exists in working
directory
error: src/backend/utils/cache/lsyscache.c: already exists in working
directory

I am happy to review and test patches that address this particular issue.
Thanks,

*Ryan Lambert*