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>
 <CAEzWdqc4jA3D4RX4u3fgHYc37W5h2q=3ucef0yT3ppeB-9MBTg@mail.gmail.com> <CAEzWdqe9U7zXSuDPpT8O_E=GykZx=3ttVEwU5wMN0AnFARSZxg@mail.gmail.com>
In-Reply-To: <CAEzWdqe9U7zXSuDPpT8O_E=GykZx=3ttVEwU5wMN0AnFARSZxg@mail.gmail.com>
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: Thu, 24 Oct 2024 10:01:21 -0400
Message-ID: <CAKAnmmL_=6PjxSJD105oSSfEPiCyk_KQp5ZCPz2AsdunznOXeA@mail.gmail.com>
Subject: Re: Query performance issue
To: yudhi s <learnerdatabase99@gmail.com>
Cc: jross@openvistas.net, pgsql-general@lists.postgresql.org, hjp-pgsql@hjp.at
Content-Type: multipart/alternative; boundary="000000000000223301062539726b"
Archived-At: <https://www.postgresql.org/message-id/CAKAnmmL_%3D6PjxSJD105oSSfEPiCyk_KQp5ZCPz2AsdunznOXeA%40mail.gmail.com>
Precedence: bulk

--000000000000223301062539726b
Content-Type: text/plain; charset="UTF-8"

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

I think trying to compare postgres and mysql plans against each other is
not a very useful endeavor. There are fundamental design decisions between
the two. Focus on making your Postgres query the best it can be, full stop.
Optimize your queries, make sure the database is analyzed, and tweak some
configs as needed.

Also, you cannot accidentally forget a join condition.


Yes, this is the primary reason. Cartesian joins hurt.

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?


It gives Postgres more options on how to do things, so yes, it can be
better.

Cheers,
Greg

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

<div dir=3D"ltr"><div dir=3D"ltr"><div class=3D"gmail_quote"><blockquote cl=
ass=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 class=3D"gmail_qu=
ote"><div dir=3D"ltr"><div class=3D"gmail_quote"><div>Additionally in the p=
lan which mysql makes and showing=C2=A0the highest response time, is it suf=
fering because of differences of the speed of the underlying IO/storage or =
is it just because of the optimization features which are available in post=
gres and not there in mysql ? Trying to understand if it can be identified =
from the execution plan itself.</div></div></div></div></div></blockquote><=
div><br></div><div>I think trying to compare postgres and mysql plans again=
st each other is not a very useful endeavor. There are fundamental design d=
ecisions between the two. Focus on making your Postgres query the best it c=
an be, full stop. Optimize your queries, make sure the database is analyzed=
, and tweak some configs as needed.</div><div><br></div><blockquote class=
=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rg=
b(204,204,204);padding-left:1ex">Also, you cannot accidentally forget a joi=
n condition.</blockquote><div><br></div><div>Yes, this is the primary reaso=
n. Cartesian joins hurt.</div><div><br></div><blockquote class=3D"gmail_quo=
te" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204=
);padding-left:1ex">Again , not able to clearly understand the third point =
you said below. Can you please clarify a bit more. Do you mean we should wr=
ite it as exists /not exists rather IN and NOT IN and that will improve the=
 performance?</blockquote><div><br></div><div>It gives Postgres more option=
s on how to do things, so yes, it can be better.</div><div><br></div><div>C=
heers,</div><div>Greg</div><div>=C2=A0</div></div></div>
</div>

--000000000000223301062539726b--