Created
April 28, 2021 12:55
-
-
Save spetrunia/94333b3e443b83e9d90dd60894f8982c to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
== 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