阅读中文版本

Database Query Tuning Workflow

Find the slowest queries, read their plans, and tune indexes or statements with measured before-and-after evidence.

Overview

Reduce database latency by targeting the highest-impact queries with measured improvements.

This workflow is reusable across teams and environments; adapt the commands to your tooling while keeping the sequence and checkpoints intact.

Environment and prerequisites

• Slow query logs or statistics
• A query plan tool
• A benchmark or load test

Procedure

1. Rank queries by total time

Use query statistics to rank by total time, not just per-call duration.

Expected state: The highest-impact queries are identified.

2. Read the execution plan

Examine the plan to find full scans, bad estimates, or missing indexes.

Expected state: The cause of slowness is understood.

3. Apply the targeted fix

Add an index, rewrite the query, or adjust the schema for the specific problem.

Expected state: The plan improves for the targeted query.

4. Measure the impact

Compare latency and load before and after under similar conditions.

Expected state: The improvement is evidenced, not assumed.

-- rank by total time
SELECT query, total_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

Verification

Confirm the targeted queries drop in latency under load and the plans show the intended change.

Decision points

Optimise by per-call or total time?

• Rank by total time
• Rank by per-call time

Recommendation: Rank by total time; frequent moderate queries often outweigh rare slow ones.

Add index or rewrite?

• Whichever the plan supports
• Add index always

Recommendation: Follow the plan: sometimes a rewrite beats another index.

Common failures

• Optimising rare slow queries ignores bigger wins.
• Adding indexes blindly worsens writes.
• No before-and-after leaves improvement unproven.