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

cd querylens
uv venv .venv && uv pip install -p .venv/bin/python -e '.[dev]'
source .venv/bin/activate

Quickstart

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

Design notes: docs/fingerprinting.md and docs/index-advisor.md.

Tests

.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.