Re: BUG #16183: PREPARED STATEMENT slowed down by jit
Ryan Lambert <[email protected]> Sun, 12 Jan 2020 17:37:33 -0700
| Newsgroups | gmane.comp.db.postgresql.bugs |
|---|---|
| Message-ID | <CAN-V+g8VD_vq4CKUJ6oNF1ncY7V0=g1BmwrAL-_3wY0Yk3Q9qg@mail.gmail.com> |
--0000000000001a8fc6059bfaafdc Content-Type: text/plain; charset="UTF-8" On Mon, Jan 6, 2020 at 10:51 AM Jeff Janes <[email protected]> wrote: > On Sun, Jan 5, 2020 at 7:21 PM Ryan Lambert <[email protected]> > wrote: > >> >> 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 >> > > I just use the GNU "patch -p1" command. I don't know what "git apply" is > up to here, and the error message isn't very informative. > > By the way, the patch says "However, there is no histogram to fall back on > when the MCE array is NULL", which is a bit of nonsense. It is ndistinct, > not histogram, which the scalar case falls back on when MCV is NULL. But > there isn't an ndistinct for MCE either, so the conclusion is still sound. > > Cheers, > > Jeff > Thank you, using patch instead of git apply works. Your patch [1] applies cleanly and passes make installcheck-world against REL_12_STABLE. It greatly improves the estimated row counts in the prepared statements [2] that seem to be causing the issue. I have not yet verified this patch fixes the originally reported performance issue regarding updating data with osm2pgsql [3]. The following query uses an ID from Colorado region's ways table. A gzipped plain pg_dump (97MB) is available [4] to make this easier to replicate if helpful. PREPARE mark_ways_by_node(BIGINT) AS SELECT id FROM public.planet_osm_ways WHERE nodes && ARRAY[$1]; EXPLAIN(ANALYZE) EXECUTE mark_ways_by_node(736973985); The explain results from Unpatched Postgres 12.1 shows estimated 10,222 rows when actual rows=1. QUERY PLAN -------------------------------------------------------------------------------------------------------------- ---------------------- Bitmap Heap Scan on planet_osm_ways (cost=119.22..28648.09 rows=10222 width=8) (actual time=0.045..0.047 rows=1 loops=1) Recheck Cond: (nodes && '{736973985}'::bigint[]) Heap Blocks: exact=1 -> Bitmap Index Scan on planet_osm_ways_nodes (cost=0.00..116.67 rows=10222 width=0) (actual time=0.032.. 0.033 rows=1 loops=1) Index Cond: (nodes && '{736973985}'::bigint[]) Planning Time: 0.288 ms Execution Time: 0.102 ms The same data with the patch [1] applied is much closer with an estimated 68 rows. The OP's query plan showed an estimated row count > 300k when actual rows was 2. It's logical to suspect the costly JIT / parallel query would not be chosen if their row count estimates improve by similar margins. QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------- ---- Bitmap Heap Scan on planet_osm_ways (cost=28.53..295.41 rows=68 width=8) (actual time=0.045..0.046 rows=1 loops=1) Recheck Cond: (nodes && '{736973985}'::bigint[]) Heap Blocks: exact=1 -> Bitmap Index Scan on planet_osm_ways_nodes (cost=0.00..28.51 rows=68 width=0) (actual time=0.034..0.035 rows=1 loops =1) Index Cond: (nodes && '{736973985}'::bigint[]) Planning Time: 0.209 ms Execution Time: 0.092 ms I'll try to do more testing in a few days to verify if this patch fixes the issue with osm2pgsql updates. Thanks, Ryan [1] https://www.postgresql.org/message-id/attachment/39315/array_type_analyze_MCE_V001.patch [2] https://github.com/openstreetmap/osm2pgsql/blob/master/src/middle-pgsql.cpp#L786 [3] https://github.com/openstreetmap/osm2pgsql/issues/1045 [4] https://drive.google.com/file/d/1hDpiTiAazG_g1ObMBTRpe-4B_r8gidch/view?usp=sharing --0000000000001a8fc6059bfaafdc Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable <div dir=3D"ltr"><div dir=3D"ltr">On Mon, Jan 6, 2020 at 10:51 AM Jeff Jane= s <<a href=3D"mailto:[email protected]">[email protected]</a>> = wrote:<br></div><div class=3D"gmail_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"><div dir=3D"ltr"><div dir=3D"ltr">On Sun, Jan 5, 2020 at = 7:21 PM Ryan Lambert <<a href=3D"mailto:[email protected]" target= =3D"_blank">[email protected]</a>> wrote:<br></div><div class=3D"gm= ail_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"><div dir=3D"lt= r"><div dir=3D"ltr"><div dir=3D"ltr" class=3D"gmail_attr"><br></div><div><d= iv dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr"><div>I tried applying your= original patch and the later patch from that thread to REL_12_STABLE, unfo= rtunately no luck on either.=C2=A0 The original patch errors with:</div><di= v><br></div><div>$ git apply -p1 < array_type_analyze_MCE_V001.patch<br>= </div><div>error: src/backend/utils/adt/array_typanalyze.c: already exists = in working directory<br></div></div></div></div></div></div></div></blockqu= ote><div><br></div><div>I just use the GNU "patch -p1" command.= =C2=A0 I don't=C2=A0 know what "git apply" is up to here, and= the error message isn't very informative.</div><div><br></div><div>By = the way, the patch says "However, there is no histogram to fall back o= n when the MCE array is NULL", which is a bit of nonsense.=C2=A0 It is= ndistinct, not histogram, which the scalar case falls back on when MCV is = NULL.=C2=A0 But there isn't an ndistinct for MCE either, so the conclus= ion is still sound.</div><div><br></div><div>Cheers,</div><div><br></div><d= iv>Jeff</div></div></div></blockquote><div><br></div>Thank you, using patch= instead of git apply works.=C2=A0 Your patch [1] applies cleanly and passe= s make installcheck-world against=20 REL_12_STABLE.=C2=A0 It greatly improves the estimated row counts in the pr= epared statements [2] that seem to be causing the issue.=C2=A0 I have not yet verified this patch fixes the originally reported performanc= e issue regarding updating data=C2=A0with osm2pgsql [3]. =C2=A0 <br><br>The following query uses an ID from Colorado region's ways tabl= e. A gzipped plain pg_dump (97MB) is available [4] to make this easier to r= eplicate if helpful.<br><br>PREPARE mark_ways_by_node(BIGINT) AS<br>SELECT = id FROM public.planet_osm_ways <br> WHERE nodes && ARRAY[$1];<br>EX= PLAIN(ANALYZE)<br>EXECUTE mark_ways_by_node(736973985);<br><br>The explain = results from Unpatched Postgres 12.1 shows estimated 10,222 rows when actua= l rows=3D1.<br><br><br>=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=A0QUERY PLAN<br><br>----------------------------------------= ----------------------------------------------------------------------<br>-= ---------------------<br>=C2=A0Bitmap Heap Scan on planet_osm_ways =C2=A0(c= ost=3D119.22..28648.09 rows=3D10222 width=3D8) (actual time=3D0.045..0.047 = rows=3D1 loops=3D1)<br>=C2=A0 =C2=A0Recheck Cond: (nodes && '{7= 36973985}'::bigint[])<br>=C2=A0 =C2=A0Heap Blocks: exact=3D1<br>=C2=A0 = =C2=A0-> =C2=A0Bitmap Index Scan on planet_osm_ways_nodes =C2=A0(cost=3D= 0.00..116.67 rows=3D10222 width=3D0) (actual time=3D0.032..<br>0.033 rows= =3D1 loops=3D1)<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: (nodes &am= p;& '{736973985}'::bigint[])<br>=C2=A0Planning Time: 0.288 ms<b= r>=C2=A0Execution Time: 0.102 ms<br><br><br>The same data with the patch [1= ] applied is much closer with an estimated 68 rows. The OP's query plan showed an estimated row count > 300k when actual= rows was 2.=C2=A0 =C2=A0It's logical to suspect the costly JIT / paral= lel query would not be chosen if their row count estimates improve by simil= ar margins.<br><br><br>=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=A0QUERY PLAN<br><br>-----------------------------------------------= ---------------------------------------------------------------------------= --<br>----<br>=C2=A0Bitmap Heap Scan on planet_osm_ways =C2=A0(cost=3D28.53= ..295.41 rows=3D68 width=3D8) (actual time=3D0.045..0.046 rows=3D1 loops=3D= 1)<br>=C2=A0 =C2=A0Recheck Cond: (nodes && '{736973985}'::b= igint[])<br>=C2=A0 =C2=A0Heap Blocks: exact=3D1<br>=C2=A0 =C2=A0-> =C2= =A0Bitmap Index Scan on planet_osm_ways_nodes =C2=A0(cost=3D0.00..28.51 row= s=3D68 width=3D0) (actual time=3D0.034..0.035 rows=3D1 loops<br>=3D1)<br>= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: (nodes && '{73697= 3985}'::bigint[])<br>=C2=A0Planning Time: 0.209 ms<br>=C2=A0Execution T= ime: 0.092 ms<br><br><br>I'll try to do more testing in a few days to v= erify if this patch fixes the issue with osm2pgsql updates.=C2=A0<br><br>Th= anks,<br>Ryan<br><br>[1] <a href=3D"https://www.postgresql.org/message-id/a= ttachment/39315/array_type_analyze_MCE_V001.patch">https://www.postgresql.o= rg/message-id/attachment/39315/array_type_analyze_MCE_V001.patch</a><br>[2]= <a href=3D"https://github.com/openstreetmap/osm2pgsql/blob/master/src/midd= le-pgsql.cpp#L786">https://github.com/openstreetmap/osm2pgsql/blob/master/s= rc/middle-pgsql.cpp#L786</a><br>[3] <a href=3D"https://github.com/openstree= tmap/osm2pgsql/issues/1045">https://github.com/openstreetmap/osm2pgsql/issu= es/1045</a>=C2=A0=C2=A0<br></div><div class=3D"gmail_quote">[4] <a href=3D"= https://drive.google.com/file/d/1hDpiTiAazG_g1ObMBTRpe-4B_r8gidch/view?usp= =3Dsharing">https://drive.google.com/file/d/1hDpiTiAazG_g1ObMBTRpe-4B_r8gid= ch/view?usp=3Dsharing</a><br><br><br><div>=C2=A0</div></div></div> --0000000000001a8fc6059bfaafdc--