When database queries slow to a crawl under production loads, throwing more RAM or CPU at your database server is rarely the answer. In 90% of cases, the culprit is missing indexes, redundant table scans, or unoptimized query plans.

In this guide, we demystify how PostgreSQL executes queries and show you how to read execution plans like a senior database engineer.

1. Decoding EXPLAIN (ANALYZE, BUFFERS)

Adding EXPLAIN before a query asks PostgreSQL to describe its planned execution path. Adding (ANALYZE, BUFFERS) actually executes the query and reports real runtime statistics and disk I/O:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, published_at
FROM public.posts
WHERE status = 'published' AND author_id = 'c3735295-8408-44ea-a4d8-b5f4b5077358'
ORDER BY published_at DESC
LIMIT 10;

The Output Red Flags to Watch For:

  • Seq Scan (Sequential Scan): The database is reading every single row on disk. For tables with millions of rows, this causes massive disk I/O bottlenecks.
  • Rows Removed by Filter: A high number indicates the database read thousands of rows only to discard them because the WHERE condition had no index.
  • Sort Method: external merge Disk: Your query ran out of work_mem and had to spill sorting operations to disk.

2. Multi-Column Composite Indexes: Order Matters!

When filtering across multiple columns simultaneously, a composite index can turn a 400ms sequential scan into a 2ms index scan:

-- Creates an index covering both author and status
CREATE INDEX idx_posts_author_status 
ON public.posts (author_id, status, published_at DESC);

Crucial Rule: The leftmost column in the index must be the most selective equality filter (author_id), followed by secondary equality filters (status), and ending with range/sort columns (published_at).

3. GIN Indexes for Lightning-Fast JSONB Searches

PostgreSQL's JSONB data type allows storing arbitrary documents, but querying them with ->> causes sequential scans unless indexed with a Generalized Inverted Index (GIN):

-- Create GIN index for JSON path searches
CREATE INDEX idx_user_metadata_gin 
ON public.profiles USING gin (metadata jsonb_path_ops);

-- Executes in sub-millisecond time:
SELECT * FROM public.profiles 
WHERE metadata @> '{"plan": "pro"}';

Key Tuning Rules

  • Avoid SELECT *: Only fetch the specific columns needed by the application.
  • Keep shared_buffers configured at approximately 25% of total system RAM on dedicated database servers.
  • Re-index bloat periodically using REINDEX TABLE CONCURRENTLY.