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:

Attachment 'index-read-statistics-old' not found

that is,

• r_rows is counted after Index Condition Pushdown check.
• r_filtered only counts selectivity of the attached_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, there are more counter:

• r_index_rows counts the number of enumerated index tuples, before any checks were made
• r_rows is the same as before - number of full rows.

Selectivities of all checks are counted:

• 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_condition_filtered is the selectivity of attached_condition check.
• Finally, r_filtered is the combined selectivity of all checks.

Attachment 'index-read-statistics-new' not found