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

join_cache.test shows plans that meaninglessly use rowid filtering

    XMLWordPrintable

Details

    Description

      Filing this in connection with MDEV-27366.

      This MDEV entry doesn't need a fix, it is just to record a proof that join_cache.result contained meaningless query plans.

      join_cache.result shows query plan that use Rowid Filtering where it obviously doesn't make any sense to do it.

      I claim that all EXPLAIN output at these lines do not make sense:

      856:1   SIMPLE  CountryLanguage hash_ALL|filter PRIMARY,Percentage      #hash#PRIMARY|Percentage        3|4     world.City.Country      984 (19%)       Using where; Using join buffer (flat, BNLH join); Using rowid filter
      1056:1  SIMPLE  CountryLanguage hash_ALL|filter PRIMARY,Percentage      #hash#PRIMARY|Percentage        3|4     world.City.Country      984 (19%)       Using where; Using join buffer (incremental, BNLH join); Using rowid filter
      2107:1  SIMPLE  CountryLanguage hash_ALL|filter PRIMARY,Percentage      #hash#PRIMARY|Percentage        3|4     world.City.Country      984 (19%)       Using where; Using join buffer (flat, BNLH join); Using rowid filter
      2211:1  SIMPLE  CountryLanguage hash_ALL|filter PRIMARY,Percentage      #hash#PRIMARY|Percentage        3|4     world.City.Country      984 (19%)       Using where; Using join buffer (incremental, BNLH join); Using rowid filter
      

      Let's analyze the first case. I'm attaching a smaller test file that contains just that query.

      Running it, one can see:

      id      1
      select_type     SIMPLE
      table   CountryLanguage
      type    hash_ALL|filter
      possible_keys   PRIMARY,Percentage
      key     #hash#PRIMARY|Percentage
      key_len 3|4
      ref     world.City.Country
      rows    984 (19%)
      Extra   Using where; Using join buffer (flat, BNLH join); Using rowid filter
      

      This means it is going to use Rowid Filter together with a full table (not index!) scan. The table is MyISAM.

      Relevant portion of ANALYZE output:

            "table": {
              "table_name": "CountryLanguage",
              "access_type": "hash_ALL",
              "possible_keys": ["PRIMARY", "Percentage"],
              "key": "#hash#PRIMARY",
              "key_length": "3",
              "used_key_parts": ["Country"],
              "ref": ["world.City.Country"],
              "rowid_filter": {
                "range": {
                  "key": "Percentage",
                  "used_key_parts": ["Percentage"]
                },
                "rows": 185,
                "selectivity_pct": 18.80081301,
                "r_rows": 168,
                "r_selectivity_pct": 0,
                "r_buffer_size": 147,
                "r_filling_time_ms": 0.086968316
              },
              "r_loops": 1,
              "rows": 984,
              "r_rows": 984,
              "r_table_time_ms": 0.187748105,
              "r_other_time_ms": 0.200407506,
              "filtered": 18.80081367,
              "r_filtered": 17.07317073,
              "attached_condition": "CountryLanguage.Percentage > 50"
            },
      

      Note that

      • rowid filter is indeed built
      • Then, it is not used: it has r_selectivity_pct=0, but the table has r_rows>0 and r_filtered>0. This can only occur when filter was not used.

      I've verified in debugger that CountryLanguage is read using full table (not index) scan:

        #1  0x0000555556a6353e in ha_myisam::rnd_next (this=0x7fffd402d540, buf=0x7fffd402d0e0 "\377AFGPashto", ' ' <repeats 24 times>, "\232\231QB", '\245' <repeats 42 times>, "\240\321\002\324\377\177") at /home/psergey/dev-git2/10.5-look/storage/myisam/ha_myisam.cc:2041
        #2  0x00005555561f5336 in handler::ha_rnd_next (this=0x7fffd402d540, buf=0x7fffd402d0e0 "\377AFGPashto", ' ' <repeats 24 times>, "\232\231QB", '\245' <repeats 42 times>, "\240\321\002\324\377\177") at /home/psergey/dev-git2/10.5-look/sql/handler.cc:3096
        #3  0x00005555563b249b in rr_sequential (info=0x7fffd40c6810) at /home/psergey/dev-git2/10.5-look/sql/records.cc:519
        #4  0x0000555555dcad93 in READ_RECORD::read_record (this=0x7fffd40c6810) at /home/psergey/dev-git2/10.5-look/sql/records.h:80
        #5  0x00005555560822ab in JOIN_TAB_SCAN::next (this=0x7fffd40cadd0) at /home/psergey/dev-git2/10.5-look/sql/sql_join_cache.cc:3462
        #6  0x0000555556080671 in JOIN_CACHE::join_matching_records (this=0x7fffd40cab90, skip_last=false) at /home/psergey/dev-git2/10.5-look/sql/sql_join_cache.cc:2338
        #7  0x00005555560800b7 in JOIN_CACHE::join_records (this=0x7fffd40cab90, skip_last=false) at /home/psergey/dev-git2/10.5-look/sql/sql_join_cache.cc:2151
        #8  0x0000555555f078c6 in sub_select_cache (join=0x7fffd401c3c0, join_tab=0x7fffd40c6748, end_of_records=true) at /home/psergey/dev-git2/10.5-look/sql/sql_select.cc:20690
        #9  0x0000555555f07ad1 in sub_select (join=0x7fffd401c3c0, join_tab=0x7fffd40c63a0, end_of_records=true) at /home/psergey/dev-git2/10.5-look/sql/sql_select.cc:20861
        #10 0x0000555555f078f2 in sub_select_cache (join=0x7fffd401c3c0, join_tab=0x7fffd40c63a0, end_of_records=true) at /home/psergey/dev-git2/10.5-look/sql/sql_select.cc:20693
        #11 0x0000555555f07ad1 in sub_select (join=0x7fffd401c3c0, join_tab=0x7fffd40c5ff8, end_of_records=true) at /home/psergey/dev-git2/10.5-look/sql/sql_select.cc:20861
        #12 0x0000555555f071d7 in do_select (join=0x7fffd401c3c0, procedure=0x0) at /home/psergey/dev-git2/10.5-look/sql/sql_select.cc:20450
        #13 0x0000555555eda76c in JOIN::exec_inner (this=0x7fffd401c3c0) at /home/psergey/dev-git2/10.5-look/sql/sql_select.cc:4540
        #14 0x0000555555ed9875 in JOIN::exec (this=0x7fffd401c3c0) at /home/psergey/dev-git2/10.5-look/sql/sql_select.cc:4320
        #15 0x0000555555edb0ea in mysql_select (thd=0x7fffd4000d78, tables=0x7fffd40184c0, fields=..., conds=0x7fffd401a3e8, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fffd401c398, unit=0x7fffd4004f20, select_lex=0x7fffd4017bd0) at /home/psergey/dev-git2/10.5-look/sql/sql_select.cc:4797
        #16 0x0000555555eca8a0 in handle_select (thd=0x7fffd4000d78, lex=0x7fffd4004e58, result=0x7fffd401c398, setup_tables_done_option=0) at /home/psergey/dev-git2/10.5-look/sql/sql_select.cc:444
        #17 0x0000555555e8d058 in execute_sqlcom_select (thd=0x7fffd4000d78, all_tables=0x7fffd40184c0) at /home/psergey/dev-git2/10.5-look/sql/sql_parse.cc:6330
        #18 0x0000555555e84279 in mysql_execute_command (thd=0x7fffd4000d78) at /home/psergey/dev-git2/10.5-look/sql/sql_parse.cc:4021
        #19 0x0000555555e91e7d in mysql_parse (thd=0x7fffd4000d78, rawbuf=0x7fffd40178e0 "analyze format=json\nSELECT City.Name, Country.Name, CountryLanguage.Language\nFROM City,Country,CountryLanguage\nWHERE City.Country=Country.Code AND\nCountryLanguage.Country=Country.Code AND\nCity.Name LI"..., length=321, parser_state=0x7ffff0741510, is_com_multi=false, is_next_command=false) at /home/psergey/dev-git2/10.5-look/sql/sql_parse.cc:8116
        #20 0x0000555555e7ddcc in dispatch_command (command=COM_QUERY, thd=0x7fffd4000d78, packet=0x7fffd400b589 "analyze format=json\nSELECT City.Name, Country.Name, CountryLanguage.Language\nFROM City,Country,CountryLanguage\nWHERE City.Country=Country.Code AND\nCountryLanguage.Country=Country.Code AND\nCity.Name LI"..., packet_length=321, is_com_multi=false, is_next_command=false) at /home/psergey/dev-git2/10.5-look/sql/sql_parse.cc:1907
        #21 0x0000555555e7c46a in do_command (thd=0x7fffd4000d78) at /home/psergey/dev-git2/10.5-look/sql/sql_parse.cc:1375
        #22 0x000055555602a767 in do_handle_one_connection (connect=0x5555589123e8, put_in_cache=true) at /home/psergey/dev-git2/10.5-look/sql/sql_connect.cc:1418
        #23 0x000055555602a426 in handle_one_connection (arg=0x5555589123e8) at /home/psergey/dev-git2/10.5-look/sql/sql_connect.cc:1312
        #24 0x000055555653c10b in pfs_spawn_thread (arg=0x555558848978) at /home/psergey/dev-git2/10.5-look/storage/perfschema/pfs.cc:2201
        #25 0x00007ffff63346db in start_thread (arg=0x7ffff0742700) at pthread_create.c:463
        #26 0x00007ffff551a61f in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95
      (gdb) up
        #1  0x0000555556a6353e in ha_myisam::rnd_next (this=0x7fffd402d540, buf=0x7fffd402d0e0 "\377AFGPashto", ' ' <repeats 24 times>, "\232\231QB", '\245' <repeats 42 times>, "\240\321\002\324\377\177") at /home/psergey/dev-git2/10.5-look/storage/myisam/ha_myisam.cc:2041
      (gdb) p table->alias
        $21 = {<Charset> = {m_charset = 0x5555578563e0 <my_charset_bin>}, <Binary_string> = {<Static_binary_string> = {<Sql_alloc> = {<No data fields>}, Ptr = 0x7fffd4078988 "CountryLanguage", str_length = 15}, Alloced_length = 16, extra_alloc = 0, alloced = true, thread_specific = false}, <No data fields>}
      (gdb) p table->alias.Ptr
        $23 = 0x7fffd4078988 "CountryLanguage"
      

      Attachments

        1. a.test
          1 kB
          Sergei Petrunia

        Issue Links

          Activity

            People

              oleg.smirnov Oleg Smirnov
              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.