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

From: Christian Quest <cquest(at)cquest(dot)org>
To: Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
Cc: github(at)cquest(dot)org, pgsql-bugs <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: Re: BUG #16183: PREPARED STATEMENT slowed down by jit
Date: 2020-01-03 19:02:18
Message-ID: 01c9805b-b0a9-f4f0-9078-d6737f407200@cquest.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Le 03/01/2020 à 15:50, Jeff Janes a écrit :
> On Thu, Jan 2, 2020 at 5:03 PM Christian Quest <cquest(at)cquest(dot)org
> <mailto: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
>

This wrong cost may have other side effect, like launching parallel workers.

Another person got the same problem, but my simple fix of disabling jit
did not make it for him. My test were done on a smaller database
(OpenStreetMap data extract only covering France), his was on a full
planet dataset. The computed rows where 10x higher.

We found a workaround (disabling jit and parallel workers for the
session), but a more global fix on this wrong evaluation of rows should
be considered for other cases ;)

Thanks for your time on this issue.

Christian

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message Andrey Borodin 2020-01-03 19:05:09 Re: BUG #16122: segfault pg_detoast_datum (datum=0x0) at fmgr.c:1833 numrange query
Previous Message PG Bug reporting form 2020-01-03 15:41:21 BUG #16185: Trigger fires twice and with wrong TG_OP when updating partion key on partitioned table