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

DISTINCT removal in aggregate func do not affect grouping strategy

Details

    Description

      The optimization of MDEV-30660 is performed too late to affect some of the optimizer choices:

      create table t1 (
      		 
        pk1 int,
      		 
        pk2 int,
      		 
        filler char(100),
      		 
        primary key(pk1, pk2)
      		 
      );
      		 
      insert into t1 select seq, seq, seq from seq_1_to_10000;


      MariaDB [test]> explain select count(distinct pk1) from t1 group by pk2 order by null;
      		 
      +------+-------------+-------+-------+---------------+---------+---------+------+-------+-----------------------------+
      		 
      | id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows  | Extra                       |
      		 
      +------+-------------+-------+-------+---------------+---------+---------+------+-------+-----------------------------+
      		 
      |    1 | SIMPLE      | t1    | index | NULL          | PRIMARY | 8       | NULL | 10030 | Using index; Using filesort |
      		 
      +------+-------------+-------+-------+---------------+---------+---------+------+-------+-----------------------------+
      		 
      1 row in set (0.000 sec)

      Optimizer Trace shows that DISTINCT was removed:

                {
      		 
                  "prepare_sum_aggregators": {
      		 
                    "function": "count(distinct t1.pk1)",
      		 
                    "aggregator_type": "simple"
      		 
                  }
      		 
                }

      but note the "Using filesort" in EXPLAIN output^^.

      It is present, because the GROUP BY optimization was preparing to use COUNT-DISTINCT which requires that one GROUP BY group is processed after another...

      If I remove DISTINCT manually, the query won't use sorting:

      MariaDB [test]> explain select count(pk1) from t1 group by pk2 order by null;
      		 
      +------+-------------+-------+-------+---------------+---------+---------+------+-------+------------------------------+
      		 
      | id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows  | Extra                        |
      		 
      +------+-------------+-------+-------+---------------+---------+---------+------+-------+------------------------------+
      		 
      |    1 | SIMPLE      | t1    | index | NULL          | PRIMARY | 8       | NULL | 10030 | Using index; Using temporary |
      		 
      +------+-------------+-------+-------+---------------+---------+---------+------+-------+------------------------------+

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30660 Aggregation functions fail to leverage uniqueness property

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            There are no comments yet on this issue.

            People

              Unassigned Unassigned
              psergei Sergei Petrunia
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.