Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1t3HJ2-00Bboy-93 for pgsql-general@arkaria.postgresql.org; Tue, 22 Oct 2024 16:00:24 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1t3HJ0-0010BL-LH for pgsql-general@arkaria.postgresql.org; Tue, 22 Oct 2024 16:00:23 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1t3HJ0-0010BD-AD for pgsql-general@lists.postgresql.org; Tue, 22 Oct 2024 16:00:22 +0000 Received: from luna.openvistas.net ([207.158.15.156] helo=openvistas.net) by makus.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1t3HIx-002JLz-Lv for pgsql-general@lists.postgresql.org; Tue, 22 Oct 2024 16:00:21 +0000 Received: (qmail 16204 invoked from network); 22 Oct 2024 16:00:18 -0000 Received: from unknown (HELO ?10.0.26.39?) (jross@154.27.111.79) de/crypted with TLSv1.3: AEAD-AES128-GCM-SHA256 [128/128] DN=none by mail.openvistas.net with ESMTPSA; 22 Oct 2024 16:00:18 -0000 Content-Type: multipart/alternative; boundary="------------ZXe2pbtTYNDhO0r3BFIkyA2l" Message-ID: <73235f76-2abd-41b5-a2a4-6cf149413a6a@openvistas.net> Date: Tue, 22 Oct 2024 10:00:17 -0600 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Query performance issue To: pgsql-general@lists.postgresql.org References: <20241016213610.wbfqj3r7gg5ba6eh@hjp.at> Content-Language: en-US From: Jeff Ross In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------ZXe2pbtTYNDhO0r3BFIkyA2l Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 10/21/24 23:31, yudhi s wrote: > > On Thu, Oct 17, 2024 at 3:06 AM Peter J. Holzer wrote: > > > 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. > > > > Tried running the same in postgres and below is the plan from bothe > postgres and mysql. Can you please guide me to understand ,  if > anything else can be done to make it better? > > https://gist.github.com/databasetech0073/746353a9e76d5e29b2fc6abdc80cdef8 > Here's your postgres query as analyzed and made more readable by the most excellent explain.depesz.com. https://explain.depesz.com/s/VyeM#html Under the hints tab are suggestions to bump your work_mem to avoid writing sorts out to disk. Jeff --------------ZXe2pbtTYNDhO0r3BFIkyA2l Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit

On 10/21/24 23:31, yudhi s wrote:


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

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.

     

Tried running the same in postgres and below is the plan from bothe postgres and mysql. Can you please guide me to understand ,  if anything else can be done to make it better?


Here's your postgres query as analyzed and made more readable by the most excellent explain.depesz.com.

https://explain.depesz.com/s/VyeM#html

Under the hints tab are suggestions to bump your work_mem to avoid writing sorts out to disk.

Jeff

--------------ZXe2pbtTYNDhO0r3BFIkyA2l--