{ "steps": [ { "join_preparation": { "select_id": 1, "steps": [ { "join_preparation": { "select_id": 2, "steps": [ { "expanded_query": "/* select#2 */ select min(PARTSUPP.PS_SUPPLYCOST) from PARTSUPP join SUPPLIER join NATION join REGION where PART.P_PARTKEY = PARTSUPP.PS_PARTKEY and SUPPLIER.S_SUPPKEY = PARTSUPP.PS_SUPPKEY and SUPPLIER.S_NATIONKEY = NATION.N_NATIONKEY and NATION.N_REGIONKEY = REGION.R_REGIONKEY and REGION.R_NAME = 'ASIA'" } ] } }, { "expanded_query": "/* select#1 */ select SUPPLIER.S_ACCTBAL AS s_acctbal,SUPPLIER.S_NAME AS s_name,NATION.N_NAME AS n_name,PART.P_PARTKEY AS p_partkey,PART.P_MFGR AS p_mfgr,SUPPLIER.S_ADDRESS AS s_address,SUPPLIER.S_PHONE AS s_phone,SUPPLIER.S_COMMENT AS s_comment from PART join SUPPLIER join PARTSUPP join NATION join REGION where PART.P_PARTKEY = PARTSUPP.PS_PARTKEY and SUPPLIER.S_SUPPKEY = PARTSUPP.PS_SUPPKEY and PART.P_SIZE = 30 and PART.P_TYPE like '%STEEL' and SUPPLIER.S_NATIONKEY = NATION.N_NATIONKEY and NATION.N_REGIONKEY = REGION.R_REGIONKEY and REGION.R_NAME = 'ASIA' and PARTSUPP.PS_SUPPLYCOST = (/* select#2 */ select min(PARTSUPP.PS_SUPPLYCOST) from PARTSUPP join SUPPLIER join NATION join REGION where PART.P_PARTKEY = PARTSUPP.PS_PARTKEY and SUPPLIER.S_SUPPKEY = PARTSUPP.PS_SUPPKEY and SUPPLIER.S_NATIONKEY = NATION.N_NATIONKEY and NATION.N_REGIONKEY = REGION.R_REGIONKEY and REGION.R_NAME = 'ASIA') order by SUPPLIER.S_ACCTBAL desc,NATION.N_NAME,SUPPLIER.S_NAME,PART.P_PARTKEY limit 100" } ] } }, { "join_optimization": { "select_id": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "PART.P_PARTKEY = PARTSUPP.PS_PARTKEY and SUPPLIER.S_SUPPKEY = PARTSUPP.PS_SUPPKEY and PART.P_SIZE = 30 and PART.P_TYPE like '%STEEL' and SUPPLIER.S_NATIONKEY = NATION.N_NATIONKEY and NATION.N_REGIONKEY = REGION.R_REGIONKEY and REGION.R_NAME = 'ASIA' and PARTSUPP.PS_SUPPLYCOST = (/* select#2 */ select min(PARTSUPP.PS_SUPPLYCOST) from PARTSUPP join SUPPLIER join NATION join REGION where PART.P_PARTKEY = PARTSUPP.PS_PARTKEY and SUPPLIER.S_SUPPKEY = PARTSUPP.PS_SUPPKEY and SUPPLIER.S_NATIONKEY = NATION.N_NATIONKEY and NATION.N_REGIONKEY = REGION.R_REGIONKEY and REGION.R_NAME = 'ASIA')", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "PART.P_TYPE like '%STEEL' and PARTSUPP.PS_SUPPLYCOST = (/* select#2 */ select min(PARTSUPP.PS_SUPPLYCOST) from PARTSUPP join SUPPLIER join NATION join REGION where PART.P_PARTKEY = PARTSUPP.PS_PARTKEY and SUPPLIER.S_SUPPKEY = PARTSUPP.PS_SUPPKEY and SUPPLIER.S_NATIONKEY = NATION.N_NATIONKEY and NATION.N_REGIONKEY = REGION.R_REGIONKEY and REGION.R_NAME = 'ASIA') and multiple equal(PART.P_PARTKEY, PARTSUPP.PS_PARTKEY) and multiple equal(SUPPLIER.S_SUPPKEY, PARTSUPP.PS_SUPPKEY) and multiple equal(30, PART.P_SIZE) and multiple equal(SUPPLIER.S_NATIONKEY, NATION.N_NATIONKEY) and multiple equal(NATION.N_REGIONKEY, REGION.R_REGIONKEY) and multiple equal('ASIA', REGION.R_NAME)" }, { "transformation": "constant_propagation", "resulting_condition": "PART.P_TYPE like '%STEEL' and PARTSUPP.PS_SUPPLYCOST = (/* select#2 */ select min(PARTSUPP.PS_SUPPLYCOST) from PARTSUPP join SUPPLIER join NATION join REGION where PART.P_PARTKEY = PARTSUPP.PS_PARTKEY and SUPPLIER.S_SUPPKEY = PARTSUPP.PS_SUPPKEY and SUPPLIER.S_NATIONKEY = NATION.N_NATIONKEY and NATION.N_REGIONKEY = REGION.R_REGIONKEY and REGION.R_NAME = 'ASIA') and multiple equal(PART.P_PARTKEY, PARTSUPP.PS_PARTKEY) and multiple equal(SUPPLIER.S_SUPPKEY, PARTSUPP.PS_SUPPKEY) and multiple equal(30, PART.P_SIZE) and multiple equal(SUPPLIER.S_NATIONKEY, NATION.N_NATIONKEY) and multiple equal(NATION.N_REGIONKEY, REGION.R_REGIONKEY) and multiple equal('ASIA', REGION.R_NAME)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "PART.P_TYPE like '%STEEL' and PARTSUPP.PS_SUPPLYCOST = (/* select#2 */ select min(PARTSUPP.PS_SUPPLYCOST) from PARTSUPP join SUPPLIER join NATION join REGION where PART.P_PARTKEY = PARTSUPP.PS_PARTKEY and SUPPLIER.S_SUPPKEY = PARTSUPP.PS_SUPPKEY and SUPPLIER.S_NATIONKEY = NATION.N_NATIONKEY and NATION.N_REGIONKEY = REGION.R_REGIONKEY and REGION.R_NAME = 'ASIA') and multiple equal(PART.P_PARTKEY, PARTSUPP.PS_PARTKEY) and multiple equal(SUPPLIER.S_SUPPKEY, PARTSUPP.PS_SUPPKEY) and multiple equal(30, PART.P_SIZE) and multiple equal(SUPPLIER.S_NATIONKEY, NATION.N_NATIONKEY) and multiple equal(NATION.N_REGIONKEY, REGION.R_REGIONKEY) and multiple equal('ASIA', REGION.R_NAME)" } ] } }, { "table_dependencies": [ { "table": "PART", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [] }, { "table": "SUPPLIER", "row_may_be_null": false, "map_bit": 1, "depends_on_map_bits": [] }, { "table": "PARTSUPP", "row_may_be_null": false, "map_bit": 2, "depends_on_map_bits": [] }, { "table": "NATION", "row_may_be_null": false, "map_bit": 3, "depends_on_map_bits": [] }, { "table": "REGION", "row_may_be_null": false, "map_bit": 4, "depends_on_map_bits": [] } ] }, { "ref_optimizer_key_uses": [ { "table": "PART", "index": "PRIMARY", "field": "P_PARTKEY", "equals": "PARTSUPP.PS_PARTKEY", "null_rejecting": false }, { "table": "SUPPLIER", "index": "PRIMARY", "field": "S_SUPPKEY", "equals": "PARTSUPP.PS_SUPPKEY", "null_rejecting": false }, { "table": "SUPPLIER", "index": "SUPPLIER_NATION_FKIDX", "field": "S_NATIONKEY", "equals": "NATION.N_NATIONKEY", "null_rejecting": true }, { "table": "SUPPLIER", "index": "SUPPLIER_NATION_FKIDX", "field": "S_SUPPKEY", "equals": "PARTSUPP.PS_SUPPKEY", "null_rejecting": false }, { "table": "PARTSUPP", "index": "PRIMARY", "field": "PS_PARTKEY", "equals": "PART.P_PARTKEY", "null_rejecting": false }, { "table": "PARTSUPP", "index": "PRIMARY", "field": "PS_SUPPKEY", "equals": "SUPPLIER.S_SUPPKEY", "null_rejecting": false }, { "table": "PARTSUPP", "index": "PARTSUPP_PART_FKIDX", "field": "PS_PARTKEY", "equals": "PART.P_PARTKEY", "null_rejecting": false }, { "table": "PARTSUPP", "index": "PARTSUPP_PART_FKIDX", "field": "PS_SUPPKEY", "equals": "SUPPLIER.S_SUPPKEY", "null_rejecting": false }, { "table": "PARTSUPP", "index": "PARTSUPP_SUPPLIER_FKIDX", "field": "PS_SUPPKEY", "equals": "SUPPLIER.S_SUPPKEY", "null_rejecting": false }, { "table": "PARTSUPP", "index": "PARTSUPP_SUPPLIER_FKIDX", "field": "PS_PARTKEY", "equals": "PART.P_PARTKEY", "null_rejecting": false }, { "table": "NATION", "index": "PRIMARY", "field": "N_NATIONKEY", "equals": "SUPPLIER.S_NATIONKEY", "null_rejecting": true }, { "table": "NATION", "index": "NATION_REGIONKEY_FKIDX", "field": "N_REGIONKEY", "equals": "REGION.R_REGIONKEY", "null_rejecting": true }, { "table": "NATION", "index": "NATION_REGIONKEY_FKIDX", "field": "N_NATIONKEY", "equals": "SUPPLIER.S_NATIONKEY", "null_rejecting": true }, { "table": "REGION", "index": "PRIMARY", "field": "R_REGIONKEY", "equals": "NATION.N_REGIONKEY", "null_rejecting": true } ] }, { "rows_estimation": [ { "selectivity_for_indexes": [], "selectivity_for_columns": [], "cond_selectivity": 1 }, { "table": "PART", "table_scan": { "rows": 198268, "read_cost": 27.709804, "read_and_compare_cost": 34.05438 } }, { "table": "SUPPLIER", "table_scan": { "rows": 9733, "read_cost": 1.4226458, "read_and_compare_cost": 1.7341018 } }, { "table": "PARTSUPP", "table_scan": { "rows": 788022, "read_cost": 110.5545508, "read_and_compare_cost": 135.7712548 } }, { "table": "NATION", "table_scan": { "rows": 25, "read_cost": 0.0141298, "read_and_compare_cost": 0.0149298 } }, { "selectivity_for_indexes": [], "selectivity_for_columns": [], "cond_selectivity": 1 }, { "table": "REGION", "table_scan": { "rows": 5, "read_cost": 0.0115098, "read_and_compare_cost": 0.0116698 } } ] }, { "considered_execution_plans": [ { "plan_prefix": "", "get_costs_for_tables": [ { "best_access_path": { "table": "REGION", "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "scan", "rows": 5, "rows_after_filter": 5, "rows_out": 5, "cost": 0.0116698, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5, "rows_out": 5, "cost": 0.0116698, "uses_join_buffering": false } } }, { "best_access_path": { "table": "NATION", "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "scan", "rows": 25, "rows_after_filter": 25, "rows_out": 25, "cost": 0.0149298, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 25, "cost": 0.0149298, "uses_join_buffering": false } } }, { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "scan", "rows": 9733, "rows_after_filter": 9733, "rows_out": 9733, "cost": 1.7341018, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 9733, "rows_out": 9733, "cost": 1.7341018, "uses_join_buffering": false } } }, { "best_access_path": { "table": "PART", "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "scan", "rows": 198268, "rows_after_filter": 198268, "rows_out": 198268, "cost": 34.05438, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 198268, "rows_out": 198268, "cost": 34.05438, "uses_join_buffering": false } } }, { "best_access_path": { "table": "PARTSUPP", "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "scan", "rows": 788022, "rows_after_filter": 788022, "rows_out": 788022, "cost": 135.7712548, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 788022, "rows_out": 788022, "cost": 135.7712548, "uses_join_buffering": false } } } ] }, { "plan_prefix": "", "table": "REGION", "rows_for_plan": 5, "cost_for_plan": 0.0116698, "rest_of_plan": [ { "plan_prefix": "REGION", "get_costs_for_tables": [ { "best_access_path": { "table": "NATION", "plan_details": { "record_count": 5 }, "considered_access_paths": [ { "access_type": "ref", "index": "NATION_REGIONKEY_FKIDX", "rows": 5, "cost": 0.030608, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 5, "cost": 0.02684238, "cost_without_join_buffer": 0.0713722, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 5, "cost": 0.02684238, "uses_join_buffering": true } } }, { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 5 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 9733, "rows_after_filter": 9733, "rows_out": 9733, "cost": 6.25373002, "cost_without_join_buffer": 8.1429442, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 9733, "rows_out": 9733, "cost": 6.25373002, "uses_join_buffering": true } } }, { "best_access_path": { "table": "PART", "plan_details": { "record_count": 5 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 198268, "rows_after_filter": 198268, "rows_out": 198268, "cost": 126.1164648, "cost_without_join_buffer": 163.652764, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 198268, "rows_out": 198268, "cost": 126.1164648, "uses_join_buffering": true } } }, { "best_access_path": { "table": "PARTSUPP", "plan_details": { "record_count": 5 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 788022, "rows_after_filter": 788022, "rows_out": 788022, "cost": 501.6738144, "cost_without_join_buffer": 650.8199732, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 788022, "rows_out": 788022, "cost": 501.6738144, "uses_join_buffering": true } } } ] }, { "plan_prefix": "REGION", "table": "NATION", "rows_for_plan": 25, "cost_for_plan": 0.03851218, "pushdown_cond_selectivity": 0.2, "filtered": 20, "rows_out": 5, "rest_of_plan": [ { "plan_prefix": "REGION,NATION", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 25 }, "considered_access_paths": [ { "access_type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows": 389, "cost": 9.8203168, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 9733, "rows_after_filter": 9733, "rows_out": 389, "cost": 39.14246235, "cost_without_join_buffer": 40.1871562, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows_read": 389, "rows_out": 389, "cost": 9.8203168, "uses_join_buffering": false } } }, { "best_access_path": { "table": "PART", "plan_details": { "record_count": 25 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 198268, "rows_after_filter": 198268, "rows_out": 198268, "cost": 796.0593061, "cost_without_join_buffer": 811.644684, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 198268, "rows_out": 198268, "cost": 796.0593061, "uses_join_buffering": true } } }, { "best_access_path": { "table": "PARTSUPP", "plan_details": { "record_count": 25 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 788022, "rows_after_filter": 788022, "rows_out": 788022, "cost": 3164.377729, "cost_without_join_buffer": 3226.063565, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 788022, "rows_out": 788022, "cost": 3164.377729, "uses_join_buffering": true } } } ] }, { "plan_prefix": "REGION,NATION", "table": "SUPPLIER", "rows_for_plan": 9725, "cost_for_plan": 9.85882898, "rest_of_plan": [ { "plan_prefix": "REGION,NATION,SUPPLIER", "get_costs_for_tables": [ { "best_access_path": { "table": "PART", "plan_details": { "record_count": 9725 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 198268, "rows_after_filter": 198268, "rows_out": 198268, "cost": 414104.7283, "cost_without_join_buffer": 315087.7259, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 198268, "rows_out": 198268, "cost": 414104.7283, "uses_join_buffering": true } } }, { "best_access_path": { "table": "PARTSUPP", "plan_details": { "record_count": 9725 }, "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "chosen": false, "cause": "no predicate for first keypart" }, { "access_type": "ref", "index": "PARTSUPP_PART_FKIDX", "chosen": false, "cause": "no predicate for first keypart" }, { "access_type": "ref", "index": "PARTSUPP_SUPPLIER_FKIDX", "rows": 80, "cost": 788.5760312, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 788022, "rows_after_filter": 788022, "rows_out": 80, "cost": 1645869.989, "cost_without_join_buffer": 1252219.206, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "index": "PARTSUPP_SUPPLIER_FKIDX", "rows_read": 80, "rows_out": 80, "cost": 788.5760312, "uses_join_buffering": false } } } ] }, { "plan_prefix": "REGION,NATION,SUPPLIER", "table": "PARTSUPP", "rows_for_plan": 778000, "cost_for_plan": 798.4348602, "rest_of_plan": [ { "plan_prefix": "REGION,NATION,SUPPLIER,PARTSUPP", "get_costs_for_tables": [ { "best_access_path": { "table": "PART", "plan_details": { "record_count": 778000 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 697.529104, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 198268, "rows_after_filter": 198268, "rows_out": 1, "cost": 42517434.04, "cost_without_join_buffer": 25206887.34, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "index": "PRIMARY", "rows_read": 1, "rows_out": 1, "cost": 697.529104, "uses_join_buffering": false } } } ] }, { "plan_prefix": "REGION,NATION,SUPPLIER,PARTSUPP", "table": "PART", "rows_for_plan": 778000, "cost_for_plan": 1495.963964 } ] }, { "plan_prefix": "REGION,NATION,SUPPLIER", "table": "PART", "rows_for_plan": 1928156300, "cost_for_plan": 414114.5872, "pruned_by_cost": true, "current_cost": 414114.5872, "best_cost": 1495.963964 } ] }, { "plan_prefix": "REGION,NATION", "table": "PART", "rows_for_plan": 4956700, "cost_for_plan": 796.0978182, "rest_of_plan": [ { "plan_prefix": "REGION,NATION,PART", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 4956700 }, "considered_access_paths": [ { "access_type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows": 389, "cost": 1919605.536, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 9733, "rows_after_filter": 9733, "rows_out": 389, "cost": 10357030.74, "cost_without_join_buffer": 7941677.413, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows_read": 389, "rows_out": 389, "cost": 1919605.536, "uses_join_buffering": false } } }, { "best_access_path": { "table": "PARTSUPP", "plan_details": { "record_count": 4956700 }, "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 4, "cost": 7211.919494, "chosen": true }, { "access_type": "ref", "index": "PARTSUPP_PART_FKIDX", "rows": 3, "cost": 18877.99503, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "PARTSUPP_SUPPLIER_FKIDX", "chosen": false, "cause": "no predicate for first keypart" }, { "access_type": "scan_with_join_cache", "rows": 788022, "rows_after_filter": 788022, "rows_out": 3, "cost": 838519607.8, "cost_without_join_buffer": 638235502.6, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "index": "PRIMARY", "rows_read": 4, "rows_out": 3, "cost": 7211.919494, "uses_join_buffering": false } } } ] }, { "plan_prefix": "REGION,NATION,PART", "table": "PARTSUPP", "rows_for_plan": 14870100, "cost_for_plan": 8008.017312, "pruned_by_cost": true, "current_cost": 8008.017312, "best_cost": 1495.963964 }, { "plan_prefix": "REGION,NATION,PART", "table": "SUPPLIER", "rows_for_plan": 1928156300, "cost_for_plan": 1920401.634, "pruned_by_cost": true, "current_cost": 1920401.634, "best_cost": 1495.963964 } ] }, { "plan_prefix": "REGION,NATION", "table": "PARTSUPP", "rows_for_plan": 19700550, "cost_for_plan": 3164.416241, "pruned_by_cost": true, "current_cost": 3164.416241, "best_cost": 1495.963964 } ] }, { "plan_prefix": "REGION", "table": "SUPPLIER", "rows_for_plan": 48665, "cost_for_plan": 6.26539982, "rest_of_plan": [ { "plan_prefix": "REGION,SUPPLIER", "get_costs_for_tables": [ { "best_access_path": { "table": "NATION", "plan_details": { "record_count": 48665 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 43.5287418, "chosen": true }, { "access_type": "eq_ref", "index": "NATION_REGIONKEY_FKIDX", "rows": 1, "cost": 83.7618898, "chosen": false, "cause": "cost" }, { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 1, "cost": 190.6038134, "cost_without_join_buffer": 686.6931682, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "index": "PRIMARY", "rows_read": 1, "rows_out": 1, "cost": 43.5287418, "uses_join_buffering": false } } }, { "best_access_path": { "table": "PART", "plan_details": { "record_count": 48665 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 198268, "rows_after_filter": 198268, "rows_out": 198268, "cost": 1484713.626, "cost_without_join_buffer": 1576727.994, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 198268, "rows_out": 198268, "cost": 1484713.626, "uses_join_buffering": true } } }, { "best_access_path": { "table": "PARTSUPP", "plan_details": { "record_count": 48665 }, "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "chosen": false, "cause": "no predicate for first keypart" }, { "access_type": "ref", "index": "PARTSUPP_PART_FKIDX", "chosen": false, "cause": "no predicate for first keypart" }, { "access_type": "ref", "index": "PARTSUPP_SUPPLIER_FKIDX", "rows": 80, "cost": 3915.532796, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 788022, "rows_after_filter": 788022, "rows_out": 80, "cost": 5901029.146, "cost_without_join_buffer": 6266218.479, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "index": "PARTSUPP_SUPPLIER_FKIDX", "rows_read": 80, "rows_out": 80, "cost": 3915.532796, "uses_join_buffering": false } } } ] }, { "plan_prefix": "REGION,SUPPLIER", "table": "NATION", "rows_for_plan": 48665, "cost_for_plan": 49.79414162, "rest_of_plan": [ { "plan_prefix": "REGION,SUPPLIER,NATION", "get_costs_for_tables": [ { "best_access_path": { "table": "PART", "plan_details": { "record_count": 48665 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 198268, "rows_after_filter": 198268, "rows_out": 198268, "cost": 2072186.542, "cost_without_join_buffer": 1576727.994, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 198268, "rows_out": 198268, "cost": 2072186.542, "uses_join_buffering": true } } }, { "best_access_path": { "table": "PARTSUPP", "plan_details": { "record_count": 48665 }, "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "chosen": false, "cause": "no predicate for first keypart" }, { "access_type": "ref", "index": "PARTSUPP_PART_FKIDX", "chosen": false, "cause": "no predicate for first keypart" }, { "access_type": "ref", "index": "PARTSUPP_SUPPLIER_FKIDX", "rows": 80, "cost": 3915.532796, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 788022, "rows_after_filter": 788022, "rows_out": 80, "cost": 8235957.469, "cost_without_join_buffer": 6266218.479, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "index": "PARTSUPP_SUPPLIER_FKIDX", "rows_read": 80, "rows_out": 80, "cost": 3915.532796, "uses_join_buffering": false } } } ] }, { "plan_prefix": "REGION,SUPPLIER,NATION", "table": "PARTSUPP", "rows_for_plan": 3893200, "cost_for_plan": 3965.326938, "pruned_by_cost": true, "current_cost": 3965.326938, "best_cost": 1495.963964 }, { "plan_prefix": "REGION,SUPPLIER,NATION", "table": "PART", "rows_for_plan": 9648712220, "cost_for_plan": 2072236.336, "pruned_by_cost": true, "current_cost": 2072236.336, "best_cost": 1495.963964 } ] }, { "plan_prefix": "REGION,SUPPLIER", "table": "PARTSUPP", "rows_for_plan": 3893200, "cost_for_plan": 3921.798196, "pruned_by_cost": true, "current_cost": 3921.798196, "best_cost": 1495.963964 }, { "plan_prefix": "REGION,SUPPLIER", "table": "PART", "rows_for_plan": 9648712220, "cost_for_plan": 1484719.892, "pruned_by_cost": true, "current_cost": 1484719.892, "best_cost": 1495.963964 } ] }, { "plan_prefix": "REGION", "table": "PART", "rows_for_plan": 991340, "cost_for_plan": 126.1281346, "rest_of_plan": [ { "plan_prefix": "REGION,PART", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 991340 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 9733, "rows_after_filter": 9733, "rows_out": 9733, "cost": 1483922.652, "cost_without_join_buffer": 1588335.588, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 9733, "rows_out": 9733, "cost": 1483922.652, "uses_join_buffering": true } } }, { "best_access_path": { "table": "NATION", "plan_details": { "record_count": 991340 }, "considered_access_paths": [ { "access_type": "ref", "index": "NATION_REGIONKEY_FKIDX", "rows": 5, "cost": 5743.746291, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 5, "cost": 3875.168456, "cost_without_join_buffer": 13988.40302, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 5, "cost": 3875.168456, "uses_join_buffering": true } } }, { "best_access_path": { "table": "PARTSUPP", "plan_details": { "record_count": 991340 }, "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 4, "cost": 1447.991159, "chosen": true }, { "access_type": "ref", "index": "PARTSUPP_PART_FKIDX", "rows": 3, "cost": 3781.710893, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "PARTSUPP_SUPPLIER_FKIDX", "chosen": false, "cause": "no predicate for first keypart" }, { "access_type": "scan_with_join_cache", "rows": 788022, "rows_after_filter": 788022, "rows_out": 3, "cost": 120139064.6, "cost_without_join_buffer": 127647106.1, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "index": "PRIMARY", "rows_read": 4, "rows_out": 3, "cost": 1447.991159, "uses_join_buffering": false } } } ] }, { "plan_prefix": "REGION,PART", "table": "PARTSUPP", "rows_for_plan": 2974020, "cost_for_plan": 1574.119294, "pruned_by_cost": true, "current_cost": 1574.119294, "best_cost": 1495.963964 }, { "plan_prefix": "REGION,PART", "table": "NATION", "rows_for_plan": 4956700, "cost_for_plan": 4001.296591, "pruned_by_cost": true, "current_cost": 4001.296591, "best_cost": 1495.963964 }, { "plan_prefix": "REGION,PART", "table": "SUPPLIER", "rows_for_plan": 9648712220, "cost_for_plan": 1484048.78, "pruned_by_cost": true, "current_cost": 1484048.78, "best_cost": 1495.963964 } ] }, { "plan_prefix": "REGION", "table": "PARTSUPP", "rows_for_plan": 3940110, "cost_for_plan": 501.6854842, "rest_of_plan": [ { "plan_prefix": "REGION,PARTSUPP", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 3940110 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 3524.32388, "chosen": true }, { "access_type": "ref", "index": "SUPPLIER_NATION_FKIDX", "chosen": false, "cause": "no predicate for first keypart" }, { "access_type": "scan_with_join_cache", "rows": 9733, "rows_after_filter": 9733, "rows_out": 1, "cost": 5896734.948, "cost_without_join_buffer": 6312886.139, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "index": "PRIMARY", "rows_read": 1, "rows_out": 1, "cost": 3524.32388, "uses_join_buffering": false } } }, { "best_access_path": { "table": "PART", "plan_details": { "record_count": 3940110 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 3525.846772, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 198268, "rows_after_filter": 198268, "rows_out": 1, "cost": 120115807.4, "cost_without_join_buffer": 127657973.9, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "index": "PRIMARY", "rows_read": 1, "rows_out": 1, "cost": 3525.846772, "uses_join_buffering": false } } }, { "best_access_path": { "table": "NATION", "plan_details": { "record_count": 3940110 }, "considered_access_paths": [ { "access_type": "ref", "index": "NATION_REGIONKEY_FKIDX", "rows": 5, "cost": 22828.68377, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 5, "cost": 15391.76222, "cost_without_join_buffer": 55597.31699, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 5, "cost": 15391.76222, "uses_join_buffering": true } } } ] }, { "plan_prefix": "REGION,PARTSUPP", "table": "PART", "rows_for_plan": 3940110, "cost_for_plan": 4027.532257, "pruned_by_cost": true, "current_cost": 4027.532257, "best_cost": 1495.963964 }, { "plan_prefix": "REGION,PARTSUPP", "table": "SUPPLIER", "rows_for_plan": 3940110, "cost_for_plan": 4026.009364, "pruned_by_cost": true, "current_cost": 4026.009364, "best_cost": 1495.963964 }, { "plan_prefix": "REGION,PARTSUPP", "table": "NATION", "rows_for_plan": 19700550, "cost_for_plan": 15893.4477, "pruned_by_cost": true, "current_cost": 15893.4477, "best_cost": 1495.963964 } ] } ] }, { "plan_prefix": "", "table": "NATION", "rows_for_plan": 25, "cost_for_plan": 0.0149298, "rest_of_plan": [ { "plan_prefix": "NATION", "get_costs_for_tables": [ { "best_access_path": { "table": "REGION", "plan_details": { "record_count": 25 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 0.0231802, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 5, "rows_after_filter": 5, "rows_out": 1, "cost": 0.0247997, "cost_without_join_buffer": 0.2720842, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "index": "PRIMARY", "rows_read": 1, "rows_out": 1, "cost": 0.0231802, "uses_join_buffering": false } } }, { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 25 }, "considered_access_paths": [ { "access_type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows": 389, "cost": 9.8203168, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 9733, "rows_after_filter": 9733, "rows_out": 389, "cost": 24.3322429, "cost_without_join_buffer": 40.1871562, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows_read": 389, "rows_out": 389, "cost": 9.8203168, "uses_join_buffering": false } } }, { "best_access_path": { "table": "PART", "plan_details": { "record_count": 25 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 198268, "rows_after_filter": 198268, "rows_out": 198268, "cost": 494.3648038, "cost_without_join_buffer": 811.644684, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 198268, "rows_out": 198268, "cost": 494.3648038, "uses_join_buffering": true } } }, { "best_access_path": { "table": "PARTSUPP", "plan_details": { "record_count": 25 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 788022, "rows_after_filter": 788022, "rows_out": 788022, "cost": 1965.284053, "cost_without_join_buffer": 3226.063565, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 788022, "rows_out": 788022, "cost": 1965.284053, "uses_join_buffering": true } } } ] }, { "plan_prefix": "NATION", "table": "REGION", "rows_for_plan": 25, "cost_for_plan": 0.03811, "rest_of_plan": [ { "plan_prefix": "NATION,REGION", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 25 }, "considered_access_paths": [ { "access_type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows": 389, "cost": 9.8203168, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 9733, "rows_after_filter": 9733, "rows_out": 389, "cost": 39.14246235, "cost_without_join_buffer": 40.1871562, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows_read": 389, "rows_out": 389, "cost": 9.8203168, "uses_join_buffering": false } } }, { "best_access_path": { "table": "PART", "plan_details": { "record_count": 25 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 198268, "rows_after_filter": 198268, "rows_out": 198268, "cost": 796.0593061, "cost_without_join_buffer": 811.644684, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 198268, "rows_out": 198268, "cost": 796.0593061, "uses_join_buffering": true } } }, { "best_access_path": { "table": "PARTSUPP", "plan_details": { "record_count": 25 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 788022, "rows_after_filter": 788022, "rows_out": 788022, "cost": 3164.377729, "cost_without_join_buffer": 3226.063565, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 788022, "rows_out": 788022, "cost": 3164.377729, "uses_join_buffering": true } } } ] }, { "plan_prefix": "NATION,REGION", "table": "SUPPLIER", "rows_for_plan": 9725, "cost_for_plan": 9.8584268, "rest_of_plan": [ { "plan_prefix": "NATION,REGION,SUPPLIER", "get_costs_for_tables": [ { "best_access_path": { "table": "PART", "plan_details": { "record_count": 9725 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 198268, "rows_after_filter": 198268, "rows_out": 198268, "cost": 414104.7283, "cost_without_join_buffer": 315087.7259, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 198268, "rows_out": 198268, "cost": 414104.7283, "uses_join_buffering": true } } }, { "best_access_path": { "table": "PARTSUPP", "plan_details": { "record_count": 9725 }, "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "chosen": false, "cause": "no predicate for first keypart" }, { "access_type": "ref", "index": "PARTSUPP_PART_FKIDX", "chosen": false, "cause": "no predicate for first keypart" }, { "access_type": "ref", "index": "PARTSUPP_SUPPLIER_FKIDX", "rows": 80, "cost": 788.5760312, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 788022, "rows_after_filter": 788022, "rows_out": 80, "cost": 1645869.989, "cost_without_join_buffer": 1252219.206, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "index": "PARTSUPP_SUPPLIER_FKIDX", "rows_read": 80, "rows_out": 80, "cost": 788.5760312, "uses_join_buffering": false } } } ] }, { "plan_prefix": "NATION,REGION,SUPPLIER", "table": "PARTSUPP", "rows_for_plan": 778000, "cost_for_plan": 798.434458, "rest_of_plan": [ { "plan_prefix": "NATION,REGION,SUPPLIER,PARTSUPP", "get_costs_for_tables": [ { "best_access_path": { "table": "PART", "plan_details": { "record_count": 778000 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 697.529104, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 198268, "rows_after_filter": 198268, "rows_out": 1, "cost": 42517434.04, "cost_without_join_buffer": 25206887.34, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "index": "PRIMARY", "rows_read": 1, "rows_out": 1, "cost": 697.529104, "uses_join_buffering": false } } } ] }, { "plan_prefix": "NATION,REGION,SUPPLIER,PARTSUPP", "table": "PART", "rows_for_plan": 778000, "cost_for_plan": 1495.963562 } ] }, { "plan_prefix": "NATION,REGION,SUPPLIER", "table": "PART", "rows_for_plan": 1928156300, "cost_for_plan": 414114.5868, "pruned_by_cost": true, "current_cost": 414114.5868, "best_cost": 1495.963562 } ] }, { "plan_prefix": "NATION,REGION", "table": "PART", "rows_for_plan": 4956700, "cost_for_plan": 796.097416, "rest_of_plan": [ { "plan_prefix": "NATION,REGION,PART", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 4956700 }, "considered_access_paths": [ { "access_type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows": 389, "cost": 1919605.536, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 9733, "rows_after_filter": 9733, "rows_out": 389, "cost": 10357030.74, "cost_without_join_buffer": 7941677.413, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows_read": 389, "rows_out": 389, "cost": 1919605.536, "uses_join_buffering": false } } }, { "best_access_path": { "table": "PARTSUPP", "plan_details": { "record_count": 4956700 }, "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 4, "cost": 7211.919494, "chosen": true }, { "access_type": "ref", "index": "PARTSUPP_PART_FKIDX", "rows": 3, "cost": 18877.99503, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "PARTSUPP_SUPPLIER_FKIDX", "chosen": false, "cause": "no predicate for first keypart" }, { "access_type": "scan_with_join_cache", "rows": 788022, "rows_after_filter": 788022, "rows_out": 3, "cost": 838519607.8, "cost_without_join_buffer": 638235502.6, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "index": "PRIMARY", "rows_read": 4, "rows_out": 3, "cost": 7211.919494, "uses_join_buffering": false } } } ] }, { "plan_prefix": "NATION,REGION,PART", "table": "PARTSUPP", "rows_for_plan": 14870100, "cost_for_plan": 8008.01691, "pruned_by_cost": true, "current_cost": 8008.01691, "best_cost": 1495.963562 }, { "plan_prefix": "NATION,REGION,PART", "table": "SUPPLIER", "rows_for_plan": 1928156300, "cost_for_plan": 1920401.634, "pruned_by_cost": true, "current_cost": 1920401.634, "best_cost": 1495.963562 } ] }, { "plan_prefix": "NATION,REGION", "table": "PARTSUPP", "rows_for_plan": 19700550, "cost_for_plan": 3164.415839, "pruned_by_cost": true, "current_cost": 3164.415839, "best_cost": 1495.963562 } ] }, { "plan_prefix": "NATION", "table": "SUPPLIER", "rows_for_plan": 9725, "cost_for_plan": 9.8352466, "pruned_by_heuristic": true }, { "plan_prefix": "NATION", "table": "PART", "rows_for_plan": 4956700, "cost_for_plan": 494.3797336, "pruned_by_heuristic": true }, { "plan_prefix": "NATION", "table": "PARTSUPP", "rows_for_plan": 19700550, "cost_for_plan": 1965.298983, "pruned_by_cost": true, "current_cost": 1965.298983, "best_cost": 1495.963562 } ] }, { "plan_prefix": "", "table": "SUPPLIER", "rows_for_plan": 9733, "cost_for_plan": 1.7341018, "rest_of_plan": [ { "plan_prefix": "SUPPLIER", "get_costs_for_tables": [ { "best_access_path": { "table": "NATION", "plan_details": { "record_count": 9733 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 8.70640372, "chosen": true }, { "access_type": "ref", "index": "NATION_REGIONKEY_FKIDX", "chosen": false, "cause": "no predicate for first keypart" }, { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 1, "cost": 23.30273223, "cost_without_join_buffer": 137.339289, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "index": "PRIMARY", "rows_read": 1, "rows_out": 1, "cost": 8.70640372, "uses_join_buffering": false } } }, { "best_access_path": { "table": "REGION", "plan_details": { "record_count": 9733 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5, "rows_after_filter": 5, "rows_out": 5, "cost": 5.199356668, "cost_without_join_buffer": 105.609709, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5, "rows_out": 5, "cost": 5.199356668, "uses_join_buffering": true } } }, { "best_access_path": { "table": "PART", "plan_details": { "record_count": 9733 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 198268, "rows_after_filter": 198268, "rows_out": 198268, "cost": 179468.9058, "cost_without_join_buffer": 315346.9227, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 198268, "rows_out": 198268, "cost": 179468.9058, "uses_join_buffering": true } } }, { "best_access_path": { "table": "PARTSUPP", "plan_details": { "record_count": 9733 }, "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "chosen": false, "cause": "no predicate for first keypart" }, { "access_type": "ref", "index": "PARTSUPP_PART_FKIDX", "chosen": false, "cause": "no predicate for first keypart" }, { "access_type": "ref", "index": "PARTSUPP_SUPPLIER_FKIDX", "rows": 80, "cost": 789.2184466, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 788022, "rows_after_filter": 788022, "rows_out": 80, "cost": 713303.029, "cost_without_join_buffer": 1253249.303, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "index": "PARTSUPP_SUPPLIER_FKIDX", "rows_read": 80, "rows_out": 80, "cost": 789.2184466, "uses_join_buffering": false } } } ] }, { "plan_prefix": "SUPPLIER", "table": "NATION", "rows_for_plan": 9733, "cost_for_plan": 10.44050552, "rest_of_plan": [ { "plan_prefix": "SUPPLIER,NATION", "get_costs_for_tables": [ { "best_access_path": { "table": "REGION", "plan_details": { "record_count": 9733 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 8.70640372, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 5, "rows_after_filter": 5, "rows_out": 1, "cost": 8.172251158, "cost_without_join_buffer": 105.609709, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5, "rows_out": 1, "cost": 8.172251158, "uses_join_buffering": true } } }, { "best_access_path": { "table": "PART", "plan_details": { "record_count": 9733 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 198268, "rows_after_filter": 198268, "rows_out": 198268, "cost": 296957.009, "cost_without_join_buffer": 315346.9227, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 198268, "rows_out": 198268, "cost": 296957.009, "uses_join_buffering": true } } }, { "best_access_path": { "table": "PARTSUPP", "plan_details": { "record_count": 9733 }, "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "chosen": false, "cause": "no predicate for first keypart" }, { "access_type": "ref", "index": "PARTSUPP_PART_FKIDX", "chosen": false, "cause": "no predicate for first keypart" }, { "access_type": "ref", "index": "PARTSUPP_SUPPLIER_FKIDX", "rows": 80, "cost": 789.2184466, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 788022, "rows_after_filter": 788022, "rows_out": 80, "cost": 1180262.941, "cost_without_join_buffer": 1253249.303, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "index": "PARTSUPP_SUPPLIER_FKIDX", "rows_read": 80, "rows_out": 80, "cost": 789.2184466, "uses_join_buffering": false } } } ] }, { "plan_prefix": "SUPPLIER,NATION", "table": "REGION", "rows_for_plan": 9733, "cost_for_plan": 18.61275668, "pushdown_cond_selectivity": 0.2, "filtered": 20, "rows_out": 1, "rest_of_plan": [ { "plan_prefix": "SUPPLIER,NATION,REGION", "get_costs_for_tables": [ { "best_access_path": { "table": "PART", "plan_details": { "record_count": 9733 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 198268, "rows_after_filter": 198268, "rows_out": 198268, "cost": 414445.1122, "cost_without_join_buffer": 315346.9227, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 198268, "rows_out": 198268, "cost": 414445.1122, "uses_join_buffering": true } } }, { "best_access_path": { "table": "PARTSUPP", "plan_details": { "record_count": 9733 }, "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "chosen": false, "cause": "no predicate for first keypart" }, { "access_type": "ref", "index": "PARTSUPP_PART_FKIDX", "chosen": false, "cause": "no predicate for first keypart" }, { "access_type": "ref", "index": "PARTSUPP_SUPPLIER_FKIDX", "rows": 80, "cost": 789.2184466, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 788022, "rows_after_filter": 788022, "rows_out": 80, "cost": 1647222.853, "cost_without_join_buffer": 1253249.303, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "index": "PARTSUPP_SUPPLIER_FKIDX", "rows_read": 80, "rows_out": 80, "cost": 789.2184466, "uses_join_buffering": false } } } ] }, { "plan_prefix": "SUPPLIER,NATION,REGION", "table": "PARTSUPP", "rows_for_plan": 778640, "cost_for_plan": 807.8312032, "rest_of_plan": [ { "plan_prefix": "SUPPLIER,NATION,REGION,PARTSUPP", "get_costs_for_tables": [ { "best_access_path": { "table": "PART", "plan_details": { "record_count": 778640 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 698.1015456, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 198268, "rows_after_filter": 198268, "rows_out": 1, "cost": 42552388.13, "cost_without_join_buffer": 25227623.08, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "index": "PRIMARY", "rows_read": 1, "rows_out": 1, "cost": 698.1015456, "uses_join_buffering": false } } } ] }, { "plan_prefix": "SUPPLIER,NATION,REGION,PARTSUPP", "table": "PART", "rows_for_plan": 778640, "cost_for_plan": 1505.932749, "pruned_by_cost": true, "current_cost": 1505.932749, "best_cost": 1495.963562 } ] }, { "plan_prefix": "SUPPLIER,NATION,REGION", "table": "PART", "rows_for_plan": 1929742444, "cost_for_plan": 414463.7249, "pruned_by_cost": true, "current_cost": 414463.7249, "best_cost": 1495.963562 } ] }, { "plan_prefix": "SUPPLIER,NATION", "table": "PARTSUPP", "rows_for_plan": 778640, "cost_for_plan": 799.6589521, "pruned_by_heuristic": true }, { "plan_prefix": "SUPPLIER,NATION", "table": "PART", "rows_for_plan": 1929742444, "cost_for_plan": 296967.4495, "pruned_by_cost": true, "current_cost": 296967.4495, "best_cost": 1495.963562 } ] }, { "plan_prefix": "SUPPLIER", "table": "REGION", "rows_for_plan": 48665, "cost_for_plan": 6.933458468, "rest_of_plan": [ { "plan_prefix": "SUPPLIER,REGION", "get_costs_for_tables": [ { "best_access_path": { "table": "NATION", "plan_details": { "record_count": 48665 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 43.5287418, "chosen": true }, { "access_type": "eq_ref", "index": "NATION_REGIONKEY_FKIDX", "rows": 1, "cost": 83.7618898, "chosen": false, "cause": "cost" }, { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 1, "cost": 190.6038134, "cost_without_join_buffer": 686.6931682, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "index": "PRIMARY", "rows_read": 1, "rows_out": 1, "cost": 43.5287418, "uses_join_buffering": false } } }, { "best_access_path": { "table": "PART", "plan_details": { "record_count": 48665 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 198268, "rows_after_filter": 198268, "rows_out": 198268, "cost": 1484713.626, "cost_without_join_buffer": 1576727.994, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 198268, "rows_out": 198268, "cost": 1484713.626, "uses_join_buffering": true } } }, { "best_access_path": { "table": "PARTSUPP", "plan_details": { "record_count": 48665 }, "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "chosen": false, "cause": "no predicate for first keypart" }, { "access_type": "ref", "index": "PARTSUPP_PART_FKIDX", "chosen": false, "cause": "no predicate for first keypart" }, { "access_type": "ref", "index": "PARTSUPP_SUPPLIER_FKIDX", "rows": 80, "cost": 3915.532796, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 788022, "rows_after_filter": 788022, "rows_out": 80, "cost": 5901029.146, "cost_without_join_buffer": 6266218.479, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "index": "PARTSUPP_SUPPLIER_FKIDX", "rows_read": 80, "rows_out": 80, "cost": 3915.532796, "uses_join_buffering": false } } } ] }, { "plan_prefix": "SUPPLIER,REGION", "table": "NATION", "rows_for_plan": 48665, "cost_for_plan": 50.46220027, "rest_of_plan": [ { "plan_prefix": "SUPPLIER,REGION,NATION", "get_costs_for_tables": [ { "best_access_path": { "table": "PART", "plan_details": { "record_count": 48665 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 198268, "rows_after_filter": 198268, "rows_out": 198268, "cost": 2072186.542, "cost_without_join_buffer": 1576727.994, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 198268, "rows_out": 198268, "cost": 2072186.542, "uses_join_buffering": true } } }, { "best_access_path": { "table": "PARTSUPP", "plan_details": { "record_count": 48665 }, "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "chosen": false, "cause": "no predicate for first keypart" }, { "access_type": "ref", "index": "PARTSUPP_PART_FKIDX", "chosen": false, "cause": "no predicate for first keypart" }, { "access_type": "ref", "index": "PARTSUPP_SUPPLIER_FKIDX", "rows": 80, "cost": 3915.532796, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 788022, "rows_after_filter": 788022, "rows_out": 80, "cost": 8235957.469, "cost_without_join_buffer": 6266218.479, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "index": "PARTSUPP_SUPPLIER_FKIDX", "rows_read": 80, "rows_out": 80, "cost": 3915.532796, "uses_join_buffering": false } } } ] }, { "plan_prefix": "SUPPLIER,REGION,NATION", "table": "PARTSUPP", "rows_for_plan": 3893200, "cost_for_plan": 3965.994996, "pruned_by_cost": true, "current_cost": 3965.994996, "best_cost": 1495.963562 }, { "plan_prefix": "SUPPLIER,REGION,NATION", "table": "PART", "rows_for_plan": 9648712220, "cost_for_plan": 2072237.004, "pruned_by_cost": true, "current_cost": 2072237.004, "best_cost": 1495.963562 } ] }, { "plan_prefix": "SUPPLIER,REGION", "table": "PARTSUPP", "rows_for_plan": 3893200, "cost_for_plan": 3922.466254, "pruned_by_cost": true, "current_cost": 3922.466254, "best_cost": 1495.963562 }, { "plan_prefix": "SUPPLIER,REGION", "table": "PART", "rows_for_plan": 9648712220, "cost_for_plan": 1484720.56, "pruned_by_cost": true, "current_cost": 1484720.56, "best_cost": 1495.963562 } ] }, { "plan_prefix": "SUPPLIER", "table": "PARTSUPP", "rows_for_plan": 778640, "cost_for_plan": 790.9525484, "pruned_by_heuristic": true }, { "plan_prefix": "SUPPLIER", "table": "PART", "rows_for_plan": 1929742444, "cost_for_plan": 179470.6399, "pruned_by_cost": true, "current_cost": 179470.6399, "best_cost": 1495.963562 } ] }, { "plan_prefix": "", "table": "PART", "rows_for_plan": 198268, "cost_for_plan": 34.05438, "rest_of_plan": [ { "plan_prefix": "PART", "get_costs_for_tables": [ { "best_access_path": { "table": "NATION", "plan_details": { "record_count": 198268 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 25, "cost": 473.0265891, "cost_without_join_buffer": 2797.68126, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 25, "cost": 473.0265891, "uses_join_buffering": true } } }, { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 198268 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 9733, "rows_after_filter": 9733, "rows_out": 9733, "cost": 179293.3632, "cost_without_join_buffer": 317667.2231, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 9733, "rows_out": 9733, "cost": 179293.3632, "uses_join_buffering": true } } }, { "best_access_path": { "table": "REGION", "plan_details": { "record_count": 198268 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5, "rows_after_filter": 5, "rows_out": 5, "cost": 104.6295073, "cost_without_join_buffer": 2151.32758, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5, "rows_out": 5, "cost": 104.6295073, "uses_join_buffering": true } } }, { "best_access_path": { "table": "PARTSUPP", "plan_details": { "record_count": 198268 }, "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 4, "cost": 295.205492, "chosen": true }, { "access_type": "ref", "index": "PARTSUPP_PART_FKIDX", "rows": 3, "cost": 762.4540659, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "PARTSUPP_SUPPLIER_FKIDX", "chosen": false, "cause": "no predicate for first keypart" }, { "access_type": "scan_with_join_cache", "rows": 788022, "rows_after_filter": 788022, "rows_out": 3, "cost": 14515283.81, "cost_without_join_buffer": 25529426.83, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "index": "PRIMARY", "rows_read": 4, "rows_out": 3, "cost": 295.205492, "uses_join_buffering": false } } } ] }, { "plan_prefix": "PART", "table": "PARTSUPP", "rows_for_plan": 594804, "cost_for_plan": 329.259872, "pushdown_cond_selectivity": 0.75, "filtered": 75, "rows_out": 3, "rest_of_plan": [ { "plan_prefix": "PART,PARTSUPP", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 594804 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 532.148381, "chosen": true }, { "access_type": "ref", "index": "SUPPLIER_NATION_FKIDX", "chosen": false, "cause": "no predicate for first keypart" }, { "access_type": "scan_with_join_cache", "rows": 9733, "rows_after_filter": 9733, "rows_out": 1, "cost": 890288.594, "cost_without_join_buffer": 953001.4056, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "index": "PRIMARY", "rows_read": 1, "rows_out": 1, "cost": 532.148381, "uses_join_buffering": false } } }, { "best_access_path": { "table": "REGION", "plan_details": { "record_count": 594804 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5, "rows_after_filter": 5, "rows_out": 5, "cost": 495.1857005, "cost_without_join_buffer": 6453.981102, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5, "rows_out": 5, "cost": 495.1857005, "uses_join_buffering": true } } }, { "best_access_path": { "table": "NATION", "plan_details": { "record_count": 594804 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 25, "cost": 2324.528511, "cost_without_join_buffer": 8393.042142, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 25, "cost": 2324.528511, "uses_join_buffering": true } } } ] }, { "plan_prefix": "PART,PARTSUPP", "table": "SUPPLIER", "rows_for_plan": 594804, "cost_for_plan": 861.4082529, "rest_of_plan": [ { "plan_prefix": "PART,PARTSUPP,SUPPLIER", "get_costs_for_tables": [ { "best_access_path": { "table": "REGION", "plan_details": { "record_count": 594804 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5, "rows_after_filter": 5, "rows_out": 5, "cost": 681.0743212, "cost_without_join_buffer": 6453.981102, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5, "rows_out": 5, "cost": 681.0743212, "uses_join_buffering": true } } }, { "best_access_path": { "table": "NATION", "plan_details": { "record_count": 594804 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 532.017309, "chosen": true }, { "access_type": "ref", "index": "NATION_REGIONKEY_FKIDX", "chosen": false, "cause": "no predicate for first keypart" }, { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 1, "cost": 3235.947677, "cost_without_join_buffer": 8393.042142, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "index": "PRIMARY", "rows_read": 1, "rows_out": 1, "cost": 532.017309, "uses_join_buffering": false } } } ] }, { "plan_prefix": "PART,PARTSUPP,SUPPLIER", "table": "NATION", "rows_for_plan": 594804, "cost_for_plan": 1393.425562, "rest_of_plan": [ { "plan_prefix": "PART,PARTSUPP,SUPPLIER,NATION", "get_costs_for_tables": [ { "best_access_path": { "table": "REGION", "plan_details": { "record_count": 594804 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 532.017309, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 5, "rows_after_filter": 5, "rows_out": 1, "cost": 862.9265187, "cost_without_join_buffer": 6453.981102, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "index": "PRIMARY", "rows_read": 1, "rows_out": 1, "cost": 532.017309, "uses_join_buffering": false } } } ] }, { "plan_prefix": "PART,PARTSUPP,SUPPLIER,NATION", "table": "REGION", "rows_for_plan": 594804, "cost_for_plan": 1925.442871, "pruned_by_cost": true, "current_cost": 1925.442871, "best_cost": 1495.963562 } ] } ] } ] }, { "plan_prefix": "PART", "table": "REGION", "rows_for_plan": 991340, "cost_for_plan": 138.6838873, "rest_of_plan": [ { "plan_prefix": "PART,REGION", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 991340 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 9733, "rows_after_filter": 9733, "rows_out": 9733, "cost": 1483922.652, "cost_without_join_buffer": 1588335.588, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 9733, "rows_out": 9733, "cost": 1483922.652, "uses_join_buffering": true } } }, { "best_access_path": { "table": "NATION", "plan_details": { "record_count": 991340 }, "considered_access_paths": [ { "access_type": "ref", "index": "NATION_REGIONKEY_FKIDX", "rows": 5, "cost": 5743.746291, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 5, "cost": 3875.168456, "cost_without_join_buffer": 13988.40302, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 5, "cost": 3875.168456, "uses_join_buffering": true } } }, { "best_access_path": { "table": "PARTSUPP", "plan_details": { "record_count": 991340 }, "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 4, "cost": 1447.991159, "chosen": true }, { "access_type": "ref", "index": "PARTSUPP_PART_FKIDX", "rows": 3, "cost": 3781.710893, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "PARTSUPP_SUPPLIER_FKIDX", "chosen": false, "cause": "no predicate for first keypart" }, { "access_type": "scan_with_join_cache", "rows": 788022, "rows_after_filter": 788022, "rows_out": 3, "cost": 120139064.6, "cost_without_join_buffer": 127647106.1, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "index": "PRIMARY", "rows_read": 4, "rows_out": 3, "cost": 1447.991159, "uses_join_buffering": false } } } ] }, { "plan_prefix": "PART,REGION", "table": "PARTSUPP", "rows_for_plan": 2974020, "cost_for_plan": 1586.675046, "pruned_by_cost": true, "current_cost": 1586.675046, "best_cost": 1495.963562 }, { "plan_prefix": "PART,REGION", "table": "NATION", "rows_for_plan": 4956700, "cost_for_plan": 4013.852344, "pruned_by_cost": true, "current_cost": 4013.852344, "best_cost": 1495.963562 }, { "plan_prefix": "PART,REGION", "table": "SUPPLIER", "rows_for_plan": 9648712220, "cost_for_plan": 1484061.335, "pruned_by_cost": true, "current_cost": 1484061.335, "best_cost": 1495.963562 } ] }, { "plan_prefix": "PART", "table": "NATION", "rows_for_plan": 4956700, "cost_for_plan": 507.0809691, "rest_of_plan": [ { "plan_prefix": "PART,NATION", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 4956700 }, "considered_access_paths": [ { "access_type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows": 389, "cost": 1919605.536, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 9733, "rows_after_filter": 9733, "rows_out": 389, "cost": 7419729.692, "cost_without_join_buffer": 7941677.413, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows_read": 389, "rows_out": 389, "cost": 1919605.536, "uses_join_buffering": false } } }, { "best_access_path": { "table": "REGION", "plan_details": { "record_count": 4956700 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 4433.471567, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 5, "rows_after_filter": 5, "rows_out": 1, "cost": 4131.00471, "cost_without_join_buffer": 53783.16984, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5, "rows_out": 1, "cost": 4131.00471, "uses_join_buffering": true } } }, { "best_access_path": { "table": "PARTSUPP", "plan_details": { "record_count": 4956700 }, "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 4, "cost": 7211.919494, "chosen": true }, { "access_type": "ref", "index": "PARTSUPP_PART_FKIDX", "rows": 3, "cost": 18877.99503, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "PARTSUPP_SUPPLIER_FKIDX", "chosen": false, "cause": "no predicate for first keypart" }, { "access_type": "scan_with_join_cache", "rows": 788022, "rows_after_filter": 788022, "rows_out": 3, "cost": 600704694.6, "cost_without_join_buffer": 638235502.6, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "index": "PRIMARY", "rows_read": 4, "rows_out": 3, "cost": 7211.919494, "uses_join_buffering": false } } } ] }, { "plan_prefix": "PART,NATION", "table": "PARTSUPP", "rows_for_plan": 14870100, "cost_for_plan": 7719.000463, "pruned_by_cost": true, "current_cost": 7719.000463, "best_cost": 1495.963562 }, { "plan_prefix": "PART,NATION", "table": "REGION", "rows_for_plan": 4956700, "cost_for_plan": 4638.085679, "pruned_by_cost": true, "current_cost": 4638.085679, "best_cost": 1495.963562 }, { "plan_prefix": "PART,NATION", "table": "SUPPLIER", "rows_for_plan": 1928156300, "cost_for_plan": 1920112.617, "pruned_by_cost": true, "current_cost": 1920112.617, "best_cost": 1495.963562 } ] }, { "plan_prefix": "PART", "table": "SUPPLIER", "rows_for_plan": 1929742444, "cost_for_plan": 179327.4175, "pruned_by_cost": true, "current_cost": 179327.4175, "best_cost": 1495.963562 } ] }, { "plan_prefix": "", "table": "PARTSUPP", "rows_for_plan": 788022, "cost_for_plan": 135.7712548, "rest_of_plan": [ { "plan_prefix": "PARTSUPP", "get_costs_for_tables": [ { "best_access_path": { "table": "NATION", "plan_details": { "record_count": 788022 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 25, "cost": 1877.997935, "cost_without_join_buffer": 11119.46405, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 25, "cost": 1877.997935, "uses_join_buffering": true } } }, { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 788022 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 704.9702889, "chosen": true }, { "access_type": "ref", "index": "SUPPLIER_NATION_FKIDX", "chosen": false, "cause": "no predicate for first keypart" }, { "access_type": "scan_with_join_cache", "rows": 9733, "rows_after_filter": 9733, "rows_out": 1, "cost": 712372.7075, "cost_without_join_buffer": 1262577.333, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "index": "PRIMARY", "rows_read": 1, "rows_out": 1, "cost": 704.9702889, "uses_join_buffering": false } } }, { "best_access_path": { "table": "PART", "plan_details": { "record_count": 788022 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 706.4931817, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 198268, "rows_after_filter": 198268, "rows_out": 1, "cost": 14510590.07, "cost_without_join_buffer": 25531596.09, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "index": "PRIMARY", "rows_read": 1, "rows_out": 1, "cost": 706.4931817, "uses_join_buffering": false } } }, { "best_access_path": { "table": "REGION", "plan_details": { "record_count": 788022 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5, "rows_after_filter": 5, "rows_out": 5, "cost": 414.2682937, "cost_without_join_buffer": 8550.512332, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5, "rows_out": 5, "cost": 414.2682937, "uses_join_buffering": true } } } ] }, { "plan_prefix": "PARTSUPP", "table": "PART", "rows_for_plan": 788022, "cost_for_plan": 842.2644365, "rest_of_plan": [ { "plan_prefix": "PARTSUPP,PART", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 788022 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 704.9702889, "chosen": true }, { "access_type": "ref", "index": "SUPPLIER_NATION_FKIDX", "chosen": false, "cause": "no predicate for first keypart" }, { "access_type": "scan_with_join_cache", "rows": 9733, "rows_after_filter": 9733, "rows_out": 1, "cost": 1179492.255, "cost_without_join_buffer": 1262577.333, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "index": "PRIMARY", "rows_read": 1, "rows_out": 1, "cost": 704.9702889, "uses_join_buffering": false } } } ] }, { "plan_prefix": "PARTSUPP,PART", "table": "SUPPLIER", "rows_for_plan": 788022, "cost_for_plan": 1547.234725, "pruned_by_cost": true, "current_cost": 1547.234725, "best_cost": 1495.963562 } ] }, { "plan_prefix": "PARTSUPP", "table": "REGION", "rows_for_plan": 3940110, "cost_for_plan": 550.0395485, "rest_of_plan": [ { "plan_prefix": "PARTSUPP,REGION", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 3940110 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 3524.32388, "chosen": true }, { "access_type": "ref", "index": "SUPPLIER_NATION_FKIDX", "chosen": false, "cause": "no predicate for first keypart" }, { "access_type": "scan_with_join_cache", "rows": 9733, "rows_after_filter": 9733, "rows_out": 1, "cost": 5896734.948, "cost_without_join_buffer": 6312886.139, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "index": "PRIMARY", "rows_read": 1, "rows_out": 1, "cost": 3524.32388, "uses_join_buffering": false } } }, { "best_access_path": { "table": "PART", "plan_details": { "record_count": 3940110 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 3525.846772, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 198268, "rows_after_filter": 198268, "rows_out": 1, "cost": 120115807.4, "cost_without_join_buffer": 127657973.9, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "index": "PRIMARY", "rows_read": 1, "rows_out": 1, "cost": 3525.846772, "uses_join_buffering": false } } }, { "best_access_path": { "table": "NATION", "plan_details": { "record_count": 3940110 }, "considered_access_paths": [ { "access_type": "ref", "index": "NATION_REGIONKEY_FKIDX", "rows": 5, "cost": 22828.68377, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 5, "cost": 15391.76222, "cost_without_join_buffer": 55597.31699, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 5, "cost": 15391.76222, "uses_join_buffering": true } } } ] }, { "plan_prefix": "PARTSUPP,REGION", "table": "PART", "rows_for_plan": 3940110, "cost_for_plan": 4075.886321, "pruned_by_cost": true, "current_cost": 4075.886321, "best_cost": 1495.963562 }, { "plan_prefix": "PARTSUPP,REGION", "table": "SUPPLIER", "rows_for_plan": 3940110, "cost_for_plan": 4074.363428, "pruned_by_cost": true, "current_cost": 4074.363428, "best_cost": 1495.963562 }, { "plan_prefix": "PARTSUPP,REGION", "table": "NATION", "rows_for_plan": 19700550, "cost_for_plan": 15941.80177, "pruned_by_cost": true, "current_cost": 15941.80177, "best_cost": 1495.963562 } ] }, { "plan_prefix": "PARTSUPP", "table": "NATION", "rows_for_plan": 19700550, "cost_for_plan": 2013.76919, "pruned_by_cost": true, "current_cost": 2013.76919, "best_cost": 1495.963562 } ] } ] }, { "best_join_order": ["NATION", "REGION", "SUPPLIER", "PARTSUPP", "PART"], "rows": 778000, "cost": 1495.963562 }, { "substitute_best_equal": { "condition": "WHERE", "resulting_condition": "PART.P_PARTKEY = PARTSUPP.PS_PARTKEY and PARTSUPP.PS_SUPPKEY = SUPPLIER.S_SUPPKEY and PART.P_SIZE = 30 and SUPPLIER.S_NATIONKEY = NATION.N_NATIONKEY and REGION.R_REGIONKEY = NATION.N_REGIONKEY and REGION.R_NAME = 'ASIA' and PART.P_TYPE like '%STEEL' and PARTSUPP.PS_SUPPLYCOST = (/* select#2 */ select min(PARTSUPP.PS_SUPPLYCOST) from PARTSUPP join SUPPLIER join NATION join REGION where PART.P_PARTKEY = PARTSUPP.PS_PARTKEY and SUPPLIER.S_SUPPKEY = PARTSUPP.PS_SUPPKEY and SUPPLIER.S_NATIONKEY = NATION.N_NATIONKEY and NATION.N_REGIONKEY = REGION.R_REGIONKEY and REGION.R_NAME = 'ASIA')" } }, { "attaching_conditions_to_tables": { "attached_conditions_computation": [], "attached_conditions_summary": [ { "table": "NATION", "attached_condition": "NATION.N_REGIONKEY is not null" }, { "table": "REGION", "attached_condition": "REGION.R_NAME = 'ASIA'" }, { "table": "SUPPLIER", "attached_condition": null }, { "table": "PARTSUPP", "attached_condition": null }, { "table": "PART", "attached_condition": "PART.P_SIZE = 30 and PART.P_TYPE like '%STEEL' and PARTSUPP.PS_SUPPLYCOST = (/* select#2 */ select min(PARTSUPP.PS_SUPPLYCOST) from PARTSUPP join SUPPLIER join NATION join REGION where PART.P_PARTKEY = PARTSUPP.PS_PARTKEY and SUPPLIER.S_SUPPKEY = PARTSUPP.PS_SUPPKEY and SUPPLIER.S_NATIONKEY = NATION.N_NATIONKEY and NATION.N_REGIONKEY = REGION.R_REGIONKEY and REGION.R_NAME = 'ASIA')" } ] } }, { "make_join_readinfo": [] }, { "join_optimization": { "select_id": 2, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "PART.P_PARTKEY = PARTSUPP.PS_PARTKEY and SUPPLIER.S_SUPPKEY = PARTSUPP.PS_SUPPKEY and SUPPLIER.S_NATIONKEY = NATION.N_NATIONKEY and NATION.N_REGIONKEY = REGION.R_REGIONKEY and REGION.R_NAME = 'ASIA'", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "PART.P_PARTKEY = PARTSUPP.PS_PARTKEY and multiple equal(SUPPLIER.S_SUPPKEY, PARTSUPP.PS_SUPPKEY) and multiple equal(SUPPLIER.S_NATIONKEY, NATION.N_NATIONKEY) and multiple equal(NATION.N_REGIONKEY, REGION.R_REGIONKEY) and multiple equal('ASIA', REGION.R_NAME)" }, { "transformation": "constant_propagation", "resulting_condition": "PART.P_PARTKEY = PARTSUPP.PS_PARTKEY and multiple equal(SUPPLIER.S_SUPPKEY, PARTSUPP.PS_SUPPKEY) and multiple equal(SUPPLIER.S_NATIONKEY, NATION.N_NATIONKEY) and multiple equal(NATION.N_REGIONKEY, REGION.R_REGIONKEY) and multiple equal('ASIA', REGION.R_NAME)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "PART.P_PARTKEY = PARTSUPP.PS_PARTKEY and multiple equal(SUPPLIER.S_SUPPKEY, PARTSUPP.PS_SUPPKEY) and multiple equal(SUPPLIER.S_NATIONKEY, NATION.N_NATIONKEY) and multiple equal(NATION.N_REGIONKEY, REGION.R_REGIONKEY) and multiple equal('ASIA', REGION.R_NAME)" } ] } }, { "table_dependencies": [ { "table": "PARTSUPP", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [] }, { "table": "SUPPLIER", "row_may_be_null": false, "map_bit": 1, "depends_on_map_bits": [] }, { "table": "NATION", "row_may_be_null": false, "map_bit": 2, "depends_on_map_bits": [] }, { "table": "REGION", "row_may_be_null": false, "map_bit": 3, "depends_on_map_bits": [] } ] }, { "ref_optimizer_key_uses": [ { "table": "PARTSUPP", "index": "PRIMARY", "field": "PS_PARTKEY", "equals": "PART.P_PARTKEY", "null_rejecting": false }, { "table": "PARTSUPP", "index": "PRIMARY", "field": "PS_SUPPKEY", "equals": "SUPPLIER.S_SUPPKEY", "null_rejecting": false }, { "table": "PARTSUPP", "index": "PARTSUPP_PART_FKIDX", "field": "PS_PARTKEY", "equals": "PART.P_PARTKEY", "null_rejecting": false }, { "table": "PARTSUPP", "index": "PARTSUPP_PART_FKIDX", "field": "PS_SUPPKEY", "equals": "SUPPLIER.S_SUPPKEY", "null_rejecting": false }, { "table": "PARTSUPP", "index": "PARTSUPP_SUPPLIER_FKIDX", "field": "PS_SUPPKEY", "equals": "SUPPLIER.S_SUPPKEY", "null_rejecting": false }, { "table": "PARTSUPP", "index": "PARTSUPP_SUPPLIER_FKIDX", "field": "PS_PARTKEY", "equals": "PART.P_PARTKEY", "null_rejecting": false }, { "table": "SUPPLIER", "index": "PRIMARY", "field": "S_SUPPKEY", "equals": "PARTSUPP.PS_SUPPKEY", "null_rejecting": false }, { "table": "SUPPLIER", "index": "SUPPLIER_NATION_FKIDX", "field": "S_NATIONKEY", "equals": "NATION.N_NATIONKEY", "null_rejecting": true }, { "table": "SUPPLIER", "index": "SUPPLIER_NATION_FKIDX", "field": "S_SUPPKEY", "equals": "PARTSUPP.PS_SUPPKEY", "null_rejecting": false }, { "table": "NATION", "index": "PRIMARY", "field": "N_NATIONKEY", "equals": "SUPPLIER.S_NATIONKEY", "null_rejecting": true }, { "table": "NATION", "index": "NATION_REGIONKEY_FKIDX", "field": "N_REGIONKEY", "equals": "REGION.R_REGIONKEY", "null_rejecting": true }, { "table": "NATION", "index": "NATION_REGIONKEY_FKIDX", "field": "N_NATIONKEY", "equals": "SUPPLIER.S_NATIONKEY", "null_rejecting": true }, { "table": "REGION", "index": "PRIMARY", "field": "R_REGIONKEY", "equals": "NATION.N_REGIONKEY", "null_rejecting": true } ] }, { "rows_estimation": [ { "table": "PARTSUPP", "table_scan": { "rows": 788022, "read_cost": 110.5545508, "read_and_compare_cost": 135.7712548 } }, { "table": "SUPPLIER", "table_scan": { "rows": 9733, "read_cost": 1.128858625, "read_and_compare_cost": 1.440314625 } }, { "table": "NATION", "table_scan": { "rows": 25, "read_cost": 0.009513045, "read_and_compare_cost": 0.010313045 } }, { "selectivity_for_indexes": [], "selectivity_for_columns": [], "cond_selectivity": 1 }, { "table": "REGION", "table_scan": { "rows": 5, "read_cost": 0.0115098, "read_and_compare_cost": 0.0116698 } } ] }, { "considered_execution_plans": [ { "plan_prefix": "", "get_costs_for_tables": [ { "best_access_path": { "table": "REGION", "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "scan", "rows": 5, "rows_after_filter": 5, "rows_out": 5, "cost": 0.0116698, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5, "rows_out": 5, "cost": 0.0116698, "uses_join_buffering": false } } }, { "best_access_path": { "table": "NATION", "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "scan", "rows": 25, "rows_after_filter": 25, "rows_out": 25, "cost": 0.010313045, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 25, "cost": 0.010313045, "uses_join_buffering": false } } }, { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "scan", "rows": 9733, "rows_after_filter": 9733, "rows_out": 9733, "cost": 1.440314625, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 9733, "rows_out": 9733, "cost": 1.440314625, "uses_join_buffering": false } } }, { "best_access_path": { "table": "PARTSUPP", "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 4, "cost": 0.00227277, "chosen": true }, { "access_type": "ref", "index": "PARTSUPP_PART_FKIDX", "rows": 3, "cost": 0.00708384, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "PARTSUPP_SUPPLIER_FKIDX", "chosen": false, "cause": "no predicate for first keypart" }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "PRIMARY", "rows_read": 4, "rows_out": 3, "cost": 0.00227277, "uses_join_buffering": false } } } ] }, { "plan_prefix": "", "table": "PARTSUPP", "rows_for_plan": 3, "cost_for_plan": 0.00227277, "pushdown_cond_selectivity": 0.75, "filtered": 75, "rows_out": 3, "rest_of_plan": [ { "plan_prefix": "PARTSUPP", "get_costs_for_tables": [ { "best_access_path": { "table": "NATION", "plan_details": { "record_count": 3 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 25, "cost": 0.017460593, "cost_without_join_buffer": 0.029300735, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 25, "cost": 0.017460593, "uses_join_buffering": true } } }, { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 3 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 0.00514092, "chosen": true }, { "access_type": "ref", "index": "SUPPLIER_NATION_FKIDX", "chosen": false, "cause": "no predicate for first keypart" }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "eq_ref", "index": "PRIMARY", "rows_read": 1, "rows_out": 1, "cost": 0.00514092, "uses_join_buffering": false } } }, { "best_access_path": { "table": "REGION", "plan_details": { "record_count": 3 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5, "rows_after_filter": 5, "rows_out": 5, "cost": 0.013245388, "cost_without_join_buffer": 0.033371, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5, "rows_out": 5, "cost": 0.013245388, "uses_join_buffering": true } } } ] }, { "plan_prefix": "PARTSUPP", "table": "SUPPLIER", "rows_for_plan": 3, "cost_for_plan": 0.00741369, "rest_of_plan": [ { "plan_prefix": "PARTSUPP,SUPPLIER", "get_costs_for_tables": [ { "best_access_path": { "table": "NATION", "plan_details": { "record_count": 3 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 0.00350252, "chosen": true }, { "access_type": "ref", "index": "NATION_REGIONKEY_FKIDX", "chosen": false, "cause": "no predicate for first keypart" }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "eq_ref", "index": "PRIMARY", "rows_read": 1, "rows_out": 1, "cost": 0.00350252, "uses_join_buffering": false } } } ] }, { "plan_prefix": "PARTSUPP,SUPPLIER", "table": "NATION", "rows_for_plan": 3, "cost_for_plan": 0.01091621, "rest_of_plan": [ { "plan_prefix": "PARTSUPP,SUPPLIER,NATION", "get_costs_for_tables": [ { "best_access_path": { "table": "REGION", "plan_details": { "record_count": 3 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 0.00350252, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "eq_ref", "index": "PRIMARY", "rows_read": 1, "rows_out": 1, "cost": 0.00350252, "uses_join_buffering": false } } } ] }, { "plan_prefix": "PARTSUPP,SUPPLIER,NATION", "table": "REGION", "rows_for_plan": 3, "cost_for_plan": 0.01441873 } ] } ], "pruned_by_hanging_leaf": true } ] }, { "plan_prefix": "", "table": "REGION", "rows_for_plan": 5, "cost_for_plan": 0.0116698, "rest_of_plan": [ { "plan_prefix": "REGION", "get_costs_for_tables": [ { "best_access_path": { "table": "NATION", "plan_details": { "record_count": 5 }, "considered_access_paths": [ { "access_type": "ref", "index": "NATION_REGIONKEY_FKIDX", "rows": 5, "cost": 0.008619925, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "NATION_REGIONKEY_FKIDX", "rows_read": 5, "rows_out": 5, "cost": 0.008619925, "uses_join_buffering": false } } }, { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 5 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 9733, "rows_after_filter": 9733, "rows_out": 9733, "cost": 5.959942845, "cost_without_join_buffer": 7.175358725, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 9733, "rows_out": 9733, "cost": 5.959942845, "uses_join_buffering": true } } }, { "best_access_path": { "table": "PARTSUPP", "plan_details": { "record_count": 5 }, "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 4, "cost": 0.01136385, "chosen": true }, { "access_type": "ref", "index": "PARTSUPP_PART_FKIDX", "rows": 3, "cost": 0.0354192, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "PARTSUPP_SUPPLIER_FKIDX", "chosen": false, "cause": "no predicate for first keypart" }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "PRIMARY", "rows_read": 4, "rows_out": 3, "cost": 0.01136385, "uses_join_buffering": false } } } ] }, { "plan_prefix": "REGION", "table": "PARTSUPP", "rows_for_plan": 15, "cost_for_plan": 0.02303365, "pruned_by_cost": true, "current_cost": 0.02303365, "best_cost": 0.01441873 }, { "plan_prefix": "REGION", "table": "NATION", "rows_for_plan": 25, "cost_for_plan": 0.020289725, "pruned_by_cost": true, "current_cost": 0.020289725, "best_cost": 0.01441873 }, { "plan_prefix": "REGION", "table": "SUPPLIER", "rows_for_plan": 48665, "cost_for_plan": 5.971612645, "pruned_by_cost": true, "current_cost": 5.971612645, "best_cost": 0.01441873 } ] }, { "plan_prefix": "", "table": "NATION", "rows_for_plan": 25, "cost_for_plan": 0.010313045, "rest_of_plan": [ { "plan_prefix": "NATION", "get_costs_for_tables": [ { "best_access_path": { "table": "REGION", "plan_details": { "record_count": 25 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 0.0231802, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 5, "rows_after_filter": 5, "rows_out": 1, "cost": 0.0247997, "cost_without_join_buffer": 0.2720842, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "index": "PRIMARY", "rows_read": 1, "rows_out": 1, "cost": 0.0231802, "uses_join_buffering": false } } }, { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 25 }, "considered_access_paths": [ { "access_type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows": 389, "cost": 1.453733225, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows_read": 389, "rows_out": 389, "cost": 1.453733225, "uses_join_buffering": false } } }, { "best_access_path": { "table": "PARTSUPP", "plan_details": { "record_count": 25 }, "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 4, "cost": 0.05681925, "chosen": true }, { "access_type": "ref", "index": "PARTSUPP_PART_FKIDX", "rows": 3, "cost": 0.177096, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "PARTSUPP_SUPPLIER_FKIDX", "chosen": false, "cause": "no predicate for first keypart" }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "PRIMARY", "rows_read": 4, "rows_out": 3, "cost": 0.05681925, "uses_join_buffering": false } } } ] }, { "plan_prefix": "NATION", "table": "REGION", "rows_for_plan": 25, "cost_for_plan": 0.033493245, "pruned_by_cost": true, "current_cost": 0.033493245, "best_cost": 0.01441873 }, { "plan_prefix": "NATION", "table": "PARTSUPP", "rows_for_plan": 75, "cost_for_plan": 0.067132295, "pruned_by_cost": true, "current_cost": 0.067132295, "best_cost": 0.01441873 }, { "plan_prefix": "NATION", "table": "SUPPLIER", "rows_for_plan": 9725, "cost_for_plan": 1.46404627, "pruned_by_cost": true, "current_cost": 1.46404627, "best_cost": 0.01441873 } ] }, { "plan_prefix": "", "table": "SUPPLIER", "rows_for_plan": 9733, "cost_for_plan": 1.440314625, "pruned_by_cost": true, "current_cost": 1.440314625, "best_cost": 0.01441873 } ] }, { "best_join_order": ["PARTSUPP", "SUPPLIER", "NATION", "REGION"], "rows": 3, "cost": 0.01441873 }, { "substitute_best_equal": { "condition": "WHERE", "resulting_condition": "SUPPLIER.S_SUPPKEY = PARTSUPP.PS_SUPPKEY and NATION.N_NATIONKEY = SUPPLIER.S_NATIONKEY and REGION.R_REGIONKEY = NATION.N_REGIONKEY and REGION.R_NAME = 'ASIA' and PART.P_PARTKEY = PARTSUPP.PS_PARTKEY" } }, { "attaching_conditions_to_tables": { "attached_conditions_computation": [], "attached_conditions_summary": [ { "table": "PARTSUPP", "attached_condition": null }, { "table": "SUPPLIER", "attached_condition": "SUPPLIER.S_NATIONKEY is not null" }, { "table": "NATION", "attached_condition": "NATION.N_REGIONKEY is not null" }, { "table": "REGION", "attached_condition": "REGION.R_NAME = 'ASIA'" } ] } }, { "make_join_readinfo": [] }, { "prepare_sum_aggregators": { "function": "min(PARTSUPP.PS_SUPPLYCOST)", "aggregator_type": "simple" } } ] } } ] } } ] }