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

Optimizer merges derived table in case it would be better to keep it

    XMLWordPrintable

Details

    Description

      When derived_merge optimization is ON, server optimizes away derived table even in case when resulting join is much slower than materializing derived table and joining to it. On production database it is difference between 0.37s without merge and 11.85s with merge on warmed up caches and buffer pool, but slow log shows even cases with 5-20 minutes.

      I prepared a test case with simpler structure (will upload dump). I use group by primary to make subquery not-mergeable - it does not change results in this case.

      explain
      SELECT a.handle,
             Sum(IF(TYPE IS NULL AND created >= '2015-04-07 06:00:00' AND created < '2015-04-08 06:00:00',1,0)) AS `count`,
             Sum(IF(TYPE IS NULL AND created >= '2015-04-07 06:00:00' AND created < '2015-04-08 06:00:00',val,0)) AS `amount`,
             Sum(IF(TYPE IS NULL AND created >= '2015-03-31 22:00:00' AND created < '2015-04-08 06:00:00',1,0)) AS `count_month`,
             Sum(IF(TYPE IS NULL AND created >= '2015-03-31 22:00:00' AND created < '2015-04-08 06:00:00',val,0)) AS `amount_month`,
             Sum(IF(collected IS NOT NULL AND collected >= '2015-03-31 22:00:00' AND collected < '2015-04-08 06:00:00',-val,0)) AS `payout_month`
      FROM a AS a
      LEFT JOIN
        (SELECT b.`type`,b.`created`, b.`val`, b.`collected`, b.`a_id`
         FROM b AS b
         WHERE ((created >= '2015-04-07 06:00:00' AND created < '2015-04-08 06:00:00')
                OR (created >= '2015-03-31 22:00:00' AND created < '2015-04-08 06:00:00')
                OR (collected IS NOT NULL AND collected >= '2015-03-31 22:00:00' AND collected < '2015-04-08 06:00:00'))
        ) AS `T` ON T.a_id = a.id
      WHERE (a.handle <> 1000)
      GROUP BY a.handle;

      +------+-------------+-------+-------+------------------------+--------+---------+-------------------------+------+--------------------------+
      | id   | select_type | table | type  | possible_keys          | key    | key_len | ref                     | rows | Extra                    |
      +------+-------------+-------+-------+------------------------+--------+---------+-------------------------+------+--------------------------+
      |    1 | SIMPLE      | a     | range | handle                 | handle | 5       | NULL                    |  399 | Using where; Using index |
      |    1 | SIMPLE      | b     | ref   | a_id,created,collected | a_id   | 5       | derived_merge_test.a.id |  365 | Using where              |
      +------+-------------+-------+-------+------------------------+--------+---------+-------------------------+------+--------------------------+
      RUN:
      399 rows in set (0.41 sec)

      explain
      SELECT a.handle,
             Sum(IF(TYPE IS NULL AND created >= '2015-04-07 06:00:00' AND created < '2015-04-08 06:00:00',1,0)) AS `count`,
             Sum(IF(TYPE IS NULL AND created >= '2015-04-07 06:00:00' AND created < '2015-04-08 06:00:00',val,0)) AS `amount`,
             Sum(IF(TYPE IS NULL AND created >= '2015-03-31 22:00:00' AND created < '2015-04-08 06:00:00',1,0)) AS `count_month`,
             Sum(IF(TYPE IS NULL  AND created >= '2015-03-31 22:00:00' AND created < '2015-04-08 06:00:00',val,0)) AS `amount_month`,
             Sum(IF(collected IS NOT NULL AND collected >= '2015-03-31 22:00:00'  AND collected < '2015-04-08 06:00:00',-val,0)) AS `payout_month`
      FROM a AS a
      LEFT JOIN
        (SELECT b.`type`, b.`created`, b.`val`, b.`collected`, b.`a_id`
         FROM b AS b
         WHERE ((created >= '2015-04-07 06:00:00' AND created < '2015-04-08 06:00:00')
                OR (created >= '2015-03-31 22:00:00' AND created < '2015-04-08 06:00:00')
                OR (collected IS NOT NULL AND collected >= '2015-03-31 22:00:00' AND collected < '2015-04-08 06:00:00'))
         GROUP BY id) AS `T` ON T.a_id = a.id
      WHERE (a.handle <> 1000)
      GROUP BY a.handle;

      +------+-------------+------------+-------------+-------------------+-------------------+---------+-------------------------+------+------------------------------------------------------------------+
      | id   | select_type | table      | type        | possible_keys     | key               | key_len | ref                     | rows | Extra                                                            |
      +------+-------------+------------+-------------+-------------------+-------------------+---------+-------------------------+------+------------------------------------------------------------------+
      |    1 | PRIMARY     | a          | range       | handle            | handle            | 5       | NULL                    |  399 | Using where; Using index                                         |
      |    1 | PRIMARY     | <derived2> | ref         | key0              | key0              | 5       | derived_merge_test.a.id |   10 |                                                                  |
      |    2 | DERIVED     | b          | index_merge | created,collected | created,collected | 8,9     | NULL                    | 3918 | Using sort_union(created,collected); Using where; Using filesort |
      +------+-------------+------------+-------------+-------------------+-------------------+---------+-------------------------+------+------------------------------------------------------------------+
      RUN:
      399 rows in set (0.05 sec)

      Attachments

        Activity

          People

            psergei Sergei Petrunia
            jkavalik Jiri Kavalik
            Votes:
            1 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

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