EXPLAIN (ANALYZE, BUFFERS) & I/O Analysis in PostgreSQL

calendar_today May 26, 2025 schedule 6 min read

Learning Objectives: By the end of this lesson, you will be able to:

Use EXPLAIN (ANALYZE, BUFFERS) to analyze query I/O behavior

Interpret buffer statistics (shared hit, shared read, local hit, local read, temp read/written)

Identify and troubleshoot high disk I/O in query plans

1. Welcome and Introduction (5 minutes)

Icebreaker Activity

Have you ever noticed a query that runs fast sometimes, but is slow at other times? What if the difference is whether your data is already in memory or needs to be read from disk? Today, you’ll learn how to see exactly how much I/O your queries are doing!

Lesson Overview

Duration: 90 minutes
Prerequisites: Completed Days 1-2, basic understanding of query plans and costs
Materials Needed: PostgreSQL database, SQL client

2. Learning Objectives and Session Outline (5 minutes)

What We’ll Cover Today:

What are buffers in PostgreSQL?
How to use EXPLAIN (ANALYZE, BUFFERS)
Understanding buffer statistics in query plans
Identifying high disk I/O and its impact
Hands-on practice and exercises
Common pitfalls and troubleshooting

Success Criteria:

By the end of this session, you should be able to interpret buffer statistics in query plans and use them to optimize I/O-heavy queries.

3. Foundation Knowledge (15-20 minutes)

Core Concepts

What Are Buffers?

Buffers are blocks of memory PostgreSQL uses to cache table and index data. When a query needs data, PostgreSQL first checks if it’s already in a buffer (a “hit”). If not, it reads from disk (a “read”).

Real-World Analogy: Think of buffers like your computer’s RAM cache. If you open a file you just used, it’s fast (cache hit). If you open a file for the first time, it takes longer (disk read).

Why This Matters:

Disk reads are much slower than memory hits
High disk I/O can slow down queries and the whole database

`EXPLAIN (ANALYZE, BUFFERS)`

Adds buffer usage statistics to the query plan
Shows how many blocks were read from disk vs. found in memory

Buffer Output Terms

shared hit: Number of blocks found in shared buffers (memory)
shared read: Number of blocks read from disk into shared buffers
local hit/read: Same as above, but for temporary tables
temp read/written: Blocks read from or written to temporary files (e.g., for large sorts)

Key Terminology

Buffer: A block of memory used to cache data
Shared Buffer: Main memory area for caching table/index data
Temp File: Disk file used for operations that don’t fit in memory
I/O (Input/Output): Reading from or writing to disk

4. Detailed Explanation and Examples (25-30 minutes)

Understanding Buffer Statistics in Query Plans

A sample plan with buffers:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE id = 42;

Example output:

Index Scan using users_pkey on users  (cost=0.29..8.31 rows=1 width=100)
  (actual time=0.021..0.022 rows=1 loops=1)
  Buffers: shared hit=3

shared hit=3 means 3 blocks were found in memory (no disk reads)

Practical Applications

Identify queries causing high disk I/O (look for high shared read or temp read/written)
Optimize queries to increase buffer hits (e.g., by adding indexes, tuning queries)
Monitor buffer usage to tune server memory settings

Common Misconceptions

Misconception: “If a query is slow, it’s always a CPU problem.” Reality: Many slow queries are due to disk I/O, not CPU.
Misconception: “All buffer hits are equally fast.” Reality: Temp file I/O is much slower than shared buffer hits.

5. Live Coding Demonstration (20-25 minutes)

Example 1: Basic Buffer Analysis

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE id = 42;

Explanation:

If the data is cached, you’ll see mostly shared hit
If not, you’ll see shared read (disk I/O)

Example 2: Forcing Disk Reads

-- Run the query once to cache data
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE id = 42;
-- Evict buffers (requires superuser, use with caution)
DISCARD BUFFERS;
-- Run again to see disk reads
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE id = 42;

Key Points:

The first run may show shared read, the second (after cache) shows shared hit
DISCARD BUFFERS clears the cache (superuser only)

Example 3: Temp File Usage

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders ORDER BY amount DESC;

If the sort is too large for memory, you’ll see temp written in the plan

Debugging Demonstration

Show how repeated queries increase buffer hits:

-- First run (likely disk read)
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE status = 'active';
-- Second run (should be buffer hit)
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE status = 'active';

6. Hands-On Practice (30-40 minutes)

Exercise 1: Basic Practice (10 minutes)

Objective: Observe buffer usage for a simple query

Instructions:

Run EXPLAIN (ANALYZE, BUFFERS) on a query
Note the values for shared hit and shared read
Run the query again and compare

Starter Code:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM your_table WHERE some_column = 'value';

Solution:

-- Example output
Seq Scan on your_table  (cost=0.00..35.50 rows=5 width=32)
  Buffers: shared read=5
-- Second run
Seq Scan on your_table  (cost=0.00..35.50 rows=5 width=32)
  Buffers: shared hit=5

Exercise 2: Intermediate Challenge (15 minutes)

Objective: Identify queries with high disk I/O

Requirements:

Run several queries with EXPLAIN (ANALYZE, BUFFERS)
Find one with high shared read or temp written
Suggest ways to reduce disk I/O (e.g., add index, increase work_mem)

Hints:

Try queries with large sorts or joins
Use ORDER BY or GROUP BY on large tables

Exercise 3: Creative Application (15 minutes)

Objective: Experiment with buffer usage and server settings

Challenge:

Change work_mem for your session (SET LOCAL work_mem = '64MB';)
Run a query with a large sort and observe buffer/temp file usage

Extension Ideas:

Try with different table sizes
Experiment with parallel query settings

7. Common Pitfalls and Troubleshooting (10 minutes)

Frequent Mistakes

Problem: Ignoring high disk I/O in query plans Cause: Only looking at timing, not buffer stats Solution: Always check shared read and temp written in plans
Problem: Assuming all queries benefit equally from more memory Cause: Some queries are limited by disk speed, not memory Solution: Tune queries and indexes, not just memory settings

Debugging Tips

Use EXPLAIN (ANALYZE, BUFFERS) for I/O analysis
Run queries multiple times to see cache effects
Monitor temp file usage for large sorts/aggregates

8. Best Practices and Professional Tips (5 minutes)

Industry Standards

Monitor buffer and temp file usage for critical queries
Tune work_mem and shared_buffers for your workload
Use indexes to reduce disk I/O

Next Steps in Your Learning Journey

Learn about statistics and the planner (see Day 4)
Explore server memory and I/O settings
Practice with real-world queries and large datasets

9. Summary and Review (5 minutes)

Key Takeaways

Buffer statistics reveal how much I/O your queries do
High disk I/O can slow down queries dramatically
Use EXPLAIN (ANALYZE, BUFFERS) to optimize for memory hits

Quick Review Questions

What does shared hit mean in a query plan?
How can you tell if a query is reading from disk or memory?
What does temp written indicate?

For Instructors

Teaching Tips

Demonstrate with queries that produce both buffer hits and disk reads
Show how repeated queries change buffer stats
Use large sorts to illustrate temp file usage

Assessment Ideas

Have students identify high I/O queries and suggest optimizations
Ask students to explain buffer stats in a sample plan
Peer review: students compare buffer usage for different queries

Adaptation Notes

For beginners: focus on simple queries and buffer stats
For advanced students: include server memory tuning and parallelism
Online: use screen sharing to show live buffer analysis