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)
  select
    *
  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.

https://www.postgresql.org/docs/current/using-explain.html