QueryLens

QueryLens reads PostgreSQL and MySQL slow query logs offline and tells you which query shapes cost the most database time, with transparent, heuristic hints about common anti-patterns.

Why it exists

A slow query log holds thousands of near-identical entries that differ only in their literal values. What you want to know is which shapes of query consume the most time and which have obvious problems. QueryLens answers that from a plain log file: no database connection, no EXPLAIN, no paid APM, and no runtime dependencies beyond the Python standard library. Its reports contain normalized queries only, so you can paste them into a pull request or a chat without leaking the data that was in your logs.

Features

• Parses PostgreSQL (log_min_duration_statement, stderr format, any log_line_prefix) and MySQL / MariaDB slow query logs, including multi-line statements, and auto-detects which one you gave it.
• Groups equivalent queries with a tokenizer-based fingerprint: literals, placeholders and comments are removed, and IN (...) (including row-value lists), multi-row VALUES and PostgreSQL ARRAY[...] literal lists collapse.
• Reports count, total, mean, p50, p95 and max time, share of total time, and (MySQL) rows examined per row sent.
• Eight heuristic hints (QL001 to QL008) that state their own limitations.
• Text, JSON (stable, versioned schema) and Markdown reports.
• CI gates: --fail-on-hint and --fail-over-ms with documented exit codes.
• Reads files, .gz files (detected by content) and stdin; streams line by line.
• Malformed entries are counted and skipped, never fatal.
• Zero runtime dependencies; Python 3.10 to 3.14.

Live demo

Try it in your browser: query-lens.netlify.app. The web playground runs this exact Python package in your browser through Pyodide, so your log is analyzed locally and never uploaded. It offers one-click sample logs, file upload or paste, search, sorting and hint filtering, per-pattern details, and the CLI's own text, Markdown and JSON reports. Demo limits: logs up to 5 MB, plain text only (no .gz or stdin), up to 500 patterns shown, and an analysis is stopped after 60 seconds. The CLI remains the primary product.

Installation

From PyPI (after the first upload — see RELEASING.md):

pipx install slowlens    # or: pip install slowlens
slowlens --version

QueryLens is the product name. On PyPI, the import package and CLI are slowlens (pip install slowlens → run slowlens). The name querylens is already taken by an unrelated project. The GitHub repository remains query-lens.

From a clone (development or before the package is on PyPI):

git clone https://github.com/yagnang-vyas/query-lens.git
cd query-lens
pipx install .          # or: python -m venv .venv && . .venv/bin/activate && pip install .
slowlens --version

Maintainer steps for GitHub Releases and PyPI Trusted Publishing are in RELEASING.md. The live demo stays on Netlify; see web/README.md.

Enabling slow query logs

PostgreSQL (postgresql.conf; reload the server afterwards). Log any statement slower than 200 ms to the standard error log:

log_min_duration_statement = 200   # milliseconds; 0 logs everything, -1 disables
logging_collector = on             # if you want the output in a file

QueryLens finds the LOG: duration: ... ms statement: ... message wherever it sits on the line, so the default log_line_prefix and custom ones both work. Use the stderr log format (not csvlog or jsonlog).

MySQL / MariaDB (my.cnf, or SET GLOBAL at runtime):

slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 0.5              # seconds

Usage

# Top 10 shapes by total time; the format is auto-detected
slowlens examples/postgres-sample.log

# Top 5 by 95th percentile, as Markdown for a pull request comment
slowlens --top 5 --sort p95 --output markdown examples/mysql-slow-sample.log

# Machine-readable output, ignoring anything faster than 50 ms
slowlens --output json --min-duration 50 examples/postgres-sample.log

# Several files, compressed files and stdin
slowlens postgresql-2026-10-24.log postgresql-2026-10-25.log.gz
zcat mysql-slow.log.gz | slowlens --format mysql -

Using it as a CI gate

# Fail the build if any query shape has SELECT * (QL001) or a leading wildcard LIKE (QL003),
# or if any shape has a p95 above 750 ms.
slowlens --fail-on-hint QL001,QL003 --fail-over-ms 750 slow.log

The report is printed as usual, followed by a one-line reason on stderr, and the exit code is 1. Fail conditions are evaluated on all fingerprints, not only the --top subset.

Sample output

Produced by running slowlens --top 5 examples/postgres-sample.log on the synthetic sample log in this repository:

QueryLens 0.1.0 | format: postgres | files: examples/postgres-sample.log
entries parsed: 73 | malformed: 1 | duration-only: 1 | skipped: 34 | filtered: 0
total time: 19513.555 ms | unique fingerprints: 10 | showing top 5 by total

#  ID            Count  Total ms   Mean ms    P95 ms    Max ms  Share %  Hints
1  e7949fc430c3      6  6872.643  1145.440  1449.955  1449.955    35.22  QL004
    select id, title from articles where published = true order by id limit ? offset ?
2  d236be3dd0c4      8  4148.673   518.584   838.875   838.875    21.26  QL003
    select id, name, price from products where name ilike ? and active = true
3  4beddc297df7      2  3103.438  1551.719  1768.626  1768.626    15.90  QL002
    update sessions set expires_at = now(), revoked = true
4  002b0a2998f8      9  1761.588   195.732   265.755   265.755     9.03  QL006
    select id, email from users where lower(email) = ?
5  25060abf2d0e     14  1713.947   122.425   217.071   217.071     8.78  QL001
    select * from orders where customer_id = ? order by created_at desc

Hints (heuristics, may be wrong; see README):
  QL001 SELECT *: Heuristic: the select list contains * or table.*, which reads every column and
      breaks covering indexes. It cannot know whether you really need all columns.
  QL002 Unfiltered write: Heuristic: an UPDATE or DELETE has no top-level WHERE clause and touches
      every row. It is sometimes intentional (maintenance jobs) and statements starting with WITH
      are not inspected.
  QL003 Leading-wildcard LIKE: Heuristic: LIKE/ILIKE with a literal pattern starting with %, which
      usually prevents B-tree index use. Patterns built at runtime (CONCAT, ||, parameters) are not
      detected.
  QL004 Large OFFSET: Heuristic: a literal OFFSET (or MySQL LIMIT offset, count) above 1000; the
      database must read and discard all skipped rows. Offsets passed as parameters are not
      detected.
  QL006 Function on filtered column: Heuristic: a column is wrapped in LOWER, UPPER, DATE, CAST,
      COALESCE or EXTRACT inside a WHERE/AND/OR comparison, which can prevent plain index use. An
      expression index may make it fine.

Hint reference

Hints are heuristics on query text (and, for QL007, on log counters). They know nothing about your schema, indexes, table sizes or query plans, so treat them as prompts to investigate, not verdicts.

A fingerprint carries a hint if any of its entries triggered it.

JSON output schema

--output json prints one JSON document (schema_version 1). Millisecond values are rounded to 3 decimals and percentages to 2 at output time; key order is fixed, so output is deterministic.

{
  "schema_version": 1,
  "tool": {"name": "slowlens", "version": "0.1.0"},
  "input": {
    "format": "postgres",
    "files": ["examples/postgres-sample.log"],
    "entries": 0, "malformed": 0, "duration_only": 0,
    "skipped_phase": 0, "filtered_out": 0
  },
  "summary": {"total_ms": 0.0, "unique_fingerprints": 0, "sort": "total", "top": 10},
  "fingerprints": [
    {
      "id": "a1b2c3d4e5f6", "query": "select ...",
      "count": 0, "total_ms": 0.0, "mean_ms": 0.0, "p50_ms": 0.0,
      "p95_ms": 0.0, "max_ms": 0.0, "share_pct": 0.0,
      "rows_sent_total": null, "rows_examined_total": null,
      "examined_per_sent": null, "hints": ["QL001"]
    }
  ],
  "hints": {"QL001": {"title": "SELECT *", "description": "..."}}
}

• input.entries is the number of entries parsed; filtered_out of them were dropped by --min-duration before aggregation. skipped_phase counts PostgreSQL parse/bind durations, auto_explain plan lines and MySQL administrator commands.
• fingerprints holds the --top subset in ranked order; summary.unique_fingerprints and summary.total_ms cover all of them.
• rows_* and examined_per_sent are null for PostgreSQL and when a ratio is undefined. examined_per_sent is rounded to 2 decimals.
• hints is the legend for the hint IDs that appear in fingerprints.

Exit codes

Errors are printed as slowlens: error: <message> with no traceback. Messages name files and counts, never log content.

How it works

1. Input. Files are opened lazily and read line by line. gzip is detected from the first two bytes, text is decoded as UTF-8 with replacement characters for invalid bytes, and CRLF is normalized.
2. Parsing. A small state machine per engine builds entries from multi-line statements. PostgreSQL parse and bind durations are skipped so a prepared statement is not counted three times.
3. Fingerprinting. A single-pass tokenizer removes comments, replaces strings (including '', backslash escapes and $tag$ quoting), numbers, hex literals and placeholders ($1, ?, :name) with ?, lowercases keywords and identifiers, collapses whitespace and trailing semicolons, and turns IN (?, ?, ...) and multi-row VALUES into (?+). The fingerprint ID is the first 12 hex characters of the SHA-1 of the normalized text. Normalizing twice gives the same result.
4. Statistics. Durations are grouped per fingerprint. Percentiles use the nearest-rank method: for n samples sorted ascending, the p-th percentile is the value at rank ceil(p / 100 * n) (1-based). For 20 samples, p95 is the 19th value. Share is a fingerprint's total time over the total time of all kept entries. Rankings sort descending by the chosen key and break ties by fingerprint ID.
5. Hints. Rules run on the token stream of each raw query and keep only a boolean; the report never sees raw SQL.

Privacy

Logs may contain personal data and secrets. QueryLens never prints raw query text, literal values, or PostgreSQL DETAIL: parameters: lines (those lines are dropped by the parser). Reports show normalized fingerprints with every literal replaced by ?. Error messages never echo log content. A test checks a fixture with fake e-mail addresses, API-key-like strings and parameter lines against all three report formats.

Normalization is not anonymization: table, column and other identifiers are kept, and so is anything the tokenizer cannot recognise as a literal. Review a report before sharing it outside your team.

Limitations

• Normalization is tokenizer based, not a SQL parser; exotic syntax can group imperfectly.
• Hints are heuristics (see the table above). They can be wrong in both directions.
• PostgreSQL support covers the stderr format only: no csvlog, jsonlog or pg_stat_statements.
• Statements are timed by their duration: message; log_statement lines without a duration are not entries.
• All durations of a fingerprint are held in memory to compute exact percentiles, so memory grows with the number of entries, not with file size.
• Auto-detection looks at the first 200 lines of the first input only; use --format for odd files. Mixed-format inputs are not supported.
• No database access, EXPLAIN output or index suggestions.

Prior art

QueryLens stands on the shoulders of pt-query-digest from Percona Toolkit (the reference for MySQL slow log digests) and pgBadger (a full-featured PostgreSQL log analyzer). If you need histograms, per-user breakdowns or HTML dashboards, use them. QueryLens differs by having zero dependencies, privacy-safe output by default, a stable JSON schema for automation, CI exit codes, and both engines in one small tool.

Development

python -m venv .venv
. .venv/bin/activate
make install   # editable install with ruff, mypy, pytest, pytest-cov
make check     # ruff, ruff format --check, mypy --strict, pytest with >= 90% branch coverage
make format    # apply ruff formatting and safe fixes
make dist      # build sdist/wheel and run twine check (does not upload)

JSON golden files for the sample logs live in tests/golden/. After an intentional output change, regenerate them and review the diff:

UPDATE_GOLDEN=1 pytest tests/integration

Test fixtures are synthetic; see tests/fixtures/README.md. The design and acceptance criteria are in SPEC.md, contribution ground rules in AGENTS.md.

Roadmap

Ideas for later versions, none of them promised: PostgreSQL csvlog / jsonlog input, pg_stat_statements snapshots, a baseline comparison mode for CI (fail on regressions rather than absolute thresholds), and configurable hint thresholds.

License

MIT © 2026 Yagnang Vyas