From: Michel SALAIS <msalais@msym.fr>
To: 'David G. Johnston' <david.g.johnston@gmail.com>
To: 'Ludwig Isaac Lim' <ludz_lim@yahoo.com>
Cc: pgsql-performance@postgresql.org
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> (raw)
In-Reply-To: <CAKFQuwYes717OwjWfnchaDQS6i3UN7zHG7qJF5bpLByN6KY5uA@mail.gmail.com>
References: <1017307461.1415193.1642600375850.ref@mail.yahoo.com>
<1017307461.1415193.1642600375850@mail.yahoo.com>
<CAKFQuwYes717OwjWfnchaDQS6i3UN7zHG7qJF5bpLByN6KY5uA@mail.gmail.com>
Michel SALAIS
De : David G. Johnston <david.g.johnston@gmail.com>
Envoyé : mercredi 19 janvier 2022 16:11
À : 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 <mailto: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 ~= 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…
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…
Michel SALAIS
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: msalais@msym.fr, david.g.johnston@gmail.com, ludz_lim@yahoo.com
Subject: RE: PostgreSQL 12.8 Same Query Same Execution Plan Different Time
In-Reply-To: <008701d80dff$bbe3f510$33abdf30$@msym.fr>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox