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>
In-Reply-To: <CAEzWdqdDTPxxzQYJfr_RdJTtLx-kNnc__z8f9nB25wVQuKzapg@mail.gmail.com>
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: Tue, 22 Oct 2024 15:01:55 -0400
Message-ID: <CAKAnmmKMzXaUeVg-WLVReCOD-=+8GUq8=Uc7a0jfU6DgzPZ_Yg@mail.gmail.com>
Subject: Re: Query performance issue
To: yudhi s <learnerdatabase99@gmail.com>
Cc: pgsql-general@lists.postgresql.org, hjp-pgsql@hjp.at
Content-Type: multipart/alternative; boundary="00000000000052c4620625156962"
Archived-At: <https://www.postgresql.org/message-id/CAKAnmmKMzXaUeVg-WLVReCOD-%3D%2B8GUq8%3DUc7a0jfU6DgzPZ_Yg%40mail.gmail.com>
Precedence: bulk

--00000000000052c4620625156962
Content-Type: text/plain; charset="UTF-8"

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 query

* There is no LIMIT. Does the client really need all 135,214 rows?

Cheers,
Greg

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

<div dir=3D"ltr">To be frank, there is so much wrong with this query that i=
t is hard to know where to start. But a few top items:<br><br>* Make sure a=
ll of the tables involved have been analyzed. You might want to bump defaul=
t_statistics_target up and see if that helps.<br><br>* As mentioned already=
, increase work_mem, as you have things spilling to disk (e.g. external mer=
ge Disk: 36280kB)<br><br>* Don&#39;t use the &quot;FROM table1, table2, tab=
le3&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 (SELEC=
T ...) are expensive and hard to optimize. <br><br>* You have useless GROUP=
 BY clauses in there. Remove to simplify the query<br><br>* There is no LIM=
IT. Does the client really need all 135,214 rows?<br><br>Cheers,<br>Greg<br=
><br></div>

--00000000000052c4620625156962--