How to Debug Slow Database Queries

How to Debug Slow Database Queries

Slow queries are the most common performance bottleneck. Here's a systematic approach to finding and fixing them.

Step 1: Identify the Slow Queries

Enable slow query logging:

-- PostgreSQL
ALTER SYSTEM SET log_min_duration_statement = 500; -- log queries over 500ms
SELECT pg_reload_conf();

-- MySQL
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;

Step 2: Analyze with EXPLAIN

EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id)
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2025-01-01'
GROUP BY u.name;

Key things to look for:

• Seq Scan on large tables — usually needs an index
• Nested Loop with high row counts — consider a hash join
• Sort operations on large datasets — add an index matching the sort order
• Actual rows much higher than estimated rows — stale statistics

Step 3: Check Index Usage

-- PostgreSQL: find missing indexes
SELECT schemaname, tablename, seq_scan, idx_scan
FROM pg_stat_user_tables
WHERE seq_scan > idx_scan
ORDER BY seq_scan - idx_scan DESC
LIMIT 10;

Common Fixes

• Add composite indexes matching your WHERE + ORDER BY clauses
• Avoid SELECT * — fetch only needed columns
• Paginate results — never fetch unbounded result sets
• Use EXISTS instead of IN for subqueries
• Update statistics — ANALYZE tablename in PostgreSQL

Step 4: Fix N+1 Queries

The most common ORM mistake:

# Bad: N+1 queries
for user in User.objects.all():
    print(user.orders.count())  # One query per user

# Good: single query with annotation
User.objects.annotate(order_count=Count('orders'))

Monitor Query Performance

Slow queries often correlate with error spikes. When Bugsly shows timeout errors, check your database query performance first. A single missing index can cascade into application-wide slowdowns that manifest as seemingly random failures.