SQL Performance Tuning: Making Queries Fast Without Rewriting Everything

SQL queries are the lifeblood of data-driven applications. When a user clicks "Load More" on a dashboard or a model triggers a batch update, the speed of that experience depends on how well the underlying SQL performs. This page details the practical, immediate steps to make existing SQL queries fast—without rearchitecting the entire data pipeline. It covers techniques that work today, in any relational database, using only standard tools and known patterns.

Diagnose the Slow Query

Start with a query that takes longer than expected. Use EXPLAIN ANALYZE to reveal the actual execution plan and timing breakdown.

EXPLAIN ANALYZE
SELECT u.name, COUNT(t.id) AS total_tasks
FROM users u
JOIN tasks t ON u.id = t.user_id
WHERE u.created_at > '2023-01-01'
  AND t.status = 'completed'
GROUP BY u.id, u.name
ORDER BY total_tasks DESC
LIMIT 10;

The output includes:

• Seq Scan on users — scanning every row in users
• Index Scan on tasks — scanning index on tasks.status, but not using user_id effectively
• Hash Aggregate — aggregating grouped data in memory
• Hash Join — joining users and tasks using a hash table

The key metric: Actual Time per operation. If Hash Join takes 1.2 seconds for 100,000 rows, it’s a bottleneck. If Index Scan on tasks reports Rows = 100000, but Actual Rows = 1000000, the index is not selective enough.

Common failure modes:

• Missing index on join column: t.user_id is not indexed, so the Hash Join reads all of tasks for each users row.
• Filter pushdown failure: WHERE u.created_at > '2023-01-01' is applied after the join, not before.
• Memory pressure: Hash Aggregate spills to disk when it exceeds work_mem setting.

Adjust work_mem for large aggregations:

SET work_mem = '16MB';

If Hash Join spills to disk, increase effective_cache_size to guide the planner:

SET effective_cache_size = '2GB';

Add and Optimize Indexes

Use composite indexes for queries with multiple WHERE and JOIN conditions.

For the query above, create:

CREATE INDEX idx_tasks_user_status ON tasks (user_id, status);

This index supports both:

• Filtering on status = 'completed'
• Efficiently joining tasks to users via user_id

But if user_id is not the leading column, the planner may fall back to index-only scans or skip scans.

To ensure the planner uses the index fully, add INCLUDE columns:

CREATE INDEX idx_tasks_user_status_created ON tasks
  (user_id, status) INCLUDE (created_at, priority);

Now, the query can satisfy SELECT user_id, status, created_at, priority without fetching from the table.

For queries with ORDER BY, use covering indexes:

CREATE INDEX idx_tasks_user_status_created_desc ON tasks
  (user_id, status, created_at DESC) INCLUDE (priority, id);

This index supports:

• Filtering tasks.status = 'completed'
• Joining users via user_id
• Sorting by created_at descending
• Returning priority and id without table access

The planner will now choose a Index Only Scan and skip the table entirely.

Common Index Mistakes

• Over-indexing: adding an index for every column pair, but not tuning autovacuum — leading to bloat and stale statistics.
• Incorrect index column order: user_id, status is better than status, user_id when filtering by status and joining by user_id.
• Missing INCLUDE: the planner reads the table even though it could get all needed data from the index.

Use pg_stat_user_indexes to monitor index efficiency:

SELECT
  schemaname,
  tablename,
  indexname,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch
FROM pg_stat_user_indexes
WHERE tablename = 'tasks';

If idx_tup_fetch is much higher than idx_tup_read, the index is not covering.

Rewrite Queries Strategically, Not Comprehensively

Avoid rewriting every query in the codebase. Instead, focus on high-impact queries that:

• Run frequently
• Take longer than expected
• Serve multiple users or processes

Use a query profiler to identify the top 10 slowest queries over a 24-hour window. Then, apply targeted changes.

Push Down Filters

Move WHERE conditions as early as possible in the query execution.

Instead of:

SELECT u.name, t.description
FROM users u
JOIN (
  SELECT *
  FROM tasks
  WHERE status = 'completed'
) t ON u.id = t.user_id
WHERE u.created_at > '2023-01-01';

Use:

SELECT u.name, t.description
FROM users u
JOIN tasks t ON u.id = t.user_id
WHERE t.status = 'completed'
  AND u.created_at > '2023-01-01';

The planner can now:

• Scan users with created_at > '2023-01-01'
• Use idx_tasks_user_status to filter and join simultaneously

Use Window Functions to Replace Joins and Subqueries

Replace a common pattern: “calculate total tasks per user, then rank them” — with a single window function.

Instead of:

WITH user_counts AS (
  SELECT user_id, COUNT(*) AS total_tasks
  FROM tasks
  GROUP BY user_id
)
SELECT u.name, uc.total_tasks,
       RANK() OVER (ORDER BY uc.total_tasks DESC) AS rank
FROM users u
JOIN user_counts uc ON u.id = uc.user_id
ORDER BY uc.total_tasks DESC;

Use:

SELECT
  u.name,
  COUNT(t.id) AS total_tasks,
  RANK() OVER (ORDER BY COUNT(t.id) DESC) AS rank
FROM users u
JOIN tasks t ON u.id = t.user_id
WHERE t.status = 'completed'
GROUP BY u.id, u.name
ORDER BY total_tasks DESC;

This query:

• Computes COUNT(t.id) directly in the GROUP BY
• Uses a window function to rank all users in one pass
• Eliminates the WITH clause and extra join

The planner now chooses a Hash Aggregate with a Window Aggregation and avoids materializing the user_counts CTE.

Combine Multiple Queries into One

For a dashboard that shows:

• User count
• Task count
• Average task duration
• Top 5 users

Instead of five separate queries, use a single query with multiple VALUES or UNION ALL.

SELECT 'user_count' AS metric, COUNT(*) AS value
FROM users
UNION ALL
SELECT 'task_count', COUNT(*)
FROM tasks
UNION ALL
SELECT 'avg_duration', AVG(duration)
FROM tasks
UNION ALL
SELECT 'top_users', u.name
FROM users u
ORDER BY u.name
LIMIT 5;

This reduces round trips and enables query coalescing.

Optimize Execution Plans Using Hints and Settings

When the planner chooses a suboptimal plan, use hints to guide it.

Force Join Order and Type

Use SET enable_hashjoin, enable_mergejoin, enable_nestloop to control which join type to use.

For large datasets, force a Hash Join:

SET enable_hashjoin = ON;
SET enable_nestloop = OFF;

For queries with sorted data, use Merge Join:

SET enable_mergejoin = ON;
SET enable_hashjoin = OFF;

Use ANALYZE and VACUUM to Keep Statistics Fresh

Run ANALYZE after bulk inserts:

ANALYZE users;
ANALYZE tasks;

This updates pg_statistic with updated most_common_values, histogram_bounds, and n_distinct values.

Run VACUUM to reduce table bloat:

VACUUM (VERBOSE, ANALYZE) users;

VACUUM reclaims space from deleted rows and updates statistics.

Use pg_stat_progress_vacuum to monitor long vacuum jobs:

SELECT
  phase,
  heap_blks_scanned,
  heap_blks_total,
  index_blks_scanned,
  index_blks_total,
  tuples_processed,
  tuples_excluded
FROM pg_stat_progress_vacuum;

Tune effective_cache_size and work_mem

Set effective_cache_size to reflect the actual amount of RAM available to the database:

SET effective_cache_size = '4GB';

This helps the planner estimate how much of the database fits in memory.

Set work_mem for operations that spill to disk:

SET work_mem = '8MB';

For queries with large GROUP BY, ORDER BY, or DISTINCT, increase work_mem:

SET work_mem = '32MB';

The planner will prefer in-memory Hash Aggregate and Sort over Merge Sort.

Handle Common Failure Modes

Query Plan Regressions Over Time

After a schema change or data load, the same query runs slower even though the index is in place.

Check pg_statio_user_tables:

SELECT
  schemaname,
  tablename,
  heap_blks_read,
  heap_blks_hit,
  idx_blks_read,
  idx_blks_hit,
  toast_blks_read,
  toast_blks_hit
FROM pg_statio_user_tables
WHERE tablename = 'tasks';

If heap_blks_read is high but heap_blks_hit is low, the table is not in cache.

Run pg_stat_statements to track query execution:

-- Enable tracking
CREATE EXTENSION pg_stat_statements;

-- View top queries by total time
SELECT
  query,
  calls,
  total_time,
  mean_time,
  rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

Use EXPLAIN (FORMAT JSON) to compare plans before and after a change:

EXPLAIN (FORMAT JSON)
SELECT u.name, COUNT(t.id)
FROM users u
JOIN tasks t ON u.id = t.user_id
WHERE u.created_at > '2023-01-01'
GROUP BY u.id, u.name;

Compare the plan output across deployments.

Silent Failure: Index Not Used

A query runs slowly, but EXPLAIN shows no index usage.

Check:

• Is the index created on the correct table?
• Is the column type the same as the query’s filter?
• Is the index created with INCLUDE or WHERE clauses?

Use pg_indexes:

SELECT
  schemaname,
  tablename,
  indexname,
  indexdef
FROM pg_indexes
WHERE tablename = 'tasks';

Look for WHERE clauses in indexdef, such as:

CREATE INDEX idx_tasks_completed ON tasks (user_id, status)
  WHERE status = 'completed';

If status = 'pending' is common but not in the index, the planner may fall back to a full scan.

Memory Pressure and Spilling

When Hash Aggregate or Sort spills to disk, monitor pg_stat_user_tables and pg_statio_user_tables.

If heap_blks_read is high, increase work_mem.

If idx_blks_read is high, increase effective_cache_size.

Use pg_settings to check current values:

SELECT
  name,
  setting,
  short_desc
FROM pg_settings
WHERE name IN ('work_mem', 'effective_cache_size', 'maintenance_work_mem');

Schema Drift and Query Degradation

After adding a column to users, a query no longer uses a composite index.

Check:

• Was the index created on the old schema?
• Are there DEFAULT values or NOT NULL constraints causing index bloat?
• Is the index used in a SELECT * query?

Use pg_get_indexdef to inspect index definition:

SELECT pg_get_indexdef('idx_tasks_user_status'::regclass);

Output:

CREATE INDEX idx_tasks_user_status ON tasks (user_id, status)

But if the query uses users.*, and users has a new last_login column, the planner may not use the index if last_login is not INCLUDEd.

Summary: The Fast Path

To make SQL queries fast without rewriting everything:

1. Diagnose with EXPLAIN ANALYZE — look at Actual Time, Rows, Plans.
2. Add composite indexes with INCLUDE for covering scans.
3. Push down filters and use window functions to replace joins and CTEs.
4. Combine queries using UNION ALL or VALUES.
5. Tune work_mem and effective_cache_size to reduce spilling.
6. Run ANALYZE and VACUUM regularly.
7. Monitor plan regressions using pg_stat_statements and pg_statio_user_tables.

Each change is a discrete, measurable improvement. No need to overhaul the entire data layer. Just target the slowest queries, apply these techniques, and watch the performance rise.

This page was rewritten on 17 October 2026. It replaced a templated version that was not specific to its own title. How these pages are written and checked. If anything here is wrong, tell us at [email protected] and we will correct it.