ANALYZE: Interpreting rows and filtered members
This article describes how to interpret r_rows
and r_filtered
members in ANALYZE FORMAT=JSON output for cases when the table uses index-based access method.
Index-based access method may employ
- Index Condition Pushdown
- Rowid Filtering
- Regular attached_condition checking
Consider ANALYZE FORMAT=JSON output listing all of the above:
"table": { "table_name": "t1", "access_type": "range", "possible_keys": ..., "key": "INDEX1", ... "rowid_filter": { ... "r_selectivity_pct": n.nnn, }, ... "rows": 123, "r_rows": 125, ... "filtered": 8.476269722, "r_filtered": 100, "index_condition": "cond1", "attached_condition": "cond2" }
Access diagram
The access is performed as follows:
Statistics before the fix for MDEV-18478
Before the fix for MDEV-18478, the counters were counted as follows:
that is,
r_rows
is counted after Index Condition Pushdown check and Rowid Filter check.r_filtered
only counts selectivity of theattached_condition
.- selectivity of the Rowid Filter is in
rowid_filter.r_selectivity_pct
.
Statistics after the fix for MDEV-18478
After the fix for MDEV-18478, the row counters are:
r_index_rows
counts the number of enumerated index tuples, before any checks are mader_rows
is the same as before - number of rows after index checks.
The selectivity counters are:
r_icp_filtered
is the percentage of records left after pushed index condition check.rowid_filter.r_selectivity_pct
shows selectivity of Rowid Filter, as before.r_filtered
is the selectivity ofattached_condition
check, as before.r_total_filtered
is the combined selectivity of all checks.
in ANALYZE FORMAT=JSON output these members are placed as follows:
"table": { "table_name": ..., "rows": 426, "r_index_rows": 349, "r_rows": 34,
Whenever applicable, r_index_rows
is shown. It is comparable with rows
- both are numbers of rows to enumerate before any filtering is done.
If r_index_rows
is not shown, r_rows
shows the number of records enumerated.
Then, filtering members:
... "filtered": 8.476269722, "r_total_filtered": 9.742120344,
filtered
is comparable with r_total_filtered
: both show total amount of filtering.
... "index_condition": "lineitem.l_quantity > 47", "r_icp_filtered": 100,
ICP and its observed filtering. The optimizer doesn't compute an estimate for this currently.
... "attached_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30'", "r_filtered": 100
attached_condition
and its observed filtering.