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

    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

            • Assignee:
              psergey Sergei Petrunia
              Reporter:
              jkavalik Jiri Kavalik
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated: