中文
devexamples
← Workflows

阅读中文版本

Slow Query Sampling

Maintain a database safely: analyze index usage, review query plans, and apply changes with a verified rollback path.

intermediate~25 mindatabaseperformanceoperations

Objective

Improve query performance without locking the table or risking a rollback that loses data.

Environment

  • A working database environment with access to the target system and its tooling
  • Configuration and credentials for the database workflow installed and verified
  • A rollback or undo path documented before any change is applied

Steps

1. Analyze index usage

Query the index statistics and identify unused or duplicate indexes and hot tables missing a needed index.

Expected state: A report lists unused indexes to drop and missing indexes to add.

2. Review query plans

Run EXPLAIN ANALYZE on the top slow queries and confirm they use the expected index without seq scans on large tables.

Expected state: Slow queries use an index scan and their cost drops after the change.

3. Apply with rollback

Apply the index or migration in a single transaction where possible and keep the forward-rollback commands ready.

Expected state: The change applies without a long lock and the rollback command is tested.

Decision points

Should the index be created online?

  • Use CONCURRENTLY to avoid blocking writes
  • Use a maintenance window with a standard create

Prefer CONCURRENTLY for production tables; it takes longer but does not block writes.

Expected outcome

Improve query performance without locking the table or risking a rollback that loses data. The change is verified and a rollback path is confirmed.

Verification

Improve query performance without locking the table or risking a rollback that loses data. Compare before and after metrics and confirm the expected state is reached.

Cleanup

Archive the run artifacts, remove temporary credentials, and update the runbook with any changes.

Overview

Maintain a database safely: analyze index usage, review query plans, and apply changes with a verified rollback path. The goal is to improve query performance without locking the table or risking a rollback that loses data.

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = $1;

Steps

  1. Analyze index usage. Query the index statistics and identify unused or duplicate indexes and hot tables missing a needed index. Expected: A report lists unused indexes to drop and missing indexes to add.

  2. Review query plans. Run EXPLAIN ANALYZE on the top slow queries and confirm they use the expected index without seq scans on large tables. Expected: Slow queries use an index scan and their cost drops after the change.

  3. Apply with rollback. Apply the index or migration in a single transaction where possible and keep the forward-rollback commands ready. Expected: The change applies without a long lock and the rollback command is tested.

Verification

Confirm the outcome by checking: improve query performance without locking the table or risking a rollback that loses data. Run the verification command and compare the result against the expected state. If any check fails, stop and investigate before continuing.

Decision points

  • Should the index be created online?
    • Use CONCURRENTLY to avoid blocking writes
    • Use a maintenance window with a standard create
    • Recommendation: Prefer CONCURRENTLY for production tables; it takes longer but does not block writes.