[MDEV-27883] reordering of full joins with left joins causes massive table scans Created: 2022-02-18  Updated: 2023-12-13

Status: Open
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.5.15
Fix Version/s: 10.5

Type: Bug Priority: Critical
Reporter: Marc Assignee: Rex Johnston
Resolution: Unresolved Votes: 0
Labels: None


 Description   

I have several queries where the optimizer reoders the tables which result in massive table scans.
I have several tables (all with indices) which are full joined as 1:1, but the optimizer prefers to sort it by a left joins and therefore can't use the indices given. I tried to reproduce this on simple tables, but I was not successfull. See the following query and the query plan:

SELECT COUNT(*) OVER() AS cnt FROM rdMPGGeraet g JOIN
 
rdMPGBezeichnung b ON (g.BezeichnungID=b.ID) JOIN
 
rdMPGGeraeteTyp t ON (b.GeraeteTyp=t.ID) JOIN
 
rdMPGAdresse h ON (b.Hersteller=h.ID) LEFT JOIN
 
rdFunkruf rf ON (rf.OrganisationID IN(103) AND rf.ID=(IF(g.Funkruf=0,g.StammFunkrufID,g.Funkruf))) LEFT JOIN
 
rdFahrzeug f ON (f.OrganisationID IN(103) AND f.ID=COALESCE(rf.Fahrzeug,rf.Stammfahrzeug,g.FesteinbauFID)) LEFT JOIN
 
rdWache w ON (w.OrganisationID IN(103) AND w.ID=COALESCE(rf.Wache,f.Wache,g.werkstatt)) LEFT JOIN
 
qmBereich br ON (br.OrganisationID IN(103) AND br.ID=w.HauptbereichID) LEFT JOIN
 
rdWache werk ON (werk.OrganisationID IN(103) AND werk.ID=g.werkstatt) LEFT JOIN
 
rdMPGClusterValues c ON (c.OrganisationID IN(103) AND g.ClusterID=c.ID) WHERE g.OrganisationID IN(103)  AND (w.ID=1119 OR g.werkstatt=1119) AND b.Einweisung="unterweisungspflichtig" AND ISNULL(g.del) LIMIT 1

The query plan is as folows:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE g ref einweisungPfl,BezeichnungID,werkstatt,ClusterID,del ClusterID 2 const 656 Using where; Using temporary
1 SIMPLE rf eq_ref PRIMARY,Wache PRIMARY 2 func 1 Using where
1 SIMPLE f eq_ref PRIMARY,Status PRIMARY 2 func 1 Using where
1 SIMPLE w eq_ref PRIMARY,Ressource,Shop,Strassensperrung PRIMARY 2 func 1 Using where
1 SIMPLE t index PRIMARY PRIMARY 3 NULL 2457 Using index; Using join buffer (flat, BNL join)
1 SIMPLE b ref PRIMARY,Hersteller,GeraeteTyp GeraeteTyp 3 qmsystems.t.ID 2 Using where
1 SIMPLE h eq_ref PRIMARY PRIMARY 3 qmsystems.b.Hersteller 1 Using index


 Comments   
Comment by Marc [ 2022-02-18 ]

as you can see reordering of b and t causes the table scan.
From the original query you can see, t requries to have b in advance so both queries are mapped 1:1. If they are reordered no condition matches for t which causes a full table scan!

Comment by Marc [ 2022-02-21 ]

Only Unique/Primary indices causes this. If I add some normal index to "t" which contains the column "ID", no table scan is generated. Unique indexes should do better than that.

Comment by Sergei Golubchik [ 2022-02-22 ]

are all tables analyzed? can you show the output from EXPLAIN EXTENDED? And EXPLAIN FORMAT=JSON too?

Comment by Marc [ 2022-02-22 ]

Sure (same query, but some more data today)

id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE g ref einweisungPfl,werkstatt,ClusterID,del einweisungPfl 2 const 710 80.72 Using index condition; Using temporary
1 SIMPLE rf eq_ref PRIMARY,Wache PRIMARY 2 func 1 3.64 Using where
1 SIMPLE f eq_ref PRIMARY,Status PRIMARY 2 func 1 7.43 Using where
1 SIMPLE w eq_ref PRIMARY,Ressource,Shop,Strassensperrung PRIMARY 2 func 1 1.19 Using where
1 SIMPLE t index PRIMARY PRIMARY 3 NULL 2460 100.00 Using index; Using join buffer (flat, BNL join)
1 SIMPLE b eq_ref PRIMARY,Hersteller PRIMARY 3 import.g.BezeichnungID 1 100.00 Using where
1 SIMPLE h eq_ref PRIMARY PRIMARY 3 import.b.Hersteller 1 100.00 Using index 

 
 
  "query_block": {
 
    "select_id": 1,
 
    "window_functions_computation": {
 
      "sorts": {
 
        "filesort": {
 
          "sort_key": "cnt"
 
        }
 
      },
 
      "temporary_table": {
 
        "table": {
 
          "table_name": "g",
 
          "access_type": "ref",
 
          "possible_keys": ["einweisungPfl", "werkstatt", "ClusterID", "del"],
 
          "key": "einweisungPfl",
 
          "key_length": "2",
 
          "used_key_parts": ["OrganisationID"],
 
          "ref": ["const"],
 
          "rows": 710,
 
          "filtered": 80.72267914,
 
          "index_condition": "g.del is null"
 
        },
 
        "table": {
 
          "table_name": "rf",
 
          "access_type": "eq_ref",
 
          "possible_keys": ["PRIMARY", "Wache"],
 
          "key": "PRIMARY",
 
          "key_length": "2",
 
          "used_key_parts": ["ID"],
 
          "ref": ["func"],
 
          "rows": 1,
 
          "filtered": 3.63860631,
 
          "attached_condition": "trigcond(rf.OrganisationID = 103 and rf.`ID` = if(g.Funkruf = 0,g.StammFunkrufID,g.Funkruf))"
 
        },
 
        "table": {
 
          "table_name": "f",
 
          "access_type": "eq_ref",
 
          "possible_keys": ["PRIMARY", "Status"],
 
          "key": "PRIMARY",
 
          "key_length": "2",
 
          "used_key_parts": ["ID"],
 
          "ref": ["func"],
 
          "rows": 1,
 
          "filtered": 7.42935276,
 
          "attached_condition": "trigcond(f.OrganisationID = 103 and f.`ID` = coalesce(rf.Fahrzeug,rf.Stammfahrzeug,g.FesteinbauFID))"
 
        },
 
        "table": {
 
          "table_name": "w",
 
          "access_type": "eq_ref",
 
          "possible_keys": ["PRIMARY", "Ressource", "Shop", "Strassensperrung"],
 
          "key": "PRIMARY",
 
          "key_length": "2",
 
          "used_key_parts": ["ID"],
 
          "ref": ["func"],
 
          "rows": 1,
 
          "filtered": 1.193520904,
 
          "attached_condition": "trigcond(w.`ID` = 1119 or g.werkstatt = 1119) and trigcond(w.OrganisationID = 103 and w.`ID` = coalesce(rf.Wache,f.Wache,g.werkstatt))"
 
        },
 
        "block-nl-join": {
 
          "table": {
 
            "table_name": "t",
 
            "access_type": "index",
 
            "possible_keys": ["PRIMARY"],
 
            "key": "PRIMARY",
 
            "key_length": "3",
 
            "used_key_parts": ["ID"],
 
            "rows": 2460,
 
            "filtered": 100,
 
            "using_index": true
 
          },
 
          "buffer_type": "flat",
 
          "buffer_size": "49Kb",
 
          "join_type": "BNL"
 
        },
 
        "table": {
 
          "table_name": "b",
 
          "access_type": "eq_ref",
 
          "possible_keys": ["PRIMARY", "Hersteller"],
 
          "key": "PRIMARY",
 
          "key_length": "3",
 
          "used_key_parts": ["ID"],
 
          "ref": ["import.g.BezeichnungID"],
 
          "rows": 1,
 
          "filtered": 100,
 
          "attached_condition": "b.GeraeteTyp = t.`ID` and b.Einweisung = 'unterweisungspflichtig'"
 
        },
 
        "table": {
 
          "table_name": "h",
 
          "access_type": "eq_ref",
 
          "possible_keys": ["PRIMARY"],
 
          "key": "PRIMARY",
 
          "key_length": "3",
 
          "used_key_parts": ["ID"],
 
          "ref": ["import.b.Hersteller"],
 
          "rows": 1,
 
          "filtered": 100,
 
          "using_index": true
 
        }
 
      }
 
    }
 
  }
 
}

Same query with duplicated indexes (DROP_INDEX_ID is always INDEX(ID) on PRIMARY(ID) )

1 SIMPLE g ref einweisungPfl,werkstatt,ClusterID,del ClusterID 2 const 1068 93.84 Using where; Using temporary
1 SIMPLE rf eq_ref PRIMARY,Wache PRIMARY 2 func 1 1.60 Using where
1 SIMPLE f eq_ref PRIMARY,Status PRIMARY 2 func 1 4.15 Using where
1 SIMPLE w eq_ref PRIMARY,Ressource,Shop,Strassensperrung PRIMARY 2 func 1 1.19 Using where
1 SIMPLE b ref PRIMARY,Hersteller,DROP_IX_ID DROP_IX_ID 3 qmsystems.g.BezeichnungID 1 100.00 Using where
1 SIMPLE t ref PRIMARY,DROP_INDEX_ID DROP_INDEX_ID 3 qmsystems.b.GeraeteTyp 1 100.00 Using index
1 SIMPLE h ref PRIMARY,DROP_INDEX_ID DROP_INDEX_ID 3 qmsystems.b.Hersteller 1 100.00 Using index

e.g.

CREATE TABLE `rdMPGGeraeteTyp` (
 
  `ID` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
 
  `OrganisationID` smallint(5) unsigned NOT NULL,
 
  `Name` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
 
  PRIMARY KEY (`ID`),
 
  UNIQUE KEY `OrganisationID` (`OrganisationID`,`Name`) USING BTREE,
 
  KEY `DROP_INDEX_ID` (`ID`)
 
) ENGINE=Aria AUTO_INCREMENT=2744 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci PACK_KEYS=1 CHECKSUM=1 PAGE_CHECKSUM=1 ROW_FORMAT=PAGE TRANSACTIONAL=0

Comment by Marc [ 2022-06-13 ]

still valid in mariaDB 10.5.16

The query performs much better if I replace "JOIN" with "LEFT JOIN". With JOIN I give the optimizer more possibilities. But JOINS should perform better than left joins.

Comment by Marc [ 2022-07-26 ]

I assume these two issues are connected: MDEV-27152

Generated at Thu Feb 08 09:56:19 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.