Deep Fix

Posted on Aug 30

Ultimate Guide to Optimizing PostgreSQL Query Performance – Speed Up Your DB

#postgres #performance #query #optimization

Introduction

Boosting PostgreSQL query speed is a top priority for developers, engineers, and DevOps teams. This guide walks you through practical techniques, from indexing to configuration tweaks, and provides a step‑by‑step troubleshooting checklist.

1. Understand the Execution Plan

The first step is to let PostgreSQL explain how it will run a query.

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

Key columns to watch:

Seq Scan → indicates missing indexes.
Index Scan → good; verify the index used.
Rows Removed by Filter → filter inefficiency.

2. Indexing Strategies

2.1 Simple B‑Tree Index

CREATE INDEX idx_orders_created_at ON orders (created_at);

2.2 Multi‑Column Index (covering frequent predicates)

CREATE INDEX idx_orders_customer_date ON orders (customer_id, created_at DESC);

2.3 Partial Index for Hot Data

CREATE INDEX idx_recent_orders ON orders (created_at)
WHERE created_at > NOW() - INTERVAL '7 days';

Partial indexes keep the index small and fast.

3. Query Refactoring

3.1 Avoid SELECT *

-- Bad
SELECT * FROM products WHERE price > 100;

-- Better
SELECT id, name, price FROM products WHERE price > 100;

Fetching only needed columns reduces I/O.

3.2 Use CTEs Wisely

PostgreSQL 12+ can inline simple CTEs. Add MATERIALIZED or NOT MATERIALIZED when you need control.

WITH NOT MATERIALIZED recent AS (
    SELECT id FROM orders WHERE created_at > NOW() - INTERVAL '1 day'
)
SELECT * FROM recent JOIN customers USING (id);

4. Configuration Tweaks

Parameter	Typical Value	When to Adjust
`shared_buffers`	25% of RAM	Low memory contention
`work_mem`	64MB per session	Complex sorts/joins
`effective_cache_size`	50‑75% of RAM	Rough OS cache estimate
`random_page_cost`	1.1 (SSD) / 4.0 (HDD)	Storage type

Apply changes in postgresql.conf and reload:

pg_ctl reload -D /var/lib/postgresql/data

5. Monitoring & Auto‑Tuning

pg_stat_statements tracks query frequency and total runtime.
auto_explain can log plans for slow queries automatically.

-- Enable extensions
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS auto_explain;

-- Configure auto_explain (in postgresql.conf)
auto_explain.log_min_duration = '500ms'
auto_explain.log_analyze = true

6. Step‑by‑Step Troubleshooting Checklist

Run EXPLAIN ANALYZE – identify scans and bottlenecks.
Check Index Usage – add missing indexes or adjust column order.
Review Statistics – run ANALYZE or increase default_statistics_target.
Inspect Configuration – tune work_mem, effective_cache_size, and random_page_cost.
Look for Lock Contention – query pg_locks or use pg_stat_activity.
Profile I/O – pg_stat_io (if installed) or OS tools like iostat.
Consider Partitioning – large tables benefit from range or hash partitioning.

7. Real‑World Example: Fixing a Slow Report Query

-- Original slow query (takes ~15s)
SELECT r.id, r.amount, u.email
FROM receipts r
JOIN users u ON r.user_id = u.id
WHERE r.created_at >= '2024-01-01'::date
  AND r.amount > 1000;

Fixes Applied

Add a covering index:

CREATE INDEX idx_receipts_user_date_amount ON receipts (created_at, amount) INCLUDE (user_id);

Rewrite JOIN using EXISTS (if only existence needed):

SELECT r.id, r.amount
FROM receipts r
WHERE r.created_at >= '2024-01-01'::date
  AND r.amount > 1000
  AND EXISTS (SELECT 1 FROM users u WHERE u.id = r.user_id);

Adjust work_mem to 128MB for the session.

SET work_mem = '128MB';

Result: execution time dropped to < 1 second.

8. Ready-to‑Use Helper Script

For a quick start, download a pre‑configured tuning script that automates many of the steps above:

Conclusion

Optimizing PostgreSQL queries blends deep understanding of execution plans, smart indexing, and fine‑tuned configuration. Apply the checklist above, monitor continuously, and iterate—your applications will feel the performance boost.

DEV Community