Database Query Optimization: How to Find and Fix Slow Queries

Database Query Optimization: How to Find and Fix Slow Queries

Slow database queries are the most common root cause of slow web applications. A single missing index on a table with a million rows can turn a 2ms query into a 2-second one. The good news: finding and fixing slow queries is methodical — there are clear tools and patterns to follow. This guide walks you through the full process.

Step 1: Find Your Slow Queries

Before you can fix slow queries, you need to identify them.

Enable slow query logging in PostgreSQL:

-- Log all queries taking longer than 100ms
ALTER SYSTEM SET log_min_duration_statement = 100;
ALTER SYSTEM SET log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d ';
SELECT pg_reload_conf();

Query pg_stat_statements (built-in PostgreSQL extension):

-- Enable the extension (one-time)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Find the 10 slowest queries by total time
SELECT
  round(total_exec_time::numeric, 2) AS total_ms,
  calls,
  round(mean_exec_time::numeric, 2) AS avg_ms,
  round(stddev_exec_time::numeric, 2) AS stddev_ms,
  query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

MySQL slow query log:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.1; -- 100ms threshold
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

Step 2: Understand Query Execution with EXPLAIN ANALYZE

Once you have a slow query, use EXPLAIN ANALYZE to see exactly what the database is doing:

EXPLAIN ANALYZE
SELECT p.id, p.name, p.status, u.email
FROM projects p
JOIN users u ON u.id = p.owner_id
WHERE p.organization_id = '123'
  AND p.status = 'RUNNING'
ORDER BY p.created_at DESC
LIMIT 20;

Key things to look for in the output:

• Seq Scan on a large table — means no index is being used
• Hash Join vs Index Scan — index scans are usually better for selective queries
• Rows estimate vs actual — large discrepancies mean stale statistics
• actual time — the actual execution time for each node

Seq Scan on projects  (cost=0.00..8432.00 rows=1 width=40)
                      (actual time=3124.234..3124.237 rows=15 loops=1)
  Filter: ((organization_id = '123') AND (status = 'RUNNING'))
  Rows Removed by Filter: 312847

This output reveals a sequential scan removing 312,847 rows — a clear case for an index.

Step 3: Add the Right Indexes

Single-column index:

CREATE INDEX CONCURRENTLY idx_projects_org_id
ON projects(organization_id);

Composite index (better for queries filtering on multiple columns):

-- Index matches WHERE organization_id = ? AND status = ?
-- AND covers ORDER BY created_at DESC
CREATE INDEX CONCURRENTLY idx_projects_org_status_date
ON projects(organization_id, status, created_at DESC);

Partial index (index only a subset of rows):

-- Only indexes running projects, much smaller index
CREATE INDEX CONCURRENTLY idx_projects_running
ON projects(organization_id, created_at DESC)
WHERE status = 'RUNNING';

Always use CONCURRENTLY in production — it builds the index without locking the table.

Step 4: Rewrite Inefficient Queries

Avoid functions on indexed columns in WHERE clauses:

-- Bad: function prevents index use
WHERE DATE(created_at) = '2026-05-01'

-- Good: range query uses the index
WHERE created_at >= '2026-05-01' AND created_at < '2026-05-02'

Replace correlated subqueries with JOINs:

-- Bad: correlated subquery runs once per row
SELECT p.*, (
  SELECT COUNT(*) FROM deployments d WHERE d.project_id = p.id
) AS deployment_count
FROM projects p;

-- Good: single JOIN with aggregation
SELECT p.*, COUNT(d.id) AS deployment_count
FROM projects p
LEFT JOIN deployments d ON d.project_id = p.id
GROUP BY p.id;

Use EXISTS instead of IN for large subqueries:

-- Slower with large subquery result
WHERE id IN (SELECT project_id FROM deployments WHERE status = 'FAILED')

-- Faster: stops at first match
WHERE EXISTS (
  SELECT 1 FROM deployments d
  WHERE d.project_id = projects.id AND d.status = 'FAILED'
)

Step 5: Eliminate N+1 Query Patterns

N+1 is when your application fetches a list and then queries the database once per item in the loop:

// N+1: 1 query for projects + 1 query per project for owner
const projects = await db.query('SELECT * FROM projects WHERE org_id = $1', [orgId]);
for (const project of projects.rows) {
  project.owner = await db.query('SELECT * FROM users WHERE id = $1', [project.owner_id]);
}

// Fixed: 1 query with JOIN
const { rows } = await db.query(`
  SELECT p.*, u.email AS owner_email, u.name AS owner_name
  FROM projects p
  JOIN users u ON u.id = p.owner_id
  WHERE p.org_id = $1
`, [orgId]);

Step 6: Update Statistics

The query planner uses table statistics to choose execution plans. After loading large amounts of data or major schema changes, refresh statistics:

ANALYZE projects;           -- Update statistics for one table
ANALYZE VERBOSE;            -- Update all tables with output

Application-Side Connection Management

Beyond query optimization, always use connection pooling. Without it, each request creates and tears down a database connection — overhead that can dominate response time at scale.

const { Pool } = require('pg');
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

[PandaStack](https://dashboard.pandastack.io) supports PostgreSQL and MySQL as managed databases alongside your containerized applications, so your database lives in the same platform as your application — minimizing network latency between your app and the database.

Optimization Checklist

• [ ] Enable slow query logging (threshold: 100ms)
• [ ] Query pg_stat_statements for top time consumers
• [ ] Run EXPLAIN ANALYZE on each slow query
• [ ] Add composite indexes matching your WHERE + ORDER BY columns
• [ ] Rewrite functions-on-columns to range queries
• [ ] Eliminate N+1 patterns with JOINs
• [ ] Use connection pooling
• [ ] Run ANALYZE after bulk data changes
• [ ] Schedule weekly slow query reviews as data grows