Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-7786

regression in query plan - join buffer (flat, BNL join) -> Range checked for each record (index map: 0x6); Rowid-ordered scan

    Details

    • Sprint:
      5.5.44

      Description

      I have a 5.5.29 and 5.5.42 with the same data. 5.5.42 was just created from the 5.5.29 instance.

      The obs query plan changes the result time significantly.

      5.5.29 uses the query:

      explain SELECT obsloc.sScreenName AS ObsLocation, obsloc.sScreenName AS SiteName, obsloc.sState AS SiteState, obsloc.sDisplayState AS SiteDisplayState, obsloc.nLat AS SiteLat, obsloc.nLong AS SiteLong, obsloc.iElevation AS SiteElevation, DATE_FORMAT( obs.dtDate, '%Y-%m-%d' ) AS ObsDate, climate.nMeanMinTemp FROM locations obsloc LEFT JOIN obs_daily obs ON obs.sLocType = obsloc.sType AND  obs.dtDate >= '2015-03-14' AND obs.dtDate <=  '2015-03-14' LEFT JOIN climatology climate ON  climate.iMonth=MONTH(obs.dtDate)  ORDER BY obs.dtDate limit 300;
      +------+-------------+---------+-------+---------------------------------+-------------------+---------+------+--------+-----------------------------------------------------------------------------------------+
      | id   | select_type | table   | type  | possible_keys                   | key               | key_len | ref  | rows   | Extra                                                                                   |
      +------+-------------+---------+-------+---------------------------------+-------------------+---------+------+--------+-----------------------------------------------------------------------------------------+
      |    1 | SIMPLE      | obsloc  | ALL   | NULL                            | NULL              | NULL    | NULL | 121555 | Using temporary; Using filesort                                                         |
      |    1 | SIMPLE      | obs     | range | idx_obs_daily_1,idx_obs_daily_2 | idx_obs_daily_2   | 3       | NULL |   5989 | Using where; Rowid-ordered scan; Using join buffer (flat, BNL join)                     |
      |    1 | SIMPLE      | climate | ref   | idx_climatology_3               | idx_climatology_3 | 4       | func |   2117 | Using where; Using join buffer (incremental, BKAH join); Key-ordered Rowid-ordered scan |

      5.5.42 on the same database and query has the plan

      +------+-------------+---------+-------+---------------------------------+-------------------+---------+------+--------+----------------------------------------------------------------------------------+
      | id   | select_type | table   | type  | possible_keys                   | key               | key_len | ref  | rows   | Extra                                                                            |
      +------+-------------+---------+-------+---------------------------------+-------------------+---------+------+--------+----------------------------------------------------------------------------------+
      |    1 | SIMPLE      | obsloc  | ALL   | NULL                            | NULL              | NULL    | NULL | 102188 | Using temporary; Using filesort                                                  |
      |    1 | SIMPLE      | obs     | range | idx_obs_daily_1,idx_obs_daily_2 | idx_obs_daily_2   | 3       | NULL |   5989 | Range checked for each record (index map: 0x6); Rowid-ordered scan               |
      |    1 | SIMPLE      | climate | ref   | idx_climatology_3               | idx_climatology_3 | 4       | func |   5041 | Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan |
      +------+-------------+---------+-------+---------------------------------+-------------------+---------+------+--------+----------------------------------------------------------------------------------+

      On a significantly larger query this is the difference between
      5.5.29

      SELECT ...
      FROM locations loc
      INNER JOIN location_time_zones tz
      ON tz.sLocType = loc.sType
      AND tz.sLocCode = loc.sCode
      LEFT OUTER JOIN locations c
      ON c.sType = 'CCODE'
      AND c.sCode = loc.sCountryCode
      INNER JOIN location_mappings_2008 xsite
      ON xsite.custom_code = 'TWC'
      AND xsite.type1 = loc.sType
      AND xsite.code1 = loc.sCode
      AND xsite.context = 'obsdaily'
      LEFT OUTER JOIN locations obsloc
      ON obsloc.sType = xsite.type2
      AND obsloc.sCode = xsite.code2
      LEFT OUTER JOIN obs_daily obs
      ON obs.sLocType = obsloc.sType
      AND obs.dtDate >= '2015-03-14'
      AND obs.dtDate <= '2015-03-14'
      LEFT OUTER JOIN climatology climate
      ON climate.sLocType = xsite.type2
      AND climate.sLocCode = xsite.code2
      AND climate.iMonth = MONTH( obs.dtDate )
      WHERE loc.sType = 'TWCID'
      AND loc.sCode = '9764'
      ORDER BY obs.dtDate;
       
      running the query on 5.5.29
      | id   | select_type | table   | type   | possible_keys                                             | key                          | key_len | ref                                          | rows | Extra                                           |
      |    1 | SIMPLE      | loc     | const  | idx_locations_1,idx_locations_4                           | idx_locations_1              | 44      | const,const                                  |    1 | Using temporary; Using filesort                 |
      |    1 | SIMPLE      | tz      | const  | idx_location_time_zones_1                                 | idx_location_time_zones_1    | 44      | const,const                                  |    1 | Using index                                     |
      |    1 | SIMPLE      | c       | const  | idx_locations_1,idx_locations_4                           | idx_locations_1              | 44      | const,const                                  |    1 |                                                 |
      |    1 | SIMPLE      | xsite   | ref    | idx_location_mappings_2008_1,idx_location_mappings_2008_2 | idx_location_mappings_2008_1 | 128     | const,const,const,const                      |    1 | Using where; Using index                        |
      |    1 | SIMPLE      | obsloc  | eq_ref | idx_locations_1,idx_locations_4                           | idx_locations_1              | 44      | weather.xsite.type2,weather.xsite.code2      |    1 | Using where                                     |
      |    1 | SIMPLE      | obs     | range  | idx_obs_daily_1,idx_obs_daily_2                           | idx_obs_daily_2              | 3       | NULL                                         | 5989 | Using where; Using join buffer (flat, BNL join) |
      |    1 | SIMPLE      | climate | eq_ref | idx_climatology_1,idx_climatology_2,idx_climatology_3     | idx_climatology_1            | 48      | weather.xsite.type2,weather.xsite.code2,func |    1 | Using where                                     |
       
      returns 2009 rows in set (0.10 sec)
       
      where as 5.5.42:
       
      | id   | select_type | table   | type   | possible_keys                                             | key                          | key_len | ref                                          | rows | Extra                                          |
      |    1 | SIMPLE      | loc     | const  | idx_locations_1,idx_locations_4                           | idx_locations_1              | 44      | const,const                                  |    1 | Using temporary; Using filesort                |
      |    1 | SIMPLE      | tz      | const  | idx_location_time_zones_1                                 | idx_location_time_zones_1    | 44      | const,const                                  |    1 | Using index                                    |
      |    1 | SIMPLE      | c       | const  | idx_locations_1,idx_locations_4                           | idx_locations_1              | 44      | const,const                                  |    1 |                                                |
      |    1 | SIMPLE      | xsite   | ref    | idx_location_mappings_2008_1,idx_location_mappings_2008_2 | idx_location_mappings_2008_1 | 128     | const,const,const,const                      |    1 | Using where; Using index                       |
      |    1 | SIMPLE      | obsloc  | eq_ref | idx_locations_1,idx_locations_4                           | idx_locations_1              | 44      | weather.xsite.type2,weather.xsite.code2      |    1 | Using where                                    |
      |    1 | SIMPLE      | obs     | range  | idx_obs_daily_1,idx_obs_daily_2                           | idx_obs_daily_2              | 3       | NULL                                         | 5989 | Range checked for each record (index map: 0x6) |
      |    1 | SIMPLE      | climate | eq_ref | idx_climatology_1,idx_climatology_2,idx_climatology_3     | idx_climatology_1            | 48      | weather.xsite.type2,weather.xsite.code2,func |    1 | Using where                                    |
       
      returned 2009 rows in set (11 min 10.44 sec)

      changing the range part on obs.dtDate to obs.dtDate = '2015-03-14'

      | id   | select_type | table   | type   | possible_keys                                             | key                          | key_len | ref                                          | rows | Extra                                                                                   |
      |    1 | SIMPLE      | loc     | const  | idx_locations_1,idx_locations_4                           | idx_locations_1              | 44      | const,const                                  |    1 | Using temporary; Using filesort                                                         |
      |    1 | SIMPLE      | tz      | const  | idx_location_time_zones_1                                 | idx_location_time_zones_1    | 44      | const,const                                  |    1 | Using index                                                                             |
      |    1 | SIMPLE      | c       | const  | idx_locations_1,idx_locations_4                           | idx_locations_1              | 44      | const,const                                  |    1 |                                                                                         |
      |    1 | SIMPLE      | xsite   | ref    | idx_location_mappings_2008_1,idx_location_mappings_2008_2 | idx_location_mappings_2008_1 | 128     | const,const,const,const                      |    1 | Using where; Using index                                                                |
      |    1 | SIMPLE      | obsloc  | eq_ref | idx_locations_1,idx_locations_4                           | idx_locations_1              | 44      | weather.xsite.type2,weather.xsite.code2      |    1 | Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan        |
      |    1 | SIMPLE      | obs     | ref    | idx_obs_daily_1,idx_obs_daily_2                           | idx_obs_daily_2              | 3       | const                                        | 5989 | Using where; Using join buffer (incremental, BKAH join); Key-ordered Rowid-ordered scan |
      |    1 | SIMPLE      | climate | eq_ref | idx_climatology_1,idx_climatology_2,idx_climatology_3     | idx_climatology_1            | 48      | weather.xsite.type2,weather.xsite.code2,func |    1 | Using where; Using join buffer (incremental, BKAH join); Key-ordered Rowid-ordered scan |
       
      resulting in 2009 rows in set (0.14 sec)

      index stats which don't change much after running analyize tables a few times.

      5.5.29
      dba@localhost [weather]  select * from information_schema.INNODB_INDEX_STATS where table_schema='weather' and table_name='obs_daily';
      +--------------+------------+-----------------+--------+------------------+-------------------+------------------+
      | table_schema | table_name | index_name      | fields | rows_per_key     | index_total_pages | index_leaf_pages |
      +--------------+------------+-----------------+--------+------------------+-------------------+------------------+
      | weather      | obs_daily  | PRIMARY         |      1 | 1                |            290176 |           253471 |
      | weather      | obs_daily  | idx_obs_daily_1 |      3 | 3034775, 3513, 1 |             71232 |            62121 |
      | weather      | obs_daily  | idx_obs_daily_2 |      2 | 8479, 1          |             29504 |            25696 |

      5.5.42
      MariaDB [weather]>  select * from information_schema.INNODB_INDEX_STATS where table_schema='weather' and table_name='obs_daily';
      +--------------+------------+-----------------+--------+-----------------+-------------------+------------------+
      | table_schema | table_name | index_name      | fields | rows_per_key    | index_total_pages | index_leaf_pages |
      +--------------+------------+-----------------+--------+-----------------+-------------------+------------------+
      | weather      | obs_daily  | PRIMARY         |      1 | 1               |            315136 |           275181 |
      | weather      | obs_daily  | idx_obs_daily_1 |      3 | 752141, 1374, 1 |             71232 |            62106 |
      | weather      | obs_daily  | idx_obs_daily_2 |      2 | 4641, 0         |             33344 |            29082 |

      | obs_daily | CREATE TABLE `obs_daily` (
        `iId` int(11) NOT NULL AUTO_INCREMENT,
        `sLocType` varchar(10) NOT NULL DEFAULT '',
        `sLocCode` varchar(30) NOT NULL DEFAULT '',
        `dtDate` date NOT NULL DEFAULT '0000-00-00',
      ...
        PRIMARY KEY (`iId`),
        UNIQUE KEY `idx_obs_daily_1` (`sLocType`,`sLocCode`,`dtDate`),
        KEY `idx_obs_daily_2` (`dtDate`)
      ) ENGINE=InnoDB AUTO_INCREMENT=24862119 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC |

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                psergey Sergei Petrunia
                Reporter:
                danblack Daniel Black
              • Votes:
                0 Vote for this issue
                Watchers:
                6 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: