Date: Wed, 16 Oct 2024 23:36:10 +0200
From: "Peter J. Holzer" <hjp-pgsql@hjp.at>
To: pgsql-general@lists.postgresql.org
Subject: Re: Query performance issue
Message-ID: <20241016213610.wbfqj3r7gg5ba6eh@hjp.at>
Mail-Followup-To: pgsql-general@lists.postgresql.org
References: <CAEzWdqfeaLov=e3Dk_wMpzh0yWhGcVJhundmL=QL1X_KqG_Jfw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="22e6kcim53mwrc7p"
Content-Disposition: inline
In-Reply-To: <CAEzWdqfeaLov=e3Dk_wMpzh0yWhGcVJhundmL=QL1X_KqG_Jfw@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/20241016213610.wbfqj3r7gg5ba6eh%40hjp.at>
Precedence: bulk


--22e6kcim53mwrc7p
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

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 top =
lines
> for the IN and NOT IN subquery evaluation and they are showing "Actual ti=
me" as
> =A0Approx ~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 w=
ith
> other "nested loop" joins.
>=20
> 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 issu=
e?

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.

        hp

--=20
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

--22e6kcim53mwrc7p
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAmcQMcoACgkQ8g5IURL+
KF2QgQ//WDaksoBv9NMehbZvR45x06e5AidgKRnMYiHO8438WX3p/sdHIi2HS5wn
cv9JqZX+EG54EIhtFOVpFiOpEFJGYQXOz5nLWeuFgWMr+35/G7Wkgp3+a4/mmQ3w
jlwD0367zSoC8cXOuKfoZL89w4CwQ5XydH6+iv1EIEoaavBB7zrQmVfdlsJHCMMW
QOhI2mYdkqsYVnRT1T/7ZLjaUwY+Cw5TDnpfLr0TgOEC8Riy7Yb7EPZTxbGTai/4
q/RooVnyK5OZUNW3pNnIOdKNlSaAJFcxnYBa+HaPHU4S/N7HC0OI9T7f+uAZRGLK
6K+4gZwRf6iqlIW1uYEJ0hd1bUYOTPf7I8XdF0O7aWyFeR74/H0JnAze73Ed+3YJ
HmePDkuwPh938fwy7KaTpxd+UeSvD1wTCbT0AQkGqUj75IsZrY3+DTrB05vQdiw0
57jIR/aNLwrSETzZBkJAsesn+v+V4Q2dxrXfWC2bEjouq3D+83IOY6nYfPlUcDrU
r2260HTvrPc/Yb8/+sOmAUQWR1Mjft6BcOfpDZcqx1wh1NCj4GXPM0h5lDr39EZx
RnQ9mjhOnowIbwzgvL/aekM2DmfRFH5/6vJoFhckVxOpNKNEmrst8Q+1Tirak0u/
cgoaqDIJmE1mHpax9TExr2ilKkhD+yYbINTU+EQ9QkaAgdJhaeU=
=If4D
-----END PGP SIGNATURE-----

--22e6kcim53mwrc7p--