MIME-Version: 1.0
References: <CAEzWdqfeaLov=e3Dk_wMpzh0yWhGcVJhundmL=QL1X_KqG_Jfw@mail.gmail.com>
 <20241016213610.wbfqj3r7gg5ba6eh@hjp.at>
In-Reply-To: <20241016213610.wbfqj3r7gg5ba6eh@hjp.at>
From: yudhi s <learnerdatabase99@gmail.com>
Date: Thu, 17 Oct 2024 11:57:24 +0530
Message-ID: <CAEzWdqe79h3o8K8KoC9hky28no9ysYutHvK22G_M-TxaLa5HXg@mail.gmail.com>
Subject: Re: Query performance issue
To: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000005424100624a64833"
Archived-At: <https://www.postgresql.org/message-id/CAEzWdqe79h3o8K8KoC9hky28no9ysYutHvK22G_M-TxaLa5HXg%40mail.gmail.com>
Precedence: bulk

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

On Thu, Oct 17, 2024 at 3:06=E2=80=AFAM Peter J. Holzer <hjp-pgsql@hjp.at> =
wrote:

> On 2024-10-16 23:20:36 +0530, yudhi s wrote:
> > Below is a query which is running for ~40 seconds.
> [...]
> > In the execution path below , the line number marked in bold are the to=
p
> lines
> > for the IN and NOT IN subquery evaluation and they are showing "Actual
> time" as
> >  Approx ~9 seconds and ~8 seconds and they seems to be summed up and th=
e
> top
> > lines showing it to be ~19 seconds. Then onwards it keeps on increasing
> with
> > other "nested loop" joins.
> >
> > Note:- This query is running on a MYSQL 8.0 database. So I'm wondering
> if there
> > is any mysql list similar to Oracle list , in which i can share this
> issue?
>
> The execution plan looks like a postgresql execution plan, not a mysql
> execution plan. Did you run this query on postgresql? That may be
> interesting for comparison purposese, but ultimately it is useless: You
> won't get mysql to work like postgresql, and any tips to speed up this
> query on postgresql (which is all you can expect on a postgresql mailing
> list) probably won't work on mysql.
>
>
>

Agreed. Postgres and mysql may have differences in how the optimizer is
interpreting the stats and coming up with the execution oath. However, I
was looking if the query can be written efficiently by tweaking the current
logic. It's actually spending the majority of the time doing the "IN" and
"NOT IN" evaluation and in that it's using the same exact subquery for the
"UNION ALL" . And the overall execution time is summation of the IN and NOT
IN clause evaluation. So  I was thinking of a better way of writing the
same logically.

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

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote">=
<div dir=3D"ltr" class=3D"gmail_attr">On Thu, Oct 17, 2024 at 3:06=E2=80=AF=
AM Peter J. Holzer &lt;<a href=3D"mailto:hjp-pgsql@hjp.at">hjp-pgsql@hjp.at=
</a>&gt; wrote:<br></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">=
On 2024-10-16 23:20:36 +0530, yudhi s wrote:<br>
&gt; Below is a query which is running for ~40 seconds.<br>
[...]<br>
&gt; In the execution path below , the line number marked in bold are the t=
op lines<br>
&gt; for the IN and NOT IN subquery evaluation and they are showing &quot;A=
ctual time&quot; as<br>
&gt; =C2=A0Approx ~9 seconds and ~8 seconds and they seems to be summed up =
and the top<br>
&gt; lines showing it to be ~19 seconds. Then onwards it keeps on increasin=
g with<br>
&gt; other &quot;nested loop&quot; joins.<br>
&gt; <br>
&gt; Note:- This query is running on a MYSQL 8.0 database. So I&#39;m wonde=
ring if there<br>
&gt; is any mysql list similar to Oracle list , in which i can share this i=
ssue?<br>
<br>
The execution plan looks like a postgresql execution plan, not a mysql<br>
execution plan. Did you run this query on postgresql? That may be<br>
interesting for comparison purposese, but ultimately it is useless: You<br>
won&#39;t get mysql to work like postgresql, and any tips to speed up this<=
br>
query on postgresql (which is all you can expect on a postgresql mailing<br=
>
list) probably won&#39;t work on mysql.<br>
<br>=C2=A0 =C2=A0 =C2=A0=C2=A0<br></blockquote><div><br></div><div>Agreed. =
Postgres and mysql may have differences in how the optimizer=C2=A0is interp=
reting the stats and coming up with the execution oath. However, I was look=
ing if the query can be written efficiently by tweaking the current logic. =
It&#39;s actually spending the majority of the time doing the &quot;IN&quot=
; and &quot;NOT IN&quot; evaluation and in that it&#39;s=C2=A0using the sam=
e exact subquery for the &quot;UNION ALL&quot; . And the overall execution =
time is summation of the IN and NOT IN clause evaluation. So=C2=A0 I was=C2=
=A0thinking=C2=A0of a better way of writing the same logically.</div></div>=
</div>

--0000000000005424100624a64833--