[MDEV-9863] TokuDB: EXPLAIN doesn't show when index_condition_pushdown is being used - Jira

XML

Word

Printable

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)

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.