First, thanks for the reply :) So, I do know which part is taking a lot of IO time and it's to be honest any 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 anymore. 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 <x...@thebuild.com> Sent: Monday, March 31, 2025 6:15:39 PM To: Eden Aharoni <ed...@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 x...@thebuild.com. Learn why this is important at https://aka.ms/LearnAboutSenderIdentification ] > On Mar 31, 2025, at 06:54, Eden Aharoni <ed...@legitsecurity.com> wrote: > Is this expected IO read rate? I can’t help but feel we’re missing something > 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 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. 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 where the I/O is being used within the query. 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.
