Slow Query Log Analyzer (QLA)

Overview

The Slow Query Log Analyzer (MySQL/MariaDB) and Query Log Optimizer (PostgreSQL) share one panel. They:

• Load workload from performance digest (events_statements_summary_by_digest / pg_stat_statements), server slow-log tables, auto-detected log files, or drag-and-drop uploads.
• Normalize statements (literals → placeholders) into fingerprints and score them by response time, call count, rows examined, and usage share.
• Prioritize with impact rank, bubble timeline, and usage breakdowns by table, database, and user.
• Tune in bulk — Optimize All Queries fills index recommendations; Create Indexes + Validate applies DDL and compares EXPLAIN before/after.
• Drill down — per-row EXPLAIN, single-query Optimize, copy SQL, or export index scripts.

How to open

• Database tree — right-click a connection or database → ⚡ Query Log Optimizer (PostgreSQL) or the connection’s slow-query command (MySQL/MariaDB).
• Command Palette — search for slow query / query log optimizer commands tied to your active connection.
• Performance Monitor — open the Query Log Optimizer from the debugger workbench when monitoring a session.

The panel opens scoped to the tree node you picked. Confirm the sidebar Active database matches the workload you intend to tune.

Home screen — choose a data source

Before any grid appears, the home screen offers one or more load paths. Filters on each card control time window, row limit, and sort order.

MySQL / MariaDB sources

PostgreSQL sources

Toolbar after load

Once a report is loaded, the top toolbar switches from load buttons to filtering plus reload shortcuts:

Summary stat cards

Six tiles above the tabs summarize the loaded report:

Main tabs

Click any column header in a grid to sort ascending/descending. On Query Fingerprints, the # rank column is fixed by impact priority (not affected by sort order).

Query Fingerprints — columns

Row action buttons

Rows that cannot be optimized (e.g. missing sample SQL, unsupported statement type) show — with a tooltip explaining why. Optimizable types: SELECT, UPDATE, DELETE, WITH.

Action bar (batch operations)

Right of the main tabs:

Bubble timeline (Query Fingerprints tab)

Above the fingerprint table, a scatter timeline plots slow executions over time:

• Bubble size reflects query duration; color matches impact rank (top 10 highlighted).
• Hover for timestamp, duration, fingerprint snippet, grade, and quality tag.
• Click a bubble to scroll and highlight the matching grid row.
• Toggle/zoom controls collapse or expand the chart when space is tight.

Operation progress banner

During Optimize All, Create Indexes, or validation, a status banner appears below the action bar:

• Status text and badge (Running, OK, Warning, Blocked).
• Progress bar with percentage and optional ETA chip.
• Cancel — stops the batch and kills the active server query when possible.
• Detail chips — thread id, DB state, elapsed time during long operations.

Create Indexes overlay

Full-screen flow opened from the action bar. Two phases: selection then results (after Create Indexes or Create Indexes + Validate).

Selection screen controls

Results screen — visual summary

After validation completes, charts summarize aggregate benefit (as shown in the demo GIF):

Results screen — drop actions

Optimizer Settings modal

Gear icon in the action bar. Two tabs — settings persist in your VS Code profile.

Auto-detect & enable slow logging

QLA probes the server for log configuration:

• MySQL/MariaDB — reads slow_query_log, slow_query_log_file, and whether mysql.slow_log is writable. Offers enable SQL when logging is off.
• PostgreSQL — checks logging_collector, log_min_duration_statement, and pg_stat_statements availability.

Recommended tuning workflow

1. Load the smallest window that captures the problem (digest is usually enough for MySQL; pg_stat_statements for PostgreSQL).
2. Filter by database, table, or query text; on PostgreSQL use Tune DB to focus one schema.
3. Review Query Fingerprints sorted by impact — inspect bubble chart spikes.
4. Optimize All Queries (or top N via settings) to populate index recommendations.
5. Export Index DDL for change review, or Create Indexes + Validate to measure EXPLAIN improvement on the server.
6. For hard cases, use row ⚡ Optimize to open the full Query Optimizer (rewrites, visual explain, trace).
7. Truncate or rotate logs after a tuning pass so the next analysis is not skewed by fixed queries.

Privileges & troubleshooting

• Integrated load needs SHOW GLOBAL VARIABLES, digest-table SELECT, and schema privileges for EXPLAIN on your queries.
• Upload path works offline — only file read access on your machine.
• If mysql.slow_log or digest buttons stay disabled, check the tooltip on the button or the home error banner for the server reason.
• PostgreSQL Not installed badge → run CREATE EXTENSION pg_stat_statements in the connect-through database and ensure the library is preloaded.
• Empty Recommended Indexes after Optimize All → statement may not be optimizable, or no index would help (check row tooltip).

Tips

• Prefer Query Digest / pg_stat_statements for ongoing triage — faster than parsing multi-gigabyte log files.
• Use Top 5 or Top 10 Optimize All scope first on large workloads; widen after reviewing results.
• Group by fingerprint surfaces ORM/query-builder patterns that raw log lines hide.
• Pair batch index creation with the Query Optimizer Create Index flow when you need single-query rewrite guidance alongside DDL.
• Related: Performance Monitor for live server metrics; Query Profiler for session-level timing.