Master PostgreSQL Execution Plans to Supercharge Your SQL Performance

What Is an Execution Plan?

An execution plan describes in detail how PostgreSQL will execute a given SQL query. After receiving a query, the optimizer creates one or more candidate plans and selects the cheapest based on cost estimates, revealing how tables are accessed and where performance bottlenecks may lie.

Core Elements of an Execution Plan

Scan operations: Seq Scan (sequential) and Index Scan, which determine how rows are read.

Join operations: Nested Loop, Hash Join, Merge Join – each affecting multi‑table query cost.

Sort and aggregation: Sort, Aggregate – these impact execution time, especially on large data sets.

How PostgreSQL Generates Plans

The optimizer analyses the SQL statement and the database statistics, generates multiple plans, and picks the one with the lowest estimated cost (Cost‑Based Optimization, CBO). Statistics such as row counts, data distribution, and index availability are gathered by the ANALYZE command.

Using EXPLAIN and EXPLAIN ANALYZE

EXPLAIN

shows the estimated plan without running the query; EXPLAIN ANALYZE executes the query and reports actual execution times and row counts.

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;

Underlying Components

Parser: Converts SQL text into an abstract syntax tree (AST).

Rewriter: Performs semantic checks and transformations such as view expansion.

Optimizer: Applies the cost model to generate and select the best plan.

Executor: Executes the chosen plan step by step, using caching to reuse plans for identical queries.

Case Studies: Optimizing Business SQL with Execution Plans

Case 1 – Removing a Full Table Scan

Problem: A query on orders uses a sequential scan to filter rows after order_date > '2024-01-01', leading to high cost and 10 ms execution.

EXPLAIN ANALYZE SELECT * FROM orders WHERE order_date > '2024-01-01';

Plan shows Seq Scan with 5 000 rows scanned.

Sequential scan reads the whole table, causing unnecessary I/O.

Actual row count confirms the filter is applied after the scan.

Optimization: Create an index on order_date so the planner can use an index scan.

CREATE INDEX idx_order_date ON orders(order_date);

EXPLAIN ANALYZE SELECT * FROM orders WHERE order_date > '2024-01-01';

After indexing, the plan shows Index Scan and execution time drops to ~5 ms.

Case 2 – Speeding Up a Multi‑Table Join

Problem: Joining orders and customers uses a Hash Join with sequential scans on both tables, taking ~40 ms.

EXPLAIN ANALYZE SELECT o.order_id, c.customer_name FROM orders o JOIN customers c ON o.customer_id = c.customer_id;

Both sides are scanned sequentially, inflating I/O.

Optimization: Add indexes on the join columns.

CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_customers_customer_id ON customers(customer_id);

EXPLAIN ANALYZE SELECT o.order_id, c.customer_name FROM orders o JOIN customers c ON o.customer_id = c.customer_id;

The new plan uses a Nested Loop with index scans, reducing execution time to ~15 ms.

Case 3 – Improving a Sort Operation

Problem: Ordering by order_date DESC triggers a full sort after a sequential scan, costing ~30 ms.

EXPLAIN ANALYZE SELECT * FROM orders ORDER BY order_date DESC;

Plan shows a Sort node with quicksort on 10 000 rows.

Optimization: Create a descending index on order_date so the order is pre‑sorted.

CREATE INDEX idx_order_date_desc ON orders(order_date DESC);

EXPLAIN ANALYZE SELECT * FROM orders ORDER BY order_date DESC;

Execution plan now uses an Index Scan and execution time falls to ~20 ms.

Practical Optimization Tips

1. Effective Index Usage

B‑Tree: Default for equality and range queries.

GIN: Full‑text search and array columns.

GiST: Geospatial and fuzzy matching.

HASH: Simple equality (rarely needed).

Covering indexes: Include all needed columns to avoid heap fetches.

CREATE INDEX idx_customer_id ON customers(customer_id);
CREATE INDEX idx_fts ON documents USING GIN(to_tsvector('english', content));
CREATE INDEX idx_gist_location ON locations USING GiST(geom);
CREATE INDEX idx_hash_email ON users USING HASH(email);
CREATE INDEX idx_orders_cover ON orders(customer_id, order_date, total_amount);

Maintain indexes regularly with REINDEX and drop unused ones.

REINDEX INDEX idx_customer_id;

2. Database Configuration

shared_buffers: Typically 25‑40% of system RAM.

work_mem: Memory per operation (sort, hash); increase to reduce disk spill.

maintenance_work_mem: Memory for VACUUM, CREATE INDEX, etc.

shared_buffers = 8GB
work_mem = 64MB
maintenance_work_mem = 512MB

Enable and tune autovacuum to keep statistics fresh.

autovacuum = on
autovacuum_naptime = 1min
autovacuum_vacuum_threshold = 50

Run ANALYZE periodically.

ANALYZE customers;

3. SQL Writing Best Practices

Avoid SELECT *; list only required columns.

Rewrite complex joins as sub‑queries when it helps the optimizer.

Use LIMIT / OFFSET to restrict large result sets.

Apply restrictive WHERE clauses before joins to reduce row counts.

SELECT customer_id, order_date, total_amount FROM orders WHERE order_date > '2024-01-01';
SELECT * FROM customers WHERE customer_id IN (SELECT customer_id FROM orders WHERE order_date > '2024-01-01');
SELECT * FROM orders ORDER BY order_date DESC LIMIT 100 OFFSET 0;
SELECT o.order_id, c.customer_name FROM orders o JOIN customers c ON o.customer_id = c.customer_id WHERE o.order_date > '2024-01-01';

Conclusion

By analysing PostgreSQL execution plans, creating appropriate indexes, tuning configuration parameters, and writing efficient SQL, developers can eliminate costly full‑table scans, reduce join overhead, and avoid expensive sorts, leading to faster, more reliable database performance.