[Tracking] Query and Filter Stats

[nsivarajan]

Search before asking

• I had searched in the issues and found no similar issues.

Version

The Query and Filter stats functionality is not working and returning 0

 admin set frontend config ("enable_query_hit_stats" = "true");
 set enable_nereids_planner = true;

What's Wrong?

Found the feature is broken post making nereids as a default Planner.

What You Expected?

Query stats availability will help on re-designing a better table design layout, so that query can get the maximum benefit of doris engine and served well.

How to Reproduce?

No response

Anything Else?

No response

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[zclllyybb]

Breakwater-GitHub-Analysis-Slot: slot_680cef225156
This content is generated by AI for reference only.

Initial triage:

I checked the live issue and the current Doris code. The issue has no labels, no comments, and no concrete reproduction sequence/output yet, so the exact affected build and SQL shape are still missing.

Code-path finding:

On current upstream master as refreshed today (2287a7e4686), this is no longer simply missing for Nereids. NereidsPlanner calls QueryStatsRecorder.record(physicalPlan, statementContext) after physical planning, and QueryStatsRecorder records column query hits/filter hits from Nereids physical OLAP scans, filters, joins, group/order/window keys, aggregates, lazy materialization, etc. The regression suite regression-test/suites/query_p0/stats/query_stats_test.groovy also enables enable_nereids_planner=true and asserts non-zero show query stats results for simple select/where, select+filter on different columns, alias, group by, order by, join, and window cases.

The branch-sensitive part is important: in refreshed upstream/branch-4.1 and upstream/branch-4.0, I do not see the QueryStatsRecorder hook/class in the Nereids planner path. In those branches, the query/filter stats storage classes exist, but Nereids-planned SELECTs do not have the current master recorder call, so show query stats returning 0 is expected for that code line. The relevant fixes are already merged to master as #63067 and #63768.

Immediate checks for the reporter/maintainers:

1. Please provide the exact Doris version/branch/commit or Docker image. If this is a 4.0/4.1 release build or a build before [Fix](Query Stats) Add QueryStatsRecorder for column-level query and filter - Part1 #63067/[Fix](Query Stats) Add QueryStatsRecorder for column-level query and filter - Part2 #63768, the next action is to upgrade or backport those PRs.
2. If the cluster has multiple FEs, retry with admin set all frontends config ("enable_query_hit_stats" = "true");. The issue uses admin set frontend config, while the current regression uses all frontends; a query routed through another FE with the config still disabled can still show 0.
3. Please attach a minimal reproducible SQL sequence: CREATE TABLE, INSERT, clean all query stats, the query being tested, and the exact show query stats from <table> / show query stats from <table> all verbose output.
4. Please include whether query cache is enabled. The current regression disables enable_query_cache, so that should also be controlled when reproducing.
5. If the failure is on current master after the above checks, please include EXPLAIN SHAPE PLAN for the query and FE logs containing any Failed to record query stats warning.

Known current-master limitations from #63768 are still relevant: set operations (UNION/INTERSECT/EXCEPT), CTE consumer columns, LATERAL VIEW/EXPLODE, aggregate-output HAVING predicates, and external tables were explicitly left out of scope. If the failing query uses one of those shapes, it should be tracked as a remaining coverage gap rather than the general Nereids recorder being absent.

[nsivarajan]

@zclllyybb Thanks for the review, The fixes are categorised to cover maximum scenarios and split into to 3 staged PR's

#63067 and #63768 were already merged, now the third and final PR #63974 is in progress. Review highlighted edge case and those will be updated as a new commit to same PR.

[morningman]

Using this issue as the tracking issue for the Query Stats (column-level query/filter hit statistics) rework. Retitled accordingly.

Background

The feature was introduced in #18805 and recorded both column-level and tablet-level hits from the legacy planner, via OriginalPlanner.collectQueryStat() → ScanNode.genQueryStats(). When the legacy planner was deleted in #51056, that write path went away with it and nothing replaced it, so show query stats has returned 0 under Nereids ever since. That is the root cause of this report.

Rebuild on Nereids

• [Fix](Query Stats) Add QueryStatsRecorder for column-level query and filter - Part1 #63067 — Part 1: scan slot registration, WHERE / JOIN filterHit, root-output queryHit
• [Fix](Query Stats) Add QueryStatsRecorder for column-level query and filter - Part2 #63768 — Part 2: GROUP BY / ORDER BY / window keys, PhysicalRepeat, PhysicalPartitionTopN, lazy materialization, PhysicalStorageLayerAggregate, alias propagation
• [Fix](Query Stats) Add QueryStatsRecorder for column-level query and filter - Part3 #63974 — Part 3: UNION / INTERSECT / EXCEPT, CTE (inline and materialized), LATERAL VIEW / EXPLODE, HAVING on aggregate outputs, IN/EXISTS mark-join predicates, recursive CTE, computed SELECT expressions

(#63061 was an earlier revision of Part 1, superseded by #63067.)

Remaining work after Part 3

These came out of reviewing Part 3 against master. None of them is a regression introduced by the three PRs above — each is either older breakage or a coverage gap that Part 3 does not claim to close. Filing them here so the issue is not closed the moment Part 3 merges.

• Tablet-level statistics are dead code. QueryStats.addStats(List<Long> replicaIds) has had no callers since [clean](planner)delete original planner #51056. The QueryHits column of show tablets / show tablet <id> / ReplicasProcNode is therefore permanently 0, and unlike the rest of the feature it does not even respond to enable_query_hit_stats. Either restore the write path — under Nereids the selected replicas are only known after the Coordinator assigns fragments, so this is not a plan-time hook — or remove TabletStats along with the column and its three readers. A column that always reads zero is worse than no column.

• The HTTP endpoints do not match the SQL statements. SHOW QUERY STATS merges counters from every live FE over thrift, and CLEAN QUERY STATS forwards to the Master and writes an editlog. But GET /api/query_stats/... reads only the local FE, and DELETE /api/query_stats/... calls clear() directly with no editlog (QueryStatsAction.java). On a multi-FE cluster the HTTP reads undercount and the HTTP deletes leave stale counters on every other frontend.

• Multi-input HAVING appears to be missed under two-phase aggregation. HAVING SUM(k2+k3) > 0 looks like it records filterHit only when the optimizer picks a one-phase aggregate. In a two-phase plan the local phase's alias lands only in Part 3's expansion map (never in exprIdToScan), while the global phase's AggregateExpression child is the partial buffer slot; since the map resolves a single hop, the chain breaks and the HAVING predicate resolves to nothing. The same reasoning applies to SUM(DISTINCT k2+k3) (where NormalizeAggregate hoists the argument into a bottom project) and to computed columns exposed through a materialized CTE. This is derived from reading SplitAggWithoutDistinct / NormalizeAggregate, not yet reproduced with a live query — the Part 3 unit test constructs a one-phase shape and the regression test only covers single-input SUM(k2), so neither would catch it. Worth confirming with a GROUP BY <non-bucket column> ... HAVING SUM(k2+k3) > ... regression case.

• Filters above set operations and window outputs look unattributed. SELECT * FROM (SELECT k1 FROM t1 UNION ALL SELECT k1 FROM t2) v WHERE k1 > 0, and the QUALIFY pattern (WHERE rn <= 10 around a row_number() subquery), both reference a freshly minted ExprId that maps to no scan, so filterHit should be lost. Also from code reading rather than a live repro. For the set-operation case the fix looks small: one output slot is fed by several branch slots, which is exactly the Set<Slot> shape Part 3's expansion map already carries.

• External tables (Hive / Iceberg / JDBC / ODBC / ES) are out of scope by design — walkPlan recognizes only PhysicalOlapScan. The non-OLAP TableStats.DEFAULT_INDEX_ID branch still present in the read path is leftover from the original implementation and is unreachable today.

Two properties worth documenting whatever we decide on the items above:

1. Every gap listed here is a silent miss — none of them produces a wrong count. So FilterCount should be read as a lower bound, not an exact value. A column with FilterCount = 0 has not necessarily gone unfiltered, which matters because the main use of this feature is deciding prefix keys and where to build indexes.
2. Recording happens at plan time (NereidsPlanner.plan()), not at execution time. A query that plans but then fails or is cancelled is still counted, while a query served from the FE SQL cache is not counted at all (its physical plan is a PhysicalSqlCache with no scan nodes). The counter means "queries planned," not "queries executed."

[morningman]

Adding a usage summary for anyone landing here from a search, since the feature is undocumented on the website. Everything below reflects master with #63974 (Part 3) applied.

What it is for

Query Stats answers "which columns does anyone actually read, and which ones do people filter on?" The two counters serve different design decisions:

Counter	Column shown by SHOW	Incremented when the column appears in	Typical use
queryHit	QueryCount	SELECT output, GROUP BY, ORDER BY, window PARTITION/ORDER keys, aggregate inputs, UNION branches, LATERAL VIEW generator inputs	Find never-read columns; decide what to drop, or move to a slower codec
filterHit	FilterCount	WHERE predicates, JOIN ON conditions (hash / other / mark), HAVING	Choose prefix-key / bucket-key columns; decide where to build an index

Rolled up: per column → per index (base table and each ROLLUP/MV) → per table → per database.

Turning it on

Off by default. It is a mutable FE config, so no restart is needed — but set it on every FE, because each frontend only counts the queries it planned itself:

ADMIN SET ALL FRONTENDS CONFIG ("enable_query_hit_stats" = "true");

Note ADMIN SET FRONTEND CONFIG (singular) only affects the FE you are connected to; a query routed to another frontend then records nothing, which is the most common reason for "it still shows 0".

The switch gates writing only. Turning it off later stops recording but leaves the accumulated counters readable.

Reading the counters

-- No database selected: per-database counts for the current catalog (needs ADMIN_PRIV)
SHOW QUERY STATS;

-- With a database selected (or given explicitly): per-table counts in that database
USE test_db;
SHOW QUERY STATS;
SHOW QUERY STATS FOR test_db;

-- Per column, base index and all MVs summed together
SHOW QUERY STATS FROM stats_table;

-- Per index (base table + each ROLLUP / MV)
SHOW QUERY STATS FROM stats_table ALL;

-- Per index, per column
SHOW QUERY STATS FROM stats_table ALL VERBOSE;

Careful with the no-argument form: it resolves to the database level whenever a database is selected, and only falls back to the catalog level when none is. VERBOSE is only accepted together with ALL.

Privileges: catalog level needs ADMIN_PRIV; database and table levels need SHOW_PRIV on the object.

Clearing the counters

CLEAN ALL QUERY STATS;                 -- whole catalog, ADMIN_PRIV
CLEAN QUERY STATS FOR test_db;         -- one database, DB ALTER_PRIV
CLEAN QUERY STATS FROM stats_table;    -- one table, table ALTER_PRIV

These forward to the Master and are journaled, so every frontend is cleared. (The DELETE /api/query_stats/... REST endpoints are not equivalent — they clear only the frontend that serves the request. See the tracking list above.)

Worked example

There is no time window built into this feature — counters accumulate since FE startup or the last CLEAN. So the intended workflow is to bracket a representative workload with CLEAN and SHOW:

ADMIN SET ALL FRONTENDS CONFIG ("enable_query_hit_stats" = "true");
SET enable_query_cache = false;   -- see caveats: cache hits are never counted

CLEAN ALL QUERY STATS;

-- ... let the real workload run, or replay it ...
SELECT k1, k2 FROM stats_table WHERE k3 > 100;
SELECT k1, SUM(k2) FROM stats_table GROUP BY k1 HAVING SUM(k2) > 0;
SELECT a.k1 FROM stats_table a JOIN stats_table b ON a.k1 = b.k1;

SHOW QUERY STATS FROM stats_table;

Illustrative output (stats_table is the schema used by regression-test/suites/query_p0/stats/query_stats_test.groovy):

+-------+------------+-------------+
| Field | QueryCount | FilterCount |
+-------+------------+-------------+
| k1    | 3          | 1           |
| k2    | 2          | 1           |
| k3    | 0          | 1           |
| k4    | 0          | 0           |
...

Reading it: k1 was output or grouped by in all three queries and filtered once (the join key). k3 never appears in a select list, only in WHERE — a good prefix-key or index candidate that costs nothing to keep out of the projection. k4 is untouched by this workload.

Per-index breakdown, useful for checking whether a ROLLUP is actually being hit:

SHOW QUERY STATS FROM stats_table ALL;
+-------------+------------+
| IndexName   | QueryCount |
+-------------+------------+
| stats_table | 3          |
| rollup_k3   | 0          |     <-- nothing routed here; candidate for removal
+-------------+------------+

How to read the numbers

• At most one increment per column per query. WHERE k1 > 0 AND k1 < 100 bumps k1's FilterCount by 1, not 2. A self-join scanning the same table twice also counts once. The counters measure "how many queries touched this column," not "how many times."
• Table QueryCount is the sum over its indexes, and each index is incremented once per query routed to it.
• A column can bump both counters in one query: SELECT k1 FROM t WHERE k1 > 0 gives k1 both a QueryCount and a FilterCount.

Caveats worth knowing before you act on the numbers

1. FilterCount is a lower bound. Several plan shapes are still not attributed (see the tracking checklist above). FilterCount = 0 does not prove a column was never filtered on — do not use it alone to justify dropping a column from the sort key. A high FilterCount, on the other hand, is reliable.
2. Counting happens at plan time, not execution time. A query that plans and then fails or is cancelled is still counted. A query answered from the FE SQL cache is not counted (its physical plan holds no scan node), which is why the regression suite sets enable_query_cache = false. Read the counter as "queries planned."
3. In-memory only. Nothing is written to the image or the editlog (only CLEAN is journaled). An FE restart resets that frontend's counters, and a Master switchover does not migrate them. For trend analysis, scrape and store externally.
4. Internal (OlapTable) only. External tables — Hive, Iceberg, JDBC, ODBC, ES — are not recorded at all, by design.
5. show tablets QueryHits is always 0 and is unrelated to this switch. See the tracking checklist.
6. Multi-FE: use the SQL statements, not the REST endpoints. SHOW merges every live frontend; GET /api/query_stats/... reads only the local one.

Cost

Recording adds one full traversal of the physical plan per planned query, proportional to the number of plan nodes times the slots per expression — light, but not free — and the counters live in FE memory for the lifetime of the process. Reasonable practice is to switch it on for a representative period while doing schema or index design work, rather than leaving it on permanently.

[morningman]

Two suggestions on where this feature should go beyond fixing the gaps listed above, plus one item i am taking.

1. Expose the counters as information_schema tables rather than SHOW statements

SHOW QUERY STATS today has five hard-coded output shapes, each with its own ShowResultSetMetaData and its own QueryStatsUtil entry point. None can be filtered, sorted, joined, or aggregated. The questions users actually have are all one WHERE clause away, and none is expressible:

• "every column in this database with filter_count > 100 and query_count = 0"
• "join against information_schema.columns to find wide STRING columns nobody reads"
• "which rollups have served zero queries"

Moving the data into system tables makes all of that ordinary SQL and lets the SHOW statements survive as thin views. It also matches the broader direction of consolidating ad-hoc SHOW commands into queryable metadata.

The two grains

The counters have two natural grains, and conflating them is the mistake to avoid. An object's query_count is "how many queries touched this object" — one increment per query, held today in IndexStats.queryHit. A column's counters are per-column booleans folded into that same query. A table's count is therefore not derivable by summing its columns; it is the sum over its indexes. So: one table per grain.

information_schema.column_query_stats — one row per (frontend, catalog_name, table_schema, table_name, index_name, column_name), with query_count and filter_count.

information_schema.object_query_stats — one row per (frontend, catalog_name, table_schema, table_name, table_type, index_name, partition_name), with query_count. index_name is NULL for external tables; partition_name is NULL when the table is unpartitioned.

information_schema.query_stats_status — one row per frontend: enabled, stats_since. Small, but it closes a real hole: the counters accumulate since FE start or the last CLEAN, with no window and no decay, and today nothing tells you which. It also turns the most common failure — one FE left at enable_query_hit_stats = false because someone used ADMIN SET FRONTEND CONFIG instead of ADMIN SET ALL FRONTENDS CONFIG — into a visible row rather than mysteriously low numbers.

Note that column_query_stats deliberately carries no partition_name. Whether a column is filtered on is a property of the plan, not of a partition; pushing that dimension down to column grain multiplies rows by the partition count for a signal nobody has asked for. Partition hotness lives in object_query_stats; column hotness lives in column_query_stats.

Row-count budget, and why a pre-filter is mandatory

This scale question decides the design rather than being an afterthought. Take 10,000 tables × 10,000 partitions: object_query_stats is 10⁸ rows at full materialization. column_query_stats is tables × indexes × columns — 10,000 tables with 100 columns and 3 indexes is another 3×10⁶. A schema-table scan serializes those rows over thrift from FE to BE. Naively that is tens of gigabytes of TRow for a query whose answer is one line.

The good news: both mechanisms this needs already exist in the schema-table framework. They have simply never been treated as a framework.

(a) Predicate pushdown into the FE generator. SchemaScanNode already serializes arbitrary conjuncts into TSchemaTableRequestParams.frontend_conjuncts (thrift field 7), and FrontendConjunctsUtils.convertToExpression() / isFiltered() let a generator evaluate them per candidate row before serializing it. sql_block_rule and view_dependency already use this. So

SELECT * FROM information_schema.object_query_stats
WHERE table_schema = 'test_db' AND table_name = 'orders';

can be answered by walking only that table's IndexStats, never materializing the other 9,999.

The critical design consequence: the generator must consult the conjuncts while walking the in-memory catalogStats tree, not after building the full row list. Filtering after materialization buys nothing — the cost being avoided is row construction and serialization, not the network.

To make that reliable rather than best-effort, the leading columns should be exactly the ones that prune the walk: catalog_name → table_schema → table_name → index_name → partition_name. Those map onto the catalogStats → dataBaseStats → tableStats → indexStats nesting, so an equality predicate on any prefix short-circuits a level of the traversal. Predicates on query_count / filter_count cannot prune the walk and are just row filters.

(b) FE fan-out already has a working precedent. MetadataGenerator.forwardToOtherFrontends() does exactly this, driven by TSchemaTableRequestParams.replay_to_other_fe. information_schema.active_queries uses it: the BE scanner sets the flag (be/src/information_schema/schema_active_queries_scanner.cpp:63), the receiving FE produces its own rows, then re-sends the request with the flag cleared to every peer and concatenates the results.

That is precisely the shape Query Stats needs — each FE only counts the queries it planned itself, so any read must fan out and merge. It means the work is generalizing an existing mechanism, not inventing one. Today, by contrast, every feature that needs a cross-FE merge hand-rolls it against ClientPool.frontendPool: QueryStatsUtil.getStats() (with its own per-TQueryStatsType merge branches), ShowProcessListCommand, StatisticsCache, FollowerColumnSender, FESessionMgr.

Two things to fix while generalizing:

• forwardToOtherFrontends() swallows per-FE failures with a LOG.warn and continues — the same silent undercount QueryStatsUtil has today. With frontend as a real column, an unreachable FE becomes a missing row, which a join against query_stats_status makes visible. Worth preserving deliberately rather than inheriting by accident.
• frontend_conjuncts must be forwarded along with the relayed request, so peers prune too. Otherwise the pre-filter saves the coordinating FE's work and none of the others'.

(c) When pushdown is absent, degrade loudly rather than silently. For a bare SELECT * FROM information_schema.object_query_stats on a 10⁸-row cluster the options are: stream in batches, cap and warn, or refuse. I'd argue for a required-predicate rule on object_query_stats once the estimated row count exceeds a threshold — reject with "add a table_schema or table_name predicate" — because the alternative is an unbounded FE-side allocation the user cannot cancel. column_query_stats and query_stats_status are small enough to serve unfiltered.

Batching is the complementary answer if unfiltered scans must work at all. TFetchSchemaTableDataResult returns a single dataBatch, and TSchemaTableRequestParams has no offset/limit today, so streaming needs a cursor field added. That is strictly more work than pushdown and helps only the unfiltered case, so it should follow pushdown rather than precede it.

What this buys

With frontend as a real column and pushdown into the walk, summation moves out of Java and into the SQL layer:

-- Today's `SHOW QUERY STATS FROM t ALL`, but now filterable and joinable
SELECT index_name, SUM(query_count)
FROM information_schema.object_query_stats
WHERE table_schema = 'test_db' AND table_name = 'stats_table'
GROUP BY index_name;

-- Cold partitions, pruned at the FE before a single row is serialized
SELECT partition_name, SUM(query_count) AS hits
FROM information_schema.object_query_stats
WHERE table_schema = 'ods' AND table_name = 'orders'
GROUP BY partition_name HAVING hits = 0;

-- Wide string columns nobody ever reads
SELECT s.table_name, s.column_name, c.data_type
FROM information_schema.column_query_stats s
JOIN information_schema.columns c
  ON  c.table_schema = s.table_schema
  AND c.table_name   = s.table_name
  AND c.column_name  = s.column_name
WHERE c.data_type IN ('string', 'varchar')
GROUP BY s.table_name, s.column_name, c.data_type
HAVING SUM(s.query_count) = 0;

-- Prefix-key candidates: heavily filtered, rarely projected
SELECT column_name, SUM(filter_count) AS f, SUM(query_count) AS q
FROM information_schema.column_query_stats
WHERE table_schema = 'ods' AND table_name = 'orders'
GROUP BY column_name ORDER BY f DESC;

The five QueryStatsUtil merge branches, one per TQueryStatsType, disappear. The SHOW statements can survive as views for compatibility.

2. Statistics dimensions: table / index / partition / MV / column

Correcting the current state, because it is easy to misread:

Dimension	Status	Evidence
Table	supported	StatsDelta keyed by catalogId_dbId_tableId_indexId
Column	supported	IndexStats.columnQueryStats / columnFilterStats
Index (ROLLUP)	already supported	The delta key includes PhysicalOlapScan.getSelectedIndexId(); SHOW QUERY STATS FROM t ALL [VERBOSE] breaks down by index name via OlapTable.getIndexNameById(). A sync MV is an index, so it rides the same path.
MTMV (async MV)	recorded — but the attribution is wrong	MTMV extends OlapTable, and BindRelation binds TableType.MATERIALIZED_VIEW through makeOlapScan() → PhysicalOlapScan. An MTMV scan is counted, under the MTMV's own table id. The problem is transparent rewrite: the base table's scan is replaced by the MTMV's, so the base table's columns record nothing and the table looks cold precisely because it is being queried successfully.
Partition	missing, and never existed	PhysicalOlapScan.getSelectedPartitionIds() is right there at plan time. Note #18805's original StatsDelta had no partition dimension either — only catalog/db/table/index plus replicas.
Tablet / replica	dead code	Distinct from partition: the old OlapScanNode.genStatsDelta() passed scanReplicaIds, and replica selection under Nereids happens after fragment assignment, so this cannot be a plan-tree hook.

Two concrete asks follow.

Add the partition dimension. The ids are already on the scan node, so the recorder change is small. The cost is cardinality: a table with thousands of partitions multiplies its counter entries accordingly, and today those counters are an unbounded ConcurrentHashMap in Env with no TTL. That is an independent argument for suggestion 1 — a schema table backed by an explicitly managed structure, rather than a map that only ever grows.

Decide the MTMV attribution semantics. When a query against a base table is transparently rewritten onto an MTMV, I'd argue the base table's columns should still record queryHit: the user's intent was to read those columns, and the entire purpose of the counter is to tell you which columns matter for schema design. The MTMV should of course also record its own hit, so WHERE table_type = 'MTMV' answers "which MVs are earning their keep." Under the schema above both rows coexist naturally; under the current SHOW-only model there is nowhere to put them.

3. External tables (I will take this)

Currently out of scope by design — walkPlan recognizes only PhysicalOlapScan. Worth noting that the structural work is smaller than it looks: PhysicalOlapScan, PhysicalFileScan, and PhysicalOdbcScan all extend PhysicalCatalogRelation, which already exposes getTable() and getDatabase() — the two things getOrCreateDelta() needs. PhysicalFileScan even carries selectedPartitions, so Hive/Iceberg partition hotness lands in the same object_query_stats shape proposed above.

Two things fall out once external tables are in:

• The index_name column becomes genuinely nullable, which retires the TableStats.DEFAULT_INDEX_ID = -1 sentinel still lurking in the read path (QueryStats.getTblStats()) as leftover from [Feature](stat) Collect the information statistics of the query hit #18805.
• catalog_name stops being decorative. Today every row is the internal catalog; the delta key already carries catalogId and the SHOW statements already accept a catalog level, so the plumbing exists and has simply never had a second catalog to hold.