{ "steps": [ { "join_preparation": { "select_id": 1, "steps": [ { "derived": { "table": "shipping", "select_id": 2, "algorithm": "merged" } }, { "join_preparation": { "select_id": 2, "steps": [ { "expanded_query": "/* select#2 */ select n1.N_NAME AS supp_nation,n2.N_NAME AS cust_nation,extract(year from LINEITEM.L_SHIPDATE) AS l_year,LINEITEM.L_EXTENDEDPRICE * (1 - LINEITEM.L_DISCOUNT) AS volume from SUPPLIER join LINEITEM join ORDERS join CUSTOMER join NATION n1 join NATION n2 where SUPPLIER.S_SUPPKEY = LINEITEM.L_SUPPKEY and ORDERS.O_ORDERKEY = LINEITEM.L_ORDERKEY and CUSTOMER.C_CUSTKEY = ORDERS.O_CUSTKEY and SUPPLIER.S_NATIONKEY = n1.N_NATIONKEY and CUSTOMER.C_NATIONKEY = n2.N_NATIONKEY and (n1.N_NAME = 'JAPAN' and n2.N_NAME = 'INDIA' or n1.N_NAME = 'INDIA' and n2.N_NAME = 'JAPAN') and LINEITEM.L_SHIPDATE between DATE'1995-01-01' and DATE'1996-12-31'" } ] } }, { "expanded_query": "/* select#1 */ select shipping.supp_nation AS supp_nation,shipping.cust_nation AS cust_nation,shipping.l_year AS l_year,sum(shipping.volume) AS revenue from (/* select#2 */ select n1.N_NAME AS supp_nation,n2.N_NAME AS cust_nation,extract(year from LINEITEM.L_SHIPDATE) AS l_year,LINEITEM.L_EXTENDEDPRICE * (1 - LINEITEM.L_DISCOUNT) AS volume from SUPPLIER join LINEITEM join ORDERS join CUSTOMER join NATION n1 join NATION n2 where SUPPLIER.S_SUPPKEY = LINEITEM.L_SUPPKEY and ORDERS.O_ORDERKEY = LINEITEM.L_ORDERKEY and CUSTOMER.C_CUSTKEY = ORDERS.O_CUSTKEY and SUPPLIER.S_NATIONKEY = n1.N_NATIONKEY and CUSTOMER.C_NATIONKEY = n2.N_NATIONKEY and (n1.N_NAME = 'JAPAN' and n2.N_NAME = 'INDIA' or n1.N_NAME = 'INDIA' and n2.N_NAME = 'JAPAN') and LINEITEM.L_SHIPDATE between DATE'1995-01-01' and DATE'1996-12-31') shipping group by shipping.supp_nation,shipping.cust_nation,shipping.l_year order by shipping.supp_nation,shipping.cust_nation,shipping.l_year" } ] } }, { "join_optimization": { "select_id": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "SUPPLIER.S_SUPPKEY = LINEITEM.L_SUPPKEY and ORDERS.O_ORDERKEY = LINEITEM.L_ORDERKEY and CUSTOMER.C_CUSTKEY = ORDERS.O_CUSTKEY and SUPPLIER.S_NATIONKEY = n1.N_NATIONKEY and CUSTOMER.C_NATIONKEY = n2.N_NATIONKEY and (n1.N_NAME = 'JAPAN' and n2.N_NAME = 'INDIA' or n1.N_NAME = 'INDIA' and n2.N_NAME = 'JAPAN') and LINEITEM.L_SHIPDATE between DATE'1995-01-01' and DATE'1996-12-31'", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(multiple equal('JAPAN', n1.N_NAME) and multiple equal('INDIA', n2.N_NAME) or multiple equal('INDIA', n1.N_NAME) and multiple equal('JAPAN', n2.N_NAME)) and LINEITEM.L_SHIPDATE between DATE'1995-01-01' and DATE'1996-12-31' and multiple equal(SUPPLIER.S_SUPPKEY, LINEITEM.L_SUPPKEY) and multiple equal(ORDERS.O_ORDERKEY, LINEITEM.L_ORDERKEY) and multiple equal(CUSTOMER.C_CUSTKEY, ORDERS.O_CUSTKEY) and multiple equal(SUPPLIER.S_NATIONKEY, n1.N_NATIONKEY) and multiple equal(CUSTOMER.C_NATIONKEY, n2.N_NATIONKEY)" }, { "transformation": "constant_propagation", "resulting_condition": "(multiple equal('JAPAN', n1.N_NAME) and multiple equal('INDIA', n2.N_NAME) or multiple equal('INDIA', n1.N_NAME) and multiple equal('JAPAN', n2.N_NAME)) and LINEITEM.L_SHIPDATE between DATE'1995-01-01' and DATE'1996-12-31' and multiple equal(SUPPLIER.S_SUPPKEY, LINEITEM.L_SUPPKEY) and multiple equal(ORDERS.O_ORDERKEY, LINEITEM.L_ORDERKEY) and multiple equal(CUSTOMER.C_CUSTKEY, ORDERS.O_CUSTKEY) and multiple equal(SUPPLIER.S_NATIONKEY, n1.N_NATIONKEY) and multiple equal(CUSTOMER.C_NATIONKEY, n2.N_NATIONKEY)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(multiple equal('JAPAN', n1.N_NAME) and multiple equal('INDIA', n2.N_NAME) or multiple equal('INDIA', n1.N_NAME) and multiple equal('JAPAN', n2.N_NAME)) and LINEITEM.L_SHIPDATE between DATE'1995-01-01' and DATE'1996-12-31' and multiple equal(SUPPLIER.S_SUPPKEY, LINEITEM.L_SUPPKEY) and multiple equal(ORDERS.O_ORDERKEY, LINEITEM.L_ORDERKEY) and multiple equal(CUSTOMER.C_CUSTKEY, ORDERS.O_CUSTKEY) and multiple equal(SUPPLIER.S_NATIONKEY, n1.N_NATIONKEY) and multiple equal(CUSTOMER.C_NATIONKEY, n2.N_NATIONKEY)" } ] } }, { "table_dependencies": [ { "table": "SUPPLIER", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [] }, { "table": "LINEITEM", "row_may_be_null": false, "map_bit": 1, "depends_on_map_bits": [] }, { "table": "ORDERS", "row_may_be_null": false, "map_bit": 2, "depends_on_map_bits": [] }, { "table": "CUSTOMER", "row_may_be_null": false, "map_bit": 3, "depends_on_map_bits": [] }, { "table": "n1", "row_may_be_null": false, "map_bit": 4, "depends_on_map_bits": [] }, { "table": "n2", "row_may_be_null": false, "map_bit": 5, "depends_on_map_bits": [] } ] }, { "ref_optimizer_key_uses": [ { "table": "SUPPLIER", "index": "PRIMARY", "field": "S_SUPPKEY", "equals": "LINEITEM.L_SUPPKEY", "null_rejecting": false }, { "table": "SUPPLIER", "index": "SUPPLIER_NATION_FKIDX", "field": "S_NATIONKEY", "equals": "n1.N_NATIONKEY", "null_rejecting": true }, { "table": "SUPPLIER", "index": "SUPPLIER_NATION_FKIDX", "field": "S_SUPPKEY", "equals": "LINEITEM.L_SUPPKEY", "null_rejecting": false }, { "table": "LINEITEM", "index": "PRIMARY", "field": "L_ORDERKEY", "equals": "ORDERS.O_ORDERKEY", "null_rejecting": false }, { "table": "LINEITEM", "index": "IDX_LINEITEM_ORDERKEY_FKIDX", "field": "L_ORDERKEY", "equals": "ORDERS.O_ORDERKEY", "null_rejecting": false }, { "table": "ORDERS", "index": "PRIMARY", "field": "O_ORDERKEY", "equals": "LINEITEM.L_ORDERKEY", "null_rejecting": false }, { "table": "ORDERS", "index": "ORDERS_FK1", "field": "O_CUSTKEY", "equals": "CUSTOMER.C_CUSTKEY", "null_rejecting": false }, { "table": "ORDERS", "index": "ORDERS_FK1", "field": "O_ORDERKEY", "equals": "LINEITEM.L_ORDERKEY", "null_rejecting": false }, { "table": "CUSTOMER", "index": "PRIMARY", "field": "C_CUSTKEY", "equals": "ORDERS.O_CUSTKEY", "null_rejecting": false }, { "table": "CUSTOMER", "index": "CUSTOMER_NATION_FKIDX", "field": "C_NATIONKEY", "equals": "n2.N_NATIONKEY", "null_rejecting": true }, { "table": "CUSTOMER", "index": "CUSTOMER_NATION_FKIDX", "field": "C_CUSTKEY", "equals": "ORDERS.O_CUSTKEY", "null_rejecting": false }, { "table": "n1", "index": "PRIMARY", "field": "N_NATIONKEY", "equals": "SUPPLIER.S_NATIONKEY", "null_rejecting": true }, { "table": "n2", "index": "PRIMARY", "field": "N_NATIONKEY", "equals": "CUSTOMER.C_NATIONKEY", "null_rejecting": true } ] }, { "rows_estimation": [ { "table": "SUPPLIER", "table_scan": { "rows": 9750, "read_cost": 1.13080827, "read_and_compare_cost": 1.44280827 } }, { "selectivity_for_indexes": [], "selectivity_for_columns": [], "cond_selectivity": 1 }, { "table": "LINEITEM", "table_scan": { "rows": 5082399, "read_cost": 704.372845, "read_and_compare_cost": 867.009613 } }, { "table": "ORDERS", "table_scan": { "rows": 1488318, "read_cost": 171.9364201, "read_and_compare_cost": 219.5625961 } }, { "table": "CUSTOMER", "table_scan": { "rows": 148188, "read_cost": 17.0939051, "read_and_compare_cost": 21.8359211 } }, { "selectivity_for_indexes": [], "selectivity_for_columns": [], "cond_selectivity": 1 }, { "table": "n1", "table_scan": { "rows": 25, "read_cost": 0.0141298, "read_and_compare_cost": 0.0149298 } }, { "selectivity_for_indexes": [], "selectivity_for_columns": [], "cond_selectivity": 1 }, { "table": "n2", "table_scan": { "rows": 25, "read_cost": 0.0141298, "read_and_compare_cost": 0.0149298 } } ] }, { "considered_execution_plans": [ { "plan_prefix": "", "get_costs_for_tables": [ { "best_access_path": { "table": "n1", "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": "n2", "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": 9750, "rows_after_filter": 9750, "rows_out": 9750, "cost": 1.44280827, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 9750, "rows_out": 9750, "cost": 1.44280827, "uses_join_buffering": false } } }, { "best_access_path": { "table": "CUSTOMER", "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "scan", "rows": 148188, "rows_after_filter": 148188, "rows_out": 148188, "cost": 21.8359211, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 148188, "rows_out": 148188, "cost": 21.8359211, "uses_join_buffering": false } } }, { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "scan", "rows": 1488318, "rows_after_filter": 1488318, "rows_out": 1488318, "cost": 219.5625961, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 1488318, "rows_out": 1488318, "cost": 219.5625961, "uses_join_buffering": false } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "scan", "rows": 5082399, "rows_after_filter": 5082399, "rows_out": 5082399, "cost": 867.009613, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5082399, "rows_out": 5082399, "cost": 867.009613, "uses_join_buffering": false } } } ] }, { "plan_prefix": "", "table": "n1", "rows_for_plan": 25, "cost_for_plan": 0.0149298, "rest_of_plan": [ { "plan_prefix": "n1", "get_costs_for_tables": [ { "best_access_path": { "table": "n2", "plan_details": { "record_count": 25 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 25, "cost": 0.0744927, "cost_without_join_buffer": 0.3535842, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 25, "cost": 0.0744927, "uses_join_buffering": true } } }, { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 25 }, "considered_access_paths": [ { "access_type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows": 390, "cost": 1.45740035, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows_read": 390, "rows_out": 390, "cost": 1.45740035, "uses_join_buffering": false } } }, { "best_access_path": { "table": "CUSTOMER", "plan_details": { "record_count": 25 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 148188, "rows_after_filter": 148188, "rows_out": 148188, "cost": 365.878113, "cost_without_join_buffer": 543.7550003, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 148188, "rows_out": 148188, "cost": 365.878113, "uses_join_buffering": true } } }, { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 25 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 1488318, "rows_after_filter": 1488318, "rows_out": 1488318, "cost": 3674.917603, "cost_without_join_buffer": 5460.478101, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 1488318, "rows_out": 1488318, "cost": 3674.917603, "uses_join_buffering": true } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 25 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5082399, "rows_after_filter": 5082399, "rows_out": 5082399, "cost": 12666.56277, "cost_without_join_buffer": 20788.14503, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5082399, "rows_out": 5082399, "cost": 12666.56277, "uses_join_buffering": true } } } ] }, { "plan_prefix": "n1", "table": "n2", "rows_for_plan": 625, "cost_for_plan": 0.0894225, "rest_of_plan": [ { "plan_prefix": "n1,n2", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 625 }, "considered_access_paths": [ { "access_type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows": 390, "cost": 36.27772235, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows_read": 390, "rows_out": 390, "cost": 36.27772235, "uses_join_buffering": false } } }, { "best_access_path": { "table": "CUSTOMER", "plan_details": { "record_count": 625 }, "considered_access_paths": [ { "access_type": "ref", "index": "CUSTOMER_NATION_FKIDX", "rows": 6174, "cost": 566.7157365, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "CUSTOMER_NATION_FKIDX", "rows_read": 6174, "rows_out": 6174, "cost": 566.7157365, "uses_join_buffering": false } } }, { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 625 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 1488318, "rows_after_filter": 1488318, "rows_out": 1488318, "cost": 143220.9149, "cost_without_join_buffer": 136483.3657, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 1488318, "rows_out": 1488318, "cost": 143220.9149, "uses_join_buffering": true } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 625 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5082399, "rows_after_filter": 5082399, "rows_out": 5082399, "cost": 489196.6496, "cost_without_join_buffer": 518816.5304, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5082399, "rows_out": 5082399, "cost": 489196.6496, "uses_join_buffering": true } } } ] }, { "plan_prefix": "n1,n2", "table": "SUPPLIER", "rows_for_plan": 243750, "cost_for_plan": 36.36714485, "rest_of_plan": [ { "plan_prefix": "n1,n2,SUPPLIER", "get_costs_for_tables": [ { "best_access_path": { "table": "CUSTOMER", "plan_details": { "record_count": 243750 }, "considered_access_paths": [ { "access_type": "ref", "index": "CUSTOMER_NATION_FKIDX", "rows": 6174, "cost": 220984.4024, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "CUSTOMER_NATION_FKIDX", "rows_read": 6174, "rows_out": 6174, "cost": 220984.4024, "uses_join_buffering": false } } }, { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 243750 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 1488318, "rows_after_filter": 1488318, "rows_out": 1488318, "cost": 77865538.8, "cost_without_join_buffer": 53228049.28, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 1488318, "rows_out": 1488318, "cost": 77865538.8, "uses_join_buffering": true } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 243750 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5082399, "rows_after_filter": 5082399, "rows_out": 5082399, "cost": 265905465.9, "cost_without_join_buffer": 202324068.5, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5082399, "rows_out": 5082399, "cost": 265905465.9, "uses_join_buffering": true } } } ] }, { "plan_prefix": "n1,n2,SUPPLIER", "table": "CUSTOMER", "rows_for_plan": 1504912500, "cost_for_plan": 221020.7695, "rest_of_plan": [ { "plan_prefix": "n1,n2,SUPPLIER,CUSTOMER", "get_costs_for_tables": [ { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 1504912500 }, "considered_access_paths": [ { "access_type": "ref", "index": "ORDERS_FK1", "rows": 14, "cost": 4334615.714, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "ORDERS_FK1", "rows_read": 14, "rows_out": 14, "cost": 4334615.714, "uses_join_buffering": false } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 1504912500 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5082399, "rows_after_filter": 5082399, "rows_out": 5082399, "cost": 2.107276e12, "cost_without_join_buffer": 1.249149e12, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5082399, "rows_out": 5082399, "cost": 2.107276e12, "uses_join_buffering": true } } } ] }, { "plan_prefix": "n1,n2,SUPPLIER,CUSTOMER", "table": "ORDERS", "rows_for_plan": 21068775000, "cost_for_plan": 4555636.483, "rest_of_plan": [ { "plan_prefix": "n1,n2,SUPPLIER,CUSTOMER,ORDERS", "get_costs_for_tables": [ { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 21068775000 }, "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 4, "cost": 30624976.24, "chosen": true }, { "access_type": "ref", "index": "IDX_LINEITEM_ORDERKEY_FKIDX", "rows": 4, "cost": 101140276.8, "chosen": false, "cause": "cost" }, { "access_type": "scan_with_join_cache", "rows": 5082399, "rows_after_filter": 5082399, "rows_out": 4, "cost": 3.60202e13, "cost_without_join_buffer": 1.748808e13, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "index": "PRIMARY", "rows_read": 4, "rows_out": 4, "cost": 30624976.24, "uses_join_buffering": false } } } ] }, { "plan_prefix": "n1,n2,SUPPLIER,CUSTOMER,ORDERS", "table": "LINEITEM", "rows_for_plan": 84275100000, "cost_for_plan": 35180612.72 } ] }, { "plan_prefix": "n1,n2,SUPPLIER,CUSTOMER", "table": "LINEITEM", "rows_for_plan": 7.648566e15, "cost_for_plan": 2.107277e12, "pruned_by_cost": true, "current_cost": 2.107277e12, "best_cost": 35180612.72 } ] }, { "plan_prefix": "n1,n2,SUPPLIER", "table": "ORDERS", "rows_for_plan": 3.627775e11, "cost_for_plan": 77865575.17, "pruned_by_cost": true, "current_cost": 77865575.17, "best_cost": 35180612.72 }, { "plan_prefix": "n1,n2,SUPPLIER", "table": "LINEITEM", "rows_for_plan": 1.238835e12, "cost_for_plan": 265905502.3, "pruned_by_cost": true, "current_cost": 265905502.3, "best_cost": 35180612.72 } ] }, { "plan_prefix": "n1,n2", "table": "CUSTOMER", "rows_for_plan": 3858750, "cost_for_plan": 566.805159, "rest_of_plan": [ { "plan_prefix": "n1,n2,CUSTOMER", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 3858750 }, "considered_access_paths": [ { "access_type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows": 390, "cost": 223938.2024, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows_read": 390, "rows_out": 390, "cost": 223938.2024, "uses_join_buffering": false } } }, { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 3858750 }, "considered_access_paths": [ { "access_type": "ref", "index": "ORDERS_FK1", "rows": 14, "cost": 11115.58733, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "ORDERS_FK1", "rows_read": 14, "rows_out": 14, "cost": 11115.58733, "uses_join_buffering": false } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 3858750 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5082399, "rows_after_filter": 5082399, "rows_out": 5082399, "cost": 4209476729, "cost_without_join_buffer": 3202945091, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5082399, "rows_out": 5082399, "cost": 4209476729, "uses_join_buffering": true } } } ] }, { "plan_prefix": "n1,n2,CUSTOMER", "table": "ORDERS", "rows_for_plan": 54022500, "cost_for_plan": 11682.39249, "rest_of_plan": [ { "plan_prefix": "n1,n2,CUSTOMER,ORDERS", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 54022500 }, "considered_access_paths": [ { "access_type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows": 390, "cost": 3135134.749, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows_read": 390, "rows_out": 390, "cost": 3135134.749, "uses_join_buffering": false } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 54022500 }, "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 4, "cost": 78562.44763, "chosen": true }, { "access_type": "ref", "index": "IDX_LINEITEM_ORDERKEY_FKIDX", "rows": 4, "cost": 259377.6703, "chosen": false, "cause": "cost" }, { "access_type": "scan_with_join_cache", "rows": 5082399, "rows_after_filter": 5082399, "rows_out": 4, "cost": 75646329448, "cost_without_join_buffer": 44841230787, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "index": "PRIMARY", "rows_read": 4, "rows_out": 4, "cost": 78562.44763, "uses_join_buffering": false } } } ] }, { "plan_prefix": "n1,n2,CUSTOMER,ORDERS", "table": "LINEITEM", "rows_for_plan": 216090000, "cost_for_plan": 90244.84012, "rest_of_plan": [ { "plan_prefix": "n1,n2,CUSTOMER,ORDERS,LINEITEM", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 216090000 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "prev_record_count": 25, "rows": 1, "cost": 6914.922041, "chosen": true }, { "access_type": "eq_ref", "index": "SUPPLIER_NATION_FKIDX", "prev_record_count": 25, "rows": 1, "cost": 6914.907614, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "eq_ref", "index": "SUPPLIER_NATION_FKIDX", "rows_read": 1, "rows_out": 1, "cost": 6914.907614, "uses_join_buffering": false } } } ] }, { "plan_prefix": "n1,n2,CUSTOMER,ORDERS,LINEITEM", "table": "SUPPLIER", "rows_for_plan": 216090000, "cost_for_plan": 97159.74773 } ] }, { "plan_prefix": "n1,n2,CUSTOMER,ORDERS", "table": "SUPPLIER", "rows_for_plan": 21068775000, "cost_for_plan": 3146817.141, "pruned_by_cost": true, "current_cost": 3146817.141, "best_cost": 97159.74773 } ] }, { "plan_prefix": "n1,n2,CUSTOMER", "table": "SUPPLIER", "rows_for_plan": 1504912500, "cost_for_plan": 224505.0076, "pruned_by_cost": true, "current_cost": 224505.0076, "best_cost": 97159.74773 }, { "plan_prefix": "n1,n2,CUSTOMER", "table": "LINEITEM", "rows_for_plan": 1.961171e13, "cost_for_plan": 4209477296, "pruned_by_cost": true, "current_cost": 4209477296, "best_cost": 97159.74773 } ] }, { "plan_prefix": "n1,n2", "table": "ORDERS", "rows_for_plan": 930198750, "cost_for_plan": 143221.0043, "pruned_by_cost": true, "current_cost": 143221.0043, "best_cost": 97159.74773 }, { "plan_prefix": "n1,n2", "table": "LINEITEM", "rows_for_plan": 3176499375, "cost_for_plan": 489196.739, "pruned_by_cost": true, "current_cost": 489196.739, "best_cost": 97159.74773 } ] }, { "plan_prefix": "n1", "table": "SUPPLIER", "rows_for_plan": 9750, "cost_for_plan": 1.47233015, "rest_of_plan": [ { "plan_prefix": "n1,SUPPLIER", "get_costs_for_tables": [ { "best_access_path": { "table": "n2", "plan_details": { "record_count": 9750 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 25, "cost": 38.0946589, "cost_without_join_buffer": 137.5791692, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 25, "cost": 38.0946589, "uses_join_buffering": true } } }, { "best_access_path": { "table": "CUSTOMER", "plan_details": { "record_count": 9750 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 148188, "rows_after_filter": 148188, "rows_out": 148188, "cost": 222161.2427, "cost_without_join_buffer": 212029.7152, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 148188, "rows_out": 148188, "cost": 222161.2427, "uses_join_buffering": true } } }, { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 9750 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 1488318, "rows_after_filter": 1488318, "rows_out": 1488318, "cost": 2231259.03, "cost_without_join_buffer": 2129123.115, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 1488318, "rows_out": 1488318, "cost": 2231259.03, "uses_join_buffering": true } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 9750 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5082399, "rows_after_filter": 5082399, "rows_out": 5082399, "cost": 7619639.44, "cost_without_join_buffer": 8092998.225, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5082399, "rows_out": 5082399, "cost": 7619639.44, "uses_join_buffering": true } } } ] }, { "plan_prefix": "n1,SUPPLIER", "table": "n2", "rows_for_plan": 243750, "cost_for_plan": 39.56698905, "rest_of_plan": [ { "plan_prefix": "n1,SUPPLIER,n2", "get_costs_for_tables": [ { "best_access_path": { "table": "CUSTOMER", "plan_details": { "record_count": 243750 }, "considered_access_paths": [ { "access_type": "ref", "index": "CUSTOMER_NATION_FKIDX", "rows": 6174, "cost": 220984.4024, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "CUSTOMER_NATION_FKIDX", "rows_read": 6174, "rows_out": 6174, "cost": 220984.4024, "uses_join_buffering": false } } }, { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 243750 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 1488318, "rows_after_filter": 1488318, "rows_out": 1488318, "cost": 77865538.8, "cost_without_join_buffer": 53228049.28, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 1488318, "rows_out": 1488318, "cost": 77865538.8, "uses_join_buffering": true } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 243750 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5082399, "rows_after_filter": 5082399, "rows_out": 5082399, "cost": 265905465.9, "cost_without_join_buffer": 202324068.5, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5082399, "rows_out": 5082399, "cost": 265905465.9, "uses_join_buffering": true } } } ] }, { "plan_prefix": "n1,SUPPLIER,n2", "table": "CUSTOMER", "rows_for_plan": 1504912500, "cost_for_plan": 221023.9693, "pruned_by_cost": true, "current_cost": 221023.9693, "best_cost": 97159.74773 }, { "plan_prefix": "n1,SUPPLIER,n2", "table": "ORDERS", "rows_for_plan": 3.627775e11, "cost_for_plan": 77865578.37, "pruned_by_cost": true, "current_cost": 77865578.37, "best_cost": 97159.74773 }, { "plan_prefix": "n1,SUPPLIER,n2", "table": "LINEITEM", "rows_for_plan": 1.238835e12, "cost_for_plan": 265905505.5, "pruned_by_cost": true, "current_cost": 265905505.5, "best_cost": 97159.74773 } ] }, { "plan_prefix": "n1,SUPPLIER", "table": "CUSTOMER", "rows_for_plan": 1444833000, "cost_for_plan": 222162.7151, "pruned_by_cost": true, "current_cost": 222162.7151, "best_cost": 97159.74773 }, { "plan_prefix": "n1,SUPPLIER", "table": "ORDERS", "rows_for_plan": 14511100500, "cost_for_plan": 2231260.502, "pruned_by_cost": true, "current_cost": 2231260.502, "best_cost": 97159.74773 }, { "plan_prefix": "n1,SUPPLIER", "table": "LINEITEM", "rows_for_plan": 49553390250, "cost_for_plan": 7619640.913, "pruned_by_cost": true, "current_cost": 7619640.913, "best_cost": 97159.74773 } ] }, { "plan_prefix": "n1", "table": "CUSTOMER", "rows_for_plan": 3704700, "cost_for_plan": 365.8930428, "rest_of_plan": [ { "plan_prefix": "n1,CUSTOMER", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 3704700 }, "considered_access_paths": [ { "access_type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows": 390, "cost": 214998.0847, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows_read": 390, "rows_out": 390, "cost": 214998.0847, "uses_join_buffering": false } } }, { "best_access_path": { "table": "n2", "plan_details": { "record_count": 3704700 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "prev_record_count": 148188, "rows": 1, "cost": 246.3544779, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 1, "cost": 14471.55365, "cost_without_join_buffer": 52275.54064, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "index": "PRIMARY", "rows_read": 1, "rows_out": 1, "cost": 246.3544779, "uses_join_buffering": false } } }, { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 3704700 }, "considered_access_paths": [ { "access_type": "ref", "index": "ORDERS_FK1", "rows": 14, "cost": 10671.87557, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "ORDERS_FK1", "rows_read": 14, "rows_out": 14, "cost": 10671.87557, "uses_join_buffering": false } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 3704700 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5082399, "rows_after_filter": 5082399, "rows_out": 5082399, "cost": 2895041931, "cost_without_join_buffer": 3075076303, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5082399, "rows_out": 5082399, "cost": 2895041931, "uses_join_buffering": true } } } ] }, { "plan_prefix": "n1,CUSTOMER", "table": "n2", "rows_for_plan": 3704700, "cost_for_plan": 612.2475207, "rest_of_plan": [ { "plan_prefix": "n1,CUSTOMER,n2", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 3704700 }, "considered_access_paths": [ { "access_type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows": 390, "cost": 214998.0847, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows_read": 390, "rows_out": 390, "cost": 214998.0847, "uses_join_buffering": false } } }, { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 3704700 }, "considered_access_paths": [ { "access_type": "ref", "index": "ORDERS_FK1", "rows": 14, "cost": 10671.87557, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "ORDERS_FK1", "rows_read": 14, "rows_out": 14, "cost": 10671.87557, "uses_join_buffering": false } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 3704700 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5082399, "rows_after_filter": 5082399, "rows_out": 5082399, "cost": 4041425394, "cost_without_join_buffer": 3075076303, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5082399, "rows_out": 5082399, "cost": 4041425394, "uses_join_buffering": true } } } ] }, { "plan_prefix": "n1,CUSTOMER,n2", "table": "ORDERS", "rows_for_plan": 51865800, "cost_for_plan": 11284.12309, "rest_of_plan": [ { "plan_prefix": "n1,CUSTOMER,n2,ORDERS", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 51865800 }, "considered_access_paths": [ { "access_type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows": 390, "cost": 3009973.101, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows_read": 390, "rows_out": 390, "cost": 3009973.101, "uses_join_buffering": false } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 51865800 }, "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 4, "cost": 75427.53321, "chosen": true }, { "access_type": "ref", "index": "IDX_LINEITEM_ORDERKEY_FKIDX", "rows": 4, "cost": 249024.4751, "chosen": false, "cause": "cost" }, { "access_type": "scan_with_join_cache", "rows": 5082399, "rows_after_filter": 5082399, "rows_out": 4, "cost": 72626357775, "cost_without_join_buffer": 43051067756, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "index": "PRIMARY", "rows_read": 4, "rows_out": 4, "cost": 75427.53321, "uses_join_buffering": false } } } ] }, { "plan_prefix": "n1,CUSTOMER,n2,ORDERS", "table": "LINEITEM", "rows_for_plan": 207463200, "cost_for_plan": 86711.6563, "rest_of_plan": [ { "plan_prefix": "n1,CUSTOMER,n2,ORDERS,LINEITEM", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 207463200 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "prev_record_count": 148188, "rows": 1, "cost": 6766.75755, "chosen": true }, { "access_type": "eq_ref", "index": "SUPPLIER_NATION_FKIDX", "prev_record_count": 148188, "rows": 1, "cost": 6763.663266, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "eq_ref", "index": "SUPPLIER_NATION_FKIDX", "rows_read": 1, "rows_out": 1, "cost": 6763.663266, "uses_join_buffering": false } } } ] }, { "plan_prefix": "n1,CUSTOMER,n2,ORDERS,LINEITEM", "table": "SUPPLIER", "rows_for_plan": 207463200, "cost_for_plan": 93475.31957 } ] }, { "plan_prefix": "n1,CUSTOMER,n2,ORDERS", "table": "SUPPLIER", "rows_for_plan": 20227662000, "cost_for_plan": 3021257.224, "pruned_by_cost": true, "current_cost": 3021257.224, "best_cost": 93475.31957 } ] }, { "plan_prefix": "n1,CUSTOMER,n2", "table": "SUPPLIER", "rows_for_plan": 1444833000, "cost_for_plan": 215610.3323, "pruned_by_cost": true, "current_cost": 215610.3323, "best_cost": 93475.31957 }, { "plan_prefix": "n1,CUSTOMER,n2", "table": "LINEITEM", "rows_for_plan": 1.882876e13, "cost_for_plan": 4041426007, "pruned_by_cost": true, "current_cost": 4041426007, "best_cost": 93475.31957 } ] }, { "plan_prefix": "n1,CUSTOMER", "table": "ORDERS", "rows_for_plan": 51865800, "cost_for_plan": 11037.76862, "pruned_by_heuristic": true }, { "plan_prefix": "n1,CUSTOMER", "table": "SUPPLIER", "rows_for_plan": 1444833000, "cost_for_plan": 215363.9778, "pruned_by_cost": true, "current_cost": 215363.9778, "best_cost": 93475.31957 }, { "plan_prefix": "n1,CUSTOMER", "table": "LINEITEM", "rows_for_plan": 1.882876e13, "cost_for_plan": 2895042296, "pruned_by_cost": true, "current_cost": 2895042296, "best_cost": 93475.31957 } ] }, { "plan_prefix": "n1", "table": "ORDERS", "rows_for_plan": 37207950, "cost_for_plan": 3674.932532, "rest_of_plan": [ { "plan_prefix": "n1,ORDERS", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 37207950 }, "considered_access_paths": [ { "access_type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows": 390, "cost": 2159321.34, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows_read": 390, "rows_out": 390, "cost": 2159321.34, "uses_join_buffering": false } } }, { "best_access_path": { "table": "CUSTOMER", "plan_details": { "record_count": 37207950 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "prev_record_count": 1488318, "rows": 1, "cost": 2475.736874, "chosen": true }, { "access_type": "ref", "index": "CUSTOMER_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": 2475.736874, "uses_join_buffering": false } } }, { "best_access_path": { "table": "n2", "plan_details": { "record_count": 37207950 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 25, "cost": 145350.1415, "cost_without_join_buffer": 525026.5001, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 25, "cost": 145350.1415, "uses_join_buffering": true } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 37207950 }, "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 4, "cost": 54121.32219, "chosen": true }, { "access_type": "ref", "index": "IDX_LINEITEM_ORDERKEY_FKIDX", "rows": 4, "cost": 178659.7594, "chosen": false, "cause": "cost" }, { "access_type": "scan_with_join_cache", "rows": 5082399, "rows_after_filter": 5082399, "rows_out": 4, "cost": 29076540502, "cost_without_join_buffer": 30884358808, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "index": "PRIMARY", "rows_read": 4, "rows_out": 4, "cost": 54121.32219, "uses_join_buffering": false } } } ] }, { "plan_prefix": "n1,ORDERS", "table": "CUSTOMER", "rows_for_plan": 37207950, "cost_for_plan": 6150.669406, "rest_of_plan": [ { "plan_prefix": "n1,ORDERS,CUSTOMER", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 37207950 }, "considered_access_paths": [ { "access_type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows": 390, "cost": 2159321.34, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows_read": 390, "rows_out": 390, "cost": 2159321.34, "uses_join_buffering": false } } }, { "best_access_path": { "table": "n2", "plan_details": { "record_count": 37207950 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "prev_record_count": 1488318, "rows": 1, "cost": 2474.240195, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 1, "cost": 201985.6379, "cost_without_join_buffer": 525026.5001, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "index": "PRIMARY", "rows_read": 1, "rows_out": 1, "cost": 2474.240195, "uses_join_buffering": false } } } ] }, { "plan_prefix": "n1,ORDERS,CUSTOMER", "table": "n2", "rows_for_plan": 37207950, "cost_for_plan": 8624.909601, "rest_of_plan": [ { "plan_prefix": "n1,ORDERS,CUSTOMER,n2", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 37207950 }, "considered_access_paths": [ { "access_type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows": 390, "cost": 2159321.34, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows_read": 390, "rows_out": 390, "cost": 2159321.34, "uses_join_buffering": false } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 37207950 }, "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 4, "cost": 54121.32219, "chosen": true }, { "access_type": "ref", "index": "IDX_LINEITEM_ORDERKEY_FKIDX", "rows": 4, "cost": 178659.7594, "chosen": false, "cause": "cost" }, { "access_type": "scan_with_join_cache", "rows": 5082399, "rows_after_filter": 5082399, "rows_out": 4, "cost": 52101343541, "cost_without_join_buffer": 30884358808, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "index": "PRIMARY", "rows_read": 4, "rows_out": 4, "cost": 54121.32219, "uses_join_buffering": false } } } ] }, { "plan_prefix": "n1,ORDERS,CUSTOMER,n2", "table": "LINEITEM", "rows_for_plan": 148831800, "cost_for_plan": 62746.23179, "rest_of_plan": [ { "plan_prefix": "n1,ORDERS,CUSTOMER,n2,LINEITEM", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 148831800 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "prev_record_count": 1488318, "rows": 1, "cost": 6046.334467, "chosen": true }, { "access_type": "eq_ref", "index": "SUPPLIER_NATION_FKIDX", "prev_record_count": 1488318, "rows": 1, "cost": 6016.390678, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "eq_ref", "index": "SUPPLIER_NATION_FKIDX", "rows_read": 1, "rows_out": 1, "cost": 6016.390678, "uses_join_buffering": false } } } ] }, { "plan_prefix": "n1,ORDERS,CUSTOMER,n2,LINEITEM", "table": "SUPPLIER", "rows_for_plan": 148831800, "cost_for_plan": 68762.62246 } ] }, { "plan_prefix": "n1,ORDERS,CUSTOMER,n2", "table": "SUPPLIER", "rows_for_plan": 14511100500, "cost_for_plan": 2167946.249, "pruned_by_cost": true, "current_cost": 2167946.249, "best_cost": 68762.62246 } ] } ] }, { "plan_prefix": "n1,ORDERS", "table": "LINEITEM", "rows_for_plan": 148831800, "cost_for_plan": 57796.25472, "pruned_by_heuristic": true }, { "plan_prefix": "n1,ORDERS", "table": "SUPPLIER", "rows_for_plan": 14511100500, "cost_for_plan": 2162996.272, "pruned_by_cost": true, "current_cost": 2162996.272, "best_cost": 68762.62246 } ] }, { "plan_prefix": "n1", "table": "LINEITEM", "rows_for_plan": 127059975, "cost_for_plan": 12666.5777, "rest_of_plan": [ { "plan_prefix": "n1,LINEITEM", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 127059975 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "prev_record_count": 5082399, "rows": 1, "cost": 8449.315285, "chosen": true }, { "access_type": "eq_ref", "index": "SUPPLIER_NATION_FKIDX", "prev_record_count": 5082399, "rows": 1, "cost": 8347.364083, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "eq_ref", "index": "SUPPLIER_NATION_FKIDX", "rows_read": 1, "rows_out": 1, "cost": 8347.364083, "uses_join_buffering": false } } }, { "best_access_path": { "table": "CUSTOMER", "plan_details": { "record_count": 127059975 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 148188, "rows_after_filter": 148188, "rows_out": 148188, "cost": 2895181484, "cost_without_join_buffer": 2763126048, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 148188, "rows_out": 148188, "cost": 2895181484, "uses_join_buffering": true } } }, { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 127059975 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "prev_record_count": 5082399, "rows": 1, "cost": 8459.393902, "chosen": true }, { "access_type": "ref", "index": "ORDERS_FK1", "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": 8459.393902, "uses_join_buffering": false } } }, { "best_access_path": { "table": "n2", "plan_details": { "record_count": 127059975 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 25, "cost": 496446.2392, "cost_without_join_buffer": 1792892.484, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 25, "cost": 496446.2392, "uses_join_buffering": true } } } ] }, { "plan_prefix": "n1,LINEITEM", "table": "SUPPLIER", "rows_for_plan": 127059975, "cost_for_plan": 21013.94179, "rest_of_plan": [ { "plan_prefix": "n1,LINEITEM,SUPPLIER", "get_costs_for_tables": [ { "best_access_path": { "table": "CUSTOMER", "plan_details": { "record_count": 127059975 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 148188, "rows_after_filter": 148188, "rows_out": 148188, "cost": 4041307889, "cost_without_join_buffer": 2763126048, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 148188, "rows_out": 148188, "cost": 4041307889, "uses_join_buffering": true } } }, { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 127059975 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "prev_record_count": 5082399, "rows": 1, "cost": 8459.393902, "chosen": true }, { "access_type": "ref", "index": "ORDERS_FK1", "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": 8459.393902, "uses_join_buffering": false } } } ] }, { "plan_prefix": "n1,LINEITEM,SUPPLIER", "table": "ORDERS", "rows_for_plan": 127059975, "cost_for_plan": 29473.33569, "rest_of_plan": [ { "plan_prefix": "n1,LINEITEM,SUPPLIER,ORDERS", "get_costs_for_tables": [ { "best_access_path": { "table": "CUSTOMER", "plan_details": { "record_count": 127059975 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "prev_record_count": 5082399, "rows": 1, "cost": 8450.680891, "chosen": true }, { "access_type": "ref", "index": "CUSTOMER_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": 8450.680891, "uses_join_buffering": false } } } ] }, { "plan_prefix": "n1,LINEITEM,SUPPLIER,ORDERS", "table": "CUSTOMER", "rows_for_plan": 127059975, "cost_for_plan": 37924.01658, "rest_of_plan": [ { "plan_prefix": "n1,LINEITEM,SUPPLIER,ORDERS,CUSTOMER", "get_costs_for_tables": [ { "best_access_path": { "table": "n2", "plan_details": { "record_count": 127059975 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "prev_record_count": 5082399, "rows": 1, "cost": 8449.184213, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 1, "cost": 1076673.854, "cost_without_join_buffer": 1792892.484, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "index": "PRIMARY", "rows_read": 1, "rows_out": 1, "cost": 8449.184213, "uses_join_buffering": false } } } ] }, { "plan_prefix": "n1,LINEITEM,SUPPLIER,ORDERS,CUSTOMER", "table": "n2", "rows_for_plan": 127059975, "cost_for_plan": 46373.20079 } ] } ] } ], "pruned_by_hanging_leaf": true } ] } ] }, { "plan_prefix": "", "table": "n2", "rows_for_plan": 25, "cost_for_plan": 0.0149298, "rest_of_plan": [ { "plan_prefix": "n2", "get_costs_for_tables": [ { "best_access_path": { "table": "n1", "plan_details": { "record_count": 25 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 25, "cost": 0.0744927, "cost_without_join_buffer": 0.3535842, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 25, "cost": 0.0744927, "uses_join_buffering": true } } }, { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 25 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 9750, "rows_after_filter": 9750, "rows_out": 9750, "cost": 24.08041742, "cost_without_join_buffer": 35.91292035, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 9750, "rows_out": 9750, "cost": 24.08041742, "uses_join_buffering": true } } }, { "best_access_path": { "table": "CUSTOMER", "plan_details": { "record_count": 25 }, "considered_access_paths": [ { "access_type": "ref", "index": "CUSTOMER_NATION_FKIDX", "rows": 6174, "cost": 22.75435055, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "CUSTOMER_NATION_FKIDX", "rows_read": 6174, "rows_out": 6174, "cost": 22.75435055, "uses_join_buffering": false } } }, { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 25 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 1488318, "rows_after_filter": 1488318, "rows_out": 1488318, "cost": 3674.917603, "cost_without_join_buffer": 5460.478101, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 1488318, "rows_out": 1488318, "cost": 3674.917603, "uses_join_buffering": true } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 25 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5082399, "rows_after_filter": 5082399, "rows_out": 5082399, "cost": 12666.56277, "cost_without_join_buffer": 20788.14503, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5082399, "rows_out": 5082399, "cost": 12666.56277, "uses_join_buffering": true } } } ] }, { "plan_prefix": "n2", "table": "n1", "rows_for_plan": 625, "cost_for_plan": 0.0894225, "rest_of_plan": [ { "plan_prefix": "n2,n1", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 625 }, "considered_access_paths": [ { "access_type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows": 390, "cost": 36.27772235, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows_read": 390, "rows_out": 390, "cost": 36.27772235, "uses_join_buffering": false } } }, { "best_access_path": { "table": "CUSTOMER", "plan_details": { "record_count": 625 }, "considered_access_paths": [ { "access_type": "ref", "index": "CUSTOMER_NATION_FKIDX", "rows": 6174, "cost": 566.7157365, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "CUSTOMER_NATION_FKIDX", "rows_read": 6174, "rows_out": 6174, "cost": 566.7157365, "uses_join_buffering": false } } }, { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 625 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 1488318, "rows_after_filter": 1488318, "rows_out": 1488318, "cost": 143220.9149, "cost_without_join_buffer": 136483.3657, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 1488318, "rows_out": 1488318, "cost": 143220.9149, "uses_join_buffering": true } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 625 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5082399, "rows_after_filter": 5082399, "rows_out": 5082399, "cost": 489196.6496, "cost_without_join_buffer": 518816.5304, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5082399, "rows_out": 5082399, "cost": 489196.6496, "uses_join_buffering": true } } } ] }, { "plan_prefix": "n2,n1", "table": "SUPPLIER", "rows_for_plan": 243750, "cost_for_plan": 36.36714485, "rest_of_plan": [ { "plan_prefix": "n2,n1,SUPPLIER", "get_costs_for_tables": [ { "best_access_path": { "table": "CUSTOMER", "plan_details": { "record_count": 243750 }, "considered_access_paths": [ { "access_type": "ref", "index": "CUSTOMER_NATION_FKIDX", "rows": 6174, "cost": 220984.4024, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "CUSTOMER_NATION_FKIDX", "rows_read": 6174, "rows_out": 6174, "cost": 220984.4024, "uses_join_buffering": false } } }, { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 243750 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 1488318, "rows_after_filter": 1488318, "rows_out": 1488318, "cost": 77865538.8, "cost_without_join_buffer": 53228049.28, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 1488318, "rows_out": 1488318, "cost": 77865538.8, "uses_join_buffering": true } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 243750 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5082399, "rows_after_filter": 5082399, "rows_out": 5082399, "cost": 265905465.9, "cost_without_join_buffer": 202324068.5, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5082399, "rows_out": 5082399, "cost": 265905465.9, "uses_join_buffering": true } } } ] }, { "plan_prefix": "n2,n1,SUPPLIER", "table": "CUSTOMER", "rows_for_plan": 1504912500, "cost_for_plan": 221020.7695, "pruned_by_cost": true, "current_cost": 221020.7695, "best_cost": 46373.20079 }, { "plan_prefix": "n2,n1,SUPPLIER", "table": "ORDERS", "rows_for_plan": 3.627775e11, "cost_for_plan": 77865575.17, "pruned_by_cost": true, "current_cost": 77865575.17, "best_cost": 46373.20079 }, { "plan_prefix": "n2,n1,SUPPLIER", "table": "LINEITEM", "rows_for_plan": 1.238835e12, "cost_for_plan": 265905502.3, "pruned_by_cost": true, "current_cost": 265905502.3, "best_cost": 46373.20079 } ] }, { "plan_prefix": "n2,n1", "table": "CUSTOMER", "rows_for_plan": 3858750, "cost_for_plan": 566.805159, "rest_of_plan": [ { "plan_prefix": "n2,n1,CUSTOMER", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 3858750 }, "considered_access_paths": [ { "access_type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows": 390, "cost": 223938.2024, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows_read": 390, "rows_out": 390, "cost": 223938.2024, "uses_join_buffering": false } } }, { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 3858750 }, "considered_access_paths": [ { "access_type": "ref", "index": "ORDERS_FK1", "rows": 14, "cost": 11115.58733, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "ORDERS_FK1", "rows_read": 14, "rows_out": 14, "cost": 11115.58733, "uses_join_buffering": false } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 3858750 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5082399, "rows_after_filter": 5082399, "rows_out": 5082399, "cost": 4209476729, "cost_without_join_buffer": 3202945091, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5082399, "rows_out": 5082399, "cost": 4209476729, "uses_join_buffering": true } } } ] }, { "plan_prefix": "n2,n1,CUSTOMER", "table": "ORDERS", "rows_for_plan": 54022500, "cost_for_plan": 11682.39249, "rest_of_plan": [ { "plan_prefix": "n2,n1,CUSTOMER,ORDERS", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 54022500 }, "considered_access_paths": [ { "access_type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows": 390, "cost": 3135134.749, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows_read": 390, "rows_out": 390, "cost": 3135134.749, "uses_join_buffering": false } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 54022500 }, "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 4, "cost": 78562.44763, "chosen": true }, { "access_type": "ref", "index": "IDX_LINEITEM_ORDERKEY_FKIDX", "rows": 4, "cost": 259377.6703, "chosen": false, "cause": "cost" }, { "access_type": "scan_with_join_cache", "rows": 5082399, "rows_after_filter": 5082399, "rows_out": 4, "cost": 75646329448, "cost_without_join_buffer": 44841230787, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "index": "PRIMARY", "rows_read": 4, "rows_out": 4, "cost": 78562.44763, "uses_join_buffering": false } } } ] }, { "plan_prefix": "n2,n1,CUSTOMER,ORDERS", "table": "LINEITEM", "rows_for_plan": 216090000, "cost_for_plan": 90244.84012, "pruned_by_cost": true, "current_cost": 90244.84012, "best_cost": 46373.20079 }, { "plan_prefix": "n2,n1,CUSTOMER,ORDERS", "table": "SUPPLIER", "rows_for_plan": 21068775000, "cost_for_plan": 3146817.141, "pruned_by_cost": true, "current_cost": 3146817.141, "best_cost": 46373.20079 } ] }, { "plan_prefix": "n2,n1,CUSTOMER", "table": "SUPPLIER", "rows_for_plan": 1504912500, "cost_for_plan": 224505.0076, "pruned_by_cost": true, "current_cost": 224505.0076, "best_cost": 46373.20079 }, { "plan_prefix": "n2,n1,CUSTOMER", "table": "LINEITEM", "rows_for_plan": 1.961171e13, "cost_for_plan": 4209477296, "pruned_by_cost": true, "current_cost": 4209477296, "best_cost": 46373.20079 } ] }, { "plan_prefix": "n2,n1", "table": "ORDERS", "rows_for_plan": 930198750, "cost_for_plan": 143221.0043, "pruned_by_cost": true, "current_cost": 143221.0043, "best_cost": 46373.20079 }, { "plan_prefix": "n2,n1", "table": "LINEITEM", "rows_for_plan": 3176499375, "cost_for_plan": 489196.739, "pruned_by_cost": true, "current_cost": 489196.739, "best_cost": 46373.20079 } ] }, { "plan_prefix": "n2", "table": "CUSTOMER", "rows_for_plan": 154350, "cost_for_plan": 22.76928035, "rest_of_plan": [ { "plan_prefix": "n2,CUSTOMER", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 154350 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 9750, "rows_after_filter": 9750, "rows_out": 9750, "cost": 231395.6435, "cost_without_join_buffer": 221685.9149, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 9750, "rows_out": 9750, "cost": 231395.6435, "uses_join_buffering": true } } }, { "best_access_path": { "table": "n1", "plan_details": { "record_count": 154350 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 25, "cost": 602.9333851, "cost_without_join_buffer": 2177.971929, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 25, "cost": 602.9333851, "uses_join_buffering": true } } }, { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 154350 }, "considered_access_paths": [ { "access_type": "ref", "index": "ORDERS_FK1", "rows": 14, "cost": 445.7669653, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "ORDERS_FK1", "rows_read": 14, "rows_out": 14, "cost": 445.7669653, "uses_join_buffering": false } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 154350 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5082399, "rows_after_filter": 5082399, "rows_out": 5082399, "cost": 120617015.9, "cost_without_join_buffer": 128117839.1, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5082399, "rows_out": 5082399, "cost": 120617015.9, "uses_join_buffering": true } } } ] }, { "plan_prefix": "n2,CUSTOMER", "table": "ORDERS", "rows_for_plan": 2160900, "cost_for_plan": 468.5362456, "rest_of_plan": [ { "plan_prefix": "n2,CUSTOMER,ORDERS", "get_costs_for_tables": [ { "best_access_path": { "table": "n1", "plan_details": { "record_count": 2160900 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 25, "cost": 11730.58718, "cost_without_join_buffer": 30491.59636, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 25, "cost": 11730.58718, "uses_join_buffering": true } } }, { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 2160900 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 9750, "rows_after_filter": 9750, "rows_out": 9750, "cost": 4522053.173, "cost_without_join_buffer": 3103602.723, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 9750, "rows_out": 9750, "cost": 4522053.173, "uses_join_buffering": true } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 2160900 }, "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 4, "cost": 3177.981717, "chosen": true }, { "access_type": "ref", "index": "IDX_LINEITEM_ORDERKEY_FKIDX", "rows": 4, "cost": 10417.09678, "chosen": false, "cause": "cost" }, { "access_type": "scan_with_join_cache", "rows": 5082399, "rows_after_filter": 5082399, "rows_out": 4, "cost": 2357184171, "cost_without_join_buffer": 1793649267, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "index": "PRIMARY", "rows_read": 4, "rows_out": 4, "cost": 3177.981717, "uses_join_buffering": false } } } ] }, { "plan_prefix": "n2,CUSTOMER,ORDERS", "table": "LINEITEM", "rows_for_plan": 8643600, "cost_for_plan": 3646.517963, "rest_of_plan": [ { "plan_prefix": "n2,CUSTOMER,ORDERS,LINEITEM", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 8643600 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "prev_record_count": 1, "rows": 1, "cost": 276.5968816, "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": 276.5968816, "uses_join_buffering": false } } }, { "best_access_path": { "table": "n1", "plan_details": { "record_count": 8643600 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 25, "cost": 60086.94475, "cost_without_join_buffer": 121966.383, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 25, "cost": 60086.94475, "uses_join_buffering": true } } } ] }, { "plan_prefix": "n2,CUSTOMER,ORDERS,LINEITEM", "table": "SUPPLIER", "rows_for_plan": 8643600, "cost_for_plan": 3923.114844, "rest_of_plan": [ { "plan_prefix": "n2,CUSTOMER,ORDERS,LINEITEM,SUPPLIER", "get_costs_for_tables": [ { "best_access_path": { "table": "n1", "plan_details": { "record_count": 8643600 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "prev_record_count": 1, "rows": 1, "cost": 276.5968816, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 1, "cost": 73243.66954, "cost_without_join_buffer": 121966.383, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "index": "PRIMARY", "rows_read": 1, "rows_out": 1, "cost": 276.5968816, "uses_join_buffering": false } } } ] }, { "plan_prefix": "n2,CUSTOMER,ORDERS,LINEITEM,SUPPLIER", "table": "n1", "rows_for_plan": 8643600, "cost_for_plan": 4199.711726 } ], "pruned_by_hanging_leaf": true } ] }, { "plan_prefix": "n2,CUSTOMER,ORDERS", "table": "n1", "rows_for_plan": 54022500, "cost_for_plan": 12199.12342, "pruned_by_cost": true, "current_cost": 12199.12342, "best_cost": 4199.711726 }, { "plan_prefix": "n2,CUSTOMER,ORDERS", "table": "SUPPLIER", "rows_for_plan": 21068775000, "cost_for_plan": 4522521.709, "pruned_by_cost": true, "current_cost": 4522521.709, "best_cost": 4199.711726 } ] }, { "plan_prefix": "n2,CUSTOMER", "table": "n1", "rows_for_plan": 3858750, "cost_for_plan": 625.7026655, "rest_of_plan": [ { "plan_prefix": "n2,CUSTOMER,n1", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 3858750 }, "considered_access_paths": [ { "access_type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows": 390, "cost": 223938.2024, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows_read": 390, "rows_out": 390, "cost": 223938.2024, "uses_join_buffering": false } } }, { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 3858750 }, "considered_access_paths": [ { "access_type": "ref", "index": "ORDERS_FK1", "rows": 14, "cost": 11115.58733, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "ORDERS_FK1", "rows_read": 14, "rows_out": 14, "cost": 11115.58733, "uses_join_buffering": false } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 3858750 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5082399, "rows_after_filter": 5082399, "rows_out": 5082399, "cost": 4209476729, "cost_without_join_buffer": 3202945091, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5082399, "rows_out": 5082399, "cost": 4209476729, "uses_join_buffering": true } } } ] }, { "plan_prefix": "n2,CUSTOMER,n1", "table": "ORDERS", "rows_for_plan": 54022500, "cost_for_plan": 11741.28999, "pruned_by_cost": true, "current_cost": 11741.28999, "best_cost": 4199.711726 }, { "plan_prefix": "n2,CUSTOMER,n1", "table": "SUPPLIER", "rows_for_plan": 1504912500, "cost_for_plan": 224563.9051, "pruned_by_cost": true, "current_cost": 224563.9051, "best_cost": 4199.711726 }, { "plan_prefix": "n2,CUSTOMER,n1", "table": "LINEITEM", "rows_for_plan": 1.961171e13, "cost_for_plan": 4209477355, "pruned_by_cost": true, "current_cost": 4209477355, "best_cost": 4199.711726 } ] }, { "plan_prefix": "n2,CUSTOMER", "table": "SUPPLIER", "rows_for_plan": 1504912500, "cost_for_plan": 231418.4128, "pruned_by_cost": true, "current_cost": 231418.4128, "best_cost": 4199.711726 }, { "plan_prefix": "n2,CUSTOMER", "table": "LINEITEM", "rows_for_plan": 7.844683e11, "cost_for_plan": 120617038.7, "pruned_by_cost": true, "current_cost": 120617038.7, "best_cost": 4199.711726 } ] }, { "plan_prefix": "n2", "table": "SUPPLIER", "rows_for_plan": 243750, "cost_for_plan": 24.09534722, "rest_of_plan": [ { "plan_prefix": "n2,SUPPLIER", "get_costs_for_tables": [ { "best_access_path": { "table": "n1", "plan_details": { "record_count": 243750 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "prev_record_count": 9750, "rows": 1, "cost": 16.2096092, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 1, "cost": 952.1633739, "cost_without_join_buffer": 3439.459569, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "index": "PRIMARY", "rows_read": 1, "rows_out": 1, "cost": 16.2096092, "uses_join_buffering": false } } }, { "best_access_path": { "table": "CUSTOMER", "plan_details": { "record_count": 243750 }, "considered_access_paths": [ { "access_type": "ref", "index": "CUSTOMER_NATION_FKIDX", "rows": 6174, "cost": 220984.4024, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "CUSTOMER_NATION_FKIDX", "rows_read": 6174, "rows_out": 6174, "cost": 220984.4024, "uses_join_buffering": false } } }, { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 243750 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 1488318, "rows_after_filter": 1488318, "rows_out": 1488318, "cost": 55778608.32, "cost_without_join_buffer": 53228049.28, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 1488318, "rows_out": 1488318, "cost": 55778608.32, "uses_join_buffering": true } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 243750 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5082399, "rows_after_filter": 5082399, "rows_out": 5082399, "cost": 190479308.3, "cost_without_join_buffer": 202324068.5, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5082399, "rows_out": 5082399, "cost": 190479308.3, "uses_join_buffering": true } } } ] }, { "plan_prefix": "n2,SUPPLIER", "table": "n1", "rows_for_plan": 243750, "cost_for_plan": 40.30495642, "rest_of_plan": [ { "plan_prefix": "n2,SUPPLIER,n1", "get_costs_for_tables": [ { "best_access_path": { "table": "CUSTOMER", "plan_details": { "record_count": 243750 }, "considered_access_paths": [ { "access_type": "ref", "index": "CUSTOMER_NATION_FKIDX", "rows": 6174, "cost": 220984.4024, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "CUSTOMER_NATION_FKIDX", "rows_read": 6174, "rows_out": 6174, "cost": 220984.4024, "uses_join_buffering": false } } }, { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 243750 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 1488318, "rows_after_filter": 1488318, "rows_out": 1488318, "cost": 77865538.8, "cost_without_join_buffer": 53228049.28, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 1488318, "rows_out": 1488318, "cost": 77865538.8, "uses_join_buffering": true } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 243750 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5082399, "rows_after_filter": 5082399, "rows_out": 5082399, "cost": 265905465.9, "cost_without_join_buffer": 202324068.5, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5082399, "rows_out": 5082399, "cost": 265905465.9, "uses_join_buffering": true } } } ] }, { "plan_prefix": "n2,SUPPLIER,n1", "table": "CUSTOMER", "rows_for_plan": 1504912500, "cost_for_plan": 221024.7073, "pruned_by_cost": true, "current_cost": 221024.7073, "best_cost": 4199.711726 }, { "plan_prefix": "n2,SUPPLIER,n1", "table": "ORDERS", "rows_for_plan": 3.627775e11, "cost_for_plan": 77865579.11, "pruned_by_cost": true, "current_cost": 77865579.11, "best_cost": 4199.711726 }, { "plan_prefix": "n2,SUPPLIER,n1", "table": "LINEITEM", "rows_for_plan": 1.238835e12, "cost_for_plan": 265905506.2, "pruned_by_cost": true, "current_cost": 265905506.2, "best_cost": 4199.711726 } ] }, { "plan_prefix": "n2,SUPPLIER", "table": "CUSTOMER", "rows_for_plan": 1504912500, "cost_for_plan": 221008.4977, "pruned_by_cost": true, "current_cost": 221008.4977, "best_cost": 4199.711726 }, { "plan_prefix": "n2,SUPPLIER", "table": "ORDERS", "rows_for_plan": 3.627775e11, "cost_for_plan": 55778632.42, "pruned_by_cost": true, "current_cost": 55778632.42, "best_cost": 4199.711726 }, { "plan_prefix": "n2,SUPPLIER", "table": "LINEITEM", "rows_for_plan": 1.238835e12, "cost_for_plan": 190479332.4, "pruned_by_cost": true, "current_cost": 190479332.4, "best_cost": 4199.711726 } ] }, { "plan_prefix": "n2", "table": "ORDERS", "rows_for_plan": 37207950, "cost_for_plan": 3674.932532, "rest_of_plan": [ { "plan_prefix": "n2,ORDERS", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 37207950 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 9750, "rows_after_filter": 9750, "rows_out": 9750, "cost": 55781340.21, "cost_without_join_buffer": 53440091.96, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 9750, "rows_out": 9750, "cost": 55781340.21, "uses_join_buffering": true } } }, { "best_access_path": { "table": "CUSTOMER", "plan_details": { "record_count": 37207950 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "prev_record_count": 1488318, "rows": 1, "cost": 2475.736874, "chosen": true }, { "access_type": "eq_ref", "index": "CUSTOMER_NATION_FKIDX", "prev_record_count": 1488318, "rows": 1, "cost": 2444.510218, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "eq_ref", "index": "CUSTOMER_NATION_FKIDX", "rows_read": 1, "rows_out": 1, "cost": 2444.510218, "uses_join_buffering": false } } }, { "best_access_path": { "table": "n1", "plan_details": { "record_count": 37207950 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 25, "cost": 145350.1415, "cost_without_join_buffer": 525026.5001, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 25, "cost": 145350.1415, "uses_join_buffering": true } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 37207950 }, "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 4, "cost": 54121.32219, "chosen": true }, { "access_type": "ref", "index": "IDX_LINEITEM_ORDERKEY_FKIDX", "rows": 4, "cost": 178659.7594, "chosen": false, "cause": "cost" }, { "access_type": "scan_with_join_cache", "rows": 5082399, "rows_after_filter": 5082399, "rows_out": 4, "cost": 29076540502, "cost_without_join_buffer": 30884358808, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "index": "PRIMARY", "rows_read": 4, "rows_out": 4, "cost": 54121.32219, "uses_join_buffering": false } } } ] }, { "plan_prefix": "n2,ORDERS", "table": "CUSTOMER", "rows_for_plan": 37207950, "cost_for_plan": 6119.44275, "pruned_by_cost": true, "current_cost": 6119.44275, "best_cost": 4199.711726 }, { "plan_prefix": "n2,ORDERS", "table": "LINEITEM", "rows_for_plan": 148831800, "cost_for_plan": 57796.25472, "pruned_by_cost": true, "current_cost": 57796.25472, "best_cost": 4199.711726 }, { "plan_prefix": "n2,ORDERS", "table": "n1", "rows_for_plan": 930198750, "cost_for_plan": 149025.0741, "pruned_by_cost": true, "current_cost": 149025.0741, "best_cost": 4199.711726 }, { "plan_prefix": "n2,ORDERS", "table": "SUPPLIER", "rows_for_plan": 3.627775e11, "cost_for_plan": 55785015.14, "pruned_by_cost": true, "current_cost": 55785015.14, "best_cost": 4199.711726 } ] }, { "plan_prefix": "n2", "table": "LINEITEM", "rows_for_plan": 127059975, "cost_for_plan": 12666.5777, "pruned_by_cost": true, "current_cost": 12666.5777, "best_cost": 4199.711726 } ] }, { "plan_prefix": "", "table": "SUPPLIER", "rows_for_plan": 9750, "cost_for_plan": 1.44280827, "rest_of_plan": [ { "plan_prefix": "SUPPLIER", "get_costs_for_tables": [ { "best_access_path": { "table": "n2", "plan_details": { "record_count": 9750 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 25, "cost": 23.2444608, "cost_without_join_buffer": 137.5791692, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 25, "cost": 23.2444608, "uses_join_buffering": true } } }, { "best_access_path": { "table": "n1", "plan_details": { "record_count": 9750 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "prev_record_count": 1, "rows": 1, "cost": 0.31368164, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 1, "cost": 23.2444608, "cost_without_join_buffer": 137.5791692, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "index": "PRIMARY", "rows_read": 1, "rows_out": 1, "cost": 0.31368164, "uses_join_buffering": false } } }, { "best_access_path": { "table": "CUSTOMER", "plan_details": { "record_count": 9750 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 148188, "rows_after_filter": 148188, "rows_out": 148188, "cost": 134198.2907, "cost_without_join_buffer": 212029.7152, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 148188, "rows_out": 148188, "cost": 134198.2907, "uses_join_buffering": true } } }, { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 9750 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 1488318, "rows_after_filter": 1488318, "rows_out": 1488318, "cost": 1347808.015, "cost_without_join_buffer": 2129123.115, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 1488318, "rows_out": 1488318, "cost": 1347808.015, "uses_join_buffering": true } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 9750 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5082399, "rows_after_filter": 5082399, "rows_out": 5082399, "cost": 4602692.742, "cost_without_join_buffer": 8092998.225, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5082399, "rows_out": 5082399, "cost": 4602692.742, "uses_join_buffering": true } } } ] }, { "plan_prefix": "SUPPLIER", "table": "n1", "rows_for_plan": 9750, "cost_for_plan": 1.75648991, "rest_of_plan": [ { "plan_prefix": "SUPPLIER,n1", "get_costs_for_tables": [ { "best_access_path": { "table": "n2", "plan_details": { "record_count": 9750 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 25, "cost": 38.0946589, "cost_without_join_buffer": 137.5791692, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 25, "cost": 38.0946589, "uses_join_buffering": true } } }, { "best_access_path": { "table": "CUSTOMER", "plan_details": { "record_count": 9750 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 148188, "rows_after_filter": 148188, "rows_out": 148188, "cost": 222161.2427, "cost_without_join_buffer": 212029.7152, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 148188, "rows_out": 148188, "cost": 222161.2427, "uses_join_buffering": true } } }, { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 9750 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 1488318, "rows_after_filter": 1488318, "rows_out": 1488318, "cost": 2231259.03, "cost_without_join_buffer": 2129123.115, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 1488318, "rows_out": 1488318, "cost": 2231259.03, "uses_join_buffering": true } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 9750 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5082399, "rows_after_filter": 5082399, "rows_out": 5082399, "cost": 7619639.44, "cost_without_join_buffer": 8092998.225, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5082399, "rows_out": 5082399, "cost": 7619639.44, "uses_join_buffering": true } } } ] }, { "plan_prefix": "SUPPLIER,n1", "table": "n2", "rows_for_plan": 243750, "cost_for_plan": 39.85114881, "rest_of_plan": [ { "plan_prefix": "SUPPLIER,n1,n2", "get_costs_for_tables": [ { "best_access_path": { "table": "CUSTOMER", "plan_details": { "record_count": 243750 }, "considered_access_paths": [ { "access_type": "ref", "index": "CUSTOMER_NATION_FKIDX", "rows": 6174, "cost": 220984.4024, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "CUSTOMER_NATION_FKIDX", "rows_read": 6174, "rows_out": 6174, "cost": 220984.4024, "uses_join_buffering": false } } }, { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 243750 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 1488318, "rows_after_filter": 1488318, "rows_out": 1488318, "cost": 77865538.8, "cost_without_join_buffer": 53228049.28, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 1488318, "rows_out": 1488318, "cost": 77865538.8, "uses_join_buffering": true } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 243750 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5082399, "rows_after_filter": 5082399, "rows_out": 5082399, "cost": 265905465.9, "cost_without_join_buffer": 202324068.5, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5082399, "rows_out": 5082399, "cost": 265905465.9, "uses_join_buffering": true } } } ] }, { "plan_prefix": "SUPPLIER,n1,n2", "table": "CUSTOMER", "rows_for_plan": 1504912500, "cost_for_plan": 221024.2535, "pruned_by_cost": true, "current_cost": 221024.2535, "best_cost": 4199.711726 }, { "plan_prefix": "SUPPLIER,n1,n2", "table": "ORDERS", "rows_for_plan": 3.627775e11, "cost_for_plan": 77865578.65, "pruned_by_cost": true, "current_cost": 77865578.65, "best_cost": 4199.711726 }, { "plan_prefix": "SUPPLIER,n1,n2", "table": "LINEITEM", "rows_for_plan": 1.238835e12, "cost_for_plan": 265905505.7, "pruned_by_cost": true, "current_cost": 265905505.7, "best_cost": 4199.711726 } ] }, { "plan_prefix": "SUPPLIER,n1", "table": "CUSTOMER", "rows_for_plan": 1444833000, "cost_for_plan": 222162.9992, "pruned_by_cost": true, "current_cost": 222162.9992, "best_cost": 4199.711726 }, { "plan_prefix": "SUPPLIER,n1", "table": "ORDERS", "rows_for_plan": 14511100500, "cost_for_plan": 2231260.786, "pruned_by_cost": true, "current_cost": 2231260.786, "best_cost": 4199.711726 }, { "plan_prefix": "SUPPLIER,n1", "table": "LINEITEM", "rows_for_plan": 49553390250, "cost_for_plan": 7619641.197, "pruned_by_cost": true, "current_cost": 7619641.197, "best_cost": 4199.711726 } ] }, { "plan_prefix": "SUPPLIER", "table": "n2", "rows_for_plan": 243750, "cost_for_plan": 24.68726907, "pruned_by_heuristic": true }, { "plan_prefix": "SUPPLIER", "table": "CUSTOMER", "rows_for_plan": 1444833000, "cost_for_plan": 134199.7336, "pruned_by_cost": true, "current_cost": 134199.7336, "best_cost": 4199.711726 }, { "plan_prefix": "SUPPLIER", "table": "ORDERS", "rows_for_plan": 14511100500, "cost_for_plan": 1347809.458, "pruned_by_cost": true, "current_cost": 1347809.458, "best_cost": 4199.711726 }, { "plan_prefix": "SUPPLIER", "table": "LINEITEM", "rows_for_plan": 49553390250, "cost_for_plan": 4602694.185, "pruned_by_cost": true, "current_cost": 4602694.185, "best_cost": 4199.711726 } ] }, { "plan_prefix": "", "table": "CUSTOMER", "rows_for_plan": 148188, "cost_for_plan": 21.8359211, "rest_of_plan": [ { "plan_prefix": "CUSTOMER", "get_costs_for_tables": [ { "best_access_path": { "table": "n2", "plan_details": { "record_count": 148188 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "prev_record_count": 1, "rows": 1, "cost": 4.74369764, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 1, "cost": 353.1457638, "cost_without_join_buffer": 2091.022412, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "index": "PRIMARY", "rows_read": 1, "rows_out": 1, "cost": 4.74369764, "uses_join_buffering": false } } }, { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 148188 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 9750, "rows_after_filter": 9750, "rows_out": 9750, "cost": 134193.5051, "cost_without_join_buffer": 212835.7136, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 9750, "rows_out": 9750, "cost": 134193.5051, "uses_join_buffering": true } } }, { "best_access_path": { "table": "n1", "plan_details": { "record_count": 148188 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 25, "cost": 353.1457638, "cost_without_join_buffer": 2091.022412, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 25, "cost": 353.1457638, "uses_join_buffering": true } } }, { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 148188 }, "considered_access_paths": [ { "access_type": "ref", "index": "ORDERS_FK1", "rows": 14, "cost": 428.0184951, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "ORDERS_FK1", "rows_read": 14, "rows_out": 14, "cost": 428.0184951, "uses_join_buffering": false } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 148188 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5082399, "rows_after_filter": 5082399, "rows_out": 5082399, "cost": 69947104.59, "cost_without_join_buffer": 123003087.6, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5082399, "rows_out": 5082399, "cost": 69947104.59, "uses_join_buffering": true } } } ] }, { "plan_prefix": "CUSTOMER", "table": "n2", "rows_for_plan": 148188, "cost_for_plan": 26.57961874, "rest_of_plan": [ { "plan_prefix": "CUSTOMER,n2", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 148188 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 9750, "rows_after_filter": 9750, "rows_out": 9750, "cost": 222159.1268, "cost_without_join_buffer": 212835.7136, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 9750, "rows_out": 9750, "cost": 222159.1268, "uses_join_buffering": true } } }, { "best_access_path": { "table": "n1", "plan_details": { "record_count": 148188 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 25, "cost": 578.8759142, "cost_without_join_buffer": 2091.022412, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 25, "cost": 578.8759142, "uses_join_buffering": true } } }, { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 148188 }, "considered_access_paths": [ { "access_type": "ref", "index": "ORDERS_FK1", "rows": 14, "cost": 428.0184951, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "ORDERS_FK1", "rows_read": 14, "rows_out": 14, "cost": 428.0184951, "uses_join_buffering": false } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 148188 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5082399, "rows_after_filter": 5082399, "rows_out": 5082399, "cost": 115802476.3, "cost_without_join_buffer": 123003087.6, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5082399, "rows_out": 5082399, "cost": 115802476.3, "uses_join_buffering": true } } } ] }, { "plan_prefix": "CUSTOMER,n2", "table": "ORDERS", "rows_for_plan": 2074632, "cost_for_plan": 454.5981138, "rest_of_plan": [ { "plan_prefix": "CUSTOMER,n2,ORDERS", "get_costs_for_tables": [ { "best_access_path": { "table": "n1", "plan_details": { "record_count": 2074632 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 25, "cost": 11262.273, "cost_without_join_buffer": 29274.30312, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 25, "cost": 11262.273, "uses_join_buffering": true } } }, { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 2074632 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 9750, "rows_after_filter": 9750, "rows_out": 9750, "cost": 4341522.338, "cost_without_join_buffer": 2979699.905, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 9750, "rows_out": 9750, "cost": 4341522.338, "uses_join_buffering": true } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 2074632 }, "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 4, "cost": 3052.58514, "chosen": true }, { "access_type": "ref", "index": "IDX_LINEITEM_ORDERKEY_FKIDX", "rows": 4, "cost": 10002.96897, "chosen": false, "cause": "cost" }, { "access_type": "scan_with_join_cache", "rows": 5082399, "rows_after_filter": 5082399, "rows_out": 4, "cost": 2263079906, "cost_without_join_buffer": 1722042746, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "index": "PRIMARY", "rows_read": 4, "rows_out": 4, "cost": 3052.58514, "uses_join_buffering": false } } } ] }, { "plan_prefix": "CUSTOMER,n2,ORDERS", "table": "LINEITEM", "rows_for_plan": 8298528, "cost_for_plan": 3507.183254, "rest_of_plan": [ { "plan_prefix": "CUSTOMER,n2,ORDERS,LINEITEM", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 8298528 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "prev_record_count": 1, "rows": 1, "cost": 265.5545776, "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": 265.5545776, "uses_join_buffering": false } } }, { "best_access_path": { "table": "n1", "plan_details": { "record_count": 8298528 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 25, "cost": 57688.14359, "cost_without_join_buffer": 117097.21, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 25, "cost": 57688.14359, "uses_join_buffering": true } } } ] }, { "plan_prefix": "CUSTOMER,n2,ORDERS,LINEITEM", "table": "SUPPLIER", "rows_for_plan": 8298528, "cost_for_plan": 3772.737832, "rest_of_plan": [ { "plan_prefix": "CUSTOMER,n2,ORDERS,LINEITEM,SUPPLIER", "get_costs_for_tables": [ { "best_access_path": { "table": "n1", "plan_details": { "record_count": 8298528 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "prev_record_count": 1, "rows": 1, "cost": 265.5545776, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 1, "cost": 70319.62024, "cost_without_join_buffer": 117097.21, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "index": "PRIMARY", "rows_read": 1, "rows_out": 1, "cost": 265.5545776, "uses_join_buffering": false } } } ] }, { "plan_prefix": "CUSTOMER,n2,ORDERS,LINEITEM,SUPPLIER", "table": "n1", "rows_for_plan": 8298528, "cost_for_plan": 4038.292409 } ], "pruned_by_hanging_leaf": true } ] }, { "plan_prefix": "CUSTOMER,n2,ORDERS", "table": "n1", "rows_for_plan": 51865800, "cost_for_plan": 11716.87112, "pruned_by_cost": true, "current_cost": 11716.87112, "best_cost": 4038.292409 }, { "plan_prefix": "CUSTOMER,n2,ORDERS", "table": "SUPPLIER", "rows_for_plan": 20227662000, "cost_for_plan": 4341976.936, "pruned_by_cost": true, "current_cost": 4341976.936, "best_cost": 4038.292409 } ] }, { "plan_prefix": "CUSTOMER,n2", "table": "n1", "rows_for_plan": 3704700, "cost_for_plan": 605.4555329, "rest_of_plan": [ { "plan_prefix": "CUSTOMER,n2,n1", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 3704700 }, "considered_access_paths": [ { "access_type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows": 390, "cost": 214998.0847, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows_read": 390, "rows_out": 390, "cost": 214998.0847, "uses_join_buffering": false } } }, { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 3704700 }, "considered_access_paths": [ { "access_type": "ref", "index": "ORDERS_FK1", "rows": 14, "cost": 10671.87557, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "ORDERS_FK1", "rows_read": 14, "rows_out": 14, "cost": 10671.87557, "uses_join_buffering": false } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 3704700 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5082399, "rows_after_filter": 5082399, "rows_out": 5082399, "cost": 4041425394, "cost_without_join_buffer": 3075076303, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5082399, "rows_out": 5082399, "cost": 4041425394, "uses_join_buffering": true } } } ] }, { "plan_prefix": "CUSTOMER,n2,n1", "table": "ORDERS", "rows_for_plan": 51865800, "cost_for_plan": 11277.33111, "pruned_by_cost": true, "current_cost": 11277.33111, "best_cost": 4038.292409 }, { "plan_prefix": "CUSTOMER,n2,n1", "table": "SUPPLIER", "rows_for_plan": 1444833000, "cost_for_plan": 215603.5403, "pruned_by_cost": true, "current_cost": 215603.5403, "best_cost": 4038.292409 }, { "plan_prefix": "CUSTOMER,n2,n1", "table": "LINEITEM", "rows_for_plan": 1.882876e13, "cost_for_plan": 4041426000, "pruned_by_cost": true, "current_cost": 4041426000, "best_cost": 4038.292409 } ] }, { "plan_prefix": "CUSTOMER,n2", "table": "SUPPLIER", "rows_for_plan": 1444833000, "cost_for_plan": 222185.7064, "pruned_by_cost": true, "current_cost": 222185.7064, "best_cost": 4038.292409 }, { "plan_prefix": "CUSTOMER,n2", "table": "LINEITEM", "rows_for_plan": 7.531505e11, "cost_for_plan": 115802502.9, "pruned_by_cost": true, "current_cost": 115802502.9, "best_cost": 4038.292409 } ] }, { "plan_prefix": "CUSTOMER", "table": "ORDERS", "rows_for_plan": 2074632, "cost_for_plan": 449.8544162, "pruned_by_heuristic": true }, { "plan_prefix": "CUSTOMER", "table": "n1", "rows_for_plan": 3704700, "cost_for_plan": 374.9816849, "pruned_by_heuristic": true }, { "plan_prefix": "CUSTOMER", "table": "SUPPLIER", "rows_for_plan": 1444833000, "cost_for_plan": 134215.341, "pruned_by_cost": true, "current_cost": 134215.341, "best_cost": 4038.292409 }, { "plan_prefix": "CUSTOMER", "table": "LINEITEM", "rows_for_plan": 7.531505e11, "cost_for_plan": 69947126.43, "pruned_by_cost": true, "current_cost": 69947126.43, "best_cost": 4038.292409 } ] }, { "plan_prefix": "", "table": "ORDERS", "rows_for_plan": 1488318, "cost_for_plan": 219.5625961, "rest_of_plan": [ { "plan_prefix": "ORDERS", "get_costs_for_tables": [ { "best_access_path": { "table": "n2", "plan_details": { "record_count": 1488318 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 25, "cost": 3546.915899, "cost_without_join_buffer": 21001.06079, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 25, "cost": 3546.915899, "uses_join_buffering": true } } }, { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 1488318 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 9750, "rows_after_filter": 9750, "rows_out": 9750, "cost": 1347777.555, "cost_without_join_buffer": 2137603.684, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 9750, "rows_out": 9750, "cost": 1347777.555, "uses_join_buffering": true } } }, { "best_access_path": { "table": "CUSTOMER", "plan_details": { "record_count": 1488318 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "prev_record_count": 1, "rows": 1, "cost": 47.62785764, "chosen": true }, { "access_type": "ref", "index": "CUSTOMER_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": 47.62785764, "uses_join_buffering": false } } }, { "best_access_path": { "table": "n1", "plan_details": { "record_count": 1488318 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 25, "cost": 3546.915899, "cost_without_join_buffer": 21001.06079, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 25, "cost": 3546.915899, "uses_join_buffering": true } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 1488318 }, "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 4, "cost": 2200.336699, "chosen": true }, { "access_type": "ref", "index": "IDX_LINEITEM_ORDERKEY_FKIDX", "rows": 4, "cost": 7188.380338, "chosen": false, "cause": "cost" }, { "access_type": "scan_with_join_cache", "rows": 5082399, "rows_after_filter": 5082399, "rows_out": 4, "cost": 702516804.6, "cost_without_join_buffer": 1235374388, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "index": "PRIMARY", "rows_read": 4, "rows_out": 4, "cost": 2200.336699, "uses_join_buffering": false } } } ] }, { "plan_prefix": "ORDERS", "table": "CUSTOMER", "rows_for_plan": 1488318, "cost_for_plan": 267.1904538, "rest_of_plan": [ { "plan_prefix": "ORDERS,CUSTOMER", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 1488318 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 9750, "rows_after_filter": 9750, "rows_out": 9750, "cost": 2231083.447, "cost_without_join_buffer": 2137603.684, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 9750, "rows_out": 9750, "cost": 2231083.447, "uses_join_buffering": true } } }, { "best_access_path": { "table": "n2", "plan_details": { "record_count": 1488318 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "prev_record_count": 1, "rows": 1, "cost": 47.62785764, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 1, "cost": 5812.334624, "cost_without_join_buffer": 21001.06079, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "index": "PRIMARY", "rows_read": 1, "rows_out": 1, "cost": 47.62785764, "uses_join_buffering": false } } } ] }, { "plan_prefix": "ORDERS,CUSTOMER", "table": "n2", "rows_for_plan": 1488318, "cost_for_plan": 314.8183114, "rest_of_plan": [ { "plan_prefix": "ORDERS,CUSTOMER,n2", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 1488318 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 9750, "rows_after_filter": 9750, "rows_out": 9750, "cost": 3114560.253, "cost_without_join_buffer": 2137603.684, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 9750, "rows_out": 9750, "cost": 3114560.253, "uses_join_buffering": true } } }, { "best_access_path": { "table": "n1", "plan_details": { "record_count": 1488318 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 25, "cost": 8079.432511, "cost_without_join_buffer": 21001.06079, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 25, "cost": 8079.432511, "uses_join_buffering": true } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 1488318 }, "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 4, "cost": 2200.336699, "chosen": true }, { "access_type": "ref", "index": "IDX_LINEITEM_ORDERKEY_FKIDX", "rows": 4, "cost": 7188.380338, "chosen": false, "cause": "cost" }, { "access_type": "scan_with_join_cache", "rows": 5082399, "rows_after_filter": 5082399, "rows_out": 4, "cost": 1623508594, "cost_without_join_buffer": 1235374388, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "index": "PRIMARY", "rows_read": 4, "rows_out": 4, "cost": 2200.336699, "uses_join_buffering": false } } } ] }, { "plan_prefix": "ORDERS,CUSTOMER,n2", "table": "LINEITEM", "rows_for_plan": 5953272, "cost_for_plan": 2515.155011, "rest_of_plan": [ { "plan_prefix": "ORDERS,CUSTOMER,n2,LINEITEM", "get_costs_for_tables": [ { "best_access_path": { "table": "n1", "plan_details": { "record_count": 5953272 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 25, "cost": 41384.83507, "cost_without_join_buffer": 84004.2407, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 25, "cost": 41384.83507, "uses_join_buffering": true } } }, { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 5953272 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "prev_record_count": 1, "rows": 1, "cost": 190.5063856, "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": 190.5063856, "uses_join_buffering": false } } } ] }, { "plan_prefix": "ORDERS,CUSTOMER,n2,LINEITEM", "table": "SUPPLIER", "rows_for_plan": 5953272, "cost_for_plan": 2705.661396, "rest_of_plan": [ { "plan_prefix": "ORDERS,CUSTOMER,n2,LINEITEM,SUPPLIER", "get_costs_for_tables": [ { "best_access_path": { "table": "n1", "plan_details": { "record_count": 5953272 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "prev_record_count": 1, "rows": 1, "cost": 190.5063856, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 1, "cost": 50446.52408, "cost_without_join_buffer": 84004.2407, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "index": "PRIMARY", "rows_read": 1, "rows_out": 1, "cost": 190.5063856, "uses_join_buffering": false } } } ] }, { "plan_prefix": "ORDERS,CUSTOMER,n2,LINEITEM,SUPPLIER", "table": "n1", "rows_for_plan": 5953272, "cost_for_plan": 2896.167782 } ], "pruned_by_hanging_leaf": true } ] }, { "plan_prefix": "ORDERS,CUSTOMER,n2", "table": "n1", "rows_for_plan": 37207950, "cost_for_plan": 8394.250822, "pruned_by_cost": true, "current_cost": 8394.250822, "best_cost": 2896.167782 }, { "plan_prefix": "ORDERS,CUSTOMER,n2", "table": "SUPPLIER", "rows_for_plan": 14511100500, "cost_for_plan": 3114875.072, "pruned_by_cost": true, "current_cost": 3114875.072, "best_cost": 2896.167782 } ] } ] }, { "plan_prefix": "ORDERS", "table": "LINEITEM", "rows_for_plan": 5953272, "cost_for_plan": 2419.899295, "pruned_by_heuristic": true }, { "plan_prefix": "ORDERS", "table": "n1", "rows_for_plan": 37207950, "cost_for_plan": 3766.478495, "pruned_by_cost": true, "current_cost": 3766.478495, "best_cost": 2896.167782 }, { "plan_prefix": "ORDERS", "table": "SUPPLIER", "rows_for_plan": 14511100500, "cost_for_plan": 1347997.118, "pruned_by_cost": true, "current_cost": 1347997.118, "best_cost": 2896.167782 } ] }, { "plan_prefix": "", "table": "LINEITEM", "rows_for_plan": 5082399, "cost_for_plan": 867.009613, "rest_of_plan": [ { "plan_prefix": "LINEITEM", "get_costs_for_tables": [ { "best_access_path": { "table": "n2", "plan_details": { "record_count": 5082399 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 25, "cost": 12116.02361, "cost_without_join_buffer": 71715.70015, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 25, "cost": 12116.02361, "uses_join_buffering": true } } }, { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 5082399 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "prev_record_count": 1, "rows": 1, "cost": 162.6384496, "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": 162.6384496, "uses_join_buffering": false } } }, { "best_access_path": { "table": "CUSTOMER", "plan_details": { "record_count": 5082399 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 148188, "rows_after_filter": 148188, "rows_out": 148188, "cost": 69953369.81, "cost_without_join_buffer": 110525042, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 148188, "rows_out": 148188, "cost": 69953369.81, "uses_join_buffering": true } } }, { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 5082399 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "prev_record_count": 1, "rows": 1, "cost": 162.6384496, "chosen": true }, { "access_type": "ref", "index": "ORDERS_FK1", "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": 162.6384496, "uses_join_buffering": false } } }, { "best_access_path": { "table": "n1", "plan_details": { "record_count": 5082399 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 25, "cost": 12116.02361, "cost_without_join_buffer": 71715.70015, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 25, "cost": 12116.02361, "uses_join_buffering": true } } } ] }, { "plan_prefix": "LINEITEM", "table": "SUPPLIER", "rows_for_plan": 5082399, "cost_for_plan": 1029.648063, "rest_of_plan": [ { "plan_prefix": "LINEITEM,SUPPLIER", "get_costs_for_tables": [ { "best_access_path": { "table": "n2", "plan_details": { "record_count": 5082399 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 25, "cost": 19852.11129, "cost_without_join_buffer": 71715.70015, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 25, "cost": 19852.11129, "uses_join_buffering": true } } }, { "best_access_path": { "table": "CUSTOMER", "plan_details": { "record_count": 5082399 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 148188, "rows_after_filter": 148188, "rows_out": 148188, "cost": 115798414.7, "cost_without_join_buffer": 110525042, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 148188, "rows_out": 148188, "cost": 115798414.7, "uses_join_buffering": true } } }, { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 5082399 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "prev_record_count": 1, "rows": 1, "cost": 162.6384496, "chosen": true }, { "access_type": "ref", "index": "ORDERS_FK1", "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": 162.6384496, "uses_join_buffering": false } } } ] }, { "plan_prefix": "LINEITEM,SUPPLIER", "table": "ORDERS", "rows_for_plan": 5082399, "cost_for_plan": 1192.286512, "rest_of_plan": [ { "plan_prefix": "LINEITEM,SUPPLIER,ORDERS", "get_costs_for_tables": [ { "best_access_path": { "table": "CUSTOMER", "plan_details": { "record_count": 5082399 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "prev_record_count": 1, "rows": 1, "cost": 162.6384496, "chosen": true }, { "access_type": "ref", "index": "CUSTOMER_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": 162.6384496, "uses_join_buffering": false } } } ] }, { "plan_prefix": "LINEITEM,SUPPLIER,ORDERS", "table": "CUSTOMER", "rows_for_plan": 5082399, "cost_for_plan": 1354.924962, "rest_of_plan": [ { "plan_prefix": "LINEITEM,SUPPLIER,ORDERS,CUSTOMER", "get_costs_for_tables": [ { "best_access_path": { "table": "n2", "plan_details": { "record_count": 5082399 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "prev_record_count": 1, "rows": 1, "cost": 162.6384496, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 1, "cost": 35325.11918, "cost_without_join_buffer": 71715.70015, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "index": "PRIMARY", "rows_read": 1, "rows_out": 1, "cost": 162.6384496, "uses_join_buffering": false } } } ] }, { "plan_prefix": "LINEITEM,SUPPLIER,ORDERS,CUSTOMER", "table": "n2", "rows_for_plan": 5082399, "cost_for_plan": 1517.563412, "rest_of_plan": [ { "plan_prefix": "LINEITEM,SUPPLIER,ORDERS,CUSTOMER,n2", "get_costs_for_tables": [ { "best_access_path": { "table": "n1", "plan_details": { "record_count": 5082399 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "prev_record_count": 1, "rows": 1, "cost": 162.6384496, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 1, "cost": 43066.96399, "cost_without_join_buffer": 71715.70015, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "index": "PRIMARY", "rows_read": 1, "rows_out": 1, "cost": 162.6384496, "uses_join_buffering": false } } } ] }, { "plan_prefix": "LINEITEM,SUPPLIER,ORDERS,CUSTOMER,n2", "table": "n1", "rows_for_plan": 5082399, "cost_for_plan": 1680.201861 } ] } ] } ] } ], "pruned_by_hanging_leaf": true } ] } ] }, { "best_join_order": [ "LINEITEM", "SUPPLIER", "ORDERS", "CUSTOMER", "n2", "n1" ], "rows": 5082399, "cost": 1680.201861 }, { "substitute_best_equal": { "condition": "WHERE", "resulting_condition": "SUPPLIER.S_SUPPKEY = LINEITEM.L_SUPPKEY and ORDERS.O_ORDERKEY = LINEITEM.L_ORDERKEY and CUSTOMER.C_CUSTKEY = ORDERS.O_CUSTKEY and n1.N_NATIONKEY = SUPPLIER.S_NATIONKEY and n2.N_NATIONKEY = CUSTOMER.C_NATIONKEY and (n1.N_NAME = 'JAPAN' and n2.N_NAME = 'INDIA' or n1.N_NAME = 'INDIA' and n2.N_NAME = 'JAPAN') and LINEITEM.L_SHIPDATE between DATE'1995-01-01' and DATE'1996-12-31'" } }, { "attaching_conditions_to_tables": { "attached_conditions_computation": [], "attached_conditions_summary": [ { "table": "LINEITEM", "attached_condition": "LINEITEM.L_SHIPDATE between DATE'1995-01-01' and DATE'1996-12-31'" }, { "table": "SUPPLIER", "attached_condition": "SUPPLIER.S_NATIONKEY is not null" }, { "table": "ORDERS", "attached_condition": null }, { "table": "CUSTOMER", "attached_condition": "CUSTOMER.C_NATIONKEY is not null" }, { "table": "n2", "attached_condition": "n2.N_NAME = 'INDIA' or n2.N_NAME = 'JAPAN'" }, { "table": "n1", "attached_condition": "n1.N_NAME = 'JAPAN' and n2.N_NAME = 'INDIA' or n1.N_NAME = 'INDIA' and n2.N_NAME = 'JAPAN'" } ] } }, { "make_join_readinfo": [] }, { "prepare_sum_aggregators": { "function": "sum(LINEITEM.L_EXTENDEDPRICE * (1 - LINEITEM.L_DISCOUNT))", "aggregator_type": "simple" } } ] } } ] }