PostgreSQL Query Execution Plan Analysis: EXPLAIN ANALYZE Deep Dive

Introduction

Understanding how PostgreSQL executes your queries is the single most valuable skill for database optimization. I had a query that was taking over 10 seconds on a table with 20 million rows, and I could not figure out why — the table had indexes on every column I was filtering by. After running EXPLAIN ANALYZE, the problem was immediately obvious: PostgreSQL was choosing a sequential scan because my statistics were stale and the planner had a wrong estimate of the result set size.

This post is a deep dive into reading and interpreting PostgreSQL execution plans, with real examples from my trading system.

Environment

• PostgreSQL: 16.1
• Table: market_data (20 million rows, ~12GB)
• Query: Time-range filter with grouping and aggregation
• Indexes: B-tree on timestamp, symbol, and several other columns

Problem

The query that was performing poorly:

SELECT
    date_trunc('hour', timestamp) AS hour,
    symbol,
    AVG(close_price) AS avg_price,
    SUM(volume) AS total_volume
FROM market_data
WHERE timestamp >= '2024-06-01'
    AND timestamp < '2024-07-01'
    AND symbol IN ('AAPL', 'GOOGL', 'MSFT')
GROUP BY date_trunc('hour', timestamp), symbol
ORDER BY hour, symbol;

This query was taking 12 seconds. I expected it to take under 1 second because I had an index on timestamp:

CREATE INDEX idx_market_data_timestamp ON market_data (timestamp);

Running EXPLAIN ANALYZE:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT ...

Sort  (cost=504321.56..504321.57 rows=2 width=48) (actual time=11845.234..11845.235 rows=14880 loops=1)
  Sort Key: (date_trunc('hour', timestamp)), symbol
  Sort Method: quicksort  Memory: 1058kB
  ->  HashAggregate  (cost=504321.50..504321.54 rows=2 width=48) (actual time=11842.123..11844.567 rows=14880 loops=1)
        Group Key: date_trunc('hour', timestamp), symbol
        Batches: 1  Memory Usage: 2353kB
        ->  Bitmap Heap Scan on market_data  (cost=15234.56..501234.78 rows=456789 width=24) (actual time=234.567..11234.567 rows=412345 loops=1)
              Recheck Cond: (timestamp >= '2024-06-01' AND timestamp < '2024-07-01')
              Filter: (symbol = ANY ('{AAPL,GOOGL,MSFT}'::text[]))
              Rows Removed by Filter: 3712345
              Heap Blocks: exact=123456
              ->  Bitmap Index Scan on idx_market_data_timestamp  (cost=0.00..15120.56 rows=456789 width=0) (actual time=198.345..198.346 rows=412345 loops=1)
Planning Time: 0.123 ms
Execution Time: 11845.567 ms

The key observations:

1. The index on timestamp was being used (Bitmap Index Scan)
2. But it was scanning 456,789 rows and filtering down to 412,345 rows that matched the symbol filter
3. 3,712,345 rows were removed by the symbol filter — this was expensive
4. The total execution time was 11,845ms

Analysis

The execution plan revealed two issues:

1. The index on timestamp alone was not selective enough:

The timestamp range for one month returned nearly 500,000 rows. The symbol filter was applied after the index scan, meaning PostgreSQL fetched all rows matching the timestamp range and then filtered by symbol in memory. This is called a "heap fetch" — the index points to rows in the table, and each row must be individually fetched.

2. Missing composite index:

A composite index on (symbol, timestamp) would be much more selective. The symbol filter has only 3 values out of 500+ unique symbols, making it highly selective. Starting the index with symbol would narrow down the result set before scanning by timestamp.

I verified the selectivity:

SELECT
    'symbol' AS field,
    count(DISTINCT symbol) AS unique_values,
    count(*) AS total_rows,
    round(count(DISTINCT symbol)::numeric / count(*), 6) AS selectivity
FROM market_data
UNION ALL
SELECT
    'timestamp_hour',
    count(DISTINCT date_trunc('hour', timestamp)),
    count(*),
    round(count(DISTINCT date_trunc('hour', timestamp))::numeric / count(*), 6)
FROM market_data;

  field   | unique_values | total_rows | selectivity
----------+---------------+------------+------------
 symbol   |           512 |   20000000 |  0.0000256
 timestamp|          8760 |   20000000 |  0.0004380

The symbol field is 17x more selective than the timestamp field. This confirms that starting the index with symbol is the right choice.

Solution

I created a composite index that matches the query pattern:

CREATE INDEX idx_market_data_symbol_timestamp
ON market_data (symbol, timestamp)
INCLUDE (close_price, volume);

The INCLUDE clause creates a covering index that includes close_price and volume directly in the index. This allows PostgreSQL to answer the query entirely from the index without fetching rows from the table.

After creating the index, I re-ran EXPLAIN ANALYZE:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT ...

HashAggregate  (cost=1234.56..1234.60 rows=2 width=48) (actual time=156.234..158.456 rows=14880 loops=1)
  Group Key: date_trunc('hour', timestamp), symbol
  Batches: 1  Memory Usage: 2353kB
  ->  Index Only Scan using idx_market_data_symbol_timestamp on market_data  (cost=0.56..1123.45 rows=456 width=24) (actual time=2.345..145.678 rows=412345 loops=1)
        Index Cond: (symbol = ANY ('{AAPL,GOOGL,MSFT}'::text[]) AND timestamp >= '2024-06-01' AND timestamp < '2024-07-01')
        Heap Fetches: 0
Planning Time: 0.089 ms
Execution Time: 158.789 ms

The improvements:

1. Index Only Scan — No heap fetches (0 heap fetches vs 456,789 before)
2. Execution time: 12 seconds -> 159ms (75x faster)
3. The index conditions include both symbol and timestamp

I also updated the statistics to help the planner make better decisions:

-- Update statistics for the market_data table
ANALYZE market_data;

-- Increase statistics target for important columns
ALTER TABLE market_data ALTER COLUMN symbol SET STATISTICS 1000;
ALTER TABLE market_data ALTER COLUMN timestamp SET STATISTICS 1000;
ANALYZE market_data;

For ongoing monitoring, I created a function to identify slow queries:

CREATE OR REPLACE FUNCTION log_slow_queries()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.mean_time > 1000 THEN  -- Queries taking more than 1 second
        RAISE NOTICE 'Slow query detected: mean_time=% ms, query=%',
            NEW.mean_time, LEFT(NEW.query, 200);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

I also set up pg_stat_statements for ongoing query performance monitoring:

-- Enable pg_stat_statements extension
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Find the slowest queries
SELECT
    query,
    calls,
    mean_exec_time,
    total_exec_time,
    rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

Lessons Learned

1. Always use EXPLAIN ANALYZE — EXPLAIN without ANALYZE only shows the planned execution, not the actual runtime. ANALYZE executes the query and shows real timings.
2. Look for sequential scans on large tables — A Seq Scan on a large table usually means a missing or unused index.
3. Use covering indexes — The INCLUDE clause adds columns to the index leaf pages, enabling index-only scans that avoid table lookups.
4. Update statistics regularly — Stale statistics cause the planner to make wrong decisions. Run ANALYZE after large data changes.
5. Monitor with pg_stat_statements — This extension tracks query performance over time, helping you identify queries that degrade gradually.

This blog does not accept any external sponsorships, affiliate marketing, or ad revenue.