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

LIMIT ROWS EXAMINED: query with NOT IN subquery and materialization+partial_match_rowid_merge examines over 900K rows while the limit is 17K

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Fixed
    • None
    • None
    • None
    • None

    Description

      The following query

      SELECT * FROM t1 AS alias1, t1 AS alias2 
      WHERE ( alias1.b, alias2.c ) NOT IN 
        ( SELECT d, a FROM t1, t2 ) 
      LIMIT ROWS_EXAMINED 17000

      on the test data (500 rows in t1, 320 rows in t2) examines ~903K rows before being interrupted.

      bzr version-info
      revision-id: timour@askmonty.org-20120305200233-lyj8vveiuwkm1nq2
      date: 2012-03-05 22:02:33 +0200
      build-date: 2012-03-08 19:42:33 +0400
      revno: 3453

      EXPLAIN:

      id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
      1       PRIMARY alias1  ALL     NULL    NULL    NULL    NULL    500     100.00
      1       PRIMARY alias2  ALL     NULL    NULL    NULL    NULL    500     100.00  Using where; Using join buffer (flat, BNL join)
      2       MATERIALIZED    t2      index   PRIMARY PRIMARY 4       NULL    320     100.00  Using index
      2       MATERIALIZED    t1      index   PRIMARY PRIMARY 4       NULL    500     100.00  Using index; Using join buffer (flat, BNL join)
      Warnings:
      Note    1003    select `test`.`alias1`.`a` AS `a`,`test`.`alias1`.`b` AS `b`,`test`.`alias1`.`c` AS `c`,`test`.`alias2`.`a` AS `a`,`test`.`alias2`.`b` AS `b`,`test`.`alias2`.`c` AS `c` from `test`.`t1` `alias1` join `test`.`t1` `alias2` where (not(<expr_cache><`test`.`alias1`.`b`,`test`.`alias2`.`c`>(<in_optimizer>((`test`.`alias1`.`b`,`test`.`alias2`.`c`),(`test`.`alias1`.`b`,`test`.`alias2`.`c`) in ( <materialize> (select `test`.`t2`.`d`,`test`.`t1`.`a` from `test`.`t1` join `test`.`t2` ), <primary_index_lookup>(`test`.`alias1`.`b` in <temporary table> on distinct_key where ((`test`.`alias1`.`b` = `<subquery2>`.`d`) and (`test`.`alias2`.`c` = `<subquery2>`.`a`))))))))

      Minimal optimizer_switch:

      materialization=on,partial_match_rowid_merge=on,subquery_cache=on

      Full optimizer_switch:

      index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on

      Switching off subquery_cache changes the number of examined rows a little, but does not eliminate the problem.

      Test case:

       
      SET optimizer_switch = 'materialization=on,partial_match_rowid_merge=on,subquery_cache=on';
       
      CREATE TABLE t1 (
        a INT AUTO_INCREMENT PRIMARY KEY,
        b INT, c INT 
      );
       
      # 500 rows		
      INSERT INTO t1 ( c, b ) VALUES 
      (132,209),(2,1),(1,4),(0,5),(0,7),(7,NULL),(3,NULL),(0,1),
      (8,7),(NULL,NULL),(7,6),(0,4),(NULL,2),(2,3),(6,3),
      (3,NULL),(7,8),(1,7),(6,0),(134,51),(7,6),(9,2),(0,8),
      (5,NULL),(NULL,2),(NULL,6),(NULL,5),(9,4),(1,4),(NULL,6),
      (6,0),(0,6),(1,2),(4,NULL),(1,NULL),(239,135),(148,18),
      (2,6),(4,6),(6,8),(2,4),(9,0),(6,NULL),(9,3),(3,3),(8,5),
      (1,5),(2,1),(6,8),(4,2),(0,6),(4,6),(1,3),(NULL,2),(9,4),
      (1,2),(0,1),(147,34),(5,2),(5,4),(8,3),(6,NULL),(3,2),
      (6,NULL),(9,2),(7,6),(80,227),(0,7),(9,NULL),(5,6),(0,2),
      (9,7),(0,NULL),(4,6),(6,5),(1,3),(0,5),(9,6),(1,7),(7,0),
      (9,6),(6,3),(3,4),(5,1),(3,4),(2,3),(NULL,8),(5,0),
      (81,187),(2,2),(6,NULL),(5,3),(0,NULL),(9,8),(9,2),
      (NULL,7),(8,6),(NULL,3),(8,4),(7,9),(4,8),(0,1),(NULL,2),
      (NULL,6),(4,4),(0,0),(3,9),(3,2),(243,17),(2,0),(2,0),
      (0,NULL),(9,9),(186,4),(4,NULL),(7,3),(8,2),(7,6),(181,15),
      (115,46),(9,1),(NULL,7),(9,2),(NULL,6),(5,3),(3,9),(9,4),
      (NULL,8),(130,129),(6,2),(2,8),(5,6),(3,4),(0,NULL),(0,4),
      (1,6),(NULL,5),(7,6),(NULL,9),(3,NULL),(2,5),(2,2),
      (8,NULL),(5,1),(2,9),(1,9),(0,3),(2,4),(8,7),(NULL,6),
      (0,8),(NULL,7),(7,1),(6,4),(8,8),(5,5),(8,5),(7,1),(0,8),
      (3,4),(2,5),(157,54),(0,5),(NULL,2),(5,7),(5,1),(7,2),
      (9,7),(212,78),(7,4),(NULL,0),(6,1),(1,0),(7,6),(6,7),
      (8,4),(0,2),(9,3),(8,1),(4,4),(6,9),(7,3),(132,59),
      (1,NULL),(0,5),(1,1),(4,2),(2,0),(2,5),(4,6),(5,4),(7,0),
      (221,163),(2,9),(NULL,5),(7,NULL),(3,7),(9,NULL),(NULL,2),
      (45,127),(1,2),(2,6),(8,7),(8,5),(NULL,4),(3,2),(7,5),
      (4,7),(4,2),(130,35),(3,0),(4,1),(1,6),(6,8),(9,2),(8,3),
      (9,7),(7,NULL),(6,1),(5,8),(3,4),(9,5),(4,0),(NULL,6),
      (6,9),(8,5),(1,0),(3,9),(1,1),(NULL,7),(8,9),(3,NULL),
      (1,2),(6,5),(3,5),(6,NULL),(3,7),(0,8),(4,9),(7,5),(6,5),
      (0,5),(2,3),(4,1),(0,4),(2,NULL),(4,1),(0,5),(0,5),(2,9),
      (5,6),(224,112),(9,5),(5,1),(6,NULL),(3,0),(2,2),(3,2),
      (3,5),(2,6),(6,4),(4,9),(NULL,9),(20,132),(8,8),(7,0),
      (6,5),(2,4),(104,101),(2,6),(7,2),(2,NULL),(7,6),(3,9),
      (8,9),(2,7),(0,NULL),(2,2),(3,6),(2,5),(2,6),(NULL,3),
      (6,6),(9,1),(21,NULL),(3,NULL),(1,3),(0,NULL),(9,7),(4,5),
      (6,1),(7,4),(NULL,6),(8,3),(180,89),(NULL,9),(8,NULL),
      (6,5),(2,0),(6,2),(3,0),(5,8),(2,1),(NULL,NULL),(0,NULL),
      (6,4),(4,4),(2,3),(9,2),(2,NULL),(5,0),(7,4),(0,8),(5,6),
      (8,4),(8,5),(6,6),(7,2),(243,71),(7,4),(4,1),(250,100),
      (1,4),(NULL,NULL),(5,9),(8,0),(2,2),(8,4),(5,NULL),
      (NULL,8),(NULL,NULL),(7,5),(2,0),(8,8),(5,8),(0,8),(9,3),
      (9,7),(1,6),(1,3),(1,4),(79,137),(3,5),(6,5),(130,29),
      (5,7),(5,3),(9,NULL),(9,8),(7,3),(8,7),(9,9),(9,NULL),
      (7,9),(8,9),(0,8),(1,2),(6,6),(0,8),(8,2),(NULL,7),
      (NULL,0),(2,2),(8,2),(9,0),(8,5),(6,8),(7,NULL),(NULL,2),
      (2,1),(1,8),(5,2),(NULL,2),(NULL,3),(9,9),(5,1),(9,5),
      (NULL,5),(NULL,6),(9,123),(5,0),(0,6),(NULL,0),(98,75),
      (5,9),(242,69),(7,8),(1,NULL),(25,51),(4,8),(8,5),(9,5),
      (NULL,3),(3,NULL),(5,NULL),(4,4),(6,8),(3,5),(NULL,5),
      (0,3),(4,8),(9,1),(1,9),(4,9),(3,5),(4,NULL),(0,NULL),
      (0,7),(4,5),(0,2),(9,8),(7,NULL),(152,245),(4,NULL),(7,7),
      (6,NULL),(NULL,9),(8,NULL),(5,6),(3,5),(3,3),(3,6),(4,3),
      (4,4),(5,2),(5,5),(9,4),(6,6),(5,4),(2,5),(5,5),(63,44),
      (4,9),(9,5),(2,6),(3,6),(5,3),(1,4),(5,0),(2,7),(1,8),
      (4,8),(2,1),(8,7),(NULL,NULL),(5,4),(4,9),(7,6),(9,4),
      (4,NULL),(4,3),(4,2),(6,7),(5,8),(2,8),(7,4),(NULL,9),
      (7,3),(32,51),(7,7),(8,7),(8,NULL),(7,8),(1,1),(7,9),(9,4),
      (NULL,3),(1,2),(9,NULL),(2,2),(9,NULL),(2,6),(4,7),(0,2),
      (4,5),(8,7),(NULL,6),(NULL,6),(0,2),(NULL,9),(8,6),(8,0),
      (6,3),(2,8),(3,8),(1,6),(NULL,0),(1,NULL),(10,158),(2,5),
      (103,163),(3,2),(6,8),(2,0),(7,9),(2,3),(2,1),(4,9),
      (6,NULL),(6,5);
       
      CREATE TABLE t2 ( d INT AUTO_INCREMENT PRIMARY KEY );
       
      # 320 rows
      INSERT INTO t2 VALUES 
        (NULL),(NULL),(NULL),(NULL),(NULL),
        (NULL),(NULL),(NULL),(NULL),(NULL);
      INSERT INTO t2 SELECT NULL FROM t2;
      INSERT INTO t2 SELECT NULL FROM t2;
      INSERT INTO t2 SELECT NULL FROM t2;
      INSERT INTO t2 SELECT NULL FROM t2;
      INSERT INTO t2 SELECT NULL FROM t2;
       
      SELECT * FROM t1 AS alias1, t1 AS alias2 
      WHERE ( alias1.b, alias2.c ) NOT IN 
        ( SELECT d, a FROM t1, t2 ) 
      LIMIT ROWS_EXAMINED 17000;

      Attachments

        Issue Links

          Activity

            People

              timour Timour Katchaounov (Inactive)
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.