How to Optimize Slow Queries in PostgreSQL for Better Performance

To optimize a slow query in PostgreSQL, start by analyzing the query execution plan using EXPLAIN ANALYZE to identify bottlenecks. Then, improve performance by adding appropriate indexes, rewriting inefficient queries, and updating database statistics with ANALYZE.

Syntax

Use EXPLAIN ANALYZE <your_query> to see how PostgreSQL executes your query and where it spends time. Add CREATE INDEX statements to speed up data retrieval on columns used in filters or joins. Use ANALYZE to update statistics that help the query planner make better decisions.

sql

EXPLAIN ANALYZE SELECT * FROM employees WHERE department_id = 5;

CREATE INDEX idx_department_id ON employees(department_id);

ANALYZE employees;

Example

This example shows how to find a slow query, create an index to speed it up, and verify the improvement.

sql

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;

-- Suppose this query is slow, create an index:
CREATE INDEX idx_customer_id ON orders(customer_id);

ANALYZE orders;

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;

Output

Seq Scan on orders  (cost=0.00..1000.00 rows=100 width=50) (actual time=50.000..55.000 rows=10 loops=1)
Index Scan using idx_customer_id on orders  (cost=0.00..10.00 rows=10 width=50) (actual time=0.100..0.200 rows=10 loops=1)

Common Pitfalls

• Not using indexes on columns used in WHERE, JOIN, or ORDER BY clauses.
• Ignoring the query plan and guessing what is slow.
• Using SELECT * instead of selecting only needed columns.
• Not updating statistics with ANALYZE after data changes.

sql

/* Wrong: No index, slow sequential scan */
SELECT * FROM sales WHERE product_id = 42;

/* Right: Create index to speed up lookup */
CREATE INDEX idx_product_id ON sales(product_id);
ANALYZE sales;
SELECT * FROM sales WHERE product_id = 42;

Quick Reference

Summary tips to optimize slow PostgreSQL queries:

• Use EXPLAIN ANALYZE to understand query performance.
• Create indexes on columns used in filters and joins.
• Update statistics regularly with ANALYZE.
• Avoid SELECT *; fetch only needed columns.
• Rewrite complex queries to simplify joins and conditions.

Key Takeaways

Always analyze slow queries with EXPLAIN ANALYZE to find bottlenecks.

Add indexes on columns frequently used in WHERE, JOIN, or ORDER BY clauses.

Keep database statistics updated using ANALYZE for better query planning.

Avoid fetching unnecessary columns by not using SELECT *.

Rewrite complex queries to improve readability and performance.