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

--000000000000242402059b6da41e
Content-Type: text/plain; charset="UTF-8"

On Fri, Jan 3, 2020 at 7:50 AM Jeff Janes <[email protected]> wrote:

> 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
>

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*

--000000000000242402059b6da41e
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr" class=3D"gmail_attr">On =
Fri, Jan 3, 2020 at 7:50 AM Jeff Janes &lt;<a href=3D"mailto:jeff.janes@gma=
il.com">[email protected]</a>&gt; wrote:<br></div><blockquote class=3D"g=
mail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204=
,204,204);padding-left:1ex"><div dir=3D"ltr"><div dir=3D"ltr">On Thu, Jan 2=
, 2020 at 5:03 PM Christian Quest &lt;<a href=3D"mailto:[email protected]" =
target=3D"_blank">[email protected]</a>&gt; wrote:<br></div><div class=3D"g=
mail_quote"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0=
.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
 =20
   =20
 =20
  <div>
    <p>osm=3D# explain analyze execute mark_ways_by_node(1836953770);<br></=
p><p>
      =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0
      QUERY
      PLAN=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0
      <br>
---------------------------------------------------------------------------=
-----------------------------------------------------------<br>
      =C2=A0Bitmap Heap Scan on planet_osm_ways=C2=A0 (cost=3D2468.37..3051=
82.32
      rows=3D301467 width=3D8) (actual time=3D0.039..0.042 rows=3D2 loops=
=3D1)<br>
      =C2=A0=C2=A0 Recheck Cond: (nodes &amp;&amp; &#39;{1836953770}&#39;::=
bigint[])<br>
      </p></div></blockquote><div>I think your estimation here is falling v=
ictim to an deficiency in how stats are computed on array types when all va=
lues in the array (across all rows) are rare.=C2=A0 See the discussion of t=
his at=C2=A0<a href=3D"https://www.postgresql.org/message-id/flat/CAMkU%3D1=
x2W1gpEP3AQsrSA30uxQk1Sau5VDOLL4LkhWLwrOY8Lw%40mail.gmail.com" target=3D"_b=
lank">https://www.postgresql.org/message-id/flat/CAMkU%3D1x2W1gpEP3AQsrSA30=
uxQk1Sau5VDOLL4LkhWLwrOY8Lw%40mail.gmail.com</a></div><div><br></div><div>(=
My quick and dirty patch posted there still compiles and works, if you woul=
d like to test that it fixes the problem for you.)</div><div>=C2=A0</div><d=
iv>Because the number of rows is vastly overestimated, so is the cost.=C2=
=A0 Which then causes JIT to kick in counter-productively, due to the deran=
ged cost exceeding jit_above_cost.</div><div><br></div><div>Cheers,</div><d=
iv><br></div><div>Jeff</div></div></div></blockquote><div><div dir=3D"ltr" =
class=3D"gmail_signature" data-smartmail=3D"gmail_signature"><div dir=3D"lt=
r"><div dir=3D"ltr"><div><br></div><div>Hi Jeff,</div><div><br></div><div>I=
 tried applying your original patch and the later patch from that thread to=
 REL_12_STABLE, unfortunately no luck on either.=C2=A0 The original patch e=
rrors with:</div><div><br></div><div>$ git apply -p1 &lt; array_type_analyz=
e_MCE_V001.patch<br></div><div>error: src/backend/utils/adt/array_typanalyz=
e.c: already exists in working directory<br></div><div><br></div><div>The o=
ther with:</div><div><br></div><div>$ git apply -p1 &lt; array_typanalyze_0=
_mce.patch<br>error: src/backend/utils/adt/array_typanalyze.c: already exis=
ts in working directory<br>error: src/backend/utils/cache/lsyscache.c: alre=
ady exists in working directory<br></div><div><br></div><div>I am happy to =
review and test patches that address this particular issue.=C2=A0 Thanks,</=
div><div><br></div><div style=3D"font-size:small"><div dir=3D"ltr"><div dir=
=3D"ltr"><div><b>Ryan Lambert</b></div><div><br></div></div></div></div></d=
iv></div></div></div><br></div><br><div class=3D"gmail_quote"><blockquote c=
lass=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px soli=
d rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div class=3D"gmail_q=
uote"><div></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px =
0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div>
  </div>

</blockquote></div></div>
</blockquote></div></div>

--000000000000242402059b6da41e--