# querylens querylens turns raw database query logs into actionable performance findings: which query shapes cost the most, where N+1 patterns hide, and which indexes would help. It reads logs you already have (PostgreSQL, MySQL, Django, pg_stat_statements), needs nothing installed on the database server, and has a single dependency (click). ## Features - Streaming parsers: PostgreSQL text logs (multi-line statements, `DETAIL: parameters:` substitution) and csvlog, MySQL slow log, Django `django.db.backends` DEBUG logs (request ids via configurable regex), pg_stat_statements CSV, plain SQL files with timings. Format auto-detection. - Own SQL tokenizer and fingerprinting (dollar quotes, comments, casts, IN lists, VALUES, arrays) with a stable hash. - Per-fingerprint calls, total/mean/p50/p95/p99/max (own DDSketch, 1% relative error), rows, first/last seen, slowest example, share of total time. - N+1 detection inside request/connection windows with estimated savings and a fix suggestion (IN-batching, `select_related` / `prefetch_related` for Django-shaped queries). - Index advisor: lightweight SELECT/UPDATE/DELETE parser, composite index proposals (equality, then range, then ORDER BY), dedupe against the schema, benefit by affected time, redundant-index detection. - Lints: `SELECT *`, leading-wildcard LIKE, functions on indexed columns, large OFFSET, missing LIMIT on big result sets, implicit casts, OR across columns, NOT IN subquery. - Workload diff: new/gone fingerprints, p95 regressions and improvements, call count changes. - Self-contained HTML report (sortable, filterable tables, inline SVG latency histograms, light and dark theme). - `--json` output on every analysis command. ## Install ```bash cd querylens uv venv .venv && uv pip install -p .venv/bin/python -e '.[dev]' source .venv/bin/activate ``` ## Quickstart ```bash python examples/generate_logs.py # optional: regenerate the sample logs querylens top examples/postgres.log querylens nplus1 examples/django.log querylens indexes examples/postgres.log --schema examples/schema.sql querylens lint examples/postgres.log --schema examples/schema.sql querylens diff examples/postgres.log examples/after/postgres.log querylens report examples/django.log --schema examples/schema.sql -o report.html ``` The examples simulate an e-commerce app with an N+1 on order items, a missing `shop_order(customer_id, created_at)` index, OFFSET pagination, and a few more anti-patterns. `examples/` holds the same workload as a PostgreSQL log, Django log and csvlog, plus a MySQL slow log, a pg_stat_statements export, an ad-hoc SQL file, `schema.sql`, and an `after/` log (a later deploy) for `diff`. ## Usage All commands take one or more log files (`-` for stdin, which needs `--format`). Common options: `--format [postgres|postgres-csv|mysql|django|pg_stat|sql]`, `--request-id-regex REGEX` (first group is the id; default `(?:req|request_id|trace_id|rid)[=:]\s*([\w-]+)`), `--json`. ### top `querylens top LOGS [--by total|p95|calls] [--limit N] [--width W]` ``` $ querylens top examples/postgres.log --limit 5 --width 130 2112 queries, 18 distinct shapes, 10,648 ms total ID CALLS TOTAL ms MEAN P50 P95 P99 MAX SHARE QUERY -------- ----- -------- ----- ----- ----- ----- ----- ----- ------------------------------------------------------------- 8a0db2d9 3 2,742 914 907 907 907 977 25.7% SELECT id FROM shop_customer WHERE id NOT IN (SELECT custo... e9442640 61 2,514 41.21 37.72 80.65 90.74 90.74 23.6% SELECT shop_order.id, shop_order.customer_id, shop_order.s... 3ffb958a 61 1,557 25.53 24.29 41.68 52.99 66.32 14.6% SELECT count(*) AS __count FROM shop_order WHERE shop_orde... 4fee8fd8 3 833 278 290 290 290 293 7.8% SELECT * FROM shop_order WHERE status = ? ddb33523 42 719 17.13 15.64 28.50 30.88 34.51 6.8% SELECT shop_product.id, shop_product.category_id, shop_pro... ``` ### nplus1 `querylens nplus1 LOGS [--window-ms 500] [--min-repeats 5] [--limit N]` ``` $ querylens nplus1 examples/django.log #1 5ba07463 926 queries in 61 bursts (largest burst 24), est. savings 34.65 ms repeated: SELECT shop_orderitem.id, shop_orderitem.order_id, shop_orderitem.product_id, shop_orderitem.quantity, shop_orderitem.unit_price FROM shop_orderitem W after: SELECT shop_order.id, shop_order.customer_id, shop_order.status, shop_order.total, shop_order.created_at FROM shop_order WHERE shop_order.customer_id fix: Django: prefetch_related('orderitem_set') on the parent queryset (or select_related if order_id is a forward FK); this collapses N queries into 1 IN-query. ``` ### indexes `querylens indexes LOGS --schema schema.sql [--max-columns 4] [--limit N]` ``` $ querylens indexes examples/postgres.log --schema examples/schema.sql #1 shop_order(customer_id, created_at) benefit 4,567 ms (42.9% of workload), 140 calls, 3 query shapes CREATE INDEX idx_shop_order_customer_id_created_at ON shop_order (customer_id, created_at); #2 shop_order(created_at) benefit 2,742 ms (25.7% of workload), 3 calls, 1 query shapes CREATE INDEX idx_shop_order_created_at ON shop_order (created_at); Redundant existing indexes: idx_customer_email (email) on shop_customer is covered by shop_customer_email_key -> DROP INDEX idx_customer_email; idx_product_category (category_id) on shop_product is covered by idx_product_category_created -> DROP INDEX idx_product_category; ``` ### lint `querylens lint LOGS [--schema schema.sql] [--offset-threshold 1000] [--scan-rows 1000]`. MISSING_LIMIT needs row counts (MySQL slow log, pg_stat_statements); IMPLICIT_CAST and the indexed-column check for FUNC_ON_COLUMN need `--schema`. ``` $ querylens lint examples/postgres.log --schema examples/schema.sql SEVERITY CODE ID TOTAL CALLS FINDING -------- -------------------- -------- ----- ----- ------------------------------------------------------------------------------ warning NOT_IN_SUBQUERY 8a0db2d9 2,742 3 id NOT IN (subquery) is slow and returns nothing if the subquery yields a NULL warning LEADING_WILDCARD ddb33523 719 42 LIKE '%...' on name cannot use a btree index warning OFFSET_PAGINATION c3b0aa1c 249 97 OFFSET up to 2232: the database reads and discards every skipped row warning FUNC_ON_COLUMN 7e9bdfda 198 40 lower(email) in WHERE prevents use of a plain index on email info SELECT_STAR 4fee8fd8 833 3 SELECT * fetches every column info OR_DIFFERENT_COLUMNS 375363a4 312 40 OR across different columns (email, phone) NOT_IN_SUBQUERY: Use NOT EXISTS (SELECT 1 ... WHERE ...) or an anti-join LEADING_WILDCARD: Use a trigram (pg_trgm GIN/GiST) index or full-text search, or anchor the pattern OFFSET_PAGINATION: Use keyset pagination: WHERE (sort_col, id) < (last_seen_value, last_seen_id) ORDER BY sort_col DESC, id DESC LIMIT n FUNC_ON_COLUMN: Compare the bare column, or create an expression index on lower(email) SELECT_STAR: List only the columns the caller uses; it enables index-only scans and cuts transfer size OR_DIFFERENT_COLUMNS: Rewrite as UNION ALL of single-column queries so each branch can use its own index ``` ### diff `querylens diff BEFORE AFTER [--p95-threshold 1.25] [--min-delta-ms 1] [--call-threshold 1.5]` ``` $ querylens diff examples/postgres.log examples/after/postgres.log --limit 4 before: 2112 queries, 10,648 ms after: 4154 queries, 7,621 ms New query shapes ID CALLS TOTAL P95 QUERY -------- ----- ----- ---- ---------------------------------------------------------------------------- 063edd91 2088 680 0.61 SELECT count(*) AS __count FROM shop_review WHERE shop_review.product_id = ? Gone query shapes none p95 regressions ID P95 BEFORE P95 AFTER RATIO QUERY -------- ---------- --------- ----- -------------------------------------------------------------------------------------------------------------- c3b0aa1c 5.00 19.11 3.8x SELECT shop_product.id, shop_product.category_id, shop_product.sku, shop_product.name, shop_product.price, ... 78ba22f9 1.42 2.44 1.7x INSERT INTO shop_orderitem(product_id, quantity, unit_price) VALUES (...) p95 improvements ID P95 BEFORE P95 AFTER RATIO QUERY -------- ---------- --------- ----- -------------------------------------------------------------------------------------------------------------- e9442640 80.65 1.34 0.02x SELECT shop_order.id, shop_order.customer_id, shop_order.status, shop_order.total, shop_order.created_at FR... c5a8bf3b 50.91 1.34 0.03x SELECT shop_order.id, shop_order.customer_id, shop_order.status, shop_order.total, shop_order.created_at FR... 3ffb958a 41.68 1.03 0.02x SELECT count(*) AS __count FROM shop_order WHERE shop_order.customer_id = ? 375363a4 15.64 9.12 0.58x SELECT shop_customer.id, shop_customer.email, shop_customer.name, shop_customer.phone, shop_customer.create... Call count changes ID CALLS BEFORE CALLS AFTER RATIO QUERY -------- ------------ ----------- ----- ---------------------------------------------------------------------------------------------------------- ddb33523 42 83 2.0x SELECT shop_product.id, shop_product.category_id, shop_product.sku, shop_product.name, shop_product.pri... ``` ### report `querylens report LOGS -o report.html [--schema schema.sql] [--limit 50]` writes one HTML file with no external requests: summary tiles, top queries (click a header to sort, type to filter, expand to see the slowest example), N+1 findings, index advice and lints. ``` $ querylens report examples/django.log --schema examples/schema.sql -o report.html wrote report.html (40 KiB, 16 query shapes) ``` ## Configuration There are no config files. Behaviour is controlled by the flags above. Input format is detected from the first 64 KiB; override with `--format`. For logs where the request id has another shape, pass `--request-id-regex 'trace=(\w+)'`. PostgreSQL text logs need a prefix that starts with the timestamp and includes `[pid]` (for example `log_line_prefix = '%m [%p] %q%u@%d '`) for connection grouping; `log_min_duration_statement = 0` logs every statement. ## Architecture | Module | Role | |---|---| | `tokenizer.py` | SQL scanner, statement splitting, parameter substitution | | `fingerprint.py` | normalisation, literal extraction, stable hash | | `sketch.py` | DDSketch quantiles and histograms | | `parsers.py` | streaming parsers and format detection | | `aggregate.py` | `Stats`, `Workload`, `analyze()` pipeline | | `nplus1.py` | streaming run detector, parent selection, fix suggestions | | `sqlshape.py` | lightweight statement parser (tables, predicates, ordering, limits) | | `schema.py` | DDL parser (tables, columns, indexes) | | `advisor.py` | index candidates, dedupe, redundant indexes | | `lints.py` | anti-pattern checks | | `diff.py` | workload comparison | | `report.py` | HTML report | | `cli.py` | click commands and text tables | Design notes: [docs/fingerprinting.md](docs/fingerprinting.md) and [docs/index-advisor.md](docs/index-advisor.md). ## Tests ```bash .venv/bin/python -m pytest -q ``` 44 tests cover tokenizer and fingerprint edge cases, every parser, sketch accuracy against numpy, N+1 windows, predicate extraction, index ordering and dedupe, lints, diff and CLI end to end on the example logs. ## Limitations - The SQL parser is deliberately partial: it handles common SELECT/UPDATE/DELETE shapes, not the full grammar. Unrecognised constructs are skipped, never guessed. - Index benefit is an upper bound (total time of affected statements), without table statistics. - pg_stat_statements has only counters, so its percentiles equal the mean (max is exact), and it cannot feed N+1 detection. - PostgreSQL text logs do not carry row counts; MISSING_LIMIT works on MySQL and pg_stat_statements input. - Fingerprints keep identifier case-insensitive; quoted mixed-case names are retained as written. - N+1 detection needs a request id, a connection id, or timestamps.