[MDEV-28444] Wrong query plan using partition by range of date Created: 2022-04-29  Updated: 2022-04-29

Status: Open
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.7.1
Fix Version/s: None

Type: Bug Priority: Major
Reporter: VAROQUI Stephane Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None


 Description   

For a table partitioned by day with PK( date , point) to respect primary key ordering 

CREATE TABLE `monde_gfs` (
 
  `date_heure` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 
  `num_point` int(11) NOT NULL DEFAULT 0,
 
  PRIMARY KEY (`date_heure`,`num_point`),
 
  KEY `num_point` (`num_point`)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1
 
 PARTITION BY RANGE (to_days(`date_heure`))
 
(PARTITION `p2022_04_27` VALUES LESS THAN (738638) ENGINE = InnoDB,
 
 PARTITION `p2022_04_28` VALUES LESS THAN (738639) ENGINE = InnoDB,
 
 PARTITION `p2022_04_29` VALUES LESS THAN (738640) ENGINE = InnoDB
 
...

The optimizer wrongly estimate the cost of the range ref access 2 vs 24M where as the 2 partions contains around 50M rows 

 analyze format=json SELECT * FROM  monde_gfs m0_ WHERE m0_.num_point = 237609 AND m0_.date_heure >=
 
 '2022-04-29 00:00:00' AND m0_.date_heure <= '2022-04-30 00:00:00'; 
{
 
 "query_block": {
 
  "select_id": 1,
 
  "r_loops": 1,
 
  "r_total_time_ms": 46326.7725,
 
  "table": {
 
   "table_name": "m0_",
 
   "partitions": ["p2022_04_29", "p2022_04_30"],
 
   "access_type": "range",
 
   "possible_keys": ["PRIMARY", "num_point"],
 
   "key": "PRIMARY",
 
   "key_length": "9",
 
   "used_key_parts": ["date_heure", "num_point"],
 
   "r_loops": 1,
 
   "rows": 2,
 
   "r_rows": 24917761,
 
   "filtered": 100,
 
   "r_filtered": 1.0033e-4,
 
   "attached_condition": "m0_.num_point = 237609 and m0_.date_heure >= '2022-04-29 00:00:00' and m0_.date_heure <= '2022-04-30 00:00:00'"
 
  }

At the same time the partition pruning should already filtering the date_heure range and a score of 2 is under estimate and should be more estimated to the number records of each pruned partition because it's a range full index scan

As the result of this miss estimate the correct plan is ignored

analyze format=json SELECT * FROM  monde_gfs m0_ force index (num_point) WHERE m0_.num_point = 237609 AND m0_.date_heure >=
 
 '2022-04-29 00:00:00' AND m0_.date_heure <= '2022-04-30 00:00:00'; 
{
 
  "query_block": {
 
    "select_id": 1,
 
    "r_loops": 1,
 
    "r_total_time_ms": 48.77394453,
 
    "table": {
 
      "table_name": "m0_",
 
      "partitions": ["p2022_04_29", "p2022_04_30"],
 
      "access_type": "ref",
 
      "possible_keys": ["num_point"],
 
      "key": "num_point",
 
      "key_length": "4",
 
      "used_key_parts": ["num_point"],
 
      "ref": ["const"],
 
      "r_loops": 1,
 
      "rows": 2,
 
      "r_rows": 48,
 
      "r_table_time_ms": 48.6800073,
 
      "r_other_time_ms": 0.056539477,
 
      "filtered": 100,
 
      "r_filtered": 52.08333333,
 
      "attached_condition": "m0_.date_heure >= '2022-04-29 00:00:00' and m0_.date_heure <= '2022-04-30 00:00:00'"
 
    }
 
  }
 
} |

We have observed this regression on pre production while production in 10.1 not affected and also the issue looks like beeing trigger at some point hitting 100% of the CPU with such queries



 Comments   
Comment by VAROQUI Stephane [ 2022-04-29 ]

Ollalala so strange select second voverd by index key or count pick the correct index while * force wrong index 

analyze format=json SELECT num_point FROM  monde_gfs m0_ WHERE m0_.num_point = 237609 AND m0_.date_heure >=  '2022-04-29 00:00:00' AND m0_.date_heure <= '2022-04-30 00:00:00';
 
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| ANALYZE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
 
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| {
 
  "query_block": {
 
    "select_id": 1,
 
    "r_loops": 1,
 
    "r_total_time_ms": 0.147626545,
 
    "table": {
 
      "table_name": "m0_",
 
      "partitions": ["p2022_04_29", "p2022_04_30"],
 
      "access_type": "ref",
 
      "possible_keys": ["PRIMARY", "num_point"],
 
      "key": "num_point",
 
      "key_length": "4",
 
      "used_key_parts": ["num_point"],
 
      "ref": ["const"],
 
      "r_loops": 1,
 
      "rows": 2,
 
      "r_rows": 48,
 
      "r_table_time_ms": 0.082915362,
 
      "r_other_time_ms": 0.042107268,
 
      "filtered": 100,
 
      "r_filtered": 52.08333333,
 
      "attached_condition": "m0_.date_heure >= '2022-04-29 00:00:00' and m0_.date_heure <= '2022-04-30 00:00:00'",
 
      "using_index": true
 
    }
 
  }
 
}

Comment by VAROQUI Stephane [ 2022-04-29 ]

Indeed this is a statitistic issue and may be not a full bug after all but 

ALTER TABLE monde_gfs  ANALYZE PARTITION p2022_04_29;
 
+------------------+---------+----------+----------+
 
| Table            | Op      | Msg_type | Msg_text |
 
+------------------+---------+----------+----------+
 
| models.monde_gfs | analyze | status   | OK       |
 
+------------------+---------+----------+----------+
 
1 row in set (0.202 sec)
 
 
 
MariaDB [models]> ALTER TABLE monde_gfs  ANALYZE PARTITION p2022_04_30;
 
+------------------+---------+----------+----------+
 
| Table            | Op      | Msg_type | Msg_text |
 
+------------------+---------+----------+----------+
 
| models.monde_gfs | analyze | status   | OK       |
 
+------------------+---------+----------+----------+
 
1 row in set (0.466 sec)
 
 
 
MariaDB [models]> analyze format=json SELECT * FROM  monde_gfs m0_ WHERE m0_.num_point = 237609 AND m0_.date_heure >=  '2022-04-29 00:00:00' AND m0_.date_heure <= '2022-04-30 00:00:00';
 
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| ANALYZE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
 
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| {
 
  "query_block": {
 
    "select_id": 1,
 
    "r_loops": 1,
 
    "r_total_time_ms": 30.20447983,
 
    "table": {
 
      "table_name": "m0_",
 
      "partitions": ["p2022_04_29", "p2022_04_30"],
 
      "access_type": "index_merge",
 
      "possible_keys": ["PRIMARY", "num_point"],
 
      "key_length": "4,9",
 
      "index_merge": {
 
        "intersect": {
 
          "range": {
 
            "key": "num_point",
 
            "used_key_parts": ["num_point"]
 
          },
 
          "range": {
 
            "key": "PRIMARY",
 
            "used_key_parts": ["date_heure", "num_point"]
 
          }
 
        }
 
      },
 
      "r_loops": 1,
 
      "rows": 12,
 
      "r_rows": 25,
 
      "r_table_time_ms": 28.19868784,
 
      "r_other_time_ms": 0.256241376,
 
      "filtered": 75,
 
      "r_filtered": 100,
 
      "attached_condition": "m0_.num_point = 237609 and m0_.date_heure >= '2022-04-29 00:00:00' and m0_.date_heure <= '2022-04-30 00:00:00'"
 
    }
 
  }
 
}

Generated at Thu Feb 08 10:00:47 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.