Show cache hits for PostgreSQL queries

In order to see if queries executed on your PostgreSQL database are running from memory or from disk, the following explain options can be specified:

EXPLAIN (analyze on, buffers on, verbose on) SELECT email FROM users

This produces the following extra information:

                                                               QUERY PLAN                                                                
-----------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..8.32 rows=1 width=0) (actual time=0.078..0.078 rows=0 loops=1)
   Output: (1)
   Buffers: shared hit=4
   ->  Index Scan using index_users_on_email on public.users  (cost=0.00..8.32 rows=1 width=0) (actual time=0.065..0.065 rows=0 loops=1)
         Output: 1
         Index Cond: ((users.email)::text = 'name@domain'::text)
         Buffers: shared hit=2 read=1
 Total runtime: 0.153 ms
(8 rows)

The output above shows that 2 blocks of the data was taken from a cache hit and 1 block of data was read into cache.

If the same query was run again, all the data would be read from cache:

                                                               QUERY PLAN                                                                
-----------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..8.32 rows=1 width=0) (actual time=0.078..0.078 rows=0 loops=1)
   Output: (1)
   Buffers: shared hit=4
   ->  Index Scan using index_users_on_email on public.users  (cost=0.00..8.32 rows=1 width=0) (actual time=0.065..0.065 rows=0 loops=1)
         Output: 1
         Index Cond: ((users.email)::text = 'name@domain'::text)
         Buffers: shared hit=3
 Total runtime: 0.153 ms
(8 rows)

Last updated: 21/10/2013