Query Logger

​

Overview

• Which queries are the slowest or the most expensive right now?
• Which query patterns (templates) dominate load?
• Which queries are scanning too much data, hitting limits, or erroring?
• How has a specific query’s latency or resource usage changed over time?

​

How it’s enabled

​

The data: key fields

* queryHash is populated only when query fingerprinting is enabled for your cluster; otherwise it may be empty. Ask StarTree Ops if you plan to rely on it.

​

Sample queries

All examples filter to the last 24 hours using logTimestampMs. Adjust the window as needed. Always keep a time filter on these queries (see Limitations & caveats).

SELECT logTimestampMs, tableName, timeUsedMs, numDocsScanned, query
FROM system_query_log
WHERE logTimestampMs > now() - 86400000
ORDER BY timeUsedMs DESC
LIMIT 10

SELECT logTimestampMs, tableName, timeUsedMs,
       offlineTotalCpuTimeNs + realtimeTotalCpuTimeNs AS totalCpuNs, query
FROM system_query_log
WHERE logTimestampMs > now() - 86400000
ORDER BY totalCpuNs DESC
LIMIT 10

SELECT queryHash,
       COUNT(*) AS executions,
       AVG(timeUsedMs) AS avgLatencyMs,
       MAX(timeUsedMs) AS maxLatencyMs,
       AVG(numEntriesScannedInFilter) AS avgFilterScan,
       ANY_VALUE(query) AS sampleQuery
FROM system_query_log
WHERE logTimestampMs > now() - 86400000
GROUP BY queryHash
ORDER BY avgLatencyMs DESC
LIMIT 20

SELECT logTimestampMs, tableName, numEntriesScannedInFilter, numDocsScanned, timeUsedMs, query
FROM system_query_log
WHERE logTimestampMs > now() - 86400000
  AND numEntriesScannedInFilter > 1000000
ORDER BY numEntriesScannedInFilter DESC
LIMIT 25

SELECT logTimestampMs, tableName, numExceptions, exceptions, query
FROM system_query_log
WHERE logTimestampMs > now() - 86400000
  AND numExceptions > 0
ORDER BY logTimestampMs DESC
LIMIT 50

SELECT logTimestampMs, tableName, numGroupsLimitReached, groupsTrimmed,
       maxRowsInJoinReached, timeUsedMs, query
FROM system_query_log
WHERE logTimestampMs > now() - 86400000
  AND (numGroupsLimitReached = true OR groupsTrimmed = true OR maxRowsInJoinReached = true)
ORDER BY logTimestampMs DESC
LIMIT 50

SELECT tableName, COUNT(*) AS queries,
       AVG(timeUsedMs) AS avgMs, PERCENTILETDIGEST(timeUsedMs, 99) AS p99Ms
FROM system_query_log
WHERE logTimestampMs > now() - 86400000
GROUP BY tableName
ORDER BY queries DESC

​

How the data is kept tidy

• Automatic segment compaction. The pipeline lands data as many small segments (one every few minutes per broker). A background merge/rollup job runs on a schedule and compacts those small segments into fewer, larger ones, keeping the total segment count bounded as the table grows. No action is required on your part.
• Automatic retention. Data older than the configured retention window is dropped automatically, so the table stays within its storage budget.

​

Limitations & caveats

⚠️ It consumes storage on your cluster. system_query_log is a real Pinot table — its segments occupy disk on your servers. StarTree keeps it bounded with an automatic retention window (older data is dropped, typically ~30 days) and a storage quota — starting 0.16.0, the default cap is 200 GiB (raised from 100 GiB) — and the background merge job (above) keeps the segment count in check — so the footprint stays finite. High-QPS clusters generate more log data; talk to StarTree Ops if you need a longer window or expect large volume.

⚠️ Your analysis queries run on the same cluster. Queries against system_query_log execute on the same brokers and servers serving your production traffic and compete for the same resources. Always filter by logTimestampMs to a bounded window and avoid unbounded SELECT * scans, so debugging doesn’t add load to an already-busy cluster.

• Near-real-time, not instant. Logged queries appear after background segment generation runs — expect a short delay (roughly 10–15 minutes) before a just-executed query is queryable. It’s for analysis and debugging, not live monitoring.
• Long fields are truncated. query, exceptions, serverStats, and traceInfo have maximum lengths; extremely large values are cut off.
• One row per broker-served query. Internal/system queries and the query-logger’s own background activity are not counted as user traffic.
• Best-effort capture. Under extreme broker load a small fraction of records may be dropped rather than slow down query serving.