Query Optimization Advanced

Production engineering guide for query optimization advanced covering patterns, implementation strategies, and operational best practices.

Query Optimization Advanced is the discipline of fine-tuning SQL queries and their execution plans to deliver predictable, low-latency, and resource-efficient performance under real workloads. It matters when a database becomes a performance bottleneck—not just under load, but under changing access patterns, evolving data volumes, and complex application logic. This page details the practices, tools, and patterns that transform a good query into a high-performance one, with no assumptions about the underlying database engine or platform.

Understanding Query Execution Plans

To optimize a query, you must first understand how it runs. The execution plan—a tree of operations like scans, joins, filters, and aggregations—is the foundation. Use the EXPLAIN command to inspect it. The plan reveals how the database accesses data, which indexes it uses, and where it spends time.

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT u.name, COUNT(t.id) AS task_count
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 task_count DESC
LIMIT 10;

The ANALYZE flag runs the query and returns actual row counts, I/O, and timing. BUFFERS shows which buffers were hit or missed, revealing cache efficiency. FORMAT JSON enables tooling integration.

The output includes:

A high Cost for a Seq Scan on a large tasks table indicates a missing or underused index. A Bitmap Heap Scan suggests multiple index lookups, possibly from a Bitmap Index Scan.

Common Plan Patterns and Their Implications

Index-Only Scan: When a query can satisfy all required columns from the index alone, the database avoids reading the table. This happens when the index includes all needed fields, or when the index is a covering index.

CREATE INDEX idx_tasks_covering ON tasks (status, user_id, created_at, id)
INCLUDE (title, priority);

With this index, a query retrieving id, title, and priority for completed tasks needs only the index.

Nested Loop Join: Efficient for small inputs. The outer row is fetched, and for each, the inner table is scanned. But it becomes expensive when the inner table is large and not indexed.

Hash Join: Best for large, unsorted datasets. The database builds a hash table from one input, then probes it with the other. The cost is dominated by memory use and hash computation.

Merge Join: Requires both inputs to be sorted on the join key. It performs well when data is already sorted or when sorting is cheap.

Choosing and Tuning Indexes

Indexes are the most impactful optimization lever. They must be chosen not just for single queries, but for sets of queries.

Composite Indexes and Covering Indexes

For queries with WHERE, JOIN, GROUP BY, or ORDER BY clauses, use composite indexes.

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

This index supports:

To reduce I/O, add INCLUDE clauses to make the index covering:

CREATE INDEX idx_tasks_covering ON tasks (user_id, status, created_at)
INCLUDE (title, priority, description, id);

Now, a query retrieving title, priority, and description for completed tasks needs only the index.

Index-Only Scans and the Tuple Id

A Index-Only Scan reads only index entries, skipping the table. But if a tuple (row) is updated and the index is not up to date, the database must fetch the tuple from the table. This is tracked via the ctid (tuple ID) field.

When Index-Only Scan shows Heap Fetch steps, the index is not covering. To diagnose, check Buffers output:

This indicates that the index is missing some columns or that tuples were updated after the index was built.

Index Maintenance and Bloat

Over time, indexes grow and become bloaty due to updates and deletions. Use VACUUM and ANALYZE to maintain them.

VACUUM (VERBOSE, ANALYZE) tasks;

VERBOSE outputs progress. ANALYZE updates statistics used by the planner.

For large indexes, use VACUUM FULL to reclaim space and rebuild the index:

VACUUM FULL (VERBOSE, ANALYZE) users;

VACUUM FULL locks the table, making it suitable for maintenance windows.

Monitoring Index Usage

Track which indexes are used and how often:

SELECT 
  schemaname,
  tablename,
  indexname,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch,
  idx_blks_read,
  idx_blks_hit
FROM pg_stat_user_indexes
WHERE idx_scan > 0
ORDER BY idx_tup_read DESC;

High idx_tup_read and low idx_blks_hit suggest frequent index scans with poor cache utilization. High idx_tup_fetch indicates that the index is not covering.

Query Plan Stability and Plan Cache

Databases use a plan cache to reuse execution plans. But plans can drift due to statistics, parameter values, or memory pressure.

Parameterized Queries and Plan Invalidation

When a query uses parameters, the database may cache a plan for each parameter set. This leads to plan bloat and suboptimal execution.

SELECT * FROM orders WHERE customer_id = $1 AND status = $2;

If this query is executed with customer_id = 100 and status = 'shipped', the database may cache a plan specific to those values.

But if the same query is run with customer_id = 1000 and status = 'pending', it may use a different plan. Over time, many plans accumulate.

To reduce this, use PREPARE to explicitly define a plan:

PREPARE get_orders (int, text) AS
  SELECT * FROM orders
  WHERE customer_id = $1 AND status = $2
  ORDER BY created_at DESC
  LIMIT 100;

Now, the plan is shared across calls with different values.

Plan Stability and Adaptive Planning

Some databases support adaptive query plans. A query may start with a plan, then re-plan based on runtime statistics.

For example, if a Hash Join starts with a small hash table, but the input grows, the database may switch to a Merge Join. This is transparent to the application.

But this introduces overhead: plan changes can cause latency spikes. To monitor, query the plan history:

SELECT 
  queryid,
  plan_id,
  plan_hash,
  total_time,
  rows,
  plan_node
FROM pg_stat_statements
WHERE queryid = (SELECT queryid FROM pg_stat_statements WHERE query LIKE '%orders%');

A query with high total_time and low rows may be plan-inadequate. High plan_hash variation indicates plan instability.

Advanced Query Patterns

Window Functions with Partitioning and Frame Clauses

Window functions are powerful but expensive. Use PARTITION BY and ORDER BY to define the window context.

SELECT 
  user_id,
  created_at,
  amount,
  ROW_NUMBER() OVER (
    PARTITION BY user_id 
    ORDER BY created_at DESC
  ) AS rn,
  AVG(amount) OVER (
    PARTITION BY user_id
    ORDER BY created_at
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS rolling_avg
FROM transactions
WHERE created_at >= '2023-01-01';

This query assigns row numbers within each user’s transactions and computes a rolling average over the current and two prior transactions.

To optimize:

The ROWS BETWEEN 2 PRECEDING AND CURRENT ROW clause requires sorting and memory for each window.

Materialized Views and Incremental Updates

Materialized views store query results in a table. They are updated periodically.

CREATE MATERIALIZED VIEW user_stats AS
SELECT 
  u.id,
  u.name,
  COUNT(t.id) AS task_count,
  SUM(t.duration) AS total_duration
FROM users u
LEFT JOIN tasks t ON u.id = t.user_id
GROUP BY u.id, u.name;

Refresh the view with:

REFRESH MATERIALIZED VIEW user_stats;

For large views, use CONCURRENTLY to avoid locking:

REFRESH MATERIALIZED VIEW CONCURRENTLY user_stats;

This allows reads during refresh.

To maintain freshness, schedule REFRESH at regular intervals or use TRIGGER-based updates.

Query Hints and Planner Directives

Some databases support query hints—inline directives to influence the planner.

/*+ INDEX(tasks tasks_status_idx) */
SELECT * FROM tasks
WHERE status = 'in_progress'
  AND created_at >= '2023-10-01';

This hints the planner to use tasks_status_idx for the tasks scan.

Other hints include:

Hints are especially useful when the planner chooses a suboptimal plan despite good statistics.

Failure Modes and Sharp Edges

Plan Regression and Statistics Drift

When a query suddenly slows, check the plan. A plan that was optimal months ago may now be poor due to data growth or statistics drift.

Use pg_stat_statements and EXPLAIN to compare plans over time. A query with increasing total_time and decreasing rows is a sign of statistics drift.

To fix, run ANALYZE on the relevant tables:

ANALYZE users, tasks, transactions;

Missing Indexes and Incomplete Covering

A common failure: a query uses an index, but the index is not covering. The database reads the table for every row.

To detect:

The solution is to add INCLUDE columns or rebuild the index with additional fields.

Index Bloat and Page Splitting

After frequent updates, indexes become fragmented. Pages split, and the index becomes less efficient.

Use pgstattuple to measure bloat:

SELECT * FROM pgstattuple('tasks');

Output includes:

A bloat_size greater than tuple_len indicates significant bloat.

To reduce, run VACUUM FULL or REINDEX:

REINDEX INDEX tasks_status_idx;

Plan Cache Pollution

When a query is executed with many unique parameter combinations, the plan cache grows.

Use pg_stat_plans to monitor plan usage:

SELECT 
  queryid,
  plan_id,
  calls,
  total_time,
  min_time,
  max_time,
  stddev_time,
  plan_hash
FROM pg_stat_plans
ORDER BY calls DESC;

High calls and high stddev_time suggest that a plan is used frequently but with variable performance.

Query Execution Timeouts and Cancellation

Queries can time out. Use statement_timeout to control execution:

SET statement_timeout = 30000; -- 30 seconds

When a query exceeds the timeout, the database cancels it and returns ERROR: canceling statement due to statement timeout.

To monitor, check pg_stat_activity:

SELECT 
  pid,
  query,
  state,
  query_start,
  backend_start,
  wait_event_type,
  wait_event,
  total_time,
  query_time
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY total_time DESC;

A query with state = 'active', wait_event_type = 'Lock', and wait_event = 'relation' is waiting for a lock.

Summary

Query optimization is not a one-time task. It requires ongoing monitoring, plan analysis, index tuning, and statistics maintenance. The goal is a stable, predictable execution path across varying workloads. Use EXPLAIN, VACUUM, REFRESH, and PREPARE as foundational tools. Diagnose with pg_stat_statements, pg_stat_user_indexes, and pgstattuple. Address bloat, plan instability, and index incompleteness with targeted maintenance. And always validate that the query executes as intended—not just efficiently, but reliably under real conditions.

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.