Query Insights to Identify Expensive / Slow Query Patterns and Prioritize Tuning

1. AtlasMart problem: optimize the fleet, not the loudest query

A support engineer captures one 600 ms query and asks for an emergency index. Meanwhile another normalized query executes 300,000 times per hour at 35 ms and scans far more index entries in aggregate. Query Insights changes the prioritization question from “which query looked bad once?” to “which query shapes contribute the most latency, errors, scanned work, and billable read load over time?”

2. Normalized query statistics: structure, not one literal

Queries with equivalent structure are grouped under a normalized query. That lets an operations team see the shape “orders by tenant and createdAt range” instead of hundreds of separate rows for each tenant/date. A normalized query is an observability grouping, not a security boundary: tenant authorization still belongs in Rules/IAM/application checks.

3. Delay and retention determine what questions Insights can answer

Query Insights is retrospective, not request tracing. Current documentation states an approximately one-to-two-hour delay, 10-minute granularity for data up to four days old, and hourly granularity out to 30 days. For an incident happening now, use application timing, request/error metrics and logs first; use Insights later to quantify the query family across the incident window.

4. Mandatory local simulation: rank normalized query families

[ {"id":"Q-catalog-search","exec":120000,"errors":30,"avg_ms":42,"avg_results":18,"avg_docs_scanned":22,"avg_index_scanned":160,"read_ops":260000}, {"id":"Q-seller-report","exec":600,"errors":1,"avg_ms":410,"avg_results":80,"avg_docs_scanned":5400,"avg_index_scanned":8800,"read_ops":3250000}, {"id":"Q-order-detail","exec":300000,"errors":12,"avg_ms":18,"avg_results":1,"avg_docs_scanned":1,"avg_index_scanned":1,"read_ops":300000}, {"id":"Q-admin-audit","exec":40,"errors":0,"avg_ms":900,"avg_results":500,"avg_docs_scanned":15000,"avg_index_scanned":0,"read_ops":20000}]

import fs from "node:fs";const rows=JSON.parse(fs.readFileSync("evidence/query-insights.json"));for (const r of rows) {  r.scanAmp=r.avg_docs_scanned/Math.max(1,r.avg_results);  r.errorRate=r.errors/Math.max(1,r.exec);  // Training score only: never ship universal weights as an SRE policy.  r.trainingPriority=(Math.log10(r.exec+1)*r.avg_ms)+(Math.log10(r.read_ops+1)*r.scanAmp);}rows.sort((a,b)=>b.trainingPriority-a.trainingPriority);console.table(rows.map(({id,exec,avg_ms,read_ops,scanAmp,errorRate,trainingPriority})=>({id,exec,avg_ms,read_ops,scanAmp,errorRate,trainingPriority})));

The score is deliberately labeled a training heuristic, not a recommended universal formula. In production, prioritize against your SLOs, cost budgets, product criticality, security risk and change safety.

5. From Insights to Explain: fleet signal → query experiment

1. Use Query Insights to select a high-impact normalized query family.
2. Reproduce the exact access pattern with representative parameter ranges.
3. Run planning-only Explain; inspect selected indexes/plan.
4. Bound and run analyze where cost permits; capture scanned/returned/read evidence.
5. Change one variable: index, filter/order contract, materialized view, data model or call frequency.
6. Verify result correctness, then compare Explain evidence.
7. After deployment, wait for Insights delay and compare the normalized family over equivalent windows.

6. Standard vs Enterprise vs MongoDB compatibility

7. Security and privacy of query observability

Normalized query text and operational metadata can still reveal collection/field names or application structure. Grant datastore.insights.get via a least-privilege role such as Datastore Viewer or a custom role. Do not copy Query Insights screenshots into public tickets if field names are sensitive. Observability data itself belongs in the governance matrix from Chapter 23.

8. Failure injection: optimize the wrong query

Choose the query with the largest average latency only. In the fixture, Q-admin-audit is slowest but rare; Q-seller-report has much greater aggregate read load and scan amplification. Repair the process by recording product criticality, frequency, read load and scan amplification before choosing the first tuning candidate.

Production judgment

Query Insights is a prioritization surface. Query Explain is a query experiment. Monitoring is a service-level signal. Application logs/traces connect symptoms to product actions. A mature workflow uses them together; none is a replacement for correctness tests or billing reports.

Knowledge check

1. Why normalize queries?
2. How delayed is Query Insights?
3. Why can the highest-latency query be a poor first optimization target?
4. What should follow a Query Insights finding?
5. Does Query Insights replace billing reports?

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.