Understand query cost

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 evidenceCopy Anchor Link

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 measurementsCopy Anchor Link

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 diagnosticsCopy Anchor Link

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 limitsCopy Anchor Link

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 ownershipCopy Anchor Link

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.

ValidationCopy Anchor Link

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 demoCopy Anchor Link

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.

BYTESMAP_QUERY_COST_TEST_URL=postgres://bytesmap_cost_reader:[email protected]: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.