Today I Learned

A Hashrocket project

Ready to join Hashrocket? Find Openings here and apply today.

Understanding Query I/O in Postgres with BUFFERS

The EXPLAIN command in Postgres can help you understand the query plan for a given query. Furthermore, you can use EXPLAIN ANALYZE to see the estimated query plan and cost vs the actual time and rows.

To take it a step further, you can use EXPLAIN (ANALYZE, BUFFERS) to include a number that represents the I/O disk usage of certain parts of your query.

explain (analyze, buffers)
  from floor_plans
  order by created_at desc

                                                  QUERY PLAN
 Sort  (cost=2.56..2.60 rows=18 width=159) (actual time=0.062..0.065 rows=16 loops=1)
   Sort Key: created_at DESC
   Sort Method: quicksort  Memory: 28kB
   Buffers: shared hit=2
   ->  Seq Scan on floor_plans  (cost=0.00..2.18 rows=18 width=159) (actual time=0.018..0.032 rows=16 loops=1)
         Buffers: shared hit=2
 Planning Time: 0.111 ms
 Execution Time: 0.106 ms
(8 rows)

Make sure that if you run this with a query that writes, that you wrap it in a BEGIN...ROLLBACK statement.

Looking for help? Hashrocket developers believe that data quality is as important as code quality. We enjoy all the challenges of relational databases, from finding the fastest index, to structuring data to fit the needs of an application. We're eager to share our experiences; check out PG Casts, our series of free PostgreSQL screencasts.