Fix PostgreSQL Query Too Slow

What's Actually Happening

PostgreSQL queries run slowly when they scan large tables without indexes, use inefficient joins, or have suboptimal execution plans. This impacts application performance and user experience.

The Error You'll See

Slow query log:

```bash $ tail /var/log/postgresql/postgresql-14-main.log

2026-04-16 00:26:00 UTC [12345] LOG: duration: 15000.123 ms statement: SELECT * FROM orders WHERE customer_id = 100; ```

pg_stat_statements:

```sql SELECT query, calls, total_exec_time, mean_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;

-- Output: -- query | calls | total_exec_time | mean_exec_time -- SELECT * FROM orders WHERE ... | 1000 | 15000000 | 15000 ```

Application timeout:

QueryTimeoutError: Query execution time exceeded timeout (30000ms)
Query: SELECT * FROM orders WHERE status = 'pending'

Why This Happens

1. 1.Missing index - No index on filter column
2. 2.Table bloat - Dead tuples slowing scans
3. 3.Wrong query plan - Optimizer choosing wrong path
4. 4.Large result set - Returning too many rows
5. 5.Inefficient joins - Poor join strategy
6. 6.Outdated statistics - Stale table statistics

Step 1: Identify Slow Queries

```sql -- Check pg_stat_statements SELECT query, calls, round(total_exec_time::numeric, 2) as total_time_ms, round(mean_exec_time::numeric, 2) as avg_time_ms, rows FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

-- If pg_stat_statements not enabled: -- In postgresql.conf: shared_preload_libraries = 'pg_stat_statements'

-- Check current slow queries SELECT pid, now() - query_start AS duration, query, state FROM pg_stat_activity WHERE state = 'active' AND now() - query_start > interval '5 seconds' ORDER BY duration DESC;

-- Check long-running queries SELECT pid, query_start, now() - query_start AS duration, query FROM pg_stat_activity WHERE state != 'idle' ORDER BY duration DESC; ```

Step 2: Analyze Query Execution Plan

```sql -- Use EXPLAIN ANALYZE for detailed plan EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 100;

-- Output analysis: -- Seq Scan on orders (cost=0.00..15000.00 rows=1 width=100) -- ^^^^^^^^^ Sequential scan = slow without index -- -- Index Scan using idx_customer on orders (cost=0.42..8.44 rows=1) -- ^^^^^^^^^^^ Index scan = fast

-- Key indicators of slow queries: -- 1. Seq Scan (sequential scan) on large table -- 2. Filter instead of Index Scan -- 3. Nested Loop with many iterations -- 4. HashAggregate with large row count -- 5. Sort Method: external merge (disk-based)

-- Use BUFFERS to see I/O EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 100;

-- Check if using temp files (bad) -- "Sort Method: external merge Disk: 1024kB" ```

Step 3: Create Missing Indexes

```sql -- Identify columns in WHERE clause without index EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 100; -- If shows "Seq Scan", need index

-- Create b-tree index (default) CREATE INDEX idx_orders_customer_id ON orders(customer_id);

-- Create composite index for multiple conditions CREATE INDEX idx_orders_customer_status ON orders(customer_id, status);

-- Create partial index for common filter CREATE INDEX idx_orders_pending ON orders(customer_id) WHERE status = 'pending';

-- Create index with include for covering queries CREATE INDEX idx_orders_covering ON orders(customer_id) INCLUDE (status, total);

-- For text search CREATE INDEX idx_orders_description ON orders USING gin(to_tsvector('english', description));

-- For JSONB fields CREATE INDEX idx_orders_data ON orders USING gin(data);

-- Check index usage after creation EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 100; -- Should show "Index Scan" ```

Step 4: Update Table Statistics

```sql -- Update statistics for query planner ANALYZE orders;

-- Analyze specific columns ANALYZE orders (customer_id, status);

-- Full vacuum and analyze VACUUM ANALYZE orders;

-- Check table statistics SELECT schemaname, tablename, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum, last_analyze FROM pg_stat_user_tables WHERE relname = 'orders';

-- If n_dead_tup high, need vacuum -- If last_analyze old, need analyze

-- Increase statistics targets for better plans ALTER TABLE orders ALTER COLUMN customer_id SET STATISTICS 500; ANALYZE orders (customer_id); ```

Step 5: Fix Table Bloat

```sql -- Check for table bloat SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size, n_dead_tup, n_live_tup, round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) as dead_ratio FROM pg_stat_user_tables ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC LIMIT 10;

-- If dead_ratio > 20%, need vacuum

-- Regular vacuum (doesn't lock) VACUUM orders;

-- Full vacuum (locks table, reclaims space) VACUUM FULL orders;

-- Or use pg_repack for online vacuum -- pg_repack --table=orders --dbname=mydb

-- Prevent future bloat with autovacuum -- In postgresql.conf: autovacuum = on autovacuum_vacuum_scale_factor = 0.1 autovacuum_analyze_scale_factor = 0.05 ```

Step 6: Optimize JOIN Queries

```sql -- Slow JOIN example EXPLAIN ANALYZE SELECT o.*, c.name FROM orders o JOIN customers c ON o.customer_id = c.id WHERE o.status = 'pending';

-- Check join type: -- Nested Loop: Good for small tables -- Hash Join: Good for medium tables -- Merge Join: Good for large sorted tables

-- Ensure join columns indexed CREATE INDEX idx_orders_customer_id ON orders(customer_id); CREATE INDEX idx_customers_id ON customers(id);

-- Optimize with index-only scan CREATE INDEX idx_orders_status_customer ON orders(status, customer_id);

-- Use appropriate join type -- Force hash join if better: SET enable_nestloop = off; SET enable_mergejoin = off;

-- Or force nested loop for small tables: SET enable_hashjoin = off;

-- Test which is faster, then optimize schema/indexes accordingly ```

Step 7: Limit Result Size

```sql -- WRONG: Fetching all rows SELECT * FROM orders WHERE status = 'pending'; -- Could return millions of rows

-- CORRECT: Use LIMIT and pagination SELECT * FROM orders WHERE status = 'pending' LIMIT 100 OFFSET 0;

-- Or use cursor for large result sets BEGIN; DECLARE order_cursor CURSOR FOR SELECT * FROM orders WHERE status = 'pending'; FETCH 100 FROM order_cursor; -- ... process FETCH 100 FROM order_cursor; COMMIT;

-- Use keyset pagination for better performance SELECT * FROM orders WHERE status = 'pending' AND id > last_id ORDER BY id LIMIT 100;

-- Only select needed columns SELECT id, customer_id, status FROM orders WHERE customer_id = 100; -- Not: SELECT * FROM orders WHERE customer_id = 100; ```

Step 8: Use Query Hints Wisely

```sql -- Disable specific plan types if optimizer wrong

-- Disable sequential scan SET enable_seqscan = off; EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 100;

-- Disable index scan (force sequential for bulk) SET enable_indexscan = off; SELECT * FROM orders WHERE customer_id IN (SELECT customer_id FROM large_list);

-- Disable hash join SET enable_hashjoin = off;

-- Disable nested loop SET enable_nestloop = off;

-- Disable merge join SET enable_mergejoin = off;

-- Reset all RESET enable_seqscan; RESET ALL;

-- Use pg_hint_plan for persistent hints /*+ IndexScan(orders idx_customer) */ SELECT * FROM orders WHERE customer_id = 100; ```

Step 9: Optimize Configuration

```sql -- Check memory settings SHOW shared_buffers; -- Should be 25% of RAM SHOW work_mem; -- Memory per query operation SHOW effective_cache_size; -- Estimate of OS cache

-- Increase for complex queries SET work_mem = '256MB';

-- For session: SET LOCAL work_mem = '256MB';

-- Permanently in postgresql.conf: work_mem = 64MB shared_buffers = 4GB effective_cache_size = 12GB

-- Check random page cost SHOW random_page_cost; -- Lower for SSD (1.1 instead of 4.0) SET random_page_cost = 1.1;

-- Parallel query settings SET max_parallel_workers_per_gather = 4; SET parallel_setup_cost = 0; SET parallel_tuple_cost = 0; ```

Step 10: Monitor Query Performance

```sql -- Create monitoring view CREATE VIEW slow_queries AS SELECT query, calls, round(total_exec_time::numeric, 2) as total_ms, round(mean_exec_time::numeric, 2) as avg_ms, rows, round(100 * total_exec_time / sum(total_exec_time) over (), 2) as pct_total FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;

-- Query the view SELECT * FROM slow_queries;

-- Reset statistics SELECT pg_stat_statements_reset();

-- Set up auto_explain for slow queries -- In postgresql.conf: shared_preload_libraries = 'auto_explain' auto_explain.log_min_duration = 1000 -- Log queries > 1 second auto_explain.log_analyze = on

-- Then check log for execution plans ```

Query Optimization Reference

Verify the Fix

```sql -- After adding index or optimizing query

-- 1. Check execution plan EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 100; -- Should show Index Scan, not Seq Scan -- Execution time should be lower

-- 2. Compare before/after -- Before: Execution Time: 15000.123 ms -- After: Execution Time: 5.234 ms

-- 3. Check index usage SELECT schemaname, tablename, indexname, idx_scan FROM pg_stat_user_indexes WHERE tablename = 'orders';

-- 4. Verify query in application -- Response time should be acceptable

-- 5. Monitor for regression SELECT * FROM slow_queries WHERE query LIKE '%orders%';

-- 6. Check table health SELECT n_dead_tup, n_live_tup FROM pg_stat_user_tables WHERE relname = 'orders'; ```

Related Issues

• [Fix PostgreSQL Connection Pool Exhausted](/articles/fix-postgresql-connection-pool-exhausted)
• [Fix PostgreSQL Table Lock Wait Timeout](/articles/fix-postgresql-table-lock-wait-timeout)
• [Fix PostgreSQL Index Not Used](/articles/fix-postgresql-index-not-used)