go-mysql-server
go-mysql-server copied to clipboard
Published
20 hours ago •
dolthub
dolthub
index ordering/index selection regression
Below is a specific query that dolt v0.35.4 plans with three index lookups. In dolt v.0.40.11 we only plan two lookups and the result is slow to execute. Adding the join order /*+ JOIN_ORDER(nd, sn, rn) */ restores the previous plan as a temporary fix.
Old plan (or new plan with /*+ JOIN_ORDER(nd, sn, rn) */ :
new_model_db> explain select DISTINCT nd.turbine_node_name FROM regulation_network rn INNER JOIN signaling_network sn ON sn.id = rn.downstream_edge_id INNER JOIN node_definition nd ON nd.id = sn.source_node_definition_id ORDER BY nd.turbine_node_name ASC;
+----------------------------------------------------------------------------------------------------------------+
| plan |
+----------------------------------------------------------------------------------------------------------------+
| Sort(nd.turbine_node_name ASC) |
| └─ Distinct |
| └─ Project(nd.turbine_node_name) |
| └─ IndexedJoin(nd.id = sn.source_node_definition_id) |
| ├─ Exchange |
| │ └─ TableAlias(nd) |
| │ └─ Table(node_definition) |
| └─ IndexedJoin(sn.id = rn.downstream_edge_id) |
| ├─ TableAlias(sn) |
| │ └─ IndexedTableAccess(signaling_network on [signaling_network.source_node_definition_id]) |
| └─ TableAlias(rn) |
| └─ IndexedTableAccess(regulation_network on [regulation_network.downstream_edge_id]) |
+----------------------------------------------------------------------------------------------------------------+
Current plan:
+----------------------------------------------------------------------------------------------------------------+
| plan |
+----------------------------------------------------------------------------------------------------------------+
| Sort(nd.turbine_node_name ASC) |
| └─ Distinct |
| └─ Project(nd.turbine_node_name) |
| └─ IndexedJoin(nd.id = sn.source_node_definition_id) |
| ├─ Exchange |
| │ └─ TableAlias(nd) |
| │ └─ Table(node_definition) |
| └─ IndexedJoin(sn.id = rn.downstream_edge_id) |
| ├─ Exchange |
| │ └─ TableAlias(rn) |
| │ └─ Table(regulation_network) |
| └─ TableAlias(sn) |
| └─ IndexedTableAccess(signaling_network on [signaling_network.source_node_definition_id]) |
+----------------------------------------------------------------------------------------------------------------+
