DEV Community

Deep Fix
Deep Fix

Posted on

Optimizing PostgreSQL Query Performance: Proven Tips & Best Practices

#postgres #performance #optimization #devops

Optimizing PostgreSQL Query Performance

PostgreSQL is a powerful open‑source RDBMS, but poorly written queries can quickly become a bottleneck. In this guide we walk through proven techniques to squeeze out maximum speed from your database.

Why Query Performance Matters

  • Faster response times for end‑users
  • Lower CPU and I/O usage → cheaper infrastructure
  • Predictable scaling as data grows

1️⃣ Understand the Execution Plan

The first step is always to look at what PostgreSQL actually does.

EXPLAIN (ANALYZE, BUFFERS) SELECT *
FROM orders
WHERE customer_id = 123
  AND order_date >= '2023-01-01';
Enter fullscreen mode Exit fullscreen mode

The output tells you if indexes are used, how many rows were scanned, and where most time is spent.

2️⃣ Index Wisely

Indexes are the single most effective tool, but they must match the query pattern.

-- Single‑column B‑Tree index (most common)
CREATE INDEX idx_orders_customer ON orders(customer_id);

-- Composite index for range + equality
CREATE INDEX idx_orders_cust_date ON orders(customer_id, order_date);
Enter fullscreen mode Exit fullscreen mode

For full‑text search or array columns consider GIN/GiST indexes.

3️⃣ Write Sargable Queries

Avoid wrapping indexed columns in functions; it prevents index usage.

-- Bad: function on column
SELECT * FROM events WHERE DATE(event_ts) = '2023-09-01';

-- Good: use range predicate
SELECT * FROM events
WHERE event_ts >= '2023-09-01'::date
  AND event_ts <  '2023-09-02'::date;
Enter fullscreen mode Exit fullscreen mode

4️⃣ Tune PostgreSQL Configuration

Default settings are conservative. Adjust the following parameters based on your workload and hardware.

# postgresql.conf
shared_buffers = 25% of RAM
effective_cache_size = 75% of RAM
work_mem = 64MB           # per sort/ hash operation
maintenance_work_mem = 256MB
max_parallel_workers_per_gather = 4
Enter fullscreen mode Exit fullscreen mode

After any change, reload the server: SELECT pg_reload_conf();.

5️⃣ Step‑by‑Step Troubleshooting Checklist

  1. Run EXPLAIN ANALYZE – locate the slowest node.
  2. Check index usage – ensure the plan uses the intended index.
  3. Look for sequential scans – add or rewrite indexes accordingly.
  4. Inspect I/O – high shared_blks_hit vs shared_blks_read indicates caching issues.
  5. Review configuration – work_mem too low leads to external sorts.
  6. Monitor locks – long‑running locks can block query execution.

6️⃣ Real‑World Example

Before optimization:

EXPLAIN ANALYZE
SELECT * FROM sales
WHERE product_id = 42 AND sale_date BETWEEN '2022-01-01' AND '2022-12-31';
Enter fullscreen mode Exit fullscreen mode 
Seq Scan on sales  (cost=0.00..12345.67 rows=5000 width=123) (actual time=0.123..45.678 rows=5000 loops=1)
Enter fullscreen mode Exit fullscreen mode

After adding a composite index:

CREATE INDEX idx_sales_prod_date ON sales(product_id, sale_date);
Enter fullscreen mode Exit fullscreen mode

Re‑run:

Index Scan using idx_sales_prod_date on sales  (cost=0.42..12.34 rows=5000 width=123) (actual time=0.012..0.567 rows=5000 loops=1)
Enter fullscreen mode Exit fullscreen mode

Execution time dropped from 45 s to 0.6 s.

7️⃣ Automate the Fixes

You can script the most common recommendations (index creation, config tuning, and stats refresh) and run them across multiple instances.

Download the pre‑configured script here

Get the complete patch tool

Access the full repository fix

By consistently applying these steps you’ll keep PostgreSQL humming even under heavy load. Happy querying!

Top comments (0)