Inconsistent Execution Times for PostgreSQL Query with Unchanged Heap Fetches

Question

I have a query that selects distinct values from a table, and I've noticed that the execution time significantly decreased after the initial execution.

Here's the query and the corresponding execution plan that i got when i executed the query for 2nd time.

    EXPLAIN ANALYZE SELECT DISTINCT integration_type FROM my_schema.my_table;
    
    Unique (cost=0.43..576843.69 rows=7 width=2) (actual time=0.032..2826.863 rows=8 loops=1)
    -> Index Only Scan using my_index on my_schema.my_table (cost=0.43..538707.38 rows=15254521 width=2) (actual time=0.031..1881.219 rows=14730886 loops=1)
    Heap Fetches: 1381786
    Planning Time: 0.557 ms
    Execution Time: 2826.897 ms

For the first time I executed the query, it took more than 30 seconds. However, successive executions consistently took less than 3 seconds, even though the number of heap fetches remained the same and did not change (still 1,381,786).

I'm curious to understand why the execution time decreased for successive executions despite unchanged heap fetches. Could this be related to caching mechanisms or other optimizations that PostgreSQL employs?

I'm using PostgreSQL version 14.9

Any insights or suggestions on why this behavior occurs and how I can further optimize the query or database settings would be greatly appreciated.

Thanks.

Answer 1

Heap fetches describe how often an index-only scan needed to consult the table to verify visibility. It doesn't describe how often it needed to fetch data from disk rather than memory. You would not expect the required number of heap fetches to change as data becomes memory resident. It still needs to consult those pages, whether they are found in memory or not.

To determine buffer reads, you need to do EXPLAIN (ANALYZE, BUFFERS) and then you would get a line like:

Buffers: shared hit=23 read=19

which tells you how many of the buffers consulted were already in the shared_buffers pool.

But that still doesn't tell you if the buffer reads were real misses (and needed to be read from disk) or were instead found is the OS file cache. So you should also turn on track_io_timing so you get not just a count but also the timings.

Getting rid of the heap fetches needs a VACUUM. A heap fetch which doesn't exist can't need to hit disk. One that does exist may or may not be satisfied by memory depending on how much memory you have and how much other activity is going on.