[MDEV-29073] Slower query #2 with MDEV-28852 comparing to 10.6 or 10.10 (820 sec vs 0.9 sec) Created: 2022-07-09  Updated: 2022-07-11  Resolved: 2022-07-11

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: N/A
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: Elena Stepanova Assignee: Sergei Petrunia
Resolution: Not a Bug Votes: 0
Labels: None

Issue Links:
Problem/Incident
is caused by MDEV-28852 Improve optimization of joins with ma... Closed

 Description   

The test uses all default server startup options, loads original data set from MDEV-28073 (xpo_full_testcase.sql), runs ANALYZE PERSISTENT FOR ALL on all tables, and executes the query:

SELECT * FROM `t1` tbl0 INNER JOIN `t1` tbl1 
ON tbl1.`c1` = CAST(tbl0.`c2` AS CHAR(30)) 
LEFT JOIN `t1` tbl2 ON tbl2.`c3` = 'str' 
INNER JOIN `t2` tbl5;

(where tX, cX and 'str' are obfuscated names of tables and columns and a constant from the dataset).

On current 10.6, the query takes below 1 sec

10.6

 
Empty set (0.907 sec)

On the preview branch for MDEV-28852 (preview-10.10-optimizer 6f979f88c), with the patch from MDEV-28929, it takes over 13 min:

Empty set (13 min 45.263 sec)

Note that it's about the actual execution, not about plan selection; on both versions EXPLAIN is nearly instantaneous. The plans are correspondingly

10.6

 
+------+-------------+-------+------+---------------+---------+---------+-------+------+----------+-------------------------------------------------+
 
| id   | select_type | table | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra                                           |
 
+------+-------------+-------+------+---------------+---------+---------+-------+------+----------+-------------------------------------------------+
 
|    1 | SIMPLE      | tbl0  | ALL  | NULL          | NULL    | NULL    | NULL  | 1314 |   100.00 |                                                 |
 
|    1 | SIMPLE      | tbl1  | ALL  | NULL          | NULL    | NULL    | NULL  | 1314 |   100.00 | Using where; Using join buffer (flat, BNL join) |
 
|    1 | SIMPLE      | tbl2  | ref  | a_ref_9       | a_ref_9 | 99      | const | 1    |   100.00 | Using where                                     |
 
|    1 | SIMPLE      | tbl5  | ALL  | NULL          | NULL    | NULL    | NULL  | 619  |   100.00 | Using join buffer (flat, BNL join)              |
 
+------+-------------+-------+------+---------------+---------+---------+-------+------+----------+-------------------------------------------------+

preview-10.10-optimizer 6f979f88c5 + patch from MDEV-28929

 
+------+-------------+-------+------+---------------+---------+---------+-------+------+----------+--------------------------------------------------------+
 
| id   | select_type | table | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra                                                  |
 
+------+-------------+-------+------+---------------+---------+---------+-------+------+----------+--------------------------------------------------------+
 
|    1 | SIMPLE      | tbl5  | ALL  | NULL          | NULL    | NULL    | NULL  | 619  |   100.00 |                                                        |
 
|    1 | SIMPLE      | tbl0  | ALL  | NULL          | NULL    | NULL    | NULL  | 1314 |   100.00 | Using join buffer (flat, BNL join)                     |
 
|    1 | SIMPLE      | tbl1  | ALL  | NULL          | NULL    | NULL    | NULL  | 1314 |   100.00 | Using where; Using join buffer (incremental, BNL join) |
 
|    1 | SIMPLE      | tbl2  | ref  | a_ref_9       | a_ref_9 | 99      | const | 1    |   100.00 | Using where                                            |
 
+------+-------------+-------+------+---------------+---------+---------+-------+------+----------+--------------------------------------------------------+



 Comments   
Comment by Sergei Petrunia [ 2022-07-11 ]

ANALYZE in 10.6

ANALYZE: {
 
  "query_block": {
 
    "select_id": 1,
 
    "r_loops": 1,
 
    "r_total_time_ms": 2881.971711,
 
    "table": {
 
      "table_name": "tbl0",
 
      "access_type": "ALL",
 
      "r_loops": 1,
 
      "rows": 1314,
 
      "r_rows": 1314,
 
      "r_table_time_ms": 216.52326,
 
      "r_other_time_ms": 2.414378573,
 
      "filtered": 100,
 
      "r_filtered": 100
 
    },
 
    "block-nl-join": {
 
      "table": {
 
        "table_name": "tbl1",
 
        "access_type": "ALL",
 
        "r_loops": 3,
 
        "rows": 1314,
 
        "r_rows": 1314,
 
        "r_table_time_ms": 652.4840785,
 
        "r_other_time_ms": 2010.371111,
 
        "filtered": 100,
 
        "r_filtered": 100
 
      },
 
      "buffer_type": "flat",
 
      "buffer_size": "256Kb",
 
      "join_type": "BNL",
 
      "attached_condition": "tbl1.a_str_1 = cast(tbl0.a_dte_3 as char(30) charset utf8mb3)",
 
      "r_filtered": 0
 
    },
 
...

Note tbl1 has r_filtered=0.

The filtering is done early.
We read tbl0, read tbl1, then join them which gives 1314*1314 record combinations, which gives ~2.8 sec total run time.

Comment by Sergei Petrunia [ 2022-07-11 ]

Thanks to SHOW ANALYZE, I don't need to wait till the query finishes on 10.10.

The zero-selectivity condition is checked at a later stage:

MariaDB [test]> show analyze format=json for 5\G
 
*************************** 1. row ***************************
 
SHOW ANALYZE: {
 
  "r_query_time_in_progress_ms": 386689,
 
  "query_block": {
 
    "select_id": 1,
 
    "r_loops": 1,
 
    "nested_loop": [
 
      {
 
        "table": {
 
          "table_name": "tbl5",
 
          "access_type": "ALL",
 
          "r_loops": 1,
 
          "rows": 619,
 
          "r_rows": 613,
 
          "r_table_time_ms": 27.99626565,
 
          "r_other_time_ms": 0.773400926,
 
          "filtered": 100,
 
          "r_filtered": 100
 
        }
 
      },
 
      {
 
        "block-nl-join": {
 
          "table": {
 
            "table_name": "tbl0",
 
            "access_type": "ALL",
 
            "r_loops": 1,
 
            "rows": 1314,
 
            "r_rows": 234,
 
            "r_table_time_ms": 34.85750579,
 
            "r_other_time_ms": 123.3221248,
 
            "filtered": 100,
 
            "r_filtered": 100
 
          },
 
          "buffer_type": "flat",
 
          "buffer_size": "256Kb",
 
          "join_type": "BNL",
 
          "r_filtered": 100
 
        }
 
      },
 
      {
 
        "block-nl-join": {
 
          "table": {
 
            "table_name": "tbl1",
 
            "access_type": "ALL",
 
            "r_loops": 260,
 
            "rows": 1314,
 
            "r_rows": 1310.303846,
 
            "r_table_time_ms": 50559.88911,
 
            "r_other_time_ms": 336830.8912,
 
            "filtered": 100,
 
            "r_filtered": 100
 
          },
 
          "buffer_type": "incremental",
 
          "buffer_size": "256Kb",
 
          "join_type": "BNL",
 
          "attached_condition": "tbl1.a_str_1 = cast(tbl0.a_dte_3 as char(30) charset utf8mb3)",
 
          "r_filtered": 0
 
        }
 
      },

tbl5 (613 rows) times tbl0 , times tbl1.

Comment by Sergei Petrunia [ 2022-07-11 ]

I think the cause is the same as in MDEV-29072.

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