Skip to content

Instantly share code, notes, and snippets.

@spetrunia
Created April 28, 2021 12:55
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save spetrunia/94333b3e443b83e9d90dd60894f8982c to your computer and use it in GitHub Desktop.
Save spetrunia/94333b3e443b83e9d90dd60894f8982c to your computer and use it in GitHub Desktop.
== Expose Index Condition Pushdown r_filtered% ==
MariaDB has Index Condition Pushdown optimization.
MariaDB also has ANALYZE FORMAT=JSON command which reports condition
selectivities.
Unfortunately the selectivity of pushed index condition is not reported.
For example, consider query:
analyze format=json
select * from tbl where key1 < 100 and mod(key1,2)=0;
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 5.2011,
"table": {
"table_name": "tbl",
"access_type": "range",
"possible_keys": ["key1"],
"key": "key1",
"key_length": "5",
"used_key_parts": ["key1"],
"r_loops": 1,
"rows": 100,
"r_rows": 50,
"r_table_time_ms": 4.9544,
"r_other_time_ms": 0.214,
"filtered": 100,
"r_filtered": 100,
"index_condition": "tbl.key1 < 100 and tbl.key1 MOD 2 = 0"
}
it does a scan on a range "key1 <100", which gives 100 rows.
Pushed Index condition additionally filters 50% of rows with "key1 MOD 2=0",
but one cannot see it.
Please add r_index_condition_filtered member and collect/report the fraction of
index tuples filtered.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment