MIME-Version: 1.0
References: <CH0PR03MB610085FA248C69CDB73494FFFE652@CH0PR03MB6100.namprd03.prod.outlook.com>
In-Reply-To: <CH0PR03MB610085FA248C69CDB73494FFFE652@CH0PR03MB6100.namprd03.prod.outlook.com>
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: Fri, 13 Sep 2024 15:04:40 -0400
Message-ID: <CAKAnmmKbnPSpzJ45N5314=nFDUtsAjwVPkxLCm87v_3qs9z09A@mail.gmail.com>
Subject: Re: Will hundred of thousands of this type of query cause Parsing issue
To: "Wong, Kam Fook (TR Technology)" <kamfook.wong@thomsonreuters.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000056a67d062204e75f"
Archived-At: <https://www.postgresql.org/message-id/CAKAnmmKbnPSpzJ45N5314%3DnFDUtsAjwVPkxLCm87v_3qs9z09A%40mail.gmail.com>
Precedence: bulk

--00000000000056a67d062204e75f
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Fri, Sep 13, 2024 at 11:35=E2=80=AFAM Wong, Kam Fook (TR Technology) <
kamfook.wong@thomsonreuters.com> wrote:

> 1) Where does query parsing occur?
>
>
Always on the server side, although your driver may do something as well.

2) Will this cause extra parsing to the posgress DB?
>

Yes


>   Any pg system table to measure parsing?
>

No

You want to send an array of values to the same query, so it can be
prepared once, like so:

SELECT abc, efg
FROM docloc a
JOIN collection b USING (collection_name)
WHERE a.column1 =3D ANY($1)
AND a.stage_id =3D (
  select max(stage_id) from collection_pit c
  where c.collection_name =3D a.collection_name
  and c.pid_id < $2 and c.stage_code =3D $3
);

Then you can always pass in three arguments, the first being an array of
all the column1 values you want.

You might also want to get familiar with plan_cache_mode:
https://www.postgresql.org/docs/current/sql-prepare.html

Cheers,
Greg

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

<div dir=3D"ltr"><div dir=3D"ltr">On Fri, Sep 13, 2024 at 11:35=E2=80=AFAM =
Wong, Kam Fook (TR Technology) &lt;<a href=3D"mailto:kamfook.wong@thomsonre=
uters.com">kamfook.wong@thomsonreuters.com</a>&gt; wrote:<br></div><div cla=
ss=3D"gmail_quote"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0p=
x 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div c=
lass=3D"msg573974594177292987">


<div lang=3D"EN-US" style=3D"overflow-wrap: break-word;">
<div class=3D"m_573974594177292987WordSection1">
<p class=3D"MsoNormal"><span style=3D"font-size:11pt">1) Where does query p=
arsing occur?</span><br></p><p class=3D"MsoNormal"><span style=3D"font-size=
:11pt">
</span></p></div></div></div></blockquote><div><br></div><div>Always on the=
 server side, although your driver may do something as well.</div><div><br>=
</div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;b=
order-left:1px solid rgb(204,204,204);padding-left:1ex"><div class=3D"msg57=
3974594177292987"><div lang=3D"EN-US" style=3D"overflow-wrap: break-word;">=
<div class=3D"m_573974594177292987WordSection1"><p class=3D"MsoNormal"><spa=
n style=3D"font-size:11pt">2) Will this cause extra parsing to the posgress=
 DB?</span></p></div></div></div></blockquote><div><br></div><div>Yes</div>=
<div>=C2=A0</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 cla=
ss=3D"msg573974594177292987"><div lang=3D"EN-US" style=3D"overflow-wrap: br=
eak-word;"><div class=3D"m_573974594177292987WordSection1"><p class=3D"MsoN=
ormal"><span style=3D"font-size:11pt">=C2=A0 Any pg system table to measure=
 parsing?</span></p></div></div></div></blockquote><div><br></div><div>No</=
div><div><br></div><div>You want to send an array of values to the same que=
ry, so it can be prepared once, like so:</div><div><br></div><div><span cla=
ss=3D"gmail-hljs-keyword">SELECT</span> abc, efg</div><div><span class=3D"g=
mail-hljs-keyword">FROM</span>=C2=A0docloc a</div><div>JOIN collection b US=
ING (collection_name)</div><div>WHERE a.column1 =3D ANY($1)</div><div>AND a=
.stage_id =3D (</div><div>=C2=A0 select max(stage_id) from collection_pit c=
</div><div>=C2=A0 where c.collection_name =3D a.collection_name</div><div>=
=C2=A0 and c.pid_id &lt; $2 and c.stage_code =3D $3</div><div>);</div><div>=
<br></div><div>Then you can always pass in three arguments, the first being=
 an array of all the column1 values you want.</div><div><br></div><div>You =
might also want to get familiar with plan_cache_mode:=C2=A0<a href=3D"https=
://www.postgresql.org/docs/current/sql-prepare.html">https://www.postgresql=
.org/docs/current/sql-prepare.html</a></div><div><br></div><div>Cheers,</di=
v><div>Greg</div><div><br></div></div></div>

--00000000000056a67d062204e75f--