From: Eden Aharoni <edena@legitsecurity.com>
To: Christophe Pettus <xof@thebuild.com>
CC: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Subject: Re: [EXTERNAL] Re: RDS IO Read time
Thread-Topic: [EXTERNAL] Re: RDS IO Read time
Thread-Index: AQHbokLzWKEJygYLh0CXroqg3FB1SrONWv+AgAAkT5U=
Date: Mon, 31 Mar 2025 17:32:15 +0000
Message-ID:
 <PA6PR03MB102418866F5C140FB0604E1EBBBAD2@PA6PR03MB10241.eurprd03.prod.outlook.com>
References:
 <DBBPR03MB102227BDDC40A11527BF6C0C0BBAD2@DBBPR03MB10222.eurprd03.prod.outlook.com>
 <DA3CFE03-C653-41D8-8F85-604A948C71F1@thebuild.com>
In-Reply-To: <DA3CFE03-C653-41D8-8F85-604A948C71F1@thebuild.com>
Accept-Language: en-US
Content-Language: en-US
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0:
 =?Windows-1252?Q?TuceEKRLqYq22vA7+MFvOUo7TiD4JL7/A0AZ0ttOEmT5+YGdgLPrslpN?=
 =?Windows-1252?Q?XlHd5d+EcgMqwL1ALt0qDZMq/zAX2t3rqDMNV9SGQsxr2k8DV3MxK/dq?=
 =?Windows-1252?Q?lNVUpYcvO/Ni1LI9IGTy+8UGG2GSVqxf3l5DbrpURBua8lllaV+2wjGN?=
 =?Windows-1252?Q?juwB2lwzCNOxg01UvMYseNzhs6DMn3+Skv4lqE6X/Da0W07s9/OiYQmG?=
 =?Windows-1252?Q?Q/EXuza1hLa30m5w/i9/RgVOhPmvJczTqqhvNpmK6FtuKjqqBZquL7jj?=
 =?Windows-1252?Q?WFuZ4U9Ofh9h/oXccfHAisadunrCCJoeYysb0AisDtoI+0B/+oJzXA/G?=
 =?Windows-1252?Q?rlpkNocJwWwd0ccxyEb06WdMI6hMhBi/57YlNk1GNs/0NtUONNiz4cTk?=
 =?Windows-1252?Q?6tInmbBSXE6VVgOHLRd1qaj05OVJZr8X52Pe3PTGc8u6w3NUUtzSQZIG?=
 =?Windows-1252?Q?dbWkDnvXKyn6xt3JdMSGlUWtYV1f46nYx0ucemmKuvAOKn0YSpZsOLda?=
 =?Windows-1252?Q?q6qo3GbSawvOBp1Ba/7PxUbsyJAmma7WFxS6Lj/tbB3NMi0iB3V9eHhY?=
 =?Windows-1252?Q?C58PmD//Hya3C1SuzZ34vReaaSTBGr6JZ8nZxqijI6695ODLCqSQjyCJ?=
 =?Windows-1252?Q?UKpLtHpujhtwohPJa/MUrEepwACjZmTJTm2XK2wDlkKdu0i9LwXwBtDH?=
 =?Windows-1252?Q?nOcJKZ2g0TAgBCekPlXMB8/NYvsFqo3wWuum6GHmPZwwSlE2CXwCt6y1?=
 =?Windows-1252?Q?0hJdbwuNYOTgV6oZ+INb4DZQGwctrXWIjH2ZkQBY7VV8TzB/VCbpEnEZ?=
 =?Windows-1252?Q?7nI/Is044mIDl8kHOLzYVtiNYJexhsdlJuE7Z5fWfHJcp/faLjBa1cYm?=
 =?Windows-1252?Q?sgxA2auPYUJPlCy1OQE4ciBAkel70aXzmO2ERUkNE1FW8M4A8RRH+MHV?=
 =?Windows-1252?Q?BCdlpEkMn778j2I8YIl8ed90t3BdR5URtE1OhhvuaMAFmQnHEMiaKxpS?=
 =?Windows-1252?Q?jxRzljCHNiQ+k0A1TthspqEMcrDHlITh4v04dyj6IqpOOGwQ7n+GWwpn?=
 =?Windows-1252?Q?+65Ad/exVmuc6RT4Y0Zcmu/CCCVRamqRwuid0ggWQbHTEbiAiiYmULLk?=
 =?Windows-1252?Q?q4w9+O8qSuHUtdm00TEPgSaCeruRD2YUOH+fu//OOD6Of5QwCpqsD4h7?=
 =?Windows-1252?Q?UgIs9FnJhLSyNzUfjld4qTJC4MvUZzWpKSdOHWabFOqMLExxLcBbwxbF?=
 =?Windows-1252?Q?RNDam1UohxYEaaJZGyc0EN0R/pj1K3Sf80dnnZSYgyrQ24vkl2WO+hBy?=
 =?Windows-1252?Q?shcZk6oq8mZRE4XMal4EfYUGtxJFXrOYbw3qS74XOPHXX76mVcEdnJGD?=
 =?Windows-1252?Q?eNV4xbhneDS7hA7egnVjtzAr1njLoEa11Xr7PHyZD4paSIeclZ01VZf+?=
 =?Windows-1252?Q?p7KzsPP/ElxJXNBg3uKyBAOwi/jJlBK0djU+pWulS+YZXT4sWYm3IDoM?=
 =?Windows-1252?Q?8PtK8yudW0YcQsMIaQq6N8/5lxYRlWSz47Az5+xguT9T5CIx7fmn3tVN?=
 =?Windows-1252?Q?0afB111gnkIGY3JUpWl26T544g5aCVLMMiGNdJxPBUNnnFQrao6F+cpk?=
 =?Windows-1252?Q?/Q25y7xdOPFNWzX81+8Gn/LIMZDspbqI/6WZ8MSJh8kYt5x9pylc2uyO?=
 =?Windows-1252?Q?brSdl/SiFIsKSTAktaWRtLCQ4uelJSUbaCWIwTjLZ2NbOQRf1HqGlw?=
 =?Windows-1252?Q?=3D=3D?=
Content-Type: multipart/alternative;
	boundary="_000_PA6PR03MB102418866F5C140FB0604E1EBBBAD2PA6PR03MB10241eu_"
MIME-Version: 1.0
X-OriginatorOrg: legitsecurity.com
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-AuthSource: PA6PR03MB10241.eurprd03.prod.outlook.com
X-MS-Exchange-CrossTenant-Network-Message-Id: d5dc6129-b884-492d-1a5b-08dd7079facf
X-MS-Exchange-CrossTenant-originalarrivaltime: 31 Mar 2025 17:32:15.7563
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: 1d036cb2-b1cc-4321-8a87-6f43158d9e1a
X-MS-Exchange-CrossTenant-mailboxtype: HOSTED
X-MS-Exchange-CrossTenant-userprincipalname: fW2+z83dhxWpuLzqpo4mHvM6dceX+dEwmmo3nfWDAbDZvuaUSEzjvUjWP2rR2hT3naJlH7ltZrMNsEIj7GIqXQ==
X-MS-Exchange-Transport-CrossTenantHeadersStamped: AS8PR03MB10038
List-Id: <pgsql-general.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-general@lists.postgresql.org>
List-Owner: <mailto:pgsql-general-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-general>
Archived-At: <https://www.postgresql.org/message-id/PA6PR03MB102418866F5C140FB0604E1EBBBAD2%40PA6PR03MB10241.eurprd03.prod.outlook.com>
Precedence: bulk

--_000_PA6PR03MB102418866F5C140FB0604E1EBBBAD2PA6PR03MB10241eu_
Content-Type: text/plain; charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable

First, thanks for the reply :)
So, I do know which part is taking a lot of IO time and it's to be honest a=
ny node that reads from the disk.. of course, we're running EXPLAIN on our =
queries (to be more specific we use auto_explain) but we can't seem to find=
 what could cause an index scan that reads 34 MB to take more than a second=
 (1.2 sec).. we do know that our dataset doesn't fit the memory and we were=
 ok with that but with IO being so slow we just don't know what to do anymo=
re. Any other suggestions other than contact AWS (which we did but it seems=
 this path won't lead anywhere).

Thanks again

Get Outlook for Android<https://aka.ms/AAb9ysg>
________________________________
From: Christophe Pettus <xof@thebuild.com>
Sent: Monday, March 31, 2025 6:15:39 PM
To: Eden Aharoni <edena@legitsecurity.com>
Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: [EXTERNAL] Re: RDS IO Read time

[You don't often get email from xof@thebuild.com. Learn why this is importa=
nt at https://aka.ms/LearnAboutSenderIdentification ]

> On Mar 31, 2025, at 06:54, Eden Aharoni <edena@legitsecurity.com> wrote:
> Is this expected IO read rate? I can=92t help but feel we=92re missing so=
mething here..

Really, no particular I/O rate is "expected": if PostgreSQL needs that much=
 data, it'll use that much I/O to get it.  From your description, it's like=
ly that it's a case of the working set for the database just not fitting in=
to the memory you have, so PostgreSQL needs to go out to secondary storage =
a lot to fetch the data.

The best first step is to use Performance Insights to see which queries are=
 using I/O, and run sample ones with EXPLAIN (ANALYZE, BUFFERS) to see wher=
e the I/O is being used within the query.  Given that you allow users to as=
semble arbitrary queries, it's likely that PostgreSQL is having to use a wi=
de variety of indexes (or sequential scans), so it can't successfully cache=
 a particular set in memory.

--_000_PA6PR03MB102418866F5C140FB0604E1EBBBAD2PA6PR03MB10241eu_
Content-Type: text/html; charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable

<html>
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3DWindows-1=
252">
</head>
<body>
<div dir=3D"auto">First, thanks for the reply :)</div>
<div dir=3D"auto">So, I do know which part is taking a lot of IO time and i=
t's to be honest any node that reads from the disk.. of course, we're runni=
ng EXPLAIN on our queries (to be more specific we use auto_explain) but we =
can't seem to find what could cause
 an index scan that reads 34 MB to take more than a second (1.2 sec).. we d=
o know that our dataset doesn't fit the memory and we were ok with that but=
 with IO being so slow we just don't know what to do anymore. Any other sug=
gestions other than contact AWS
 (which we did but it seems this path won't lead anywhere).</div>
<div dir=3D"auto"><br>
</div>
<div dir=3D"auto">Thanks again&nbsp;</div>
<div id=3D"ms-outlook-mobile-body-separator-line" dir=3D"auto"><br>
</div>
<div id=3D"ms-outlook-mobile-signature" dir=3D"auto">Get <a href=3D"https:/=
/aka.ms/AAb9ysg">
Outlook for Android</a></div>
<hr style=3D"display:inline-block;width:98%" tabindex=3D"-1">
<div id=3D"divRplyFwdMsg" dir=3D"ltr"><font face=3D"Calibri, sans-serif" st=
yle=3D"font-size:11pt" color=3D"#000000"><b>From:</b> Christophe Pettus &lt=
;xof@thebuild.com&gt;<br>
<b>Sent:</b> Monday, March 31, 2025 6:15:39 PM<br>
<b>To:</b> Eden Aharoni &lt;edena@legitsecurity.com&gt;<br>
<b>Cc:</b> pgsql-general@lists.postgresql.org &lt;pgsql-general@lists.postg=
resql.org&gt;<br>
<b>Subject:</b> [EXTERNAL] Re: RDS IO Read time</font>
<div>&nbsp;</div>
</div>
<div class=3D"BodyFragment"><font size=3D"2"><span style=3D"font-size:11pt;=
">
<div class=3D"PlainText">[You don't often get email from xof@thebuild.com. =
Learn why this is important at
<a href=3D"https://aka.ms/LearnAboutSenderIdentification">https://aka.ms/Le=
arnAboutSenderIdentification</a> ]<br>
<br>
&gt; On Mar 31, 2025, at 06:54, Eden Aharoni &lt;edena@legitsecurity.com&gt=
; wrote:<br>
&gt; Is this expected IO read rate? I can=92t help but feel we=92re missing=
 something here..<br>
<br>
Really, no particular I/O rate is &quot;expected&quot;: if PostgreSQL needs=
 that much data, it'll use that much I/O to get it.&nbsp; From your descrip=
tion, it's likely that it's a case of the working set for the database just=
 not fitting into the memory you have, so PostgreSQL
 needs to go out to secondary storage a lot to fetch the data.<br>
<br>
The best first step is to use Performance Insights to see which queries are=
 using I/O, and run sample ones with EXPLAIN (ANALYZE, BUFFERS) to see wher=
e the I/O is being used within the query.&nbsp; Given that you allow users =
to assemble arbitrary queries, it's likely
 that PostgreSQL is having to use a wide variety of indexes (or sequential =
scans), so it can't successfully cache a particular set in memory.<br>
</div>
</span></font></div>
</body>
</html>

--_000_PA6PR03MB102418866F5C140FB0604E1EBBBAD2PA6PR03MB10241eu_--