system.predicate_statistics_log

System table containing sampled filter and index selectivity statistics collected from MergeTree read pipelines during query execution.

Querying in ClickHouse Cloud

The data in this system table is held locally on each node in ClickHouse Cloud. Obtaining a complete view of all data, therefore, requires the clusterAllReplicas function. See here for further details.

Description

Contains sampled selectivity statistics collected while reading from MergeTree tables. The table is populated only when predicate_statistics_sample_rate is greater than 0.

Availability

system.predicate_statistics_log is created only when the server configuration contains a predicate_statistics_log section. After creating the log, set predicate_statistics_sample_rate to a value greater than 0 to collect rows. Without the log section, queries against the table fail with UNKNOWN_TABLE.

<clickhouse>
    <predicate_statistics_log>
        <database>system</database>
        <table>predicate_statistics_log</table>
        <partition_by>toYYYYMM(event_date)</partition_by>
        <flush_interval_milliseconds>7500</flush_interval_milliseconds>
    </predicate_statistics_log>
</clickhouse>

Use this table to inspect how selective user predicates are in real workloads and how many granules remain after primary-key or skip-index filtering. The data is intended as input for workload-driven index and projection recommendations.

Row shapes

A single query can produce two kinds of rows in system.predicate_statistics_log:

• Filter rows, emitted per prewhere/filter step in MergeTreeSelectProcessor. They populate predicate_expression, input_rows, passed_rows, filter_selectivity, and the whole-predicate columns total_input_rows, total_passed_rows, total_selectivity. Index-related columns are empty.
• Index rows, emitted per read step in ReadFromMergeTree. They populate the index_names, index_types, total_granules, granules_after, and index_selectivities arrays, one entry per index stage (primary key, partition, skip indexes). Predicate-related columns are empty.

Filter rows and index rows for the same query share the same query_id and table, so they can be joined when both are needed.

Sampling and overhead

Sampling is controlled by predicate_statistics_sample_rate:

• 0 disables collection.
• 1 samples every query.
• N > 1 samples approximately 1 / N of queries, hashed by query_id.

Lower values produce more data but add CPU work on the read path and more writes to the system log. After enabling the setting, use SYSTEM FLUSH LOGS if you need rows to appear immediately.

It is safe to truncate or drop this table at any time.

Columns

• hostname (LowCardinality(String)) — Hostname of the server executing the query.
• clickhouse_version (LowCardinality(String)) — Version of the ClickHouse server that produced the row.
• system_processor (LowCardinality(String)) — CPU architecture of the ClickHouse server that produced the row.
• event_date (Date) — Event date.
• event_time (DateTime) — Timestamp when this log entry was written.
• database (LowCardinality(String)) — Database name of the target table.
• table (LowCardinality(String)) — Table name of the target table.
• query_id (String) — Query ID for linking back to query_log.
• predicate_expression (String) — Whole filter expression handled by this prewhere/filter step (ActionsDAG dump).
• input_rows (UInt64) — Rows entering this prewhere/filter step.
• passed_rows (UInt64) — Rows surviving this prewhere/filter step.
• filter_selectivity (Float64) — Selectivity of this step: passed_rows / input_rows.
• total_input_rows (UInt64) — Rows entering the first prewhere step (total rows read from granules).
• total_passed_rows (UInt64) — Rows surviving all prewhere steps (rows delivered to the query).
• total_selectivity (Float64) — Selectivity of the whole predicate: total_passed_rows / total_input_rows.
• index_names (Array(LowCardinality(String))) — Names of indexes applied, e.g. [‘PrimaryKey’, ‘idx_bf_status’] (index rows only).
• index_types (Array(LowCardinality(String))) — Types of indexes applied: PrimaryKey, Skip, MinMax, Partition (index rows only).
• total_granules (Array(UInt64)) — Granules entering each index stage (index rows only).
• granules_after (Array(UInt64)) — Granules remaining after each index stage (index rows only).
• index_selectivities (Array(Float64)) — Per-index selectivity: granules_after / total_granules (index rows only).

Example

SET predicate_statistics_sample_rate = 1;

SELECT *
FROM hits
WHERE URL LIKE '%/product/%' AND EventDate >= today() - 7
FORMAT Null;

SYSTEM FLUSH LOGS predicate_statistics_log;

SELECT
    query_id,
    predicate_expression,
    round(filter_selectivity, 3) AS step_selectivity,
    round(total_selectivity, 3) AS query_selectivity,
    index_names,
    index_selectivities
FROM system.predicate_statistics_log
WHERE table = 'hits'
ORDER BY event_time DESC
LIMIT 10;

See also

• predicate_statistics_sample_rate
• system.query_log