Deep Fix

Posted on Sep 5

Ultimate PostgreSQL Query Performance Optimization Guide for Developers

#postgres #performance #optimization #sql

Introduction

PostgreSQL is a powerhouse for relational data, but poorly written queries can turn that power into a bottleneck. In this guide we dive into practical techniques to boost query speed, backed by real‑world examples and step‑by‑step troubleshooting.

1. Read the Execution Plan First

The fastest way to understand why a query is slow is to look at its execution plan.

EXPLAIN (ANALYZE, BUFFERS) 
SELECT *
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.created_at > CURRENT_DATE - INTERVAL '30 days';

Key columns in the output:

Seq Scan vs Index Scan – indicates whether an index is being used.
Rows Removed by Filter – shows excessive filtering after a scan.
Total Runtime – the actual time spent.

If you see a Seq Scan on a large table, consider adding an index (see section 2).

2. Indexing Strategies that Actually Help

2.1 Simple B‑Tree Index

CREATE INDEX idx_orders_created_at ON orders (created_at);

2.2 Composite Index for Join + Filter

When a query filters on created_at and joins on customer_id, a composite index can cover both operations:

CREATE INDEX idx_orders_customer_date ON orders (customer_id, created_at);

2.3 Partial Indexes for Hot Subsets

If you only ever query recent orders, a partial index keeps the index small and fast:

CREATE INDEX idx_recent_orders ON orders (customer_id)
WHERE created_at > CURRENT_DATE - INTERVAL '90 days';

3. Query Rewriting Tips

Avoid SELECT * – fetch only needed columns.
Use EXISTS instead of IN when the sub‑query returns many rows.
Leverage CTEs wisely – materialize only when necessary (WITH ... MATERIALIZED).
Apply LIMIT early if you only need a subset.

-- Bad: SELECT * with IN
SELECT * FROM products p
WHERE p.id IN (SELECT product_id FROM sales WHERE amount > 1000);

-- Good: EXISTS with column list
SELECT p.id, p.name, p.price
FROM products p
WHERE EXISTS (
    SELECT 1 FROM sales s WHERE s.product_id = p.id AND s.amount > 1000
);

4. Configuration Tuning (PostgreSQL Settings)

Parameter	Typical Adjustment	Effect
`work_mem`	`SET work_mem = '64MB';` (per session)	Allows larger hash tables, faster sorts.
`shared_buffers`	25‑30% of RAM	Improves cache hit ratio.
`effective_cache_size`	Approx. 50‑75% of RAM	Helps planner estimate available cache.
`max_parallel_workers_per_gather`	`2` or `4`	Enables parallel query execution.

-- Example: increase work_mem for a heavy report
SET work_mem = '128MB';
SELECT * FROM big_report();

5. Monitoring & Troubleshooting Workflow

Identify the slow query – use pg_stat_statements or APM tools.
Run EXPLAIN ANALYZE – capture plan and runtime.
Check index usage – look for Seq Scan.
Adjust indexes or rewrite – apply changes from sections 2‑3.
Retest – repeat step 2 until runtime meets SLA.
Persist configuration – add successful SET commands to postgresql.conf or connection pool settings.

Quick Checklist (copy‑paste into your incident report)

- Query text:
- EXPLAIN ANALYZE output (trimmed):
- Indexes present:
- work_mem / other GUC values:
- Observed rows vs estimated rows:
- Action taken:

6. Real‑World Example: Fixing a 30‑second Report

The following repository contains a pre‑configured script that applies the exact fixes described above:

Running the script creates the recommended indexes and adjusts work_mem for the session, slashing the report time from 30 s to ≈2 s on a 200 M‑row table.

Conclusion

Optimizing PostgreSQL query performance is a blend of observability, smart indexing, thoughtful query design, and tuned configuration. By following the systematic approach above, developers, engineers, and DevOps teams can consistently shave seconds—or even minutes—off critical workloads.

Happy querying!

DEV Community