MIME-Version: 1.0
References: <CAEzWdqfeaLov=e3Dk_wMpzh0yWhGcVJhundmL=QL1X_KqG_Jfw@mail.gmail.com>
 <20241016213610.wbfqj3r7gg5ba6eh@hjp.at> <CAEzWdqdDTPxxzQYJfr_RdJTtLx-kNnc__z8f9nB25wVQuKzapg@mail.gmail.com>
 <CAKAnmmKMzXaUeVg-WLVReCOD-=+8GUq8=Uc7a0jfU6DgzPZ_Yg@mail.gmail.com>
In-Reply-To: <CAKAnmmKMzXaUeVg-WLVReCOD-=+8GUq8=Uc7a0jfU6DgzPZ_Yg@mail.gmail.com>
From: yudhi s <learnerdatabase99@gmail.com>
Date: Wed, 23 Oct 2024 02:06:18 +0530
Message-ID: <CAEzWdqc4jA3D4RX4u3fgHYc37W5h2q=3ucef0yT3ppeB-9MBTg@mail.gmail.com>
Subject: Re: Query performance issue
To: Greg Sabino Mullane <htamfids@gmail.com>, jross@openvistas.net
Cc: pgsql-general@lists.postgresql.org, hjp-pgsql@hjp.at
Content-Type: multipart/alternative; boundary="00000000000071c381062516b910"
Archived-At: <https://www.postgresql.org/message-id/CAEzWdqc4jA3D4RX4u3fgHYc37W5h2q%3D3ucef0yT3ppeB-9MBTg%40mail.gmail.com>
Precedence: bulk

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

On Wed, Oct 23, 2024 at 12:32=E2=80=AFAM Greg Sabino Mullane <htamfids@gmai=
l.com>
wrote:

> To be frank, there is so much wrong with this query that it is hard to
> know where to start. But a few top items:
>
> * Make sure all of the tables involved have been analyzed. You might want
> to bump default_statistics_target up and see if that helps.
>
> * As mentioned already, increase work_mem, as you have things spilling to
> disk (e.g. external merge Disk: 36280kB)
>
> * Don't use the "FROM table1, table2, table3" syntax but use "FROM table1
> JOIN table2 ON (...) JOIN table3 ON (...)
>
> * Try not to use subselects. Things like WHERE x IN (SELECT ...) are
> expensive and hard to optimize.
>
> * You have useless GROUP BY clauses in there. Remove to simplify the quer=
y
>
> * There is no LIMIT. Does the client really need all 135,214 rows?
>
>
>
I tried running the query by removing both the "group by" from the inner
subqueries (I think the initial thought was that they will give distinct
records to the outer query and will thus help), and added limit 500 at the
last and also set the work_mem to 2GB for that session before running the
query. But seeing the response increased to ~5 seconds (from ~3.1 seconds
earlier). Below I have updated the execution plan for the same at the last
section.

https://gist.github.com/databasetech0073/746353a9e76d5e29b2fc6abdc80cdef8

Again , not able to clearly understand the third point you said below. Can
you please clarify a bit more. Do you mean we should write it as exists
/not exists rather IN and NOT IN and that will improve the performance? I
hope the third point doesn't matter much as we have all equijoin used here.
Correct me if I'm wrong.

*"Try not to use subselects. Things like WHERE x IN (SELECT ...) are
expensive and hard to optimize*."

Additionally in the plan which mysql makes and showing the highest response
time, is it suffering because of differences of the speed of the underlying
IO/storage or is it just because of the optimization features which are
available in postgres and not there in mysql ? Trying to understand if it
can be identified from the execution plan itself.

--00000000000071c381062516b910
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 Wed, Oct 23, 2024 at 12:32=E2=80=
=AFAM Greg Sabino Mullane &lt;<a href=3D"mailto:htamfids@gmail.com">htamfid=
s@gmail.com</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"><div dir=3D"ltr">To be frank, there is so much wrong with this q=
uery that it is hard to know where to start. But a few top items:<br><br>* =
Make sure all of the tables involved have been analyzed. You might want to =
bump default_statistics_target up and see if that helps.<br><br>* As mentio=
ned already, increase work_mem, as you have things spilling to disk (e.g. e=
xternal merge Disk: 36280kB)<br><br>* Don&#39;t use the &quot;FROM table1, =
table2, table3&quot; syntax but use &quot;FROM table1 JOIN table2 ON (...) =
JOIN table3 ON (...)<br><br>* Try not to use subselects. Things like WHERE =
x IN (SELECT ...) are expensive and hard to optimize. <br><br>* You have us=
eless GROUP BY clauses in there. Remove to simplify the query<br><br>* Ther=
e is no LIMIT. Does the client really need all 135,214 rows?<br><br><br></d=
iv></blockquote><div><br></div>I tried running the query by removing both t=
he &quot;group by&quot; from the inner subqueries (I think the initial thou=
ght was that they will give distinct records to the outer query and will th=
us help), and added limit 500 at the last and also set the work_mem to 2GB =
for that session before running the query. But seeing the response increase=
d to ~5 seconds (from ~3.1 seconds earlier). Below I have updated the execu=
tion plan for the same at the last section.</div><div class=3D"gmail_quote"=
><br></div><div class=3D"gmail_quote"><a href=3D"https://gist.github.com/da=
tabasetech0073/746353a9e76d5e29b2fc6abdc80cdef8">https://gist.github.com/da=
tabasetech0073/746353a9e76d5e29b2fc6abdc80cdef8</a><br><br>Again , not able=
 to clearly understand the third point you said below. Can you please clari=
fy a bit more. Do you mean we should write it as exists /not exists rather =
IN and NOT IN and that will improve the performance? I hope the third point=
 doesn&#39;t=C2=A0matter much as we have all equijoin used here. Correct me=
 if I&#39;m wrong.<br><br><div><i>&quot;Try not to use subselects. Things l=
ike WHERE x IN (SELECT ...) are expensive and hard to optimize</i>.&quot;=
=C2=A0</div><div><br></div><div>Additionally in the plan which mysql makes =
and showing=C2=A0the highest response time, is it suffering because of diff=
erences of the speed of the underlying IO/storage or is it just because of =
the optimization features which are available in postgres and not there in =
mysql ? Trying to understand if it can be identified from the execution pla=
n itself.</div></div></div>

--00000000000071c381062516b910--