Query Performance Audit and Safe Indexing Script

Role: Staff Performance DBA specializing in low-overhead query optimization and non-blocking database tuning.

Context

• Database system: {{rdbms_type}}
• Query latency cutoff: {{slow_query_threshold_ms}} ms
• Operating environment: {{workload_environment}}
• Source telemetry feed: {{audit_log_format}}
• Maximum lock wait threshold: {{lock_timeout_seconds}} seconds
• Orchestration medium: {{output_script_language}}

Task

Construct an end-to-end audit and safe index generation script that extracts slow query metrics, identifies missing indexes, and builds non-blocking indexing DDL.

Method

1. Connect to telemetry outputs formatted as {{audit_log_format}} within {{rdbms_type}}.
2. Filter query signatures exceeding the latency cutoff of {{slow_query_threshold_ms}} ms.
3. Aggregate queries by query fingerprint and extract sequential scan access paths.
4. Map scanned filter predicates and join keys to target table definitions.
5. Generate non-blocking index creation commands tailored for {{workload_environment}}.
6. Inject session-level lock timeouts using {{lock_timeout_seconds}} before each DDL statement.
7. Add pre-execution index existence checks to make index creation strictly idempotent.
8. Include health verification queries measuring index usage and storage footprint.

Constraints

• Index creation MUST use online/concurrent building modes to prevent blocking live writes.
• The script MUST NOT create duplicate or redundant composite indexes with identical leading columns.
• All DDL statements must set lock timeout limits matching {{lock_timeout_seconds}}.
• Syntax and command parameters must be fully valid for {{output_script_language}} and {{rdbms_type}}.

Output format

Return the deliverable organized into the following exact sections:

1. Audit & Extraction Script: Formatted code block in {{output_script_language}} to parse logs.
2. Idempotent Migration DDL: SQL code block containing concurrent index creation statements.
3. Operational Rollback Block: SQL statements to drop newly added indexes cleanly if needed.

Self-review

• Confirm that online indexing clauses (e.g., CONCURRENTLY / ONLINE = ON) are present.
• Validate that lock wait limits enforce {{lock_timeout_seconds}} prior to every DDL attempt.
• Verify query filtering matches {{slow_query_threshold_ms}} ms without dropping top offenders.