[MDEV-28712] join_cache.test shows plans that meaninglessly use rowid filtering Created: 2022-05-31  Updated: 2023-09-13

Status: Open
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.5, 10.6, 10.7, 10.8
Fix Version/s: 10.5, 10.6

Type: Bug Priority: Minor
Reporter: Sergei Petrunia Assignee: Oleg Smirnov
Resolution: Unresolved Votes: 0
Labels: None

Attachments: File a.test    
Issue Links:
Relates
relates to MDEV-27366 SIGSEGV in handler_index_cond_check o... In Review

 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"



 Comments   
Comment by Sergei Petrunia [ 2022-06-01 ]

CC: igor.

Comment by Oleg Smirnov [ 2022-06-02 ]

These combinations of "hash_ALL|filter" are gone after the MDEV-27366 patch. There are now only "hash_ALL" access.

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