[MDEV-16374] filtered shows 0 for materilization scan for a semi join, which makes optimizer always pick materialization scan over materialization lookup Created: 2018-06-02  Updated: 2018-06-09  Resolved: 2018-06-09

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.0, 10.1, 10.2, 10.3, 10.4
Fix Version/s: 10.0.36, 10.1.34, 10.2.16, 10.3.8, 10.4.0

Type: Bug Priority: Major
Reporter: Varun Gupta (Inactive) Assignee: Varun Gupta (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
PartOf
is part of MDEV-15253 Default optimizer setting changes for... Closed

 Description   

The dataset:

create table t0(a int);
 
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
 
create table t1 (a int, b int);
 
insert into t1 select A.a + B.a* 10 + C.a * 100,  A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C;


MariaDB [test]> set @@optimizer_use_condition_selectivity=2;
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
MariaDB [test]> explain format=json select * from t1 where a in (select max(a) from t1 group by b);
 
 
 
EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 | {
 
  "query_block": {
 
    "select_id": 1,
 
    "table": {
 
      "table_name": "<subquery2>",
 
      "access_type": "ALL",
 
      "possible_keys": ["distinct_key"],
 
      "rows": 1000,
 
      "filtered": 0,
 
      "materialized": {
 
        "unique": 1,
 
        "query_block": {
 
          "select_id": 2,
 
          "temporary_table": {
 
            "table": {
 
              "table_name": "t1",
 
              "access_type": "ALL",
 
              "rows": 1000,
 
              "filtered": 100
 
            }
 
          }
 
        }
 
      }
 
    },
 
    "block-nl-join": {
 
      "table": {
 
        "table_name": "t1",
 
        "access_type": "ALL",
 
        "rows": 1000,
 
        "filtered": 100
 
      },
 
      "buffer_type": "flat",
 
      "buffer_size": "256Kb",
 
      "join_type": "BNL",
 
      "attached_condition": "t1.a = `<subquery2>`.`max(a)`"
 
    }
 
  }
 
} |

If you look closely filtered=0 for the <subquery2> which means the optimizer predicted that no rows would be read. Due to this , the cost for such a plan would always be less than any other plan.

 Comments   
Comment by Varun Gupta (Inactive) [ 2018-06-02 ]

Introduced by this commit

commit 06ef0bdad254fbb2e826fabcd050134df2d11e75
 
Author:    Igor Babaev <igor@askmonty.org>  Tue Mar 18 22:30:50 2014
 
Committer:    Igor Babaev <igor@askmonty.org>  Tue Mar 18 22:30:50 2014
 
 
 
Fixed bug mdev-5191.
 
Corrected cost estimates when a join buffer is used and the optimizer is requested
 
to use condition selectivities.

Comment by Varun Gupta (Inactive) [ 2018-06-02 ]

Running the same query by using a derived table, we get the correct plan

explain format=json
 
select * from t1 ,(select max(a) as p from t1 group by b)q where t1.a=q.p ;
 
EXPLAIN
 
{
 
  "query_block": {
 
    "select_id": 1,
 
    "table": {
 
      "table_name": "t1",
 
      "access_type": "ALL",
 
      "rows": 1000,
 
      "filtered": 100,
 
      "attached_condition": "t1.a is not null"
 
    },
 
    "table": {
 
      "table_name": "<derived2>",
 
      "access_type": "ref",
 
      "possible_keys": ["key0"],
 
      "key": "key0",
 
      "key_length": "5",
 
      "used_key_parts": ["p"],
 
      "ref": ["test.t1.a"],
 
      "rows": 10,
 
      "filtered": 100,
 
      "materialized": {
 
        "query_block": {
 
          "select_id": 2,
 
          "filesort": {
 
            "sort_key": "t1.b",
 
            "temporary_table": {
 
              "table": {
 
                "table_name": "t1",
 
                "access_type": "ALL",
 
                "rows": 1000,
 
                "filtered": 100
 
              }
 
            }
 
          }
 
        }
 
      }
 
    }
 
  }
 
}

Comment by Varun Gupta (Inactive) [ 2018-06-04 ]

Patch:
http://lists.askmonty.org/pipermail/commits/2018-June/012591.html

Comment by Sergei Petrunia [ 2018-06-05 ]

There is this code in make_join_statistics() which fetches the number of rows in the materialized table:

    /* Approximate found rows and time to read them */
 
    if (s->table->is_filled_at_execution())
 
    {
 
      get_delayed_table_estimates(s->table, &s->records, &s->read_time,
 
                                  &s->startup_cost);
 
      s->found_records= s->records;
 
      table->quick_condition_rows=s->records;
 
    }
 
    else
 
    {
 
       s->scan_time();
 
    }

But then, the code in matching_candidates_in_table() uses table->stat_records():

  if (use_cond_selectivity > 1)
 
  {
 
    TABLE *table= s->table;
 
    double sel= table->cond_selectivity;
 
    double table_records= (double)table->stat_records();
 
    dbl_records= table_records * sel;
 
    return dbl_records;

Maybe it should it use s->records or s->found_records instead?

Comment by Sergei Petrunia [ 2018-06-05 ]

cond_selectivity is selectivity of condition w.r.t. the total number of rows in the table (that is, if the table has a range access, the cond_selectivity "includes" the selectivity of the range access). That is, the code in matching_candidates_in_table needs the number of rows in the table.

Looking at members in JOIN_TAB:

  /* Either #rows in the table or 1 for const table.  */
 
  ha_rows	records;

(Also one can see that the code in best_access_path and elsewhere assumes s->records is the total number of rows in the table)

Comment by Sergei Petrunia [ 2018-06-07 ]

Yesterday on the optimizer call agreed that

  • The patch should go into 10.0
  • On the question of whether the rows estimate should be stored just in JOIN_TAB::records or in table->stats.records too, decided that It should be put into table->file.stats.records (and the existing code will copy the value from there into JOIN_TAB::records). That is, Varun's patch is ok.
Comment by Varun Gupta (Inactive) [ 2018-06-09 ]

Pushed to 10.0

Generated at Thu Feb 08 08:28:26 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.