[MDEV-9863] TokuDB: EXPLAIN doesn't show when index_condition_pushdown is being used Created: 2016-04-01  Updated: 2020-10-28  Resolved: 2020-10-28

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.0.24, 10.1.13
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Geoff Montee (Inactive) Assignee: Sergei Petrunia
Resolution: Won't Fix Votes: 0
Labels: optimizer, tokudb, upstream


 Description   

While trying to reproduce MDEV-9860, I noticed that EXPLAIN does not indicate whether index_condition_pushdown is being used.

For example, EXPLAIN shows this with index_condition_pushdown=on:

MariaDB [db1]> SET optimizer_switch='index_condition_pushdown=on';
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [db1]> EXPLAIN SELECT SQL_NO_CACHE col4 , col5, col1
 
    -> FROM tokudb_icp_bug
 
    -> WHERE col2 = "1999888"
 
    -> AND col3="099"
 
    -> AND col6 IS NOT NULL
 
    -> AND col4 BETWEEN STR_TO_DATE( "01/03/2016" , '%d/%m/%Y')
 
    -> AND STR_TO_DATE( "31/03/2016" , '%d/%m/%Y')
 
    -> ORDER BY col4 DESC, col5 DESC, col1 DESC;
 
+------+-------------+----------------+-------+------------------------------+----------------------+---------+------+------+-------------+
 
| id   | select_type | table          | type  | possible_keys                | key                  | key_len | ref  | rows | Extra       |
 
+------+-------------+----------------+-------+------------------------------+----------------------+---------+------+------+-------------+
 
|    1 | SIMPLE      | tokudb_icp_bug | range | PRIMARY,col2col3col4col5_idx | col2col3col4col5_idx | 15      | NULL |   24 | Using where |
 
+------+-------------+----------------+-------+------------------------------+----------------------+---------+------+------+-------------+
 
1 row in set (0.00 sec)

But the query is relatively slow:

MariaDB [db1]> SELECT SQL_NO_CACHE col4 , col5, col1
 
    -> FROM tokudb_icp_bug
 
    -> WHERE col2 = "1999888"
 
    -> AND col3="099"
 
    -> AND col6 IS NOT NULL
 
    -> AND col4 BETWEEN STR_TO_DATE( "01/03/2016" , '%d/%m/%Y')
 
    -> AND STR_TO_DATE( "31/03/2016" , '%d/%m/%Y')
 
    -> ORDER BY col4 DESC, col5 DESC, col1 DESC;
 
+------------+--------+-----------+
 
| col4       | col5   | col1      |
 
+------------+--------+-----------+
 
| 2016-03-31 | 154732 | 617312062 |
 
| 2016-03-31 | 152220 | 617304573 |
 
| 2016-03-31 | 150846 | 617300219 |
 
| 2016-03-31 | 125003 | 617259519 |
 
| 2016-03-31 | 115240 | 617241909 |
 
| 2016-03-31 | 114357 | 617239150 |
 
| 2016-03-31 | 103555 | 617221171 |
 
| 2016-03-31 | 102942 | 617219162 |
 
| 2016-03-30 | 203301 | 617107392 |
 
| 2016-03-30 | 202449 | 617105258 |
 
| 2016-03-30 | 192830 | 617090437 |
 
| 2016-03-30 | 191350 | 617086349 |
 
| 2016-03-30 | 175306 | 617060614 |
 
| 2016-03-30 | 164238 | 617039550 |
 
| 2016-03-28 | 173032 | 616444951 |
 
| 2016-03-17 | 135427 | 613723098 |
 
| 2016-03-16 | 153202 | 613477432 |
 
| 2016-03-16 | 094315 | 613377198 |
 
| 2016-03-15 | 211529 | 613295731 |
 
| 2016-03-07 | 144453 | 610974835 |
 
| 2016-03-04 | 131400 | 610138965 |
 
| 2016-03-04 | 102825 | 610084403 |
 
| 2016-03-04 | 093225 | 610064611 |
 
| 2016-03-01 | 114944 | 609236733 |
 
+------------+--------+-----------+
 
24 rows in set (0.13 sec)

And when index_condition_pushdown=off, EXPLAIN shows an identical plan:

MariaDB [db1]> SET optimizer_switch='index_condition_pushdown=off';
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [db1]> EXPLAIN SELECT SQL_NO_CACHE col4 , col5, col1
 
    -> FROM tokudb_icp_bug
 
    -> WHERE col2 = "1999888"
 
    -> AND col3="099"
 
    -> AND col6 IS NOT NULL
 
    -> AND col4 BETWEEN STR_TO_DATE( "01/03/2016" , '%d/%m/%Y')
 
    -> AND STR_TO_DATE( "31/03/2016" , '%d/%m/%Y')
 
    -> ORDER BY col4 DESC, col5 DESC, col1 DESC;
 
+------+-------------+----------------+-------+------------------------------+----------------------+---------+------+------+-------------+
 
| id   | select_type | table          | type  | possible_keys                | key                  | key_len | ref  | rows | Extra       |
 
+------+-------------+----------------+-------+------------------------------+----------------------+---------+------+------+-------------+
 
|    1 | SIMPLE      | tokudb_icp_bug | range | PRIMARY,col2col3col4col5_idx | col2col3col4col5_idx | 15      | NULL |   24 | Using where |
 
+------+-------------+----------------+-------+------------------------------+----------------------+---------+------+------+-------------+
 
1 row in set (0.00 sec)

But the query executes much quicker:

MariaDB [db1]> SELECT SQL_NO_CACHE col4 , col5, col1
 
    -> FROM tokudb_icp_bug
 
    -> WHERE col2 = "1999888"
 
    -> AND col3="099"
 
    -> AND col6 IS NOT NULL
 
    -> AND col4 BETWEEN STR_TO_DATE( "01/03/2016" , '%d/%m/%Y')
 
    -> AND STR_TO_DATE( "31/03/2016" , '%d/%m/%Y')
 
    -> ORDER BY col4 DESC, col5 DESC, col1 DESC;
 
+------------+--------+-----------+
 
| col4       | col5   | col1      |
 
+------------+--------+-----------+
 
| 2016-03-31 | 154732 | 617312062 |
 
| 2016-03-31 | 152220 | 617304573 |
 
| 2016-03-31 | 150846 | 617300219 |
 
| 2016-03-31 | 125003 | 617259519 |
 
| 2016-03-31 | 115240 | 617241909 |
 
| 2016-03-31 | 114357 | 617239150 |
 
| 2016-03-31 | 103555 | 617221171 |
 
| 2016-03-31 | 102942 | 617219162 |
 
| 2016-03-30 | 203301 | 617107392 |
 
| 2016-03-30 | 202449 | 617105258 |
 
| 2016-03-30 | 192830 | 617090437 |
 
| 2016-03-30 | 191350 | 617086349 |
 
| 2016-03-30 | 175306 | 617060614 |
 
| 2016-03-30 | 164238 | 617039550 |
 
| 2016-03-28 | 173032 | 616444951 |
 
| 2016-03-17 | 135427 | 613723098 |
 
| 2016-03-16 | 153202 | 613477432 |
 
| 2016-03-16 | 094315 | 613377198 |
 
| 2016-03-15 | 211529 | 613295731 |
 
| 2016-03-07 | 144453 | 610974835 |
 
| 2016-03-04 | 131400 | 610138965 |
 
| 2016-03-04 | 102825 | 610084403 |
 
| 2016-03-04 | 093225 | 610064611 |
 
| 2016-03-01 | 114944 | 609236733 |
 
+------------+--------+-----------+
 
24 rows in set (0.00 sec)



 Comments   
Comment by Sergei Petrunia [ 2016-04-03 ]

Confirm, but it's only for TokuDB. It is also not related to ORDER BY ... DESC problems from MDEV-9860.

TokuDB table:

mysql> explain SELECT SQL_NO_CACHE col4 , col5, col1  FROM tokudb_icp_bug WHERE    col2 = "1999888"    AND col3="099"    AND col6 IS NOT NULL    AND col4 BETWEEN STR_TO_DATE( "01/03/2016" , '%d/%m/%Y') AND STR_TO_DATE( "31/03/2016" , '%d/%m/%Y') \G
 
*************************** 1. row ***************************
 
           id: 1
 
  select_type: SIMPLE
 
        table: tokudb_icp_bug
 
         type: range
 
possible_keys: PRIMARY,col2col3col4col5_idx
 
          key: col2col3col4col5_idx
 
      key_len: 15
 
          ref: NULL
 
         rows: 24
 
        Extra: Using where
 
1 row in set (0.01 sec)

InnoDB table:

mysql> explain SELECT SQL_NO_CACHE col4 , col5, col1  FROM tokudb_icp_bug_innodb WHERE    col2 = "1999888"    AND col3="099"    AND col6 IS NOT NULL    AND col4 BETWEEN STR_TO_DATE( "01/03/2016" , '%d/%m/%Y') AND STR_TO_DATE( "31/03/2016" , '%d/%m/%Y') \G
 
*************************** 1. row ***************************
 
           id: 1
 
  select_type: SIMPLE
 
        table: tokudb_icp_bug_innodb
 
         type: range
 
possible_keys: PRIMARY,col2col3col4col5_idx
 
          key: col2col3col4col5_idx
 
      key_len: 15
 
          ref: NULL
 
         rows: 24
 
        Extra: Using index condition; Using where
 
1 row in set (0.01 sec)

Comment by Sergei Petrunia [ 2016-04-03 ]

ha_tokudb.cc has this:

// we cache the information so we can do filtering ourselves,
 
// but as far as MySQL knows, we are not doing any filtering,
 
// so if we happen to miss filtering a row that does not match
 
// idx_cond_arg, MySQL will catch it.
 
// This allows us the ability to deal with only index_next and index_prev,
 
// and not need to worry about other index_XXX functions
 
Item* ha_tokudb::idx_cond_push(uint keyno_arg, Item* idx_cond_arg) {
 
    toku_pushed_idx_cond_keyno = keyno_arg;
 
    toku_pushed_idx_cond = idx_cond_arg;
 
    return idx_cond_arg;
 
}

So they intentionally don't tell the SQL layer that they are doing ICP.

Comment by Sergei Petrunia [ 2020-10-28 ]

Closing as Wont Fix as TokuDB is not supported anymore.

Generated at Thu Feb 08 07:37:53 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.