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.
We've bumped into this, too.
edit: I just noticed that it's been resolved FIXED. Is it, really? Disabling ICP resolved the scan table issue for us with exactly the same query and index structure as described in this bug.
This issue status is OPEN which means the problem still exists. Please ignore the resolution as it was set incorrectly by a migration tool from github to JIRA.
I think that i am also hitting this one on MariaDB 10.0.21.
I dont know how the optimizer works but would it be possible for it to skip the ICP for TokuDB tables so it would still be effective for other engines rather than having to disable it for all engines at once (as its in most cases not a viable option to modify all applications/scripts to locally set the optimizer switch).
I've pushed a patch for this in MariaDB:
this is not the best solution (it disables ICP for reverse scans on TokuDB) but it's available now. Enabling ICP for reverse scans is doable but more complicated (and I don't even know if TokuDB as upstream will care to accept the fix)
Seems same as