# Understand query cost

Separate estimated work, measured database execution and connection timing.

Use **Dashboards → Estimate query cost** before opening a dashboard, or **Query cost**
inside a dashboard with its current parameters. Opening or recalculating an estimate
never executes the application query. Recalculation preserves the report, selected
request, expanded details, and calculator inputs.

The summary separates two kinds of evidence:

1. **Before you run:** PostgreSQL, MySQL and MariaDB plans estimate work for the selected
   parameters and limits. Planner units are never converted to milliseconds.
2. **Measured database work:** a shared summary uses mean database execution time,
   execution count, observation period and scope. Ordinary runs improve the evidence;
   BytesMap never runs an extra copy of the application query just to measure it.

Recent measurements are preferred over retained server history. The Light / Moderate /
Heavy rating uses the same fixed time and read bands across engines. Dashboard time is the
sum of each distinct statement's mean, not an average of unrelated queries or dashboard
wall-clock time. Partial measurements and failed requests stay explicit.

For multiple database requests, selection changes the plan and server history together.
The chart/query/request counts still describe the whole dashboard. Technical plans, buffer
activity, connection diagnostics, and the refresh calculator are initially collapsed.

A plan's cost units are relative work estimates, **not milliseconds or a percentage of
production capacity**. They are not converted into a runtime or slowdown percentage.
The server's historical average is also not a guarantee about the next run: parameters,
plans, cache state, contention, and concurrent traffic can differ. CPU utilization,
physical I/O, and slowdown of other production traffic require separate resource and
workload measurements. No percentage is inferred from the number of charts or requests.

## PostgreSQL plan and server evidence

Plans use `EXPLAIN (FORMAT JSON, ANALYZE FALSE, COSTS TRUE, VERBOSE TRUE)`. The wrapper,
bound parameters, limits, and truncation sentinel match normal execution. A bound catalog
lookup adds relation cardinalities and sizes without counting application rows.

On PostgreSQL 14+, the plan's query identifier can match retained `pg_stat_statements`
statistics. This requires the extension loaded on the server and installed in the source
database, query identifiers enabled (`compute_query_id = auto` or `on`), and statement
tracking enabled. BytesMap does not install extensions, restart servers, reset statistics,
or change production configuration. Missing telemetry remains explicitly unavailable;
a missing retained entry says **No recorded database runs** rather than zero cost.

The adapter:

- Preserves EXPLAIN's signed 64-bit query identifier without JSON number rounding. Its
  query-local JSON parser does not change normal application JSON results.
- Reads `pg_stat_statements(false)` for that identifier, current database, current role,
  and top-level statements. Query text is not retrieved. Values are bound, and the
  extension's schema comes from the catalog and is quoted as an identifier.
- Uses `mean_exec_time` and the server's retained execution counters. These are PostgreSQL
  executor timings, not planning time or BytesMap's round trip. They include server waits,
  potentially including waits while delivering results to the client.
- Labels the scope as **same query shape**. PostgreSQL normalizes constants, so parameter
  values and row-limit values can differ. Other clients using the same database role may
  contribute. This is not exact-parameter or BytesMap-only execution history.
- Reads existing evidence only during explicit estimation, with no extra instrumentation
  requests on Run/Refresh. Server collection happens within PostgreSQL.
- Uses a savepoint so missing permissions, disabled extensions, version incompatibilities,
  or an optional statistics timeout do not discard an otherwise valid plan. Cancellation
  still aborts the operation.

Newer PostgreSQL versions expose per-statement statistics epochs. A min/max-only reset
can leave a zero range or a range from a different period than the average; those ranges
are hidden. Older versions show an unknown statistics start time. On servers exposing per-statement
`stats_since`, BytesMap saves up to 30 distinct counter snapshots during explicit cost
inspection, using only those from the last 7 days. The recent mean is
`(total_exec_time_end - total_exec_time_start) / (calls_end - calls_start)`, never an average
of cumulative means. The UI shows the actual observation interval, which can be shorter
than seven days. Same-counter inspections do not consume extra snapshot slots or add calls.

Only a contiguous, monotonic, same-epoch interval is used. Resets, stale snapshots and
missing epochs fall back to **Retained server history**. Cumulative min/max and percentiles
cannot be differenced: the recent interval omits them. Older servers retain their honest
historical average rather than receiving an invented rolling measurement. The timestamp
says **Checked**, not last executed, because PostgreSQL does not expose the latter here.

Details show mean shared-buffer reads, temporary data writes, and cache hit ratio. A
PostgreSQL buffer miss can still hit the operating system cache, so these are **not
physical disk reads**. The cache percentage has that specific denominator and never
represents CPU, disk utilization, or production impact.

## MySQL and MariaDB measurements

Normal execution reads the completed statement from Performance Schema using a unique marker
on the same pinned connection. The observation includes source execution time, rows examined,
rows sent, locks and temporary tables. Missing instrumentation remains unavailable.

The shared summary averages successful source measurements from the latest 30 request attempts
within seven days. It includes mean examined rows, measured min/max, count and last measured
execution time. At least 20 source measurements are required to show the empirical nearest-rank
95th percentile; smaller samples show the observed range and an early-measurement hint.
Failures, cancellations, cache hits and missing instrumentation never become zero-time runs.
Exact inputs and execution limits remain separate; changed inputs start new history.

## Request diagnostics

The previous client timer remains under **Details → Connection timings**. It is never
used as PostgreSQL execution time or as a fallback when server statistics are absent.
It includes driver queue, the database request, network, and result transfer, and excludes
connection establishment, local flow transformations, and rendering. The client and server
histories have different scopes; their averages are not subtracted to invent network time.

The shared execution path observes main statements, grand totals, and explicit counts.
Only actual adapter requests contribute: cache hits, schema probes, and estimates do not.
Failures, timeouts, and cancellations keep their outcomes and are not counted as successful
timings. Diagnostics show the median of successful matching requests and any failed attempts.

History matches the compiled SQL and typed bound values, source and organization,
connection settings, row/byte ceilings, and timeout. Changed parameters or limits start
separate history. Opaque Mongo prerequisites cannot be matched by an incomplete serialization.
The display uses up to 30 attempts from the last 7 days; stale or unreadable history is labeled.

`query_runtime` in BytesMap's internal metadata database stores only a SHA-256 fingerprint,
source/organization IDs, timestamps, outcomes, counts, durations and optional server measurements. It stores no SQL, parameters,
credentials, or results. The additive startup migration supports PostgreSQL metadata and
embedded PGlite. Writes happen off the response path with atomic bounded append; immediately
following estimates await matching pending writes. Storage failures never fail chart execution.
Each source retains at most 256 signatures and recording prunes signatures older than 30 days.
Deletion cascades and reads/writes are organization-scoped.

`query_execution_history` stores PostgreSQL counter snapshots separately, keyed by a hashed
source/connection identity and the lossless normalized query ID. This scope deliberately spans
parameter values. It has the same 256-signature / 30-day retention limits and cascading tenant
isolation. Counter updates are atomic and idempotent for unchanged source statistics. Metadata
storage errors preserve the current plan and retained source evidence. Startup migration is
additive for both PostgreSQL metadata and embedded PGlite.

The report timestamp is taken at completion, independently of the fixed time used to resolve
query parameters. Newly collected server evidence therefore cannot appear to come from the
future relative to the report.

## Scope and limits

Sequential scan estimates include rows filtered out. A filtered count can examine a million
rows to return one. Simple unfiltered limits can stop early; blocking sorts and aggregates
consume their input. Filtered limits and offsets retain an upper scan scope. Unknown loops,
bitmap rechecks, and index residual filters remain unknown. Catalog sizes are table
footprints, not bytes read; estimates depend on the freshness of PostgreSQL statistics.

One report describes the first page of one refresh:

- Charts sharing a saved query reuse one execution. Eight charts backed by one query have
  the same database plan as that query backing one chart.
- Grand totals and explicit counts include their additional statements.
- Flows include distinct native/table reads and actual column selections and limits.
  Local transformations and chart rendering are outside the database estimate.
- Live resource metrics use separate catalog reads and are explicitly excluded.
- Failed or unsupported plans remain unavailable; partial totals only include known work.

The calculator models fresh executions without changing dashboard settings:

`statements/hour = statements/refresh × 3,600 ÷ interval_seconds × open_sessions`

Actual cache reuse and user activity can reduce the workload. Concurrent request durations
are not added into dashboard loading time. Selection, expansion, and calculator edits make
no additional API or database requests.

## API and shared ownership

`POST /api/dashboards/:id/cost` accepts `{ params?: {...} }`.
`POST /api/queries/:id/cost` accepts saved-query run options except `explain`.
Flow conditions and limits stay in nodes as on the run path. MCP's `dashboard_cost` and
`query_cost` use the same endpoints.

All queries and sources are authorized before planning. Flows require SQL-run permission
on each source. Planning uses a read-only transaction, at most three seconds per statement,
and at most one second of lock wait; a tighter source timeout wins. The HTTP operation has
a 20-second budget and cancellation support.

Evidence types and buffer calculations live in `@bytesmap/core`, PostgreSQL collection in
`@bytesmap/adapters`, and rendering in `@bytesmap/ui/patterns/query-cost` for both the app
and SDK hosts. The API owns authorization and client diagnostic persistence. The existing
run option `explain: true` returns compiled SQL with executed results and is unrelated to
this non-executing cost endpoint.

## Validation

Unit checks cover lossless identifiers, role/database scoping, optional telemetry failures,
resets, buffer ratios, limits, and the absence of extra executions. A disposable PostgreSQL
fixture with `pg_stat_statements` verifies that EXPLAIN matches real server execution
counters, parameter changes share the documented normalized scope, and recalculation never
increments execution counts. Set `BYTESMAP_QUERY_EXECUTION_TEST_URL` to that isolated fixture
for `packages/adapters/src/postgres-execution.test.ts`.

Dashboard browser tests cover light/dark/dim, narrow screens, keyboard navigation, loading,
refresh and error retention, local request selection, absent server history, and keeping
client timings separate from server execution evidence.

## Local demo

```sh
bun scripts/seed-query-cost-demo.ts
bun scripts/register-query-cost-demo.ts
```

The seed starts the dedicated `bytesmap-query-cost-demo` container on `127.0.0.1:54384`.
It adds 50,000 customers, 500,000 orders, and 1,000,000 events to `bytesmap_cost_demo`,
schema `cost_demo`, creates lookup indexes, and runs `ANALYZE`. Repeat runs add missing
fixture rows without deleting data or touching other Postgres containers.

Registration needs the normal local API on port 4077 and a configured local workspace.
It adds a read-only connection and **Production query cost demo** with six charts:
indexed lookup, event scan, join, shared query, grand totals, and bounded visual flow.
Existing demo definitions are preserved on repeat runs.

Local-only reader credentials: `bytesmap_cost_reader` / `local_reader_only`.
Local-only administrator credentials: `bytesmap_demo` / `local_demo_only`.

```sh
BYTESMAP_QUERY_COST_TEST_URL=postgres://bytesmap_cost_reader:local_reader_only@127.0.0.1:54384/bytesmap_cost_demo bun test packages/adapters/src/postgres-plan.test.ts
```

The integration check explains `pg_sleep(5)` under a 500ms timeout, proving the statement
is not executed. It also checks indexed versus sequential-scan cost and real row limits.