PostgreSQL Performance Optimization: Indexes, Partitioning, and EXPLAIN ANALYZE in Practice

Introduction: Why PostgreSQL Optimization Is a Skill, Not a One-Time Task

PostgreSQL is one of the most powerful and mature relational database systems available, but its production performance depends heavily on how well a team understands the internal mechanics of the database. Optimizing PostgreSQL is not a "add an index and forget it" deal — it's a continuous, iterative process embedded in engineering culture.

As data grows, queries that ran instantly with 10,000 rows start hanging with 10 million. Data distributions change, new query patterns emerge, and workloads shift. That's exactly why PostgreSQL performance optimization is a living skill that needs to be applied regularly.

In this article, we'll walk through the entire journey from diagnosing slow queries to fine-tuning configuration — with real SQL examples and query plan breakdowns.

Diagnostic Tools: pg_stat_statements, auto_explain, and EXPLAIN ANALYZE

pg_stat_statements: Finding the Most Expensive Queries

The first step in optimization is identifying what's actually slow. The pg_stat_statements extension collects statistics on all executed queries and is the de facto standard for PostgreSQL performance diagnostics.

Enable the extension:

-- postgresql.conf\nshared_preload_libraries = 'pg_stat_statements'\n\n-- In the database\nCREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Query to find the slowest queries by total execution time:

SELECT\n  query,\n  calls,\n  round(total_exec_time::numeric, 2) AS total_ms,\n  round(mean_exec_time::numeric, 2) AS mean_ms,\n  round(stddev_exec_time::numeric, 2) AS stddev_ms,\n  rows\nFROM pg_stat_statements\nORDER BY total_exec_time DESC\nLIMIT 20;

Pay close attention to queries with high mean_exec_time and a large number of calls — these yield the greatest gains when optimized.

auto_explain: Automatic Logging of Slow Query Plans

The auto_explain extension automatically logs execution plans for queries that exceed a time threshold:

-- postgresql.conf\nshared_preload_libraries = 'pg_stat_statements,auto_explain'\nauto_explain.log_min_duration = 1000  -- queries longer than 1 second\nauto_explain.log_analyze = true\nauto_explain.log_buffers = true\nauto_explain.log_format = text

This is especially useful in production, where reproducing a slow query manually is difficult.

EXPLAIN ANALYZE: Reading Query Plans the Right Way

EXPLAIN ANALYZE is the primary tool for query analysis in PostgreSQL. Let's look at a real example:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)\nSELECT u.id, u.email, COUNT(o.id) AS order_count\nFROM users u\nJOIN orders o ON o.user_id = u.id\nWHERE u.created_at > '2024-01-01'\nGROUP BY u.id, u.email\nHAVING COUNT(o.id) > 5\nORDER BY order_count DESC\nLIMIT 100;

Sample output:

Limit  (cost=15234.56..15234.81 rows=100 width=52) (actual time=892.341..892.387 rows=100 loops=1)\n  ->  Sort  (cost=15234.56..15259.56 rows=10000 width=52) (actual time=892.338..892.352 rows=100 loops=1)\n        Sort Key: (count(o.id)) DESC\n        Sort Method: top-N heapsort  Memory: 33kB\n        ->  HashAggregate  (cost=14734.56..14884.56 rows=10000 width=52) (actual time=878.234..889.123 rows=8934 loops=1)\n              Group Key: u.id, u.email\n              ->  Hash Join  (cost=3456.78..13234.56 rows=200000 width=24) (actual time=45.234..756.123 rows=198432 loops=1)\n                    Hash Cond: (o.user_id = u.id)\n                    Buffers: shared hit=1234 read=8932\n                    ->  Seq Scan on orders o  (cost=0.00..8234.56 rows=200000 width=8) (actual time=0.012..312.456 rows=200000 loops=1)\n                    ->  Hash  (cost=2956.78..2956.78 rows=40000 width=24) (actual time=44.123..44.123 rows=39876 loops=1)\n                          ->  Seq Scan on users u  (cost=0.00..2956.78 rows=40000 width=24) (actual time=0.015..38.234 rows=39876 loops=1)\n                                Filter: (created_at > '2024-01-01'::timestamp)\n                                Rows Removed by Filter: 12456\nPlanning Time: 2.345 ms\nExecution Time: 892.567 ms

Key things to notice here:

• Seq Scan on orders — a full sequential scan of the orders table. 200,000 rows without an index is a red flag.
• Buffers: shared read=8932 — most data is being read from disk rather than cache.
• Rows Removed by Filter: 12456 — the filter on created_at in users is working, but without an index.

The key rule: watch for discrepancies between estimated rows and actual rows. A large gap indicates stale statistics — run ANALYZE.

Indexing in PostgreSQL: Choosing the Right Type

B-tree: The Universal Default

B-tree indexes are used by default and suit most scenarios: equality checks, range queries, sorting, and LIKE with a fixed prefix.

-- Standard index\nCREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);\n\n-- Composite index (column order matters!)\nCREATE INDEX CONCURRENTLY idx_orders_user_status ON orders(user_id, status);\n\n-- Index for sorting and range queries\nCREATE INDEX CONCURRENTLY idx_users_created_at ON users(created_at DESC);

Important: always create indexes with CONCURRENTLY in production — this avoids locking the table.

GIN: For Arrays, JSONB, and Full-Text Search

GIN (Generalized Inverted Index) is optimal for data types that contain multiple values.

-- Index for JSONB\nCREATE INDEX CONCURRENTLY idx_products_attributes ON products USING GIN(attributes);\n\n-- Query using GIN\nSELECT * FROM products\nWHERE attributes @> '{\"color\": \"red\", \"size\": \"XL\"}';\n\n-- Full-text search\nCREATE INDEX CONCURRENTLY idx_articles_search\nON articles USING GIN(to_tsvector('english', title || ' ' || body));\n\nSELECT * FROM articles\nWHERE to_tsvector('english', title || ' ' || body) @@ to_tsquery('english', 'optimization & PostgreSQL');

GiST: Geospatial Data and Fuzzy Search

GiST indexes are used with geospatial types (PostGIS), range types (tsrange, int4range), and the pg_trgm extension for fuzzy text search.

-- Index for fuzzy search (requires pg_trgm)\nCREATE EXTENSION IF NOT EXISTS pg_trgm;\nCREATE INDEX CONCURRENTLY idx_users_email_trgm ON users USING GiST(email gist_trgm_ops);\n\n-- LIKE without a fixed prefix now uses the index\nSELECT * FROM users WHERE email LIKE '%gmail%';

BRIN: For Large Tables with Natural Ordering

BRIN (Block Range INdex) is a compact index for tables with physically ordered data (time series, logs, events).

-- For an events table with a monotonically increasing timestamp\nCREATE INDEX CONCURRENTLY idx_events_occurred_at\nON events USING BRIN(occurred_at) WITH (pages_per_range = 128);\n\n-- BRIN takes thousands of times less space than B-tree\n-- Suitable for date range queries on very large tables

Partial and Functional Indexes

Partial Indexes: Indexing Only the Rows You Need

A partial index contains only rows that satisfy a WHERE condition. This significantly reduces index size and speeds up queries.

-- Index only active orders (not completed)\nCREATE INDEX CONCURRENTLY idx_orders_active\nON orders(created_at)\nWHERE status NOT IN ('completed', 'cancelled');\n\n-- Index for unread notifications\nCREATE INDEX CONCURRENTLY idx_notifications_unread\nON notifications(user_id, created_at)\nWHERE read_at IS NULL;\n\n-- Query automatically uses the partial index\nSELECT * FROM notifications\nWHERE user_id = 42 AND read_at IS NULL\nORDER BY created_at DESC;

Functional Indexes: Indexing Expression Results

-- Case-insensitive email search\nCREATE INDEX CONCURRENTLY idx_users_email_lower\nON users(LOWER(email));\n\n-- Query now uses the index\nSELECT * FROM users WHERE LOWER(email) = LOWER('User@Example.com');\n\n-- Index on a date portion\nCREATE INDEX CONCURRENTLY idx_orders_date\nON orders(DATE(created_at));\n\nSELECT COUNT(*) FROM orders WHERE DATE(created_at) = '2024-12-01';

Table Partitioning in PostgreSQL 16+

Partitioning splits a large table into logical segments (partitions), speeding up queries through partition pruning — PostgreSQL skips partitions that cannot contain the relevant data.

Range Partitioning: For Time-Based Data

-- Create a partitioned table\nCREATE TABLE events (\n  id BIGSERIAL,\n  user_id BIGINT NOT NULL,\n  event_type VARCHAR(50) NOT NULL,\n  payload JSONB,\n  occurred_at TIMESTAMP NOT NULL\n) PARTITION BY RANGE (occurred_at);\n\n-- Create monthly partitions\nCREATE TABLE events_2024_11 PARTITION OF events\n  FOR VALUES FROM ('2024-11-01') TO ('2024-12-01');\n\nCREATE TABLE events_2024_12 PARTITION OF events\n  FOR VALUES FROM ('2024-12-01') TO ('2025-01-01');\n\nCREATE TABLE events_2025_01 PARTITION OF events\n  FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');\n\n-- Indexes are created on each partition\nCREATE INDEX ON events_2024_12(user_id);\nCREATE INDEX ON events_2025_01(user_id);\n\n-- Partitioning + automatic partition creation via pg_partman

Verify that partition pruning is working:

EXPLAIN SELECT * FROM events\nWHERE occurred_at BETWEEN '2025-01-01' AND '2025-01-31';\n\n-- Output will show Append with a single partition events_2025_01\n-- All other partitions are skipped entirely

List Partitioning: For Categories and Regions

CREATE TABLE orders (\n  id BIGSERIAL,\n  user_id BIGINT NOT NULL,\n  region VARCHAR(20) NOT NULL,\n  total NUMERIC(12,2),\n  created_at TIMESTAMP DEFAULT NOW()\n) PARTITION BY LIST (region);\n\nCREATE TABLE orders_eu PARTITION OF orders\n  FOR VALUES IN ('DE', 'FR', 'NL', 'IT', 'ES');\n\nCREATE TABLE orders_us PARTITION OF orders\n  FOR VALUES IN ('NY', 'CA', 'TX', 'FL');\n\nCREATE TABLE orders_default PARTITION OF orders DEFAULT;

Hash Partitioning: Even Distribution

CREATE TABLE user_events (\n  id BIGSERIAL,\n  user_id BIGINT NOT NULL,\n  data JSONB,\n  created_at TIMESTAMP DEFAULT NOW()\n) PARTITION BY HASH (user_id);\n\n-- 4 partitions for even distribution\nCREATE TABLE user_events_0 PARTITION OF user_events\n  FOR VALUES WITH (MODULUS 4, REMAINDER 0);\nCREATE TABLE user_events_1 PARTITION OF user_events\n  FOR VALUES WITH (MODULUS 4, REMAINDER 1);\nCREATE TABLE user_events_2 PARTITION OF user_events\n  FOR VALUES WITH (MODULUS 4, REMAINDER 2);\nCREATE TABLE user_events_3 PARTITION OF user_events\n  FOR VALUES WITH (MODULUS 4, REMAINDER 3);

PostgreSQL Configuration Tuning

Proper PostgreSQL configuration delivers performance gains without any schema or query changes.

Key Parameters

-- shared_buffers size: 25% of RAM for a dedicated server\nshared_buffers = 8GB\n\n-- Estimated OS cache memory (influences the query planner)\neffective_cache_size = 24GB\n\n-- Memory for sorts and hash joins (per query/connection!)\nwork_mem = 64MB\n\n-- Memory for maintenance operations (VACUUM, CREATE INDEX)\nmaintenance_work_mem = 2GB\n\n-- Parallel worker processes\nmax_parallel_workers_per_gather = 4\nmax_parallel_workers = 8\nmax_worker_processes = 16\n\n-- Cost of random I/O (reduce for SSD)\nrandom_page_cost = 1.1  -- SSD\n# random_page_cost = 4.0  -- HDD (default)\n\n-- Enable JIT for analytical queries\njit = on\njit_above_cost = 100000

Important: work_mem is multiplied by the number of connections and sort operations within a single query. With 100 connections and work_mem = 256MB, you could theoretically consume 25GB of RAM on sorting alone.

Optimizing JOINs and Subqueries

Use CTEs Wisely

In PostgreSQL versions prior to 12, CTEs acted as "optimization fences" — the planner could not look inside them. Starting with PostgreSQL 12, this behavior changed, but sometimes you need to control it explicitly:

-- Materializing CTE (force materialization)\nWITH expensive_cte AS MATERIALIZED (\n  SELECT user_id, SUM(amount) as total\n  FROM orders\n  WHERE created_at > NOW() - INTERVAL '30 days'\n  GROUP BY user_id\n)\nSELECT u.email, c.total\nFROM users u\nJOIN expensive_cte c ON c.user_id = u.id\nWHERE c.total > 1000;\n\n-- NOT MATERIALIZED — allow the planner to optimize\nWITH recent_users AS NOT MATERIALIZED (\n  SELECT id FROM users WHERE created_at > '2024-01-01'\n)\nSELECT * FROM orders WHERE user_id IN (SELECT id FROM recent_users);

EXISTS vs IN vs JOIN

-- Slow for large subqueries\nSELECT * FROM users\nWHERE id IN (SELECT user_id FROM orders WHERE total > 1000);\n\n-- Faster: EXISTS with a correlated subquery\nSELECT * FROM users u\nWHERE EXISTS (\n  SELECT 1 FROM orders o\n  WHERE o.user_id = u.id AND o.total > 1000\n);\n\n-- Or JOIN with DISTINCT\nSELECT DISTINCT u.*\nFROM users u\nJOIN orders o ON o.user_id = u.id\nWHERE o.total > 1000;

Controlling JOIN Strategy

-- If the planner is choosing the wrong JOIN type\nSET enable_hashjoin = off;  -- Disable hash join\nSET enable_nestloop = off;  -- Disable nested loop\n\n-- Use pg_hint_plan for more precise control in production\n-- SELECT /*+ HashJoin(u o) */ u.*, o.total FROM users u JOIN orders o ON ...\n\n-- Don't forget to reset settings afterward\nRESET enable_hashjoin;\nRESET enable_nestloop;

Vacuum and Bloat: Monitoring Table Health

PostgreSQL uses MVCC — when rows are UPDATEd or DELETEd, old row versions are not removed immediately. The accumulation of "dead" rows is called bloat and leads to performance degradation.

Monitoring Bloat and Vacuum Status

-- Vacuum statistics per table\nSELECT\n  schemaname,\n  relname AS table_name,\n  n_dead_tup AS dead_tuples,\n  n_live_tup AS live_tuples,\n  round(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_ratio_pct,\n  last_autovacuum,\n  last_autoanalyze\nFROM pg_stat_user_tables\nORDER BY dead_ratio_pct DESC NULLS LAST\nLIMIT 20;\n\n-- Index bloat sizes\nSELECT\n  indexrelname,\n  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,\n  idx_scan,\n  idx_tup_read,\n  idx_tup_fetch\nFROM pg_stat_user_indexes\nORDER BY pg_relation_size(indexrelid) DESC\nLIMIT 20;

Tuning Autovacuum for High-Traffic Tables

-- Aggressive autovacuum settings for a specific table\nALTER TABLE orders SET (\n  autovacuum_vacuum_scale_factor = 0.01,   -- 1% dead rows triggers vacuum\n  autovacuum_analyze_scale_factor = 0.005, -- 0.5% triggers ANALYZE\n  autovacuum_vacuum_cost_delay = 2,        -- ms delay between pages\n  autovacuum_vacuum_threshold = 100\n);\n\n-- Manual VACUUM ANALYZE when needed\nVACUUM (ANALYZE, VERBOSE) orders;\n\n-- For severe bloat — VACUUM FULL (locks the table!)\n-- Better to use pg_repack in production\n-- pg_repack --table orders mydb

Detecting Unused Indexes

-- Indexes that have never been scanned\nSELECT\n  schemaname,\n  tablename,\n  indexname,\n  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,\n  idx_scan AS times_used\nFROM pg_stat_user_indexes\nWHERE idx_scan = 0\n  AND indexrelname NOT LIKE 'pg_%'\nORDER BY pg_relation_size(indexrelid) DESC;

Unused indexes are not just wasted space. They slow down INSERT, UPDATE, and DELETE operations, since PostgreSQL must update every index whenever data changes.

Conclusion: Regular Audits as Part of Engineering Culture

PostgreSQL performance optimization is a discipline, not a one-time effort. Here is a recommended minimum cycle for production systems:

• Daily: monitor slow queries via pg_stat_statements, set alerts for response time degradation.
• Weekly: review autovacuum health, analyze new and unused indexes.
• Monthly: full audit of query plans for the top 20 most loaded queries, check for bloat.
• On schema changes: run EXPLAIN ANALYZE on all queries touching the modified tables.

Investing in a deep understanding of PostgreSQL internals pays off many times over. A well-chosen index can turn a 30-second query into a 5-millisecond one. Partitioning lets you work with tables hundreds of gigabytes in size as fast as with small ones. And regular monitoring prevents late-night incidents.

PostgreSQL gives developers all the tools they need — you just have to learn how to use them.