DEV Community

Deep Fix
Deep Fix

Posted on

Boost PostgreSQL Query Performance: Proven Tips & Indexing Strategies

#postgres #performance #optimization #sql

Boost PostgreSQL Query Performance

Target audience: software developers, engineers, and DevOps professionals.

1. Understand the Query Planner

PostgreSQL decides how to execute a statement using its planner. The first step in any performance tune‑up is to see what the planner is doing.

EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT *
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.created_at >= '2023-01-01' AND o.amount > 1000;
Enter fullscreen mode Exit fullscreen mode

The output shows the actual execution time, rows processed, and buffer usage. Look for:

  • Seq Scan on large tables – often a sign that an index is missing.
  • High cost values – indicates expensive operations like sorts or hash joins.
  • Rows Removed by Filter – suggests the planner underestimated selectivity.

2. Indexing Best Practices

2.1 Single‑Column Indexes

If a query filters on a single column, a B‑tree index is usually sufficient.

CREATE INDEX idx_orders_created_at ON orders (created_at);
Enter fullscreen mode Exit fullscreen mode

2.2 Multi‑Column (Composite) Indexes

When predicates use multiple columns, the order matters. Put the most selective column first.

CREATE INDEX idx_orders_customer_amount ON orders (customer_id, amount);
Enter fullscreen mode Exit fullscreen mode

2.3 Covering Indexes (Include Clause)

PostgreSQL 12+ lets you add non‑key columns to avoid heap look‑ups.

CREATE INDEX idx_orders_cover ON orders (customer_id) INCLUDE (amount, created_at);
Enter fullscreen mode Exit fullscreen mode

3. Keep Statistics Fresh

The planner relies on table statistics. Stale stats lead to bad plans.

VACUUM ANALYZE;
-- Or target a single table
ANALYZE orders;
Enter fullscreen mode Exit fullscreen mode

Run VACUUM (ANALYZE) after bulk loads or massive updates.

4. Configuration Tweaks

Parameter Typical Adjustment Effect
work_mem Increase for large sorts/HashAgg Reduces disk spill
shared_buffers 25‑30% of RAM Improves cache hit rate
effective_cache_size Approx. 50‑75% of RAM Helps planner estimate cache availability
max_parallel_workers_per_gather 2‑4 Enables parallel query execution

Apply changes in postgresql.conf and reload:

pg_ctl reload -D /var/lib/postgresql/data
Enter fullscreen mode Exit fullscreen mode

5. Step‑by‑Step Troubleshooting Workflow

  1. Capture the slow query – use pg_stat_statements or logs.
  2. Run EXPLAIN ANALYZE – identify scans, joins, and bottlenecks.
  3. Check indexes – are they used? If not, adjust column order or add INCLUDE columns.
  4. Refresh statistics – VACUUM ANALYZE.
  5. Adjust work_mem – especially for queries with large sorts or hash aggregates.
  6. Re‑run EXPLAIN – confirm the plan improves.
  7. Monitor – keep an eye on pg_stat_activity and pg_stat_io to ensure the change has real impact.

6. Real‑World Example

A reporting query was taking >30 seconds:

SELECT p.product_name,
       SUM(s.quantity) AS total_sold,
       AVG(s.price)   AS avg_price
FROM sales s
JOIN products p ON s.product_id = p.id
WHERE s.sale_date BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY p.product_name
ORDER BY total_sold DESC;
Enter fullscreen mode Exit fullscreen mode

What we did:

  1. EXPLAIN ANALYZE showed a Seq Scan on sales and a Hash Aggregate spilling to disk.
  2. Created a partial index on the date range: 
   CREATE INDEX idx_sales_date_qty_price ON sales (sale_date, quantity, price);
Enter fullscreen mode Exit fullscreen mode
  1. Increased work_mem from 4MB to 64MB.
  2. Ran VACUUM ANALYZE sales.

Result: Execution time dropped to ~2 seconds.

7. Automate the Fixes

If you frequently encounter the same pattern, you can drop a ready‑made script into your CI pipeline. Download the pre‑configured script here, or grab the full toolset with Get the complete patch tool. For a deeper dive, Access the full repository fix.

Conclusion

Optimizing PostgreSQL queries is an iterative process: observe → hypothesize → test → repeat. By mastering EXPLAIN ANALYZE, applying the right indexes, keeping statistics up‑to‑date, and tuning a few key parameters, you can turn sluggish workloads into lightning‑fast responses.

Happy tuning!

Top comments (0)