Garvit's Blog

  An Index-only scan is supposed to return query results just by accessing the index but in Postgresql, an index-only scan can end up accessing table rows (heap memory) as well, which might result in the query taking more time (or other resources) than anticipated. In this blog, I will discuss how we discovered this behavior of Postgresql and how we solved this for our use case. The Problem: We optimized a high IO-consuming read query some time back ( detailed blog ). The optimization we had done was to create appropriate indexes so that query can be resolved using an index-only scan so that there is no need to read table rows, thereby reducing IOPs (Input-Output per second) consumed by the query. But a few weeks down the line we again started observing a gradual increase in IOPs consumed by the query. On checking the query plan, it was still using index-only scan but we found that the query was also doing a lot of disk access and it was accessing heap memory as well. It was not intuit