{ "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": 0.19 }, { "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": 37670.92, "rows_out": 37670.92, "cost": 34.05438, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 37670.92, "rows_out": 37670.92, "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": 37670.92, "rows_out": 37670.92, "cost": 51.54642261, "cost_without_join_buffer": 163.652764, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 37670.92, "rows_out": 37670.92, "cost": 51.54642261, "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": 37670.92, "rows_out": 37670.92, "cost": 178.8365485, "cost_without_join_buffer": 811.644684, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 37670.92, "rows_out": 37670.92, "cost": 178.8365485, "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": 37670.92, "rows_out": 37670.92, "cost": 78944.15494, "cost_without_join_buffer": 315087.7259, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 37670.92, "rows_out": 37670.92, "cost": 78944.15494, "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": 37670.92, "rows_out": 1, "cost": 8099714.514, "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, "pushdown_cond_selectivity": 0.19, "filtered": 19, "rows_out": 0.19, "selectivity": 0.19, "estimated_join_cardinality": 147820 } ] }, { "plan_prefix": "REGION,NATION,SUPPLIER", "table": "PART", "rows_for_plan": 366349697, "cost_for_plan": 78954.01377, "pruned_by_cost": true, "current_cost": 78954.01377, "best_cost": 1495.963964 } ] }, { "plan_prefix": "REGION,NATION", "table": "PART", "rows_for_plan": 941773, "cost_for_plan": 178.8750607, "rest_of_plan": [ { "plan_prefix": "REGION,NATION,PART", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 941773 }, "considered_access_paths": [ { "access_type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows": 389, "cost": 364725.164, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 9733, "rows_after_filter": 9733, "rows_out": 389, "cost": 1967836.668, "cost_without_join_buffer": 1508918.815, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows_read": 389, "rows_out": 389, "cost": 364725.164, "uses_join_buffering": false } } }, { "best_access_path": { "table": "PARTSUPP", "plan_details": { "record_count": 941773 }, "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 4, "cost": 1375.942055, "chosen": true }, { "access_type": "ref", "index": "PARTSUPP_PART_FKIDX", "rows": 3, "cost": 3593.007341, "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": 159318789.1, "cost_without_join_buffer": 121264751.2, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "index": "PRIMARY", "rows_read": 4, "rows_out": 3, "cost": 1375.942055, "uses_join_buffering": false } } } ] }, { "plan_prefix": "REGION,NATION,PART", "table": "PARTSUPP", "rows_for_plan": 2825319, "cost_for_plan": 1554.817115, "pruned_by_cost": true, "current_cost": 1554.817115, "best_cost": 1495.963964 }, { "plan_prefix": "REGION,NATION,PART", "table": "SUPPLIER", "rows_for_plan": 366349697, "cost_for_plan": 364904.0391, "pruned_by_cost": true, "current_cost": 364904.0391, "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": 37670.92, "rows_out": 37670.92, "cost": 283227.8066, "cost_without_join_buffer": 1576727.994, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 37670.92, "rows_out": 37670.92, "cost": 283227.8066, "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": 37670.92, "rows_out": 37670.92, "cost": 395005.1226, "cost_without_join_buffer": 1576727.994, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 37670.92, "rows_out": 37670.92, "cost": 395005.1226, "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": 1833255322, "cost_for_plan": 395054.9167, "pruned_by_cost": true, "current_cost": 395054.9167, "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": 1833255322, "cost_for_plan": 283234.072, "pruned_by_cost": true, "current_cost": 283234.072, "best_cost": 1495.963964 } ] }, { "plan_prefix": "REGION", "table": "PART", "rows_for_plan": 188354.6, "cost_for_plan": 51.55809241, "rest_of_plan": [ { "plan_prefix": "REGION,PART", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 188354.6 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 9733, "rows_after_filter": 9733, "rows_out": 9733, "cost": 281945.7471, "cost_without_join_buffer": 301783.8686, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 9733, "rows_out": 9733, "cost": 281945.7471, "uses_join_buffering": true } } }, { "best_access_path": { "table": "NATION", "plan_details": { "record_count": 188354.6 }, "considered_access_paths": [ { "access_type": "ref", "index": "NATION_REGIONKEY_FKIDX", "rows": 5, "cost": 1091.313122, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 5, "cost": 736.2856335, "cost_without_join_buffer": 2657.797238, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 5, "cost": 736.2856335, "uses_join_buffering": true } } }, { "best_access_path": { "table": "PARTSUPP", "plan_details": { "record_count": 188354.6 }, "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 4, "cost": 280.7956711, "chosen": true }, { "access_type": "ref", "index": "PARTSUPP_PART_FKIDX", "rows": 3, "cost": 724.7133556, "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": 22826454.99, "cost_without_join_buffer": 24252955.84, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "index": "PRIMARY", "rows_read": 4, "rows_out": 3, "cost": 280.7956711, "uses_join_buffering": false } } } ] }, { "plan_prefix": "REGION,PART", "table": "PARTSUPP", "rows_for_plan": 565063.8, "cost_for_plan": 332.3537635, "pushdown_cond_selectivity": 0.75, "filtered": 75, "rows_out": 3, "rest_of_plan": [ { "plan_prefix": "REGION,PART,PARTSUPP", "get_costs_for_tables": [ { "best_access_path": { "table": "NATION", "plan_details": { "record_count": 565063.8 }, "considered_access_paths": [ { "access_type": "ref", "index": "NATION_REGIONKEY_FKIDX", "rows": 5, "cost": 3273.93609, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 5, "cost": 3069.037358, "cost_without_join_buffer": 7973.390075, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 5, "cost": 3069.037358, "uses_join_buffering": true } } }, { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 565063.8 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 505.5475565, "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": 1180625.788, "cost_without_join_buffer": 905351.3419, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "index": "PRIMARY", "rows_read": 1, "rows_out": 1, "cost": 505.5475565, "uses_join_buffering": false } } } ] }, { "plan_prefix": "REGION,PART,PARTSUPP", "table": "SUPPLIER", "rows_for_plan": 565063.8, "cost_for_plan": 837.90132, "rest_of_plan": [ { "plan_prefix": "REGION,PART,PARTSUPP,SUPPLIER", "get_costs_for_tables": [ { "best_access_path": { "table": "NATION", "plan_details": { "record_count": 565063.8 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 505.4164845, "chosen": true }, { "access_type": "eq_ref", "index": "NATION_REGIONKEY_FKIDX", "rows": 1, "cost": 972.5668484, "chosen": false, "cause": "cost" }, { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 1, "cost": 3934.891916, "cost_without_join_buffer": 7973.390075, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "index": "PRIMARY", "rows_read": 1, "rows_out": 1, "cost": 505.4164845, "uses_join_buffering": false } } } ] }, { "plan_prefix": "REGION,PART,PARTSUPP,SUPPLIER", "table": "NATION", "rows_for_plan": 565063.8, "cost_for_plan": 1343.317804 } ], "pruned_by_hanging_leaf": true } ] }, { "plan_prefix": "REGION,PART", "table": "NATION", "rows_for_plan": 941773, "cost_for_plan": 787.8437259, "pushdown_cond_selectivity": 0.2, "filtered": 20, "rows_out": 5, "rest_of_plan": [ { "plan_prefix": "REGION,PART,NATION", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 941773 }, "considered_access_paths": [ { "access_type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows": 389, "cost": 364725.164, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 9733, "rows_after_filter": 9733, "rows_out": 389, "cost": 1967836.668, "cost_without_join_buffer": 1508918.815, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows_read": 389, "rows_out": 389, "cost": 364725.164, "uses_join_buffering": false } } }, { "best_access_path": { "table": "PARTSUPP", "plan_details": { "record_count": 941773 }, "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 4, "cost": 1375.942055, "chosen": true }, { "access_type": "ref", "index": "PARTSUPP_PART_FKIDX", "rows": 3, "cost": 3593.007341, "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": 159318789.1, "cost_without_join_buffer": 121264751.2, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "index": "PRIMARY", "rows_read": 4, "rows_out": 3, "cost": 1375.942055, "uses_join_buffering": false } } } ] }, { "plan_prefix": "REGION,PART,NATION", "table": "PARTSUPP", "rows_for_plan": 2825319, "cost_for_plan": 2163.785781, "pruned_by_cost": true, "current_cost": 2163.785781, "best_cost": 1343.317804 }, { "plan_prefix": "REGION,PART,NATION", "table": "SUPPLIER", "rows_for_plan": 366349697, "cost_for_plan": 365513.0077, "pruned_by_cost": true, "current_cost": 365513.0077, "best_cost": 1343.317804 } ] }, { "plan_prefix": "REGION,PART", "table": "SUPPLIER", "rows_for_plan": 1833255322, "cost_for_plan": 281997.3052, "pruned_by_cost": true, "current_cost": 281997.3052, "best_cost": 1343.317804 } ] }, { "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": 37670.92, "rows_out": 1, "cost": 22838784.99, "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": 1343.317804 }, { "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": 1343.317804 }, { "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": 1343.317804 } ] } ] }, { "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": 37670.92, "rows_out": 37670.92, "cost": 121.5145931, "cost_without_join_buffer": 811.644684, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 37670.92, "rows_out": 37670.92, "cost": 121.5145931, "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": 37670.92, "rows_out": 37670.92, "cost": 178.8365485, "cost_without_join_buffer": 811.644684, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 37670.92, "rows_out": 37670.92, "cost": 178.8365485, "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": 37670.92, "rows_out": 37670.92, "cost": 78944.15494, "cost_without_join_buffer": 315087.7259, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 37670.92, "rows_out": 37670.92, "cost": 78944.15494, "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": 37670.92, "rows_out": 1, "cost": 8099714.514, "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, "pruned_by_cost": true, "current_cost": 1495.963562, "best_cost": 1343.317804 } ] }, { "plan_prefix": "NATION,REGION,SUPPLIER", "table": "PART", "rows_for_plan": 366349697, "cost_for_plan": 78954.01337, "pruned_by_cost": true, "current_cost": 78954.01337, "best_cost": 1343.317804 } ] }, { "plan_prefix": "NATION,REGION", "table": "PART", "rows_for_plan": 941773, "cost_for_plan": 178.8746585, "rest_of_plan": [ { "plan_prefix": "NATION,REGION,PART", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 941773 }, "considered_access_paths": [ { "access_type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows": 389, "cost": 364725.164, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 9733, "rows_after_filter": 9733, "rows_out": 389, "cost": 1967836.668, "cost_without_join_buffer": 1508918.815, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows_read": 389, "rows_out": 389, "cost": 364725.164, "uses_join_buffering": false } } }, { "best_access_path": { "table": "PARTSUPP", "plan_details": { "record_count": 941773 }, "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 4, "cost": 1375.942055, "chosen": true }, { "access_type": "ref", "index": "PARTSUPP_PART_FKIDX", "rows": 3, "cost": 3593.007341, "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": 159318789.1, "cost_without_join_buffer": 121264751.2, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "index": "PRIMARY", "rows_read": 4, "rows_out": 3, "cost": 1375.942055, "uses_join_buffering": false } } } ] }, { "plan_prefix": "NATION,REGION,PART", "table": "PARTSUPP", "rows_for_plan": 2825319, "cost_for_plan": 1554.816713, "pruned_by_cost": true, "current_cost": 1554.816713, "best_cost": 1343.317804 }, { "plan_prefix": "NATION,REGION,PART", "table": "SUPPLIER", "rows_for_plan": 366349697, "cost_for_plan": 364904.0387, "pruned_by_cost": true, "current_cost": 364904.0387, "best_cost": 1343.317804 } ] }, { "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": 1343.317804 } ] }, { "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": 941773, "cost_for_plan": 121.5295229, "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": 1343.317804 } ] }, { "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": 37670.92, "rows_out": 37670.92, "cost": 34310.8617, "cost_without_join_buffer": 315346.9227, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 37670.92, "rows_out": 37670.92, "cost": 34310.8617, "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": 37670.92, "rows_out": 37670.92, "cost": 56659.84498, "cost_without_join_buffer": 315346.9227, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 37670.92, "rows_out": 37670.92, "cost": 56659.84498, "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": 37670.92, "rows_out": 37670.92, "cost": 79008.82826, "cost_without_join_buffer": 315346.9227, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 37670.92, "rows_out": 37670.92, "cost": 79008.82826, "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": 37670.92, "rows_out": 1, "cost": 8106355.822, "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": 1343.317804 } ] }, { "plan_prefix": "SUPPLIER,NATION,REGION", "table": "PART", "rows_for_plan": 366651064.4, "cost_for_plan": 79027.44102, "pruned_by_cost": true, "current_cost": 79027.44102, "best_cost": 1343.317804 } ] }, { "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": 366651064.4, "cost_for_plan": 56670.28549, "pruned_by_cost": true, "current_cost": 56670.28549, "best_cost": 1343.317804 } ] }, { "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": 37670.92, "rows_out": 37670.92, "cost": 283227.8066, "cost_without_join_buffer": 1576727.994, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 37670.92, "rows_out": 37670.92, "cost": 283227.8066, "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": 37670.92, "rows_out": 37670.92, "cost": 395005.1226, "cost_without_join_buffer": 1576727.994, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 37670.92, "rows_out": 37670.92, "cost": 395005.1226, "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": 1343.317804 }, { "plan_prefix": "SUPPLIER,REGION,NATION", "table": "PART", "rows_for_plan": 1833255322, "cost_for_plan": 395055.5848, "pruned_by_cost": true, "current_cost": 395055.5848, "best_cost": 1343.317804 } ] }, { "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": 1343.317804 }, { "plan_prefix": "SUPPLIER,REGION", "table": "PART", "rows_for_plan": 1833255322, "cost_for_plan": 283234.74, "pruned_by_cost": true, "current_cost": 283234.74, "best_cost": 1343.317804 } ] }, { "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": 366651064.4, "cost_for_plan": 34312.59581, "pruned_by_cost": true, "current_cost": 34312.59581, "best_cost": 1343.317804 } ] }, { "plan_prefix": "", "table": "PART", "rows_for_plan": 37670.92, "cost_for_plan": 34.05438, "rest_of_plan": [ { "plan_prefix": "PART", "get_costs_for_tables": [ { "best_access_path": { "table": "NATION", "plan_details": { "record_count": 37670.92 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 25, "cost": 89.87938423, "cost_without_join_buffer": 531.560103, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 25, "cost": 89.87938423, "uses_join_buffering": true } } }, { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 37670.92 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 9733, "rows_after_filter": 9733, "rows_out": 9733, "cost": 34066.26241, "cost_without_join_buffer": 60356.87923, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 9733, "rows_out": 9733, "cost": 34066.26241, "uses_join_buffering": true } } }, { "best_access_path": { "table": "REGION", "plan_details": { "record_count": 37670.92 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5, "rows_after_filter": 5, "rows_out": 5, "cost": 19.8830911, "cost_without_join_buffer": 408.7529038, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5, "rows_out": 5, "cost": 19.8830911, "uses_join_buffering": true } } }, { "best_access_path": { "table": "PARTSUPP", "plan_details": { "record_count": 37670.92 }, "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 4, "cost": 61.76639438, "chosen": true }, { "access_type": "ref", "index": "PARTSUPP_PART_FKIDX", "rows": 3, "cost": 151.0545585, "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": 61.76639438, "uses_join_buffering": false } } } ] }, { "plan_prefix": "PART", "table": "PARTSUPP", "rows_for_plan": 113012.76, "cost_for_plan": 95.82077438, "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": 113012.76 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 101.2150243, "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": 169156.2855, "cost_without_join_buffer": 181070.3739, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "index": "PRIMARY", "rows_read": 1, "rows_out": 1, "cost": 101.2150243, "uses_join_buffering": false } } }, { "best_access_path": { "table": "REGION", "plan_details": { "record_count": 113012.76 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5, "rows_after_filter": 5, "rows_out": 5, "cost": 94.09506115, "cost_without_join_buffer": 1226.257073, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5, "rows_out": 5, "cost": 94.09506115, "uses_join_buffering": true } } }, { "best_access_path": { "table": "NATION", "plan_details": { "record_count": 113012.76 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 25, "cost": 441.6729336, "cost_without_join_buffer": 1594.67867, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 25, "cost": 441.6729336, "uses_join_buffering": true } } } ] }, { "plan_prefix": "PART,PARTSUPP", "table": "SUPPLIER", "rows_for_plan": 113012.76, "cost_for_plan": 197.0357986, "rest_of_plan": [ { "plan_prefix": "PART,PARTSUPP,SUPPLIER", "get_costs_for_tables": [ { "best_access_path": { "table": "REGION", "plan_details": { "record_count": 113012.76 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5, "rows_after_filter": 5, "rows_out": 5, "cost": 129.4105354, "cost_without_join_buffer": 1226.257073, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5, "rows_out": 5, "cost": 129.4105354, "uses_join_buffering": true } } }, { "best_access_path": { "table": "NATION", "plan_details": { "record_count": 113012.76 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 101.0839523, "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": 614.8382008, "cost_without_join_buffer": 1594.67867, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "index": "PRIMARY", "rows_read": 1, "rows_out": 1, "cost": 101.0839523, "uses_join_buffering": false } } } ] }, { "plan_prefix": "PART,PARTSUPP,SUPPLIER", "table": "NATION", "rows_for_plan": 113012.76, "cost_for_plan": 298.1197509, "rest_of_plan": [ { "plan_prefix": "PART,PARTSUPP,SUPPLIER,NATION", "get_costs_for_tables": [ { "best_access_path": { "table": "REGION", "plan_details": { "record_count": 113012.76 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 101.0839523, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 5, "rows_after_filter": 5, "rows_out": 1, "cost": 163.9664677, "cost_without_join_buffer": 1226.257073, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "index": "PRIMARY", "rows_read": 1, "rows_out": 1, "cost": 101.0839523, "uses_join_buffering": false } } } ] }, { "plan_prefix": "PART,PARTSUPP,SUPPLIER,NATION", "table": "REGION", "rows_for_plan": 113012.76, "cost_for_plan": 399.2037031 } ] } ], "pruned_by_hanging_leaf": true } ] }, { "plan_prefix": "PART", "table": "REGION", "rows_for_plan": 188354.6, "cost_for_plan": 53.9374711, "rest_of_plan": [ { "plan_prefix": "PART,REGION", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 188354.6 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 9733, "rows_after_filter": 9733, "rows_out": 9733, "cost": 281945.7471, "cost_without_join_buffer": 301783.8686, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 9733, "rows_out": 9733, "cost": 281945.7471, "uses_join_buffering": true } } }, { "best_access_path": { "table": "NATION", "plan_details": { "record_count": 188354.6 }, "considered_access_paths": [ { "access_type": "ref", "index": "NATION_REGIONKEY_FKIDX", "rows": 5, "cost": 1091.313122, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 5, "cost": 736.2856335, "cost_without_join_buffer": 2657.797238, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 5, "cost": 736.2856335, "uses_join_buffering": true } } }, { "best_access_path": { "table": "PARTSUPP", "plan_details": { "record_count": 188354.6 }, "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 4, "cost": 280.7956711, "chosen": true }, { "access_type": "ref", "index": "PARTSUPP_PART_FKIDX", "rows": 3, "cost": 724.7133556, "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": 22826454.99, "cost_without_join_buffer": 24252955.84, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "index": "PRIMARY", "rows_read": 4, "rows_out": 3, "cost": 280.7956711, "uses_join_buffering": false } } } ] }, { "plan_prefix": "PART,REGION", "table": "PARTSUPP", "rows_for_plan": 565063.8, "cost_for_plan": 334.7331422, "pushdown_cond_selectivity": 0.75, "filtered": 75, "rows_out": 3, "rest_of_plan": [ { "plan_prefix": "PART,REGION,PARTSUPP", "get_costs_for_tables": [ { "best_access_path": { "table": "NATION", "plan_details": { "record_count": 565063.8 }, "considered_access_paths": [ { "access_type": "ref", "index": "NATION_REGIONKEY_FKIDX", "rows": 5, "cost": 3273.93609, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 5, "cost": 3069.037358, "cost_without_join_buffer": 7973.390075, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 5, "cost": 3069.037358, "uses_join_buffering": true } } }, { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 565063.8 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 505.5475565, "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": 1180625.788, "cost_without_join_buffer": 905351.3419, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "index": "PRIMARY", "rows_read": 1, "rows_out": 1, "cost": 505.5475565, "uses_join_buffering": false } } } ] }, { "plan_prefix": "PART,REGION,PARTSUPP", "table": "SUPPLIER", "rows_for_plan": 565063.8, "cost_for_plan": 840.2806987, "pruned_by_cost": true, "current_cost": 840.2806987, "best_cost": 399.2037031 }, { "plan_prefix": "PART,REGION,PARTSUPP", "table": "NATION", "rows_for_plan": 2825319, "cost_for_plan": 3403.7705, "pruned_by_cost": true, "current_cost": 3403.7705, "best_cost": 399.2037031 } ] }, { "plan_prefix": "PART,REGION", "table": "NATION", "rows_for_plan": 941773, "cost_for_plan": 790.2231046, "pruned_by_cost": true, "current_cost": 790.2231046, "best_cost": 399.2037031 }, { "plan_prefix": "PART,REGION", "table": "SUPPLIER", "rows_for_plan": 1833255322, "cost_for_plan": 281999.6846, "pruned_by_cost": true, "current_cost": 281999.6846, "best_cost": 399.2037031 } ] }, { "plan_prefix": "PART", "table": "NATION", "rows_for_plan": 941773, "cost_for_plan": 123.9337642, "rest_of_plan": [ { "plan_prefix": "PART,NATION", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 941773 }, "considered_access_paths": [ { "access_type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows": 389, "cost": 364725.164, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 9733, "rows_after_filter": 9733, "rows_out": 389, "cost": 1409749.037, "cost_without_join_buffer": 1508918.815, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows_read": 389, "rows_out": 389, "cost": 364725.164, "uses_join_buffering": false } } }, { "best_access_path": { "table": "REGION", "plan_details": { "record_count": 941773 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 842.3602613, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 5, "rows_after_filter": 5, "rows_out": 1, "cost": 784.8935116, "cost_without_join_buffer": 10218.80293, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5, "rows_out": 1, "cost": 784.8935116, "uses_join_buffering": true } } }, { "best_access_path": { "table": "PARTSUPP", "plan_details": { "record_count": 941773 }, "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 4, "cost": 1375.942055, "chosen": true }, { "access_type": "ref", "index": "PARTSUPP_PART_FKIDX", "rows": 3, "cost": 3593.007341, "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": 114133920.8, "cost_without_join_buffer": 121264751.2, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "index": "PRIMARY", "rows_read": 4, "rows_out": 3, "cost": 1375.942055, "uses_join_buffering": false } } } ] }, { "plan_prefix": "PART,NATION", "table": "PARTSUPP", "rows_for_plan": 2825319, "cost_for_plan": 1499.875819, "pruned_by_cost": true, "current_cost": 1499.875819, "best_cost": 399.2037031 }, { "plan_prefix": "PART,NATION", "table": "REGION", "rows_for_plan": 941773, "cost_for_plan": 908.8272758, "pruned_by_cost": true, "current_cost": 908.8272758, "best_cost": 399.2037031 }, { "plan_prefix": "PART,NATION", "table": "SUPPLIER", "rows_for_plan": 366349697, "cost_for_plan": 364849.0978, "pruned_by_cost": true, "current_cost": 364849.0978, "best_cost": 399.2037031 } ] }, { "plan_prefix": "PART", "table": "SUPPLIER", "rows_for_plan": 366651064.4, "cost_for_plan": 34100.31679, "pruned_by_cost": true, "current_cost": 34100.31679, "best_cost": 399.2037031 } ] }, { "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": 37670.92, "rows_out": 1, "cost": 2758023.321, "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, "pruned_by_cost": true, "current_cost": 842.2644365, "best_cost": 399.2037031 }, { "plan_prefix": "PARTSUPP", "table": "SUPPLIER", "rows_for_plan": 788022, "cost_for_plan": 840.7415437, "pruned_by_cost": true, "current_cost": 840.7415437, "best_cost": 399.2037031 }, { "plan_prefix": "PARTSUPP", "table": "REGION", "rows_for_plan": 3940110, "cost_for_plan": 550.0395485, "pruned_by_cost": true, "current_cost": 550.0395485, "best_cost": 399.2037031 }, { "plan_prefix": "PARTSUPP", "table": "NATION", "rows_for_plan": 19700550, "cost_for_plan": 2013.76919, "pruned_by_cost": true, "current_cost": 2013.76919, "best_cost": 399.2037031 } ] } ] }, { "best_join_order": ["PART", "PARTSUPP", "SUPPLIER", "NATION", "REGION"], "rows": 113012.76, "cost": 399.2037031 }, { "substitute_best_equal": { "condition": "WHERE", "resulting_condition": "PARTSUPP.PS_PARTKEY = PART.P_PARTKEY and SUPPLIER.S_SUPPKEY = PARTSUPP.PS_SUPPKEY and PART.P_SIZE = 30 and NATION.N_NATIONKEY = SUPPLIER.S_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": "PART", "attached_condition": "PART.P_SIZE = 30 and PART.P_TYPE like '%STEEL'" }, { "table": "PARTSUPP", "attached_condition": "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')" }, { "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": [] }, { "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" } } ] } } ] } } ] }