I've (Zardosht) noticed a scenario with index condition pushdown (ICP) in MariaDB
that leads to really bad performance in TokuDB. I don't know if it is
a bug in ICP or if TokuDB is misusing/misunderstanding the API.
Here is the problem. Suppose we run the following query on the following schema:
The output of explain is:
The query is doing a reverse range scan, as it should.
We get handler::idx_cond_push called, but end_range is not set. As a
result, TokuDB thinks it can use index condition pushdown to filter
rows. As we do this and get to the end, because end_range is not set,
we never get a result of ICP_OUT_OF_RANGE, and always get a result of
ICP_NO_MATCH. So, when we go to retrieve that first row past the end
of the range, the row that will tell MySQL it should stop searching,
TokuDB never finds a row and never gets ICP_NO_MATCH. It scans to the
beginning of the index (because it is running in reverse order),
getting ICP_NO_MATCH for every row it encounters.
Although my index is clustering, I've seen this with a normal key as well.
If col1 is an int instead of the funky varchar, handler::idx_cond_push
is never called, so this problem does not exist.
It seems to me that if end_range is not set and we cannot reliably
learn when we go out of range, we should not have
handler::idx_cond_push called, otherwise we can get bad performance
such as the example above.