morningman commented on issue #65423:
URL: https://github.com/apache/doris/issues/65423#issuecomment-4933757895
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:
```sql
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
```sql
-- 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
```sql
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`:
```sql
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:
```sql
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.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
