Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nAXHX-0001mk-8t for pgsql-performance@arkaria.postgresql.org; Thu, 20 Jan 2022 13:15:15 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nAXHW-0000FL-7j for pgsql-performance@arkaria.postgresql.org; Thu, 20 Jan 2022 13:15:14 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nAXHV-0000FB-QJ for pgsql-performance@lists.postgresql.org; Thu, 20 Jan 2022 13:15:13 +0000 Received: from mout.kundenserver.de ([217.72.192.75]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nAXHS-0007vA-A7 for pgsql-performance@postgresql.org; Thu, 20 Jan 2022 13:15:12 +0000 Received: from ANAT ([78.197.128.121]) by mrelayeu.kundenserver.de (mreue107 [212.227.15.184]) with ESMTPSA (Nemesis) id 1MowbA-1mWWZC2YDW-00qPK5; Thu, 20 Jan 2022 14:15:04 +0100 From: "Michel SALAIS" To: "'David G. Johnston'" , "'Ludwig Isaac Lim'" Cc: References: <1017307461.1415193.1642600375850.ref@mail.yahoo.com> <1017307461.1415193.1642600375850@mail.yahoo.com> In-Reply-To: Subject: RE: PostgreSQL 12.8 Same Query Same Execution Plan Different Time Date: Thu, 20 Jan 2022 14:15:02 +0100 Message-ID: <008701d80dff$bbe3f510$33abdf30$@msym.fr> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0088_01D80E08.1DA96E80" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQLeqA/Y6WJHL33hn1hp4/iMX2f0yAG4iDHtAigw/I2qP6caQA== Content-Language: fr X-Provags-ID: V03:K1:ts9av9/kEcysArputjXw0UtBmBwn0Ja/loLKFJZ1WB2dSbfW7BX 41JicfmmwL/gm+e3A2w0RRYNkfSdMbNM70yXfjDIREGrN/2a2SYys6N7GZvuaRNimwWET8i f0XFHWrM212vTmAB7MsyO0KrKOMnaMQ/AROSTW2rkVNonmUyMytL4cc78kR2b8FO5TI4+jx E1FydiyopLNbiJec1QK8g== X-Spam-Flag: NO X-UI-Out-Filterresults: notjunk:1;V03:K0:xV8WGIO28hU=:YN9bQ20fQrDpH39Aj3sS2b dVjJRGs96t2U09dAFSSiwrR0X2QmPmBZEyNvmO8kVBkBn0kNfwDNtlCRIYOoVUi1Vn7H9ZTwa Tim5Mf44mxlbObR/OsQzWBzzOojpuK/TbDBTC7UNm7lC9LdKAU8a0zCmpgvtHc58AhzqccPpe hWerPQ4EL1Nw6vRC2VnDxVBLTx0rIek7r0wTEM6plJ9HX3hAlsO6fwVPtMbhiPP4j9RnrgMYt CuIC7ykG6N/thHWaRivSelcCVx/O8hES4Qv6C9XchpP/+19xp7VWwv9mgHvnJ9BlIlqo8qz33 +X8PNyTv0RoZLHDWWwtUxXSjOBrjXiteO7ji1Gx+WeL8PUOfmgdfWEhjypNzTk+gs2aDgoOIC 4TuozmTUcJ12ghkO0bB4tniUZ42NnCnjTZ4uL3RaqBntR2pw78lEerlEhSI/rpIHvd4TUwoX0 KC59Dz2i+MqKkavgPxyZEjFHa90ESxzS3ZO1PhQkrR5dTfil/T0sXtcEgGuuXMgey4pciZnhI 1vKv/LudSPY04KdhQgA35fgV42/0DXGAiuNmExMKPU1NPizwvX9DeLuC4ZOZxD5xXDbWub+J6 +Aww0kyYq9ZKXyqR4o3XVqICm3WYg8eDbteconUyugdSO/9AhgjJuvSvn1EhUUTThTBCjjYR2 g9OWYWydkMZdQWEoWikoSlhZYemHWulndVf2R9gt+pXEn4lVAq168kHlHn+VCHf7ftHE= List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_0088_01D80E08.1DA96E80 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Michel SALAIS De : David G. Johnston =20 Envoy=C3=A9 : mercredi 19 janvier 2022 16:11 =C3=80 : Ludwig Isaac Lim Cc : pgsql-performance@postgresql.org Objet : Re: PostgreSQL 12.8 Same Query Same Execution Plan Different = Time =20 On Wed, Jan 19, 2022 at 7:59 AM Ludwig Isaac Lim > wrote: I noticed that different is actually in Nested Loop join. One is taking = 2 minutes, other is taking 12 seconds. I find this puzzling as I assume = the nested loop should be done in memory. =20 Everything is done in memory, but the data has to get there first (hence = BUFFERS as you figured out below). =20 The disk is gp2 SDD so I'm even more baffled by this. What could be the = factors that affect the speed of nested loop. I notice for that both = loops the rows is 7780 and loops is 1. I don't think those are big = numbers =20 The loops are ~=3D 400 and 6,000 =20 It was only after the running the 2 queries that I realize I could do = EXPLAIN (ANALYZE, BUFFERS), but I couldn't reproduce the slowness. =20 Did you (can you even in RDS) attempt to clear those buffers? If the = first query ran slowly because none of the data was in memory (which you = don't know for certain because you didn't run with BUFFERS option then) = then subsequent runs would indeed be faster (the implementation of = shared buffers having fulfilled one of its major purposes in life). =20 I'll agree buffers for that query does not seem to account for nearly = two minutes...though as RDS is a shared resource I'd probably chalk at = least some of it to contention on the underlying hardware (disk likely = being more problematic than memory). =20 David J. Hi, =20 Another point to check is eventually IOPS=E2=80=A6 It depends on the contracted service, If the quantity of IOPS is = guaranteed or not. When it is not guaranteed and a sufficiently heavy = load (in I/O) was executed for a while, the value of IOPS falls down = dramatically and then you are sure to have performance problems=E2=80=A6 =20 Michel SALAIS =20 ------=_NextPart_000_0088_01D80E08.1DA96E80 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

Michel = SALAIS

De : David G. Johnston = <david.g.johnston@gmail.com>
Envoy=C3=A9 : = mercredi 19 janvier 2022 16:11
=C3=80 : Ludwig Isaac Lim = <ludz_lim@yahoo.com>
Cc : = pgsql-performance@postgresql.org
Objet : Re: PostgreSQL = 12.8 Same Query Same Execution Plan Different = Time

 

On Wed, = Jan 19, 2022 at 7:59 AM Ludwig Isaac Lim <ludz_lim@yahoo.com> = wrote:


I = noticed that different is actually in Nested Loop join. One is = taking 2 minutes, other is taking 12 seconds. I find this puzzling as I = assume the nested loop should be done in = memory.

 

Everything is done in memory, = but the data has to get there first (hence BUFFERS as you figured out = below).

 


The = disk is gp2 SDD so I'm even more baffled by this. What could = be the factors that affect the speed of nested loop. I notice for that = both loops the rows is 7780 and loops is 1. I don't = think those are big numbers

 

The = loops are ~=3D 400 and 6,000

 


It = was only after the running the 2 queries that I realize I could = do EXPLAIN (ANALYZE, BUFFERS), but I couldn't reproduce the = slowness.

 

Did you = (can you even in RDS) attempt to clear those buffers?  If the first = query ran slowly because none of the data was in memory (which you don't = know for certain because you didn't run with BUFFERS option then) then = subsequent runs would indeed be faster (the implementation of shared = buffers having fulfilled one of its major purposes in = life).

 

I'll agree buffers for that = query does not seem to account for nearly two minutes...though as RDS is = a shared resource I'd probably chalk at least some of it to contention = on the underlying hardware (disk likely being more problematic than = memory).

 

David = J.

Hi,

 

Another point to check is = eventually IOPS=E2=80=A6

It depends on the = contracted service, If the quantity of IOPS is guaranteed or not. When = it is not guaranteed and a sufficiently heavy load (in I/O) was executed = for a while, the value of IOPS falls down dramatically and then you are = sure to have performance problems=E2=80=A6

 

Michel = SALAIS

 

------=_NextPart_000_0088_01D80E08.1DA96E80--