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.