Query Explain to Inspect Execution, Index Use, Scanned / Returned Work, and Optimization Evidence

1. AtlasMart problem: a query “works” but its work is opaque

AtlasMart’s operations console runs a query for in-stock cameras in a price range. The response is correct, but latency and read cost have grown with catalog size. The team proposes a new composite index because “indexes make queries faster.” Query Explain turns that guess into an experiment: what index was selected, how many entries/documents were scanned, how many results were returned, how long execution took, and what was billable?

2. Planning and execution are different experiments

3. Standard/Core Query Explain fields that change decisions

The useful ratio is not a universal threshold; it is a comparison across your own stable workload. A scan ratio of 20:1 may be acceptable for a rare admin report and unacceptable for a query executed thousands of times per minute.

4. Server-side code: plan first, analyze second

process.env.FIRESTORE_EMULATOR_HOST = "127.0.0.1:8080";process.env.GCLOUD_PROJECT = "demo-atlasmart-firestore";import { initializeApp } from "firebase-admin/app";import { getFirestore } from "firebase-admin/firestore";initializeApp({projectId: process.env.GCLOUD_PROJECT});const db=getFirestore();const q=db.collection("catalogItems")  .where("category","==","camera")  .where("price",">=",80)  .where("price","<=",250)  .orderBy("price")  .limit(20);// Production server library: planning-only first.const plan=await q.explain({analyze:false});console.log(plan.metrics.planSummary);// Optional bounded production analysis; this executes the query.// const run=await q.explain({analyze:true});// console.log(run.metrics.executionStats);

The emulator is suitable for code-path and index/rules development, but do not claim that local Explain output—if a specific capability is unavailable or differs—matches managed execution. The mandatory exercise therefore uses a deterministic saved Explain fixture for runtime statistics and an optional real-project step for actual Explain.

5. Deterministic saved trace: diagnose scan amplification

{  "query":"catalog camera price range",  "results_returned":20,  "index_entries_scanned":2400,  "documents_scanned":400,  "read_operations":401,  "execution_duration_ms":92,  "indexes_used":["(category ASC, price ASC, __name__ ASC)"],  "source":"DETERMINISTIC_TRAINING_FIXTURE"}

{  "query":"catalog camera price range + published equality",  "results_returned":20,  "index_entries_scanned":80,  "documents_scanned":20,  "read_operations":21,  "execution_duration_ms":18,  "indexes_used":["(category ASC, published ASC, price ASC, __name__ ASC)"],  "source":"DETERMINISTIC_TRAINING_FIXTURE"}

The numbers are explicitly synthetic training evidence, not claimed Firestore output. The lab assertion is that the mechanism detects lower scan/read amplification after the query contract and index are changed.

6. Mandatory lab: compare evidence, not anecdotes

import fs from "node:fs";const before=JSON.parse(fs.readFileSync("evidence/explain-before.json"));const after=JSON.parse(fs.readFileSync("evidence/explain-after.json"));const ratio=x => x.index_entries_scanned/Math.max(1,x.results_returned);console.log({beforeScanPerResult:ratio(before), afterScanPerResult:ratio(after)});if (!(after.index_entries_scanned < before.index_entries_scanned)) throw new Error("scan-not-improved");if (!(after.read_operations < before.read_operations)) throw new Error("read-work-not-improved");console.log("QUERY_EVIDENCE_IMPROVED");

1. Seed the same deterministic catalog fixture used in Lesson 1.
2. Run the query contract and verify result correctness first.
3. Evaluate saved before/after Explain fixtures with the comparator.
4. Optional cloud step: collect real planning-only Explain first; only run analyze on a bounded dataset after estimating read cost.
5. Store timestamp, database edition/mode, index definition, SDK version, dataset size and query text with the evidence.

7. Enterprise Pipeline and MongoDB compatibility: same goal, different report shape

Enterprise Pipeline Explain returns a summary and execution tree with records scanned, bytes read, memory, latency and read units. Because Enterprise indexes are optional, a successful query can contain a TableScan; “it ran” is not evidence that it is economical at scale. MongoDB compatibility supports explain on documented commands such as find, aggregate, count, distinct, update, delete and findAndModify, with Mongo-style verbosity modes. Do not compare field names across these interfaces as if they were identical.

8. Boundary: Query Explain is not streaming/listener profiling

Standard Query Explain currently supports polled queries, not streaming queries. If a realtime screen is expensive, use listener counts, document changes, Rules behavior, application timing and billing/read evidence; do not expect Query Explain to profile an active snapshot listener directly.

Production judgment

Use Explain when a query has enough frequency, latency, cost or uncertainty to justify a targeted experiment. Preserve correctness tests before changing index/order/filter semantics. An index can reduce read work while increasing write/storage overhead, so Chapter 24 records both sides of the change and Chapter 25 will cost-model them explicitly.

Knowledge check

1. Does Standard planning-only Explain execute the query?
2. What is the difference between documents_scanned and results_returned?
3. Why is a successful Enterprise unindexed query not proof of good performance?
4. Can Standard Query Explain profile snapshot listeners directly?
5. What evidence should be stored with an Explain result?

Authoritative references

• Firebase: Monitor Cloud Firestore activity — usage dashboards, Monitoring metrics, Security Rules metrics, listener/connections, and dashboard-versus-billing caveats.
• Google Cloud Monitoring metrics reference — current firestore.googleapis.com/* metric types, labels, sampling and launch stages.
• Firebase: Query Explain for Standard/Core queries — planner vs analyze behavior, scan statistics, IAM authentication, and billing semantics.
• Firebase: Enterprise Native Pipeline Query Explain — explain/analyze/stats modes and execution-tree evidence.
• Firebase: Query Insights — normalized queries, latency/read-load statistics, retention, delay and IAM.
• Firebase: Key Visualizer overview — Standard Native hotspot heatmaps, eligibility, metrics, limits, and data retention.
• Firebase: Cloud Firestore audit logging — Data Access/Admin Activity audit evidence and processing_duration.
• Firebase: Firestore editions overview — current observability availability and Standard/Enterprise distinctions.