{ "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": 9733, "read_cost": 1.128858625, "read_and_compare_cost": 1.440314625 } }, { "selectivity_for_indexes": [], "selectivity_for_columns": [], "cond_selectivity": 1 }, { "table": "LINEITEM", "table_scan": { "rows": 5810970, "read_cost": 806.4899244, "read_and_compare_cost": 992.4409644 } }, { "table": "ORDERS", "table_scan": { "rows": 1490801, "read_cost": 172.2228926, "read_and_compare_cost": 219.9285246 } }, { "table": "CUSTOMER", "table_scan": { "rows": 148804, "read_cost": 17.16455106, "read_and_compare_cost": 21.92627906 } }, { "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": 9733, "rows_after_filter": 9733, "rows_out": 9733, "cost": 1.440314625, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 9733, "rows_out": 9733, "cost": 1.440314625, "uses_join_buffering": false } } }, { "best_access_path": { "table": "CUSTOMER", "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "scan", "rows": 148804, "rows_after_filter": 148804, "rows_out": 148804, "cost": 21.92627906, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 148804, "rows_out": 148804, "cost": 21.92627906, "uses_join_buffering": false } } }, { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "scan", "rows": 1490801, "rows_after_filter": 1490801, "rows_out": 1490801, "cost": 219.9285246, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 1490801, "rows_out": 1490801, "cost": 219.9285246, "uses_join_buffering": false } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "scan", "rows": 5810970, "rows_after_filter": 5810970, "rows_out": 5810970, "cost": 992.4409644, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5810970, "rows_out": 5810970, "cost": 992.4409644, "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": 389, "cost": 1.453733225, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows_read": 389, "rows_out": 389, "cost": 1.453733225, "uses_join_buffering": false } } }, { "best_access_path": { "table": "CUSTOMER", "plan_details": { "record_count": 25 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 148804, "rows_after_filter": 148804, "rows_out": 148804, "cost": 367.3986073, "cost_without_join_buffer": 546.0139493, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 148804, "rows_out": 148804, "cost": 367.3986073, "uses_join_buffering": true } } }, { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 25 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 1490801, "rows_after_filter": 1490801, "rows_out": 1490801, "cost": 3681.048188, "cost_without_join_buffer": 5469.58699, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 1490801, "rows_out": 1490801, "cost": 3681.048188, "uses_join_buffering": true } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 25 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5810970, "rows_after_filter": 5810970, "rows_out": 5810970, "cost": 14483.48099, "cost_without_join_buffer": 23770.41729, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5810970, "rows_out": 5810970, "cost": 14483.48099, "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": 389, "cost": 36.18604422, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows_read": 389, "rows_out": 389, "cost": 36.18604422, "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": 6200, "cost": 569.0993678, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "CUSTOMER_NATION_FKIDX", "rows_read": 6200, "rows_out": 6200, "cost": 569.0993678, "uses_join_buffering": false } } }, { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 625 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 1490801, "rows_after_filter": 1490801, "rows_out": 1490801, "cost": 143459.8536, "cost_without_join_buffer": 136711.0486, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 1490801, "rows_out": 1490801, "cost": 143459.8536, "uses_join_buffering": true } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 625 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5810970, "rows_after_filter": 5810970, "rows_out": 5810970, "cost": 559325.004, "cost_without_join_buffer": 593219.8254, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5810970, "rows_out": 5810970, "cost": 559325.004, "uses_join_buffering": true } } } ] }, { "plan_prefix": "n1,n2", "table": "SUPPLIER", "rows_for_plan": 243125, "cost_for_plan": 36.27546672, "rest_of_plan": [ { "plan_prefix": "n1,n2,SUPPLIER", "get_costs_for_tables": [ { "best_access_path": { "table": "CUSTOMER", "plan_details": { "record_count": 243125 }, "considered_access_paths": [ { "access_type": "ref", "index": "CUSTOMER_NATION_FKIDX", "rows": 6200, "cost": 221345.0085, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "CUSTOMER_NATION_FKIDX", "rows_read": 6200, "rows_out": 6200, "cost": 221345.0085, "uses_join_buffering": false } } }, { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 243125 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 1490801, "rows_after_filter": 1490801, "rows_out": 1490801, "cost": 77795273.3, "cost_without_join_buffer": 53180135.13, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 1490801, "rows_out": 1490801, "cost": 77795273.3, "uses_join_buffering": true } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 243125 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5810970, "rows_after_filter": 5810970, "rows_out": 5810970, "cost": 303243154.5, "cost_without_join_buffer": 230745688.9, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5810970, "rows_out": 5810970, "cost": 303243154.5, "uses_join_buffering": true } } } ] }, { "plan_prefix": "n1,n2,SUPPLIER", "table": "CUSTOMER", "rows_for_plan": 1507375000, "cost_for_plan": 221381.2839, "rest_of_plan": [ { "plan_prefix": "n1,n2,SUPPLIER,CUSTOMER", "get_costs_for_tables": [ { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 1507375000 }, "considered_access_paths": [ { "access_type": "ref", "index": "ORDERS_FK1", "rows": 15, "cost": 4562817.781, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "ORDERS_FK1", "rows_read": 15, "rows_out": 15, "cost": 4562817.781, "uses_join_buffering": false } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 1507375000 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5810970, "rows_after_filter": 5810970, "rows_out": 5810970, "cost": 2.413301e12, "cost_without_join_buffer": 1.430623e12, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5810970, "rows_out": 5810970, "cost": 2.413301e12, "uses_join_buffering": true } } } ] }, { "plan_prefix": "n1,n2,SUPPLIER,CUSTOMER", "table": "ORDERS", "rows_for_plan": 22610625000, "cost_for_plan": 4784199.065, "rest_of_plan": [ { "plan_prefix": "n1,n2,SUPPLIER,CUSTOMER,ORDERS", "get_costs_for_tables": [ { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 22610625000 }, "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 3, "cost": 29180637.66, "chosen": true }, { "access_type": "ref", "index": "IDX_LINEITEM_ORDERKEY_FKIDX", "rows": 3, "cost": 86079604.91, "chosen": false, "cause": "cost" }, { "access_type": "scan_with_join_cache", "rows": 5810970, "rows_after_filter": 5810970, "rows_out": 3, "cost": 4.419765e13, "cost_without_join_buffer": 2.145935e13, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "index": "PRIMARY", "rows_read": 3, "rows_out": 3, "cost": 29180637.66, "uses_join_buffering": false } } } ] }, { "plan_prefix": "n1,n2,SUPPLIER,CUSTOMER,ORDERS", "table": "LINEITEM", "rows_for_plan": 67831875000, "cost_for_plan": 33964836.73 } ] }, { "plan_prefix": "n1,n2,SUPPLIER,CUSTOMER", "table": "LINEITEM", "rows_for_plan": 8.759311e15, "cost_for_plan": 2.413301e12, "pruned_by_cost": true, "current_cost": 2.413301e12, "best_cost": 33964836.73 } ] }, { "plan_prefix": "n1,n2,SUPPLIER", "table": "ORDERS", "rows_for_plan": 3.62451e11, "cost_for_plan": 77795309.58, "pruned_by_cost": true, "current_cost": 77795309.58, "best_cost": 33964836.73 }, { "plan_prefix": "n1,n2,SUPPLIER", "table": "LINEITEM", "rows_for_plan": 1.412792e12, "cost_for_plan": 303243190.8, "pruned_by_cost": true, "current_cost": 303243190.8, "best_cost": 33964836.73 } ] }, { "plan_prefix": "n1,n2", "table": "CUSTOMER", "rows_for_plan": 3875000, "cost_for_plan": 569.1887903, "rest_of_plan": [ { "plan_prefix": "n1,n2,CUSTOMER", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 3875000 }, "considered_access_paths": [ { "access_type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows": 389, "cost": 224312.8484, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows_read": 389, "rows_out": 389, "cost": 224312.8484, "uses_join_buffering": false } } }, { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 3875000 }, "considered_access_paths": [ { "access_type": "ref", "index": "ORDERS_FK1", "rows": 15, "cost": 11730.79838, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "ORDERS_FK1", "rows_read": 15, "rows_out": 15, "cost": 11730.79838, "uses_join_buffering": false } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 3875000 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5810970, "rows_after_filter": 5810970, "rows_out": 5810970, "cost": 4833180686, "cost_without_join_buffer": 3677694137, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5810970, "rows_out": 5810970, "cost": 4833180686, "uses_join_buffering": true } } } ] }, { "plan_prefix": "n1,n2,CUSTOMER", "table": "ORDERS", "rows_for_plan": 58125000, "cost_for_plan": 12299.98717, "rest_of_plan": [ { "plan_prefix": "n1,n2,CUSTOMER,ORDERS", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 58125000 }, "considered_access_paths": [ { "access_type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows": 389, "cost": 3364692.635, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows_read": 389, "rows_out": 389, "cost": 3364692.635, "uses_join_buffering": false } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 58125000 }, "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 3, "cost": 75057.73987, "chosen": true }, { "access_type": "ref", "index": "IDX_LINEITEM_ORDERKEY_FKIDX", "rows": 3, "cost": 221335.3066, "chosen": false, "cause": "cost" }, { "access_type": "scan_with_join_cache", "rows": 5810970, "rows_after_filter": 5810970, "rows_out": 3, "cost": 93058495504, "cost_without_join_buffer": 55165411451, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "index": "PRIMARY", "rows_read": 3, "rows_out": 3, "cost": 75057.73987, "uses_join_buffering": false } } } ] }, { "plan_prefix": "n1,n2,CUSTOMER,ORDERS", "table": "LINEITEM", "rows_for_plan": 174375000, "cost_for_plan": 87357.72704, "rest_of_plan": [ { "plan_prefix": "n1,n2,CUSTOMER,ORDERS,LINEITEM", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 174375000 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 155968.1069, "chosen": true }, { "access_type": "eq_ref", "index": "SUPPLIER_NATION_FKIDX", "rows": 1, "cost": 152474.3784, "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": 152474.3784, "uses_join_buffering": false } } } ] }, { "plan_prefix": "n1,n2,CUSTOMER,ORDERS,LINEITEM", "table": "SUPPLIER", "rows_for_plan": 174375000, "cost_for_plan": 239832.1055 } ] }, { "plan_prefix": "n1,n2,CUSTOMER,ORDERS", "table": "SUPPLIER", "rows_for_plan": 22610625000, "cost_for_plan": 3376992.622, "pruned_by_cost": true, "current_cost": 3376992.622, "best_cost": 239832.1055 } ] }, { "plan_prefix": "n1,n2,CUSTOMER", "table": "SUPPLIER", "rows_for_plan": 1507375000, "cost_for_plan": 224882.0372, "rest_of_plan": [ { "plan_prefix": "n1,n2,CUSTOMER,SUPPLIER", "get_costs_for_tables": [ { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 1507375000 }, "considered_access_paths": [ { "access_type": "ref", "index": "ORDERS_FK1", "rows": 15, "cost": 4562817.781, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "ORDERS_FK1", "rows_read": 15, "rows_out": 15, "cost": 4562817.781, "uses_join_buffering": false } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 1507375000 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5810970, "rows_after_filter": 5810970, "rows_out": 5810970, "cost": 2.413301e12, "cost_without_join_buffer": 1.430623e12, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5810970, "rows_out": 5810970, "cost": 2.413301e12, "uses_join_buffering": true } } } ] }, { "plan_prefix": "n1,n2,CUSTOMER,SUPPLIER", "table": "ORDERS", "rows_for_plan": 22610625000, "cost_for_plan": 4787699.818, "pruned_by_cost": true, "current_cost": 4787699.818, "best_cost": 239832.1055 }, { "plan_prefix": "n1,n2,CUSTOMER,SUPPLIER", "table": "LINEITEM", "rows_for_plan": 8.759311e15, "cost_for_plan": 2.413301e12, "pruned_by_cost": true, "current_cost": 2.413301e12, "best_cost": 239832.1055 } ] }, { "plan_prefix": "n1,n2,CUSTOMER", "table": "LINEITEM", "rows_for_plan": 2.251751e13, "cost_for_plan": 4833181255, "pruned_by_cost": true, "current_cost": 4833181255, "best_cost": 239832.1055 } ] }, { "plan_prefix": "n1,n2", "table": "ORDERS", "rows_for_plan": 931750625, "cost_for_plan": 143459.9431, "rest_of_plan": [ { "plan_prefix": "n1,n2,ORDERS", "get_costs_for_tables": [ { "best_access_path": { "table": "CUSTOMER", "plan_details": { "record_count": 931750625 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 833396.5265, "chosen": true }, { "access_type": "eq_ref", "index": "CUSTOMER_NATION_FKIDX", "rows": 1, "cost": 814727.4945, "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": 814727.4945, "uses_join_buffering": false } } }, { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 931750625 }, "considered_access_paths": [ { "access_type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows": 389, "cost": 53936420.81, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows_read": 389, "rows_out": 389, "cost": 53936420.81, "uses_join_buffering": false } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 931750625 }, "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 3, "cost": 1202532.763, "chosen": true }, { "access_type": "ref", "index": "IDX_LINEITEM_ORDERKEY_FKIDX", "rows": 3, "cost": 3547263.006, "chosen": false, "cause": "cost" }, { "access_type": "scan_with_join_cache", "rows": 5810970, "rows_after_filter": 5810970, "rows_out": 3, "cost": 1.162157e12, "cost_without_join_buffer": 8.843081e11, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "index": "PRIMARY", "rows_read": 3, "rows_out": 3, "cost": 1202532.763, "uses_join_buffering": false } } } ] }, { "plan_prefix": "n1,n2,ORDERS", "table": "CUSTOMER", "rows_for_plan": 931750625, "cost_for_plan": 958187.4376, "pruned_by_cost": true, "current_cost": 958187.4376, "best_cost": 239832.1055 }, { "plan_prefix": "n1,n2,ORDERS", "table": "LINEITEM", "rows_for_plan": 2795251875, "cost_for_plan": 1345992.706, "pruned_by_cost": true, "current_cost": 1345992.706, "best_cost": 239832.1055 }, { "plan_prefix": "n1,n2,ORDERS", "table": "SUPPLIER", "rows_for_plan": 3.62451e11, "cost_for_plan": 54079880.75, "pruned_by_cost": true, "current_cost": 54079880.75, "best_cost": 239832.1055 } ] }, { "plan_prefix": "n1,n2", "table": "LINEITEM", "rows_for_plan": 3631856250, "cost_for_plan": 559325.0935, "pruned_by_cost": true, "current_cost": 559325.0935, "best_cost": 239832.1055 } ] }, { "plan_prefix": "n1", "table": "SUPPLIER", "rows_for_plan": 9725, "cost_for_plan": 1.468663025, "rest_of_plan": [ { "plan_prefix": "n1,SUPPLIER", "get_costs_for_tables": [ { "best_access_path": { "table": "n2", "plan_details": { "record_count": 9725 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 25, "cost": 37.99705475, "cost_without_join_buffer": 137.2264042, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 25, "cost": 37.99705475, "uses_join_buffering": true } } }, { "best_access_path": { "table": "CUSTOMER", "plan_details": { "record_count": 9725 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 148804, "rows_after_filter": 148804, "rows_out": 148804, "cost": 222512.8379, "cost_without_join_buffer": 212364.7807, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 148804, "rows_out": 148804, "cost": 222512.8379, "uses_join_buffering": true } } }, { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 9725 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 1490801, "rows_after_filter": 1490801, "rows_out": 1490801, "cost": 2229251.899, "cost_without_join_buffer": 2127206.55, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 1490801, "rows_out": 1490801, "cost": 2229251.899, "uses_join_buffering": true } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 9725 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5810970, "rows_after_filter": 5810970, "rows_out": 5810970, "cost": 8689596.205, "cost_without_join_buffer": 9229869.181, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5810970, "rows_out": 5810970, "cost": 8689596.205, "uses_join_buffering": true } } } ] }, { "plan_prefix": "n1,SUPPLIER", "table": "n2", "rows_for_plan": 243125, "cost_for_plan": 39.46571778, "rest_of_plan": [ { "plan_prefix": "n1,SUPPLIER,n2", "get_costs_for_tables": [ { "best_access_path": { "table": "CUSTOMER", "plan_details": { "record_count": 243125 }, "considered_access_paths": [ { "access_type": "ref", "index": "CUSTOMER_NATION_FKIDX", "rows": 6200, "cost": 221345.0085, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "CUSTOMER_NATION_FKIDX", "rows_read": 6200, "rows_out": 6200, "cost": 221345.0085, "uses_join_buffering": false } } }, { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 243125 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 1490801, "rows_after_filter": 1490801, "rows_out": 1490801, "cost": 77795273.3, "cost_without_join_buffer": 53180135.13, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 1490801, "rows_out": 1490801, "cost": 77795273.3, "uses_join_buffering": true } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 243125 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5810970, "rows_after_filter": 5810970, "rows_out": 5810970, "cost": 303243154.5, "cost_without_join_buffer": 230745688.9, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5810970, "rows_out": 5810970, "cost": 303243154.5, "uses_join_buffering": true } } } ] }, { "plan_prefix": "n1,SUPPLIER,n2", "table": "CUSTOMER", "rows_for_plan": 1507375000, "cost_for_plan": 221384.4742, "rest_of_plan": [ { "plan_prefix": "n1,SUPPLIER,n2,CUSTOMER", "get_costs_for_tables": [ { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 1507375000 }, "considered_access_paths": [ { "access_type": "ref", "index": "ORDERS_FK1", "rows": 15, "cost": 4562817.781, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "ORDERS_FK1", "rows_read": 15, "rows_out": 15, "cost": 4562817.781, "uses_join_buffering": false } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 1507375000 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5810970, "rows_after_filter": 5810970, "rows_out": 5810970, "cost": 2.413301e12, "cost_without_join_buffer": 1.430623e12, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5810970, "rows_out": 5810970, "cost": 2.413301e12, "uses_join_buffering": true } } } ] }, { "plan_prefix": "n1,SUPPLIER,n2,CUSTOMER", "table": "ORDERS", "rows_for_plan": 22610625000, "cost_for_plan": 4784202.255, "pruned_by_cost": true, "current_cost": 4784202.255, "best_cost": 239832.1055 }, { "plan_prefix": "n1,SUPPLIER,n2,CUSTOMER", "table": "LINEITEM", "rows_for_plan": 8.759311e15, "cost_for_plan": 2.413301e12, "pruned_by_cost": true, "current_cost": 2.413301e12, "best_cost": 239832.1055 } ] }, { "plan_prefix": "n1,SUPPLIER,n2", "table": "ORDERS", "rows_for_plan": 3.62451e11, "cost_for_plan": 77795312.77, "pruned_by_cost": true, "current_cost": 77795312.77, "best_cost": 239832.1055 }, { "plan_prefix": "n1,SUPPLIER,n2", "table": "LINEITEM", "rows_for_plan": 1.412792e12, "cost_for_plan": 303243193.9, "pruned_by_cost": true, "current_cost": 303243193.9, "best_cost": 239832.1055 } ] }, { "plan_prefix": "n1,SUPPLIER", "table": "CUSTOMER", "rows_for_plan": 1447118900, "cost_for_plan": 222514.3066, "rest_of_plan": [ { "plan_prefix": "n1,SUPPLIER,CUSTOMER", "get_costs_for_tables": [ { "best_access_path": { "table": "n2", "plan_details": { "record_count": 1447118900 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 1294361.03, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 1, "cost": 7855539.853, "cost_without_join_buffer": 20419715.95, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "index": "PRIMARY", "rows_read": 1, "rows_out": 1, "cost": 1294361.03, "uses_join_buffering": false } } }, { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 1447118900 }, "considered_access_paths": [ { "access_type": "ref", "index": "ORDERS_FK1", "rows": 15, "cost": 4380422.867, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "ORDERS_FK1", "rows_read": 15, "rows_out": 15, "cost": 4380422.867, "uses_join_buffering": false } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 1447118900 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5810970, "rows_after_filter": 5810970, "rows_out": 5810970, "cost": 1.804841e12, "cost_without_join_buffer": 1.373435e12, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5810970, "rows_out": 5810970, "cost": 1.804841e12, "uses_join_buffering": true } } } ] }, { "plan_prefix": "n1,SUPPLIER,CUSTOMER", "table": "n2", "rows_for_plan": 1447118900, "cost_for_plan": 1516875.336, "pruned_by_cost": true, "current_cost": 1516875.336, "best_cost": 239832.1055 }, { "plan_prefix": "n1,SUPPLIER,CUSTOMER", "table": "ORDERS", "rows_for_plan": 21706783500, "cost_for_plan": 4602937.174, "pruned_by_cost": true, "current_cost": 4602937.174, "best_cost": 239832.1055 }, { "plan_prefix": "n1,SUPPLIER,CUSTOMER", "table": "LINEITEM", "rows_for_plan": 8.409165e15, "cost_for_plan": 1.804842e12, "pruned_by_cost": true, "current_cost": 1.804842e12, "best_cost": 239832.1055 } ] }, { "plan_prefix": "n1,SUPPLIER", "table": "ORDERS", "rows_for_plan": 14498039725, "cost_for_plan": 2229253.368, "pruned_by_cost": true, "current_cost": 2229253.368, "best_cost": 239832.1055 }, { "plan_prefix": "n1,SUPPLIER", "table": "LINEITEM", "rows_for_plan": 56511683250, "cost_for_plan": 8689597.673, "pruned_by_cost": true, "current_cost": 8689597.673, "best_cost": 239832.1055 } ] }, { "plan_prefix": "n1", "table": "CUSTOMER", "rows_for_plan": 3720100, "cost_for_plan": 367.4135371, "rest_of_plan": [ { "plan_prefix": "n1,CUSTOMER", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 3720100 }, "considered_access_paths": [ { "access_type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows": 389, "cost": 215346.1235, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows_read": 389, "rows_out": 389, "cost": 215346.1235, "uses_join_buffering": false } } }, { "best_access_path": { "table": "n2", "plan_details": { "record_count": 3720100 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 3327.407063, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 1, "cost": 14531.70603, "cost_without_join_buffer": 52492.84388, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "index": "PRIMARY", "rows_read": 1, "rows_out": 1, "cost": 3327.407063, "uses_join_buffering": false } } }, { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 3720100 }, "considered_access_paths": [ { "access_type": "ref", "index": "ORDERS_FK1", "rows": 15, "cost": 11261.91685, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "ORDERS_FK1", "rows_read": 15, "rows_out": 15, "cost": 11261.91685, "uses_join_buffering": false } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 3720100 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5810970, "rows_after_filter": 5810970, "rows_out": 5810970, "cost": 3323810584, "cost_without_join_buffer": 3530681282, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5810970, "rows_out": 5810970, "cost": 3323810584, "uses_join_buffering": true } } } ] }, { "plan_prefix": "n1,CUSTOMER", "table": "n2", "rows_for_plan": 3720100, "cost_for_plan": 3694.8206, "rest_of_plan": [ { "plan_prefix": "n1,CUSTOMER,n2", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 3720100 }, "considered_access_paths": [ { "access_type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows": 389, "cost": 215346.1235, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows_read": 389, "rows_out": 389, "cost": 215346.1235, "uses_join_buffering": false } } }, { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 3720100 }, "considered_access_paths": [ { "access_type": "ref", "index": "ORDERS_FK1", "rows": 15, "cost": 11261.91685, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "ORDERS_FK1", "rows_read": 15, "rows_out": 15, "cost": 11261.91685, "uses_join_buffering": false } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 3720100 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5810970, "rows_after_filter": 5810970, "rows_out": 5810970, "cost": 4639978922, "cost_without_join_buffer": 3530681282, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5810970, "rows_out": 5810970, "cost": 4639978922, "uses_join_buffering": true } } } ] }, { "plan_prefix": "n1,CUSTOMER,n2", "table": "ORDERS", "rows_for_plan": 55801500, "cost_for_plan": 14956.73746, "rest_of_plan": [ { "plan_prefix": "n1,CUSTOMER,n2,ORDERS", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 55801500 }, "considered_access_paths": [ { "access_type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows": 389, "cost": 3230191.76, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows_read": 389, "rows_out": 389, "cost": 3230191.76, "uses_join_buffering": false } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 55801500 }, "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 3, "cost": 72059.10047, "chosen": true }, { "access_type": "ref", "index": "IDX_LINEITEM_ORDERKEY_FKIDX", "rows": 3, "cost": 212489.6492, "chosen": false, "cause": "cost" }, { "access_type": "scan_with_join_cache", "rows": 5810970, "rows_after_filter": 5810970, "rows_out": 3, "cost": 89338557173, "cost_without_join_buffer": 52960218618, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "index": "PRIMARY", "rows_read": 3, "rows_out": 3, "cost": 72059.10047, "uses_join_buffering": false } } } ] }, { "plan_prefix": "n1,CUSTOMER,n2,ORDERS", "table": "LINEITEM", "rows_for_plan": 167404500, "cost_for_plan": 87015.83793, "rest_of_plan": [ { "plan_prefix": "n1,CUSTOMER,n2,ORDERS,LINEITEM", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 167404500 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 149733.4129, "chosen": true }, { "access_type": "eq_ref", "index": "SUPPLIER_NATION_FKIDX", "rows": 1, "cost": 146379.3384, "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": 146379.3384, "uses_join_buffering": false } } } ] }, { "plan_prefix": "n1,CUSTOMER,n2,ORDERS,LINEITEM", "table": "SUPPLIER", "rows_for_plan": 167404500, "cost_for_plan": 233395.1763 } ] }, { "plan_prefix": "n1,CUSTOMER,n2,ORDERS", "table": "SUPPLIER", "rows_for_plan": 21706783500, "cost_for_plan": 3245148.498, "pruned_by_cost": true, "current_cost": 3245148.498, "best_cost": 233395.1763 } ] }, { "plan_prefix": "n1,CUSTOMER,n2", "table": "SUPPLIER", "rows_for_plan": 1447118900, "cost_for_plan": 219040.9441, "rest_of_plan": [ { "plan_prefix": "n1,CUSTOMER,n2,SUPPLIER", "get_costs_for_tables": [ { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 1447118900 }, "considered_access_paths": [ { "access_type": "ref", "index": "ORDERS_FK1", "rows": 15, "cost": 4380422.867, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "ORDERS_FK1", "rows_read": 15, "rows_out": 15, "cost": 4380422.867, "uses_join_buffering": false } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 1447118900 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5810970, "rows_after_filter": 5810970, "rows_out": 5810970, "cost": 2.316831e12, "cost_without_join_buffer": 1.373435e12, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5810970, "rows_out": 5810970, "cost": 2.316831e12, "uses_join_buffering": true } } } ] }, { "plan_prefix": "n1,CUSTOMER,n2,SUPPLIER", "table": "ORDERS", "rows_for_plan": 21706783500, "cost_for_plan": 4599463.812, "pruned_by_cost": true, "current_cost": 4599463.812, "best_cost": 233395.1763 }, { "plan_prefix": "n1,CUSTOMER,n2,SUPPLIER", "table": "LINEITEM", "rows_for_plan": 8.409165e15, "cost_for_plan": 2.316831e12, "pruned_by_cost": true, "current_cost": 2.316831e12, "best_cost": 233395.1763 } ] }, { "plan_prefix": "n1,CUSTOMER,n2", "table": "LINEITEM", "rows_for_plan": 2.161739e13, "cost_for_plan": 4639982617, "pruned_by_cost": true, "current_cost": 4639982617, "best_cost": 233395.1763 } ] }, { "plan_prefix": "n1,CUSTOMER", "table": "ORDERS", "rows_for_plan": 55801500, "cost_for_plan": 11629.33039, "pruned_by_heuristic": true }, { "plan_prefix": "n1,CUSTOMER", "table": "SUPPLIER", "rows_for_plan": 1447118900, "cost_for_plan": 215713.537, "pruned_by_heuristic": true }, { "plan_prefix": "n1,CUSTOMER", "table": "LINEITEM", "rows_for_plan": 2.161739e13, "cost_for_plan": 3323810951, "pruned_by_cost": true, "current_cost": 3323810951, "best_cost": 233395.1763 } ] }, { "plan_prefix": "n1", "table": "ORDERS", "rows_for_plan": 37270025, "cost_for_plan": 3681.063118, "rest_of_plan": [ { "plan_prefix": "n1,ORDERS", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 37270025 }, "considered_access_paths": [ { "access_type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows": 389, "cost": 2157456.839, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows_read": 389, "rows_out": 389, "cost": 2157456.839, "uses_join_buffering": false } } }, { "best_access_path": { "table": "CUSTOMER", "plan_details": { "record_count": 37270025 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 33337.29866, "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": 33337.29866, "uses_join_buffering": false } } }, { "best_access_path": { "table": "n2", "plan_details": { "record_count": 37270025 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 25, "cost": 145592.6337, "cost_without_join_buffer": 525902.4156, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 25, "cost": 145592.6337, "uses_join_buffering": true } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 37270025 }, "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 3, "cost": 48142.93478, "chosen": true }, { "access_type": "ref", "index": "IDX_LINEITEM_ORDERKEY_FKIDX", "rows": 3, "cost": 141939.5826, "chosen": false, "cause": "cost" }, { "access_type": "scan_with_join_cache", "rows": 5810970, "rows_after_filter": 5810970, "rows_out": 3, "cost": 33300177697, "cost_without_join_buffer": 35372322836, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "index": "PRIMARY", "rows_read": 3, "rows_out": 3, "cost": 48142.93478, "uses_join_buffering": false } } } ] }, { "plan_prefix": "n1,ORDERS", "table": "CUSTOMER", "rows_for_plan": 37270025, "cost_for_plan": 37018.36178, "rest_of_plan": [ { "plan_prefix": "n1,ORDERS,CUSTOMER", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 37270025 }, "considered_access_paths": [ { "access_type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows": 389, "cost": 2157456.839, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows_read": 389, "rows_out": 389, "cost": 2157456.839, "uses_join_buffering": false } } }, { "best_access_path": { "table": "n2", "plan_details": { "record_count": 37270025 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 33335.80198, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 1, "cost": 202322.6147, "cost_without_join_buffer": 525902.4156, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "index": "PRIMARY", "rows_read": 1, "rows_out": 1, "cost": 33335.80198, "uses_join_buffering": false } } } ] }, { "plan_prefix": "n1,ORDERS,CUSTOMER", "table": "n2", "rows_for_plan": 37270025, "cost_for_plan": 70354.16376, "rest_of_plan": [ { "plan_prefix": "n1,ORDERS,CUSTOMER,n2", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 37270025 }, "considered_access_paths": [ { "access_type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows": 389, "cost": 2157456.839, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows_read": 389, "rows_out": 389, "cost": 2157456.839, "uses_join_buffering": false } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 37270025 }, "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 3, "cost": 48142.93478, "chosen": true }, { "access_type": "ref", "index": "IDX_LINEITEM_ORDERKEY_FKIDX", "rows": 3, "cost": 141939.5826, "chosen": false, "cause": "cost" }, { "access_type": "scan_with_join_cache", "rows": 5810970, "rows_after_filter": 5810970, "rows_out": 3, "cost": 59669547915, "cost_without_join_buffer": 35372322836, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "index": "PRIMARY", "rows_read": 3, "rows_out": 3, "cost": 48142.93478, "uses_join_buffering": false } } } ] }, { "plan_prefix": "n1,ORDERS,CUSTOMER,n2", "table": "LINEITEM", "rows_for_plan": 111810075, "cost_for_plan": 118497.0985, "rest_of_plan": [ { "plan_prefix": "n1,ORDERS,CUSTOMER,n2,LINEITEM", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 111810075 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 100007.5354, "chosen": true }, { "access_type": "eq_ref", "index": "SUPPLIER_NATION_FKIDX", "rows": 1, "cost": 97767.29518, "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": 97767.29518, "uses_join_buffering": false } } } ] }, { "plan_prefix": "n1,ORDERS,CUSTOMER,n2,LINEITEM", "table": "SUPPLIER", "rows_for_plan": 111810075, "cost_for_plan": 216264.3937 } ] }, { "plan_prefix": "n1,ORDERS,CUSTOMER,n2", "table": "SUPPLIER", "rows_for_plan": 14498039725, "cost_for_plan": 2227811.002, "pruned_by_cost": true, "current_cost": 2227811.002, "best_cost": 216264.3937 } ] } ] }, { "plan_prefix": "n1,ORDERS", "table": "LINEITEM", "rows_for_plan": 111810075, "cost_for_plan": 51823.9979, "pruned_by_heuristic": true }, { "plan_prefix": "n1,ORDERS", "table": "SUPPLIER", "rows_for_plan": 14498039725, "cost_for_plan": 2161137.902, "pruned_by_cost": true, "current_cost": 2161137.902, "best_cost": 216264.3937 } ] }, { "plan_prefix": "n1", "table": "LINEITEM", "rows_for_plan": 145274250, "cost_for_plan": 14483.49592, "rest_of_plan": [ { "plan_prefix": "n1,LINEITEM", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 145274250 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 129939.2321, "chosen": true }, { "access_type": "eq_ref", "index": "SUPPLIER_NATION_FKIDX", "rows": 1, "cost": 127028.5371, "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": 127028.5371, "uses_join_buffering": false } } }, { "best_access_path": { "table": "CUSTOMER", "plan_details": { "record_count": 145274250 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 148804, "rows_after_filter": 148804, "rows_out": 148804, "cost": 3323971054, "cost_without_join_buffer": 3172351801, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 148804, "rows_out": 148804, "cost": 3323971054, "uses_join_buffering": true } } }, { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 145274250 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 129949.3107, "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": 129949.3107, "uses_join_buffering": false } } }, { "best_access_path": { "table": "n2", "plan_details": { "record_count": 145274250 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 25, "cost": 567612.6967, "cost_without_join_buffer": 2049906.833, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 25, "cost": 567612.6967, "uses_join_buffering": true } } } ] }, { "plan_prefix": "n1,LINEITEM", "table": "SUPPLIER", "rows_for_plan": 145274250, "cost_for_plan": 141512.033, "rest_of_plan": [ { "plan_prefix": "n1,LINEITEM,SUPPLIER", "get_costs_for_tables": [ { "best_access_path": { "table": "CUSTOMER", "plan_details": { "record_count": 145274250 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 148804, "rows_after_filter": 148804, "rows_out": 148804, "cost": 4639844006, "cost_without_join_buffer": 3172351801, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 148804, "rows_out": 148804, "cost": 4639844006, "uses_join_buffering": true } } }, { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 145274250 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 129949.3107, "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": 129949.3107, "uses_join_buffering": false } } } ] }, { "plan_prefix": "n1,LINEITEM,SUPPLIER", "table": "ORDERS", "rows_for_plan": 145274250, "cost_for_plan": 271461.3437, "pruned_by_cost": true, "current_cost": 271461.3437, "best_cost": 216264.3937 } ] }, { "plan_prefix": "n1,LINEITEM", "table": "n2", "rows_for_plan": 3631856250, "cost_for_plan": 582096.1926, "pruned_by_cost": true, "current_cost": 582096.1926, "best_cost": 216264.3937 }, { "plan_prefix": "n1,LINEITEM", "table": "CUSTOMER", "rows_for_plan": 2.161739e13, "cost_for_plan": 3323985537, "pruned_by_cost": true, "current_cost": 3323985537, "best_cost": 216264.3937 } ] } ] }, { "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": 9733, "rows_after_filter": 9733, "rows_out": 9733, "cost": 24.03845573, "cost_without_join_buffer": 35.85057922, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 9733, "rows_out": 9733, "cost": 24.03845573, "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": 6200, "cost": 22.8496958, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "CUSTOMER_NATION_FKIDX", "rows_read": 6200, "rows_out": 6200, "cost": 22.8496958, "uses_join_buffering": false } } }, { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 25 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 1490801, "rows_after_filter": 1490801, "rows_out": 1490801, "cost": 3681.048188, "cost_without_join_buffer": 5469.58699, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 1490801, "rows_out": 1490801, "cost": 3681.048188, "uses_join_buffering": true } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 25 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5810970, "rows_after_filter": 5810970, "rows_out": 5810970, "cost": 14483.48099, "cost_without_join_buffer": 23770.41729, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5810970, "rows_out": 5810970, "cost": 14483.48099, "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": 389, "cost": 36.18604422, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows_read": 389, "rows_out": 389, "cost": 36.18604422, "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": 6200, "cost": 569.0993678, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "CUSTOMER_NATION_FKIDX", "rows_read": 6200, "rows_out": 6200, "cost": 569.0993678, "uses_join_buffering": false } } }, { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 625 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 1490801, "rows_after_filter": 1490801, "rows_out": 1490801, "cost": 143459.8536, "cost_without_join_buffer": 136711.0486, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 1490801, "rows_out": 1490801, "cost": 143459.8536, "uses_join_buffering": true } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 625 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5810970, "rows_after_filter": 5810970, "rows_out": 5810970, "cost": 559325.004, "cost_without_join_buffer": 593219.8254, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5810970, "rows_out": 5810970, "cost": 559325.004, "uses_join_buffering": true } } } ] }, { "plan_prefix": "n2,n1", "table": "SUPPLIER", "rows_for_plan": 243125, "cost_for_plan": 36.27546672, "rest_of_plan": [ { "plan_prefix": "n2,n1,SUPPLIER", "get_costs_for_tables": [ { "best_access_path": { "table": "CUSTOMER", "plan_details": { "record_count": 243125 }, "considered_access_paths": [ { "access_type": "ref", "index": "CUSTOMER_NATION_FKIDX", "rows": 6200, "cost": 221345.0085, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "CUSTOMER_NATION_FKIDX", "rows_read": 6200, "rows_out": 6200, "cost": 221345.0085, "uses_join_buffering": false } } }, { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 243125 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 1490801, "rows_after_filter": 1490801, "rows_out": 1490801, "cost": 77795273.3, "cost_without_join_buffer": 53180135.13, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 1490801, "rows_out": 1490801, "cost": 77795273.3, "uses_join_buffering": true } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 243125 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5810970, "rows_after_filter": 5810970, "rows_out": 5810970, "cost": 303243154.5, "cost_without_join_buffer": 230745688.9, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5810970, "rows_out": 5810970, "cost": 303243154.5, "uses_join_buffering": true } } } ] }, { "plan_prefix": "n2,n1,SUPPLIER", "table": "CUSTOMER", "rows_for_plan": 1507375000, "cost_for_plan": 221381.2839, "pruned_by_cost": true, "current_cost": 221381.2839, "best_cost": 216264.3937 }, { "plan_prefix": "n2,n1,SUPPLIER", "table": "ORDERS", "rows_for_plan": 3.62451e11, "cost_for_plan": 77795309.58, "pruned_by_cost": true, "current_cost": 77795309.58, "best_cost": 216264.3937 }, { "plan_prefix": "n2,n1,SUPPLIER", "table": "LINEITEM", "rows_for_plan": 1.412792e12, "cost_for_plan": 303243190.8, "pruned_by_cost": true, "current_cost": 303243190.8, "best_cost": 216264.3937 } ] }, { "plan_prefix": "n2,n1", "table": "CUSTOMER", "rows_for_plan": 3875000, "cost_for_plan": 569.1887903, "rest_of_plan": [ { "plan_prefix": "n2,n1,CUSTOMER", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 3875000 }, "considered_access_paths": [ { "access_type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows": 389, "cost": 224312.8484, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows_read": 389, "rows_out": 389, "cost": 224312.8484, "uses_join_buffering": false } } }, { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 3875000 }, "considered_access_paths": [ { "access_type": "ref", "index": "ORDERS_FK1", "rows": 15, "cost": 11730.79838, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "ORDERS_FK1", "rows_read": 15, "rows_out": 15, "cost": 11730.79838, "uses_join_buffering": false } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 3875000 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5810970, "rows_after_filter": 5810970, "rows_out": 5810970, "cost": 4833180686, "cost_without_join_buffer": 3677694137, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5810970, "rows_out": 5810970, "cost": 4833180686, "uses_join_buffering": true } } } ] }, { "plan_prefix": "n2,n1,CUSTOMER", "table": "ORDERS", "rows_for_plan": 58125000, "cost_for_plan": 12299.98717, "rest_of_plan": [ { "plan_prefix": "n2,n1,CUSTOMER,ORDERS", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 58125000 }, "considered_access_paths": [ { "access_type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows": 389, "cost": 3364692.635, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows_read": 389, "rows_out": 389, "cost": 3364692.635, "uses_join_buffering": false } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 58125000 }, "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 3, "cost": 75057.73987, "chosen": true }, { "access_type": "ref", "index": "IDX_LINEITEM_ORDERKEY_FKIDX", "rows": 3, "cost": 221335.3066, "chosen": false, "cause": "cost" }, { "access_type": "scan_with_join_cache", "rows": 5810970, "rows_after_filter": 5810970, "rows_out": 3, "cost": 93058495504, "cost_without_join_buffer": 55165411451, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "index": "PRIMARY", "rows_read": 3, "rows_out": 3, "cost": 75057.73987, "uses_join_buffering": false } } } ] }, { "plan_prefix": "n2,n1,CUSTOMER,ORDERS", "table": "LINEITEM", "rows_for_plan": 174375000, "cost_for_plan": 87357.72704, "rest_of_plan": [ { "plan_prefix": "n2,n1,CUSTOMER,ORDERS,LINEITEM", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 174375000 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 155968.1069, "chosen": true }, { "access_type": "eq_ref", "index": "SUPPLIER_NATION_FKIDX", "rows": 1, "cost": 152474.3784, "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": 152474.3784, "uses_join_buffering": false } } } ] }, { "plan_prefix": "n2,n1,CUSTOMER,ORDERS,LINEITEM", "table": "SUPPLIER", "rows_for_plan": 174375000, "cost_for_plan": 239832.1055, "pruned_by_cost": true, "current_cost": 239832.1055, "best_cost": 216264.3937 } ] }, { "plan_prefix": "n2,n1,CUSTOMER,ORDERS", "table": "SUPPLIER", "rows_for_plan": 22610625000, "cost_for_plan": 3376992.622, "pruned_by_cost": true, "current_cost": 3376992.622, "best_cost": 216264.3937 } ] }, { "plan_prefix": "n2,n1,CUSTOMER", "table": "SUPPLIER", "rows_for_plan": 1507375000, "cost_for_plan": 224882.0372, "pruned_by_cost": true, "current_cost": 224882.0372, "best_cost": 216264.3937 }, { "plan_prefix": "n2,n1,CUSTOMER", "table": "LINEITEM", "rows_for_plan": 2.251751e13, "cost_for_plan": 4833181255, "pruned_by_cost": true, "current_cost": 4833181255, "best_cost": 216264.3937 } ] }, { "plan_prefix": "n2,n1", "table": "ORDERS", "rows_for_plan": 931750625, "cost_for_plan": 143459.9431, "rest_of_plan": [ { "plan_prefix": "n2,n1,ORDERS", "get_costs_for_tables": [ { "best_access_path": { "table": "CUSTOMER", "plan_details": { "record_count": 931750625 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 833396.5265, "chosen": true }, { "access_type": "eq_ref", "index": "CUSTOMER_NATION_FKIDX", "rows": 1, "cost": 814727.4945, "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": 814727.4945, "uses_join_buffering": false } } }, { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 931750625 }, "considered_access_paths": [ { "access_type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows": 389, "cost": 53936420.81, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows_read": 389, "rows_out": 389, "cost": 53936420.81, "uses_join_buffering": false } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 931750625 }, "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 3, "cost": 1202532.763, "chosen": true }, { "access_type": "ref", "index": "IDX_LINEITEM_ORDERKEY_FKIDX", "rows": 3, "cost": 3547263.006, "chosen": false, "cause": "cost" }, { "access_type": "scan_with_join_cache", "rows": 5810970, "rows_after_filter": 5810970, "rows_out": 3, "cost": 1.162157e12, "cost_without_join_buffer": 8.843081e11, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "index": "PRIMARY", "rows_read": 3, "rows_out": 3, "cost": 1202532.763, "uses_join_buffering": false } } } ] }, { "plan_prefix": "n2,n1,ORDERS", "table": "CUSTOMER", "rows_for_plan": 931750625, "cost_for_plan": 958187.4376, "pruned_by_cost": true, "current_cost": 958187.4376, "best_cost": 216264.3937 }, { "plan_prefix": "n2,n1,ORDERS", "table": "LINEITEM", "rows_for_plan": 2795251875, "cost_for_plan": 1345992.706, "pruned_by_cost": true, "current_cost": 1345992.706, "best_cost": 216264.3937 }, { "plan_prefix": "n2,n1,ORDERS", "table": "SUPPLIER", "rows_for_plan": 3.62451e11, "cost_for_plan": 54079880.75, "pruned_by_cost": true, "current_cost": 54079880.75, "best_cost": 216264.3937 } ] }, { "plan_prefix": "n2,n1", "table": "LINEITEM", "rows_for_plan": 3631856250, "cost_for_plan": 559325.0935, "pruned_by_cost": true, "current_cost": 559325.0935, "best_cost": 216264.3937 } ] }, { "plan_prefix": "n2", "table": "CUSTOMER", "rows_for_plan": 155000, "cost_for_plan": 22.8646256, "rest_of_plan": [ { "plan_prefix": "n2,CUSTOMER", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 155000 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 9733, "rows_after_filter": 9733, "rows_out": 9733, "cost": 231966.2522, "cost_without_join_buffer": 222232.9654, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 9733, "rows_out": 9733, "cost": 231966.2522, "uses_join_buffering": true } } }, { "best_access_path": { "table": "n1", "plan_details": { "record_count": 155000 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 25, "cost": 605.4852036, "cost_without_join_buffer": 2187.143819, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 25, "cost": 605.4852036, "uses_join_buffering": true } } }, { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 155000 }, "considered_access_paths": [ { "access_type": "ref", "index": "ORDERS_FK1", "rows": 15, "cost": 470.3769802, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "ORDERS_FK1", "rows_read": 15, "rows_out": 15, "cost": 470.3769802, "uses_join_buffering": false } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 155000 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5810970, "rows_after_filter": 5810970, "rows_out": 5810970, "cost": 138489297, "cost_without_join_buffer": 147107807.1, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5810970, "rows_out": 5810970, "cost": 138489297, "uses_join_buffering": true } } } ] }, { "plan_prefix": "n2,CUSTOMER", "table": "ORDERS", "rows_for_plan": 2325000, "cost_for_plan": 493.2416058, "rest_of_plan": [ { "plan_prefix": "n2,CUSTOMER,ORDERS", "get_costs_for_tables": [ { "best_access_path": { "table": "n1", "plan_details": { "record_count": 2325000 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 25, "cost": 12621.41512, "cost_without_join_buffer": 32807.14582, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 25, "cost": 12621.41512, "uses_join_buffering": true } } }, { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 2325000 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 9733, "rows_after_filter": 9733, "rows_out": 9733, "cost": 4856977.44, "cost_without_join_buffer": 3333494.39, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 9733, "rows_out": 9733, "cost": 4856977.44, "uses_join_buffering": true } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 2325000 }, "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 3, "cost": 3043.933868, "chosen": true }, { "access_type": "ref", "index": "IDX_LINEITEM_ORDERKEY_FKIDX", "rows": 3, "cost": 8902.474611, "chosen": false, "cause": "cost" }, { "access_type": "scan_with_join_cache", "rows": 5810970, "rows_after_filter": 5810970, "rows_out": 3, "cost": 2899757525, "cost_without_join_buffer": 2206616500, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "index": "PRIMARY", "rows_read": 3, "rows_out": 3, "cost": 3043.933868, "uses_join_buffering": false } } } ] }, { "plan_prefix": "n2,CUSTOMER,ORDERS", "table": "LINEITEM", "rows_for_plan": 6975000, "cost_for_plan": 3537.175473, "rest_of_plan": [ { "plan_prefix": "n2,CUSTOMER,ORDERS,LINEITEM", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 6975000 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 6238.850891, "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": 6238.850891, "uses_join_buffering": false } } }, { "best_access_path": { "table": "n1", "plan_details": { "record_count": 6975000 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 25, "cost": 48487.48879, "cost_without_join_buffer": 98421.43582, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 25, "cost": 48487.48879, "uses_join_buffering": true } } } ] }, { "plan_prefix": "n2,CUSTOMER,ORDERS,LINEITEM", "table": "SUPPLIER", "rows_for_plan": 6975000, "cost_for_plan": 9776.026365, "rest_of_plan": [ { "plan_prefix": "n2,CUSTOMER,ORDERS,LINEITEM,SUPPLIER", "get_costs_for_tables": [ { "best_access_path": { "table": "n1", "plan_details": { "record_count": 6975000 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 6238.719819, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 1, "cost": 59104.38408, "cost_without_join_buffer": 98421.43582, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "index": "PRIMARY", "rows_read": 1, "rows_out": 1, "cost": 6238.719819, "uses_join_buffering": false } } } ] }, { "plan_prefix": "n2,CUSTOMER,ORDERS,LINEITEM,SUPPLIER", "table": "n1", "rows_for_plan": 6975000, "cost_for_plan": 16014.74618 } ], "pruned_by_hanging_leaf": true } ] }, { "plan_prefix": "n2,CUSTOMER,ORDERS", "table": "n1", "rows_for_plan": 58125000, "cost_for_plan": 13114.65673, "pruned_by_heuristic": true }, { "plan_prefix": "n2,CUSTOMER,ORDERS", "table": "SUPPLIER", "rows_for_plan": 22629225000, "cost_for_plan": 4857470.682, "pruned_by_cost": true, "current_cost": 4857470.682, "best_cost": 16014.74618 } ] }, { "plan_prefix": "n2,CUSTOMER", "table": "n1", "rows_for_plan": 3875000, "cost_for_plan": 628.3498292, "rest_of_plan": [ { "plan_prefix": "n2,CUSTOMER,n1", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 3875000 }, "considered_access_paths": [ { "access_type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows": 389, "cost": 224312.8484, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows_read": 389, "rows_out": 389, "cost": 224312.8484, "uses_join_buffering": false } } }, { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 3875000 }, "considered_access_paths": [ { "access_type": "ref", "index": "ORDERS_FK1", "rows": 15, "cost": 11730.79838, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "ORDERS_FK1", "rows_read": 15, "rows_out": 15, "cost": 11730.79838, "uses_join_buffering": false } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 3875000 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5810970, "rows_after_filter": 5810970, "rows_out": 5810970, "cost": 4833180686, "cost_without_join_buffer": 3677694137, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5810970, "rows_out": 5810970, "cost": 4833180686, "uses_join_buffering": true } } } ] }, { "plan_prefix": "n2,CUSTOMER,n1", "table": "ORDERS", "rows_for_plan": 58125000, "cost_for_plan": 12359.14821, "rest_of_plan": [ { "plan_prefix": "n2,CUSTOMER,n1,ORDERS", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 58125000 }, "considered_access_paths": [ { "access_type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows": 389, "cost": 3364692.635, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows_read": 389, "rows_out": 389, "cost": 3364692.635, "uses_join_buffering": false } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 58125000 }, "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 3, "cost": 75057.73987, "chosen": true }, { "access_type": "ref", "index": "IDX_LINEITEM_ORDERKEY_FKIDX", "rows": 3, "cost": 221335.3066, "chosen": false, "cause": "cost" }, { "access_type": "scan_with_join_cache", "rows": 5810970, "rows_after_filter": 5810970, "rows_out": 3, "cost": 93058495504, "cost_without_join_buffer": 55165411451, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "index": "PRIMARY", "rows_read": 3, "rows_out": 3, "cost": 75057.73987, "uses_join_buffering": false } } } ] }, { "plan_prefix": "n2,CUSTOMER,n1,ORDERS", "table": "LINEITEM", "rows_for_plan": 174375000, "cost_for_plan": 87416.88808, "pruned_by_cost": true, "current_cost": 87416.88808, "best_cost": 16014.74618 }, { "plan_prefix": "n2,CUSTOMER,n1,ORDERS", "table": "SUPPLIER", "rows_for_plan": 22610625000, "cost_for_plan": 3377051.783, "pruned_by_cost": true, "current_cost": 3377051.783, "best_cost": 16014.74618 } ] }, { "plan_prefix": "n2,CUSTOMER,n1", "table": "SUPPLIER", "rows_for_plan": 1507375000, "cost_for_plan": 224941.1983, "pruned_by_cost": true, "current_cost": 224941.1983, "best_cost": 16014.74618 }, { "plan_prefix": "n2,CUSTOMER,n1", "table": "LINEITEM", "rows_for_plan": 2.251751e13, "cost_for_plan": 4833181314, "pruned_by_cost": true, "current_cost": 4833181314, "best_cost": 16014.74618 } ] }, { "plan_prefix": "n2,CUSTOMER", "table": "SUPPLIER", "rows_for_plan": 1508615000, "cost_for_plan": 231989.1169, "pruned_by_cost": true, "current_cost": 231989.1169, "best_cost": 16014.74618 }, { "plan_prefix": "n2,CUSTOMER", "table": "LINEITEM", "rows_for_plan": 9.007004e11, "cost_for_plan": 138489319.8, "pruned_by_cost": true, "current_cost": 138489319.8, "best_cost": 16014.74618 } ] }, { "plan_prefix": "n2", "table": "SUPPLIER", "rows_for_plan": 243325, "cost_for_plan": 24.05338553, "rest_of_plan": [ { "plan_prefix": "n2,SUPPLIER", "get_costs_for_tables": [ { "best_access_path": { "table": "n1", "plan_details": { "record_count": 243325 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 217.6404322, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 1, "cost": 950.5041034, "cost_without_join_buffer": 3433.462564, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "index": "PRIMARY", "rows_read": 1, "rows_out": 1, "cost": 217.6404322, "uses_join_buffering": false } } }, { "best_access_path": { "table": "CUSTOMER", "plan_details": { "record_count": 243325 }, "considered_access_paths": [ { "access_type": "ref", "index": "CUSTOMER_NATION_FKIDX", "rows": 6200, "cost": 221527.0917, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "CUSTOMER_NATION_FKIDX", "rows_read": 6200, "rows_out": 6200, "cost": 221527.0917, "uses_join_buffering": false } } }, { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 243325 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 1490801, "rows_after_filter": 1490801, "rows_out": 1490801, "cost": 55774262.07, "cost_without_join_buffer": 53223882.28, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 1490801, "rows_out": 1490801, "cost": 55774262.07, "uses_join_buffering": true } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 243325 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5810970, "rows_after_filter": 5810970, "rows_out": 5810970, "cost": 217405192.9, "cost_without_join_buffer": 230935505.4, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5810970, "rows_out": 5810970, "cost": 217405192.9, "uses_join_buffering": true } } } ] }, { "plan_prefix": "n2,SUPPLIER", "table": "n1", "rows_for_plan": 243325, "cost_for_plan": 241.6938177, "rest_of_plan": [ { "plan_prefix": "n2,SUPPLIER,n1", "get_costs_for_tables": [ { "best_access_path": { "table": "CUSTOMER", "plan_details": { "record_count": 243325 }, "considered_access_paths": [ { "access_type": "ref", "index": "CUSTOMER_NATION_FKIDX", "rows": 6200, "cost": 221527.0917, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "CUSTOMER_NATION_FKIDX", "rows_read": 6200, "rows_out": 6200, "cost": 221527.0917, "uses_join_buffering": false } } }, { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 243325 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 1490801, "rows_after_filter": 1490801, "rows_out": 1490801, "cost": 77859476.63, "cost_without_join_buffer": 53223882.28, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 1490801, "rows_out": 1490801, "cost": 77859476.63, "uses_join_buffering": true } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 243325 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5810970, "rows_after_filter": 5810970, "rows_out": 5810970, "cost": 303493508.1, "cost_without_join_buffer": 230935505.4, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5810970, "rows_out": 5810970, "cost": 303493508.1, "uses_join_buffering": true } } } ] }, { "plan_prefix": "n2,SUPPLIER,n1", "table": "CUSTOMER", "rows_for_plan": 1508615000, "cost_for_plan": 221768.7855, "pruned_by_cost": true, "current_cost": 221768.7855, "best_cost": 16014.74618 }, { "plan_prefix": "n2,SUPPLIER,n1", "table": "ORDERS", "rows_for_plan": 3.627492e11, "cost_for_plan": 77859718.33, "pruned_by_cost": true, "current_cost": 77859718.33, "best_cost": 16014.74618 }, { "plan_prefix": "n2,SUPPLIER,n1", "table": "LINEITEM", "rows_for_plan": 1.413954e12, "cost_for_plan": 303493749.8, "pruned_by_cost": true, "current_cost": 303493749.8, "best_cost": 16014.74618 } ] }, { "plan_prefix": "n2,SUPPLIER", "table": "CUSTOMER", "rows_for_plan": 1508615000, "cost_for_plan": 221551.1451, "pruned_by_cost": true, "current_cost": 221551.1451, "best_cost": 16014.74618 }, { "plan_prefix": "n2,SUPPLIER", "table": "ORDERS", "rows_for_plan": 3.627492e11, "cost_for_plan": 55774286.12, "pruned_by_cost": true, "current_cost": 55774286.12, "best_cost": 16014.74618 }, { "plan_prefix": "n2,SUPPLIER", "table": "LINEITEM", "rows_for_plan": 1.413954e12, "cost_for_plan": 217405216.9, "pruned_by_cost": true, "current_cost": 217405216.9, "best_cost": 16014.74618 } ] }, { "plan_prefix": "n2", "table": "ORDERS", "rows_for_plan": 37270025, "cost_for_plan": 3681.063118, "rest_of_plan": [ { "plan_prefix": "n2,ORDERS", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 37270025 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 9733, "rows_after_filter": 9733, "rows_out": 9733, "cost": 55776983.74, "cost_without_join_buffer": 53436309.25, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 9733, "rows_out": 9733, "cost": 55776983.74, "uses_join_buffering": true } } }, { "best_access_path": { "table": "CUSTOMER", "plan_details": { "record_count": 37270025 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 33337.29866, "chosen": true }, { "access_type": "eq_ref", "index": "CUSTOMER_NATION_FKIDX", "rows": 1, "cost": 32589.1855, "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": 32589.1855, "uses_join_buffering": false } } }, { "best_access_path": { "table": "n1", "plan_details": { "record_count": 37270025 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 25, "cost": 145592.6337, "cost_without_join_buffer": 525902.4156, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 25, "cost": 145592.6337, "uses_join_buffering": true } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 37270025 }, "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 3, "cost": 48142.93478, "chosen": true }, { "access_type": "ref", "index": "IDX_LINEITEM_ORDERKEY_FKIDX", "rows": 3, "cost": 141939.5826, "chosen": false, "cause": "cost" }, { "access_type": "scan_with_join_cache", "rows": 5810970, "rows_after_filter": 5810970, "rows_out": 3, "cost": 33300177697, "cost_without_join_buffer": 35372322836, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "index": "PRIMARY", "rows_read": 3, "rows_out": 3, "cost": 48142.93478, "uses_join_buffering": false } } } ] }, { "plan_prefix": "n2,ORDERS", "table": "CUSTOMER", "rows_for_plan": 37270025, "cost_for_plan": 36270.24862, "pruned_by_cost": true, "current_cost": 36270.24862, "best_cost": 16014.74618 }, { "plan_prefix": "n2,ORDERS", "table": "LINEITEM", "rows_for_plan": 111810075, "cost_for_plan": 51823.9979, "pruned_by_cost": true, "current_cost": 51823.9979, "best_cost": 16014.74618 }, { "plan_prefix": "n2,ORDERS", "table": "n1", "rows_for_plan": 931750625, "cost_for_plan": 149273.6969, "pruned_by_cost": true, "current_cost": 149273.6969, "best_cost": 16014.74618 }, { "plan_prefix": "n2,ORDERS", "table": "SUPPLIER", "rows_for_plan": 3.627492e11, "cost_for_plan": 55780664.81, "pruned_by_cost": true, "current_cost": 55780664.81, "best_cost": 16014.74618 } ] }, { "plan_prefix": "n2", "table": "LINEITEM", "rows_for_plan": 145274250, "cost_for_plan": 14483.49592, "rest_of_plan": [ { "plan_prefix": "n2,LINEITEM", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 145274250 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 129939.2321, "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": 129939.2321, "uses_join_buffering": false } } }, { "best_access_path": { "table": "CUSTOMER", "plan_details": { "record_count": 145274250 }, "considered_access_paths": [ { "access_type": "ref", "index": "CUSTOMER_NATION_FKIDX", "rows": 6200, "cost": 132260019.1, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "CUSTOMER_NATION_FKIDX", "rows_read": 6200, "rows_out": 6200, "cost": 132260019.1, "uses_join_buffering": false } } }, { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 145274250 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 129949.3107, "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": 129949.3107, "uses_join_buffering": false } } }, { "best_access_path": { "table": "n1", "plan_details": { "record_count": 145274250 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 25, "cost": 567612.6967, "cost_without_join_buffer": 2049906.833, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 25, "cost": 567612.6967, "uses_join_buffering": true } } } ] }, { "plan_prefix": "n2,LINEITEM", "table": "SUPPLIER", "rows_for_plan": 145274250, "cost_for_plan": 144422.728, "pruned_by_cost": true, "current_cost": 144422.728, "best_cost": 16014.74618 }, { "plan_prefix": "n2,LINEITEM", "table": "ORDERS", "rows_for_plan": 145274250, "cost_for_plan": 144432.8066, "pruned_by_cost": true, "current_cost": 144432.8066, "best_cost": 16014.74618 }, { "plan_prefix": "n2,LINEITEM", "table": "n1", "rows_for_plan": 3631856250, "cost_for_plan": 582096.1926, "pruned_by_cost": true, "current_cost": 582096.1926, "best_cost": 16014.74618 }, { "plan_prefix": "n2,LINEITEM", "table": "CUSTOMER", "rows_for_plan": 9.007004e11, "cost_for_plan": 132274502.6, "pruned_by_cost": true, "current_cost": 132274502.6, "best_cost": 16014.74618 } ] } ] }, { "plan_prefix": "", "table": "SUPPLIER", "rows_for_plan": 9733, "cost_for_plan": 1.440314625, "rest_of_plan": [ { "plan_prefix": "SUPPLIER", "get_costs_for_tables": [ { "best_access_path": { "table": "n2", "plan_details": { "record_count": 9733 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 25, "cost": 23.20395803, "cost_without_join_buffer": 137.339289, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 25, "cost": 23.20395803, "uses_join_buffering": true } } }, { "best_access_path": { "table": "n1", "plan_details": { "record_count": 9733 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 8.70640372, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 1, "cost": 23.20395803, "cost_without_join_buffer": 137.339289, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "index": "PRIMARY", "rows_read": 1, "rows_out": 1, "cost": 8.70640372, "uses_join_buffering": false } } }, { "best_access_path": { "table": "CUSTOMER", "plan_details": { "record_count": 9733 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 148804, "rows_after_filter": 148804, "rows_out": 148804, "cost": 134521.2131, "cost_without_join_buffer": 212539.4766, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 148804, "rows_out": 148804, "cost": 134521.2131, "uses_join_buffering": true } } }, { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 9733 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 1490801, "rows_after_filter": 1490801, "rows_out": 1490801, "cost": 1347703.036, "cost_without_join_buffer": 2128956.436, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 1490801, "rows_out": 1490801, "cost": 1347703.036, "uses_join_buffering": true } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 9733 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5810970, "rows_after_filter": 5810970, "rows_out": 5810970, "cost": 5253324.142, "cost_without_join_buffer": 9237461.84, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5810970, "rows_out": 5810970, "cost": 5253324.142, "uses_join_buffering": true } } } ] }, { "plan_prefix": "SUPPLIER", "table": "n1", "rows_for_plan": 9733, "cost_for_plan": 10.14671835, "rest_of_plan": [ { "plan_prefix": "SUPPLIER,n1", "get_costs_for_tables": [ { "best_access_path": { "table": "n2", "plan_details": { "record_count": 9733 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 25, "cost": 38.02828808, "cost_without_join_buffer": 137.339289, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 25, "cost": 38.02828808, "uses_join_buffering": true } } }, { "best_access_path": { "table": "CUSTOMER", "plan_details": { "record_count": 9733 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 148804, "rows_after_filter": 148804, "rows_out": 148804, "cost": 222695.8459, "cost_without_join_buffer": 212539.4766, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 148804, "rows_out": 148804, "cost": 222695.8459, "uses_join_buffering": true } } }, { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 9733 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 1490801, "rows_after_filter": 1490801, "rows_out": 1490801, "cost": 2231085.37, "cost_without_join_buffer": 2128956.436, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 1490801, "rows_out": 1490801, "cost": 2231085.37, "uses_join_buffering": true } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 9733 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5810970, "rows_after_filter": 5810970, "rows_out": 5810970, "cost": 8696742.861, "cost_without_join_buffer": 9237461.84, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5810970, "rows_out": 5810970, "cost": 8696742.861, "uses_join_buffering": true } } } ] }, { "plan_prefix": "SUPPLIER,n1", "table": "n2", "rows_for_plan": 243325, "cost_for_plan": 48.17500642, "rest_of_plan": [ { "plan_prefix": "SUPPLIER,n1,n2", "get_costs_for_tables": [ { "best_access_path": { "table": "CUSTOMER", "plan_details": { "record_count": 243325 }, "considered_access_paths": [ { "access_type": "ref", "index": "CUSTOMER_NATION_FKIDX", "rows": 6200, "cost": 221527.0917, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "CUSTOMER_NATION_FKIDX", "rows_read": 6200, "rows_out": 6200, "cost": 221527.0917, "uses_join_buffering": false } } }, { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 243325 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 1490801, "rows_after_filter": 1490801, "rows_out": 1490801, "cost": 77859476.63, "cost_without_join_buffer": 53223882.28, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 1490801, "rows_out": 1490801, "cost": 77859476.63, "uses_join_buffering": true } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 243325 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5810970, "rows_after_filter": 5810970, "rows_out": 5810970, "cost": 303493508.1, "cost_without_join_buffer": 230935505.4, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5810970, "rows_out": 5810970, "cost": 303493508.1, "uses_join_buffering": true } } } ] }, { "plan_prefix": "SUPPLIER,n1,n2", "table": "CUSTOMER", "rows_for_plan": 1508615000, "cost_for_plan": 221575.2667, "pruned_by_cost": true, "current_cost": 221575.2667, "best_cost": 16014.74618 }, { "plan_prefix": "SUPPLIER,n1,n2", "table": "ORDERS", "rows_for_plan": 3.627492e11, "cost_for_plan": 77859524.81, "pruned_by_cost": true, "current_cost": 77859524.81, "best_cost": 16014.74618 }, { "plan_prefix": "SUPPLIER,n1,n2", "table": "LINEITEM", "rows_for_plan": 1.413954e12, "cost_for_plan": 303493556.3, "pruned_by_cost": true, "current_cost": 303493556.3, "best_cost": 16014.74618 } ] }, { "plan_prefix": "SUPPLIER,n1", "table": "CUSTOMER", "rows_for_plan": 1448309332, "cost_for_plan": 222705.9926, "pruned_by_cost": true, "current_cost": 222705.9926, "best_cost": 16014.74618 }, { "plan_prefix": "SUPPLIER,n1", "table": "ORDERS", "rows_for_plan": 14509966133, "cost_for_plan": 2231095.517, "pruned_by_cost": true, "current_cost": 2231095.517, "best_cost": 16014.74618 }, { "plan_prefix": "SUPPLIER,n1", "table": "LINEITEM", "rows_for_plan": 56558171010, "cost_for_plan": 8696753.008, "pruned_by_cost": true, "current_cost": 8696753.008, "best_cost": 16014.74618 } ] }, { "plan_prefix": "SUPPLIER", "table": "n2", "rows_for_plan": 243325, "cost_for_plan": 24.64427265, "pruned_by_heuristic": true }, { "plan_prefix": "SUPPLIER", "table": "CUSTOMER", "rows_for_plan": 1448309332, "cost_for_plan": 134522.6534, "pruned_by_cost": true, "current_cost": 134522.6534, "best_cost": 16014.74618 }, { "plan_prefix": "SUPPLIER", "table": "ORDERS", "rows_for_plan": 14509966133, "cost_for_plan": 1347704.476, "pruned_by_cost": true, "current_cost": 1347704.476, "best_cost": 16014.74618 }, { "plan_prefix": "SUPPLIER", "table": "LINEITEM", "rows_for_plan": 56558171010, "cost_for_plan": 5253325.583, "pruned_by_cost": true, "current_cost": 5253325.583, "best_cost": 16014.74618 } ] }, { "plan_prefix": "", "table": "CUSTOMER", "rows_for_plan": 148804, "cost_for_plan": 21.92627906, "rest_of_plan": [ { "plan_prefix": "CUSTOMER", "get_costs_for_tables": [ { "best_access_path": { "table": "n2", "plan_details": { "record_count": 148804 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 133.097069, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 1, "cost": 354.6133937, "cost_without_join_buffer": 2099.714542, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "index": "PRIMARY", "rows_read": 1, "rows_out": 1, "cost": 133.097069, "uses_join_buffering": false } } }, { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 148804 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 9733, "rows_after_filter": 9733, "rows_out": 9733, "cost": 134516.3606, "cost_without_join_buffer": 213349.3821, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 9733, "rows_out": 9733, "cost": 134516.3606, "uses_join_buffering": true } } }, { "best_access_path": { "table": "n1", "plan_details": { "record_count": 148804 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 25, "cost": 354.6133937, "cost_without_join_buffer": 2099.714542, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 25, "cost": 354.6133937, "uses_join_buffering": true } } }, { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 148804 }, "considered_access_paths": [ { "access_type": "ref", "index": "ORDERS_FK1", "rows": 15, "cost": 451.6217192, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "ORDERS_FK1", "rows_read": 15, "rows_out": 15, "cost": 451.6217192, "uses_join_buffering": false } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 148804 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5810970, "rows_after_filter": 5810970, "rows_out": 5810970, "cost": 80306566.63, "cost_without_join_buffer": 141227292.9, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5810970, "rows_out": 5810970, "cost": 80306566.63, "uses_join_buffering": true } } } ] }, { "plan_prefix": "CUSTOMER", "table": "n2", "rows_for_plan": 148804, "cost_for_plan": 155.023348, "rest_of_plan": [ { "plan_prefix": "CUSTOMER,n2", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 148804 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 9733, "rows_after_filter": 9733, "rows_out": 9733, "cost": 222693.5304, "cost_without_join_buffer": 213349.3821, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 9733, "rows_out": 9733, "cost": 222693.5304, "uses_join_buffering": true } } }, { "best_access_path": { "table": "n1", "plan_details": { "record_count": 148804 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 25, "cost": 581.2808805, "cost_without_join_buffer": 2099.714542, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 25, "cost": 581.2808805, "uses_join_buffering": true } } }, { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 148804 }, "considered_access_paths": [ { "access_type": "ref", "index": "ORDERS_FK1", "rows": 15, "cost": 451.6217192, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "ORDERS_FK1", "rows_read": 15, "rows_out": 15, "cost": 451.6217192, "uses_join_buffering": false } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 148804 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5810970, "rows_after_filter": 5810970, "rows_out": 5810970, "cost": 132953262.2, "cost_without_join_buffer": 141227292.9, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5810970, "rows_out": 5810970, "cost": 132953262.2, "uses_join_buffering": true } } } ] }, { "plan_prefix": "CUSTOMER,n2", "table": "ORDERS", "rows_for_plan": 2232060, "cost_for_plan": 606.6450672, "rest_of_plan": [ { "plan_prefix": "CUSTOMER,n2,ORDERS", "get_costs_for_tables": [ { "best_access_path": { "table": "n1", "plan_details": { "record_count": 2232060 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 25, "cost": 12116.88579, "cost_without_join_buffer": 31495.70666, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 25, "cost": 12116.88579, "uses_join_buffering": true } } }, { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 2232060 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 9733, "rows_after_filter": 9733, "rows_out": 9733, "cost": 4662823.84, "cost_without_join_buffer": 3200240.64, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 9733, "rows_out": 9733, "cost": 4662823.84, "uses_join_buffering": true } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 2232060 }, "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 3, "cost": 2923.988292, "chosen": true }, { "access_type": "ref", "index": "IDX_LINEITEM_ORDERKEY_FKIDX", "rows": 3, "cost": 8548.648314, "chosen": false, "cause": "cost" }, { "access_type": "scan_with_join_cache", "rows": 5810970, "rows_after_filter": 5810970, "rows_out": 3, "cost": 2783842161, "cost_without_join_buffer": 2118408786, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "index": "PRIMARY", "rows_read": 3, "rows_out": 3, "cost": 2923.988292, "uses_join_buffering": false } } } ] }, { "plan_prefix": "CUSTOMER,n2,ORDERS", "table": "LINEITEM", "rows_for_plan": 6696180, "cost_for_plan": 3530.633359, "rest_of_plan": [ { "plan_prefix": "CUSTOMER,n2,ORDERS,LINEITEM", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 6696180 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 5989.46313, "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": 5989.46313, "uses_join_buffering": false } } }, { "best_access_path": { "table": "n1", "plan_details": { "record_count": 6696180 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 25, "cost": 46549.23925, "cost_without_join_buffer": 94487.11833, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 25, "cost": 46549.23925, "uses_join_buffering": true } } } ] }, { "plan_prefix": "CUSTOMER,n2,ORDERS,LINEITEM", "table": "SUPPLIER", "rows_for_plan": 6696180, "cost_for_plan": 9520.096489, "rest_of_plan": [ { "plan_prefix": "CUSTOMER,n2,ORDERS,LINEITEM,SUPPLIER", "get_costs_for_tables": [ { "best_access_path": { "table": "n1", "plan_details": { "record_count": 6696180 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 5989.332058, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 1, "cost": 56741.72698, "cost_without_join_buffer": 94487.11833, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "index": "PRIMARY", "rows_read": 1, "rows_out": 1, "cost": 5989.332058, "uses_join_buffering": false } } } ] }, { "plan_prefix": "CUSTOMER,n2,ORDERS,LINEITEM,SUPPLIER", "table": "n1", "rows_for_plan": 6696180, "cost_for_plan": 15509.42855 } ], "pruned_by_hanging_leaf": true } ] }, { "plan_prefix": "CUSTOMER,n2,ORDERS", "table": "n1", "rows_for_plan": 55801500, "cost_for_plan": 12723.53086, "pruned_by_heuristic": true }, { "plan_prefix": "CUSTOMER,n2,ORDERS", "table": "SUPPLIER", "rows_for_plan": 21724639980, "cost_for_plan": 4663430.485, "pruned_by_cost": true, "current_cost": 4663430.485, "best_cost": 15509.42855 } ] }, { "plan_prefix": "CUSTOMER,n2", "table": "n1", "rows_for_plan": 3720100, "cost_for_plan": 736.3042285, "rest_of_plan": [ { "plan_prefix": "CUSTOMER,n2,n1", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 3720100 }, "considered_access_paths": [ { "access_type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows": 389, "cost": 215346.1235, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows_read": 389, "rows_out": 389, "cost": 215346.1235, "uses_join_buffering": false } } }, { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 3720100 }, "considered_access_paths": [ { "access_type": "ref", "index": "ORDERS_FK1", "rows": 15, "cost": 11261.91685, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "ORDERS_FK1", "rows_read": 15, "rows_out": 15, "cost": 11261.91685, "uses_join_buffering": false } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 3720100 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 5810970, "rows_after_filter": 5810970, "rows_out": 5810970, "cost": 4639978922, "cost_without_join_buffer": 3530681282, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 5810970, "rows_out": 5810970, "cost": 4639978922, "uses_join_buffering": true } } } ] }, { "plan_prefix": "CUSTOMER,n2,n1", "table": "ORDERS", "rows_for_plan": 55801500, "cost_for_plan": 11998.22108, "rest_of_plan": [ { "plan_prefix": "CUSTOMER,n2,n1,ORDERS", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 55801500 }, "considered_access_paths": [ { "access_type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows": 389, "cost": 3230191.76, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "index": "SUPPLIER_NATION_FKIDX", "rows_read": 389, "rows_out": 389, "cost": 3230191.76, "uses_join_buffering": false } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 55801500 }, "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 3, "cost": 72059.10047, "chosen": true }, { "access_type": "ref", "index": "IDX_LINEITEM_ORDERKEY_FKIDX", "rows": 3, "cost": 212489.6492, "chosen": false, "cause": "cost" }, { "access_type": "scan_with_join_cache", "rows": 5810970, "rows_after_filter": 5810970, "rows_out": 3, "cost": 89338557173, "cost_without_join_buffer": 52960218618, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "index": "PRIMARY", "rows_read": 3, "rows_out": 3, "cost": 72059.10047, "uses_join_buffering": false } } } ] }, { "plan_prefix": "CUSTOMER,n2,n1,ORDERS", "table": "LINEITEM", "rows_for_plan": 167404500, "cost_for_plan": 84057.32156, "pruned_by_cost": true, "current_cost": 84057.32156, "best_cost": 15509.42855 }, { "plan_prefix": "CUSTOMER,n2,n1,ORDERS", "table": "SUPPLIER", "rows_for_plan": 21706783500, "cost_for_plan": 3242189.981, "pruned_by_cost": true, "current_cost": 3242189.981, "best_cost": 15509.42855 } ] }, { "plan_prefix": "CUSTOMER,n2,n1", "table": "SUPPLIER", "rows_for_plan": 1447118900, "cost_for_plan": 216082.4277, "pruned_by_cost": true, "current_cost": 216082.4277, "best_cost": 15509.42855 }, { "plan_prefix": "CUSTOMER,n2,n1", "table": "LINEITEM", "rows_for_plan": 2.161739e13, "cost_for_plan": 4639979658, "pruned_by_cost": true, "current_cost": 4639979658, "best_cost": 15509.42855 } ] }, { "plan_prefix": "CUSTOMER,n2", "table": "SUPPLIER", "rows_for_plan": 1448309332, "cost_for_plan": 222848.5537, "pruned_by_cost": true, "current_cost": 222848.5537, "best_cost": 15509.42855 }, { "plan_prefix": "CUSTOMER,n2", "table": "LINEITEM", "rows_for_plan": 8.646956e11, "cost_for_plan": 132953417.2, "pruned_by_cost": true, "current_cost": 132953417.2, "best_cost": 15509.42855 } ] }, { "plan_prefix": "CUSTOMER", "table": "ORDERS", "rows_for_plan": 2232060, "cost_for_plan": 473.5479982, "pruned_by_heuristic": true }, { "plan_prefix": "CUSTOMER", "table": "n1", "rows_for_plan": 3720100, "cost_for_plan": 376.5396727, "pruned_by_heuristic": true }, { "plan_prefix": "CUSTOMER", "table": "SUPPLIER", "rows_for_plan": 1448309332, "cost_for_plan": 134538.2869, "pruned_by_cost": true, "current_cost": 134538.2869, "best_cost": 15509.42855 }, { "plan_prefix": "CUSTOMER", "table": "LINEITEM", "rows_for_plan": 8.646956e11, "cost_for_plan": 80306588.56, "pruned_by_cost": true, "current_cost": 80306588.56, "best_cost": 15509.42855 } ] }, { "plan_prefix": "", "table": "ORDERS", "rows_for_plan": 1490801, "cost_for_plan": 219.9285246, "rest_of_plan": [ { "plan_prefix": "ORDERS", "get_costs_for_tables": [ { "best_access_path": { "table": "n2", "plan_details": { "record_count": 1490801 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 25, "cost": 3552.831686, "cost_without_join_buffer": 21036.09741, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 25, "cost": 3552.831686, "uses_join_buffering": true } } }, { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 1490801 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 9733, "rows_after_filter": 9733, "rows_out": 9733, "cost": 1347672.19, "cost_without_join_buffer": 2137452.376, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 9733, "rows_out": 9733, "cost": 1347672.19, "uses_join_buffering": true } } }, { "best_access_path": { "table": "CUSTOMER", "plan_details": { "record_count": 1490801 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 1334.929544, "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": 1334.929544, "uses_join_buffering": false } } }, { "best_access_path": { "table": "n1", "plan_details": { "record_count": 1490801 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 25, "cost": 3552.831686, "cost_without_join_buffer": 21036.09741, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 25, "cost": 3552.831686, "uses_join_buffering": true } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 1490801 }, "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 3, "cost": 1967.341664, "chosen": true }, { "access_type": "ref", "index": "IDX_LINEITEM_ORDERKEY_FKIDX", "rows": 3, "cost": 5726.64565, "chosen": false, "cause": "cost" }, { "access_type": "scan_with_join_cache", "rows": 5810970, "rows_after_filter": 5810970, "rows_out": 3, "cost": 804563768.3, "cost_without_join_buffer": 1414892955, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "index": "PRIMARY", "rows_read": 3, "rows_out": 3, "cost": 1967.341664, "uses_join_buffering": false } } } ] }, { "plan_prefix": "ORDERS", "table": "CUSTOMER", "rows_for_plan": 1490801, "cost_for_plan": 1554.858069, "rest_of_plan": [ { "plan_prefix": "ORDERS,CUSTOMER", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 1490801 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 9733, "rows_after_filter": 9733, "rows_out": 9733, "cost": 2230908.911, "cost_without_join_buffer": 2137452.376, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 9733, "rows_out": 9733, "cost": 2230908.911, "uses_join_buffering": true } } }, { "best_access_path": { "table": "n2", "plan_details": { "record_count": 1490801 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 1333.432866, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 1, "cost": 5822.028668, "cost_without_join_buffer": 21036.09741, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "index": "PRIMARY", "rows_read": 1, "rows_out": 1, "cost": 1333.432866, "uses_join_buffering": false } } } ] }, { "plan_prefix": "ORDERS,CUSTOMER", "table": "n2", "rows_for_plan": 1490801, "cost_for_plan": 2888.290934, "rest_of_plan": [ { "plan_prefix": "ORDERS,CUSTOMER,n2", "get_costs_for_tables": [ { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 1490801 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 9733, "rows_after_filter": 9733, "rows_out": 9733, "cost": 3114317.682, "cost_without_join_buffer": 2137452.376, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 9733, "rows_out": 9733, "cost": 3114317.682, "uses_join_buffering": true } } }, { "best_access_path": { "table": "n1", "plan_details": { "record_count": 1490801 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 25, "cost": 8092.918922, "cost_without_join_buffer": 21036.09741, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 25, "cost": 8092.918922, "uses_join_buffering": true } } }, { "best_access_path": { "table": "LINEITEM", "plan_details": { "record_count": 1490801 }, "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 3, "cost": 1967.341664, "chosen": true }, { "access_type": "ref", "index": "IDX_LINEITEM_ORDERKEY_FKIDX", "rows": 3, "cost": 5726.64565, "chosen": false, "cause": "cost" }, { "access_type": "scan_with_join_cache", "rows": 5810970, "rows_after_filter": 5810970, "rows_out": 3, "cost": 1859338767, "cost_without_join_buffer": 1414892955, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "index": "PRIMARY", "rows_read": 3, "rows_out": 3, "cost": 1967.341664, "uses_join_buffering": false } } } ] }, { "plan_prefix": "ORDERS,CUSTOMER,n2", "table": "LINEITEM", "rows_for_plan": 4472403, "cost_for_plan": 4855.632598, "rest_of_plan": [ { "plan_prefix": "ORDERS,CUSTOMER,n2,LINEITEM", "get_costs_for_tables": [ { "best_access_path": { "table": "n1", "plan_details": { "record_count": 4472403 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 25, "cost": 31090.40993, "cost_without_join_buffer": 63108.29059, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 25, "cost": 31090.40993, "uses_join_buffering": true } } }, { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 4472403 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 4000.428031, "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": 4000.428031, "uses_join_buffering": false } } } ] }, { "plan_prefix": "ORDERS,CUSTOMER,n2,LINEITEM", "table": "SUPPLIER", "rows_for_plan": 4472403, "cost_for_plan": 8856.060629, "rest_of_plan": [ { "plan_prefix": "ORDERS,CUSTOMER,n2,LINEITEM,SUPPLIER", "get_costs_for_tables": [ { "best_access_path": { "table": "n1", "plan_details": { "record_count": 4472403 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 4000.296959, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 1, "cost": 37898.01499, "cost_without_join_buffer": 63108.29059, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "index": "PRIMARY", "rows_read": 1, "rows_out": 1, "cost": 4000.296959, "uses_join_buffering": false } } } ] }, { "plan_prefix": "ORDERS,CUSTOMER,n2,LINEITEM,SUPPLIER", "table": "n1", "rows_for_plan": 4472403, "cost_for_plan": 12856.35759 } ], "pruned_by_hanging_leaf": true } ] }, { "plan_prefix": "ORDERS,CUSTOMER,n2", "table": "n1", "rows_for_plan": 37270025, "cost_for_plan": 10981.20986, "pruned_by_heuristic": true }, { "plan_prefix": "ORDERS,CUSTOMER,n2", "table": "SUPPLIER", "rows_for_plan": 14509966133, "cost_for_plan": 3117205.973, "pruned_by_cost": true, "current_cost": 3117205.973, "best_cost": 12856.35759 } ] } ] }, { "plan_prefix": "ORDERS", "table": "LINEITEM", "rows_for_plan": 4472403, "cost_for_plan": 2187.270189, "pruned_by_heuristic": true }, { "plan_prefix": "ORDERS", "table": "n1", "rows_for_plan": 37270025, "cost_for_plan": 3772.760211, "pruned_by_heuristic": true }, { "plan_prefix": "ORDERS", "table": "SUPPLIER", "rows_for_plan": 14509966133, "cost_for_plan": 1347892.119, "pruned_by_cost": true, "current_cost": 1347892.119, "best_cost": 12856.35759 } ] }, { "plan_prefix": "", "table": "LINEITEM", "rows_for_plan": 5810970, "cost_for_plan": 992.4409644, "rest_of_plan": [ { "plan_prefix": "LINEITEM", "get_costs_for_tables": [ { "best_access_path": { "table": "n2", "plan_details": { "record_count": 5810970 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 25, "cost": 13852.87164, "cost_without_join_buffer": 81996.2741, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 25, "cost": 13852.87164, "uses_join_buffering": true } } }, { "best_access_path": { "table": "SUPPLIER", "plan_details": { "record_count": 5810970 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 5197.695898, "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": 5197.695898, "uses_join_buffering": false } } }, { "best_access_path": { "table": "CUSTOMER", "plan_details": { "record_count": 5810970 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 148804, "rows_after_filter": 148804, "rows_out": 148804, "cost": 80313773.44, "cost_without_join_buffer": 126894072.1, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 148804, "rows_out": 148804, "cost": 80313773.44, "uses_join_buffering": true } } }, { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 5810970 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 5207.774516, "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": 5207.774516, "uses_join_buffering": false } } }, { "best_access_path": { "table": "n1", "plan_details": { "record_count": 5810970 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 25, "cost": 13852.87164, "cost_without_join_buffer": 81996.2741, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 25, "cost": 13852.87164, "uses_join_buffering": true } } } ] }, { "plan_prefix": "LINEITEM", "table": "SUPPLIER", "rows_for_plan": 5810970, "cost_for_plan": 6190.136862, "rest_of_plan": [ { "plan_prefix": "LINEITEM,SUPPLIER", "get_costs_for_tables": [ { "best_access_path": { "table": "n2", "plan_details": { "record_count": 5810970 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 25, "rows_after_filter": 25, "rows_out": 25, "cost": 22697.94215, "cost_without_join_buffer": 81996.2741, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 25, "rows_out": 25, "cost": 22697.94215, "uses_join_buffering": true } } }, { "best_access_path": { "table": "CUSTOMER", "plan_details": { "record_count": 5810970 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 148804, "rows_after_filter": 148804, "rows_out": 148804, "cost": 132948680.2, "cost_without_join_buffer": 126894072.1, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 148804, "rows_out": 148804, "cost": 132948680.2, "uses_join_buffering": true } } }, { "best_access_path": { "table": "ORDERS", "plan_details": { "record_count": 5810970 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 5207.774516, "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": 5207.774516, "uses_join_buffering": false } } } ] }, { "plan_prefix": "LINEITEM,SUPPLIER", "table": "ORDERS", "rows_for_plan": 5810970, "cost_for_plan": 11397.91138, "rest_of_plan": [ { "plan_prefix": "LINEITEM,SUPPLIER,ORDERS", "get_costs_for_tables": [ { "best_access_path": { "table": "CUSTOMER", "plan_details": { "record_count": 5810970 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 5199.061504, "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": 5199.061504, "uses_join_buffering": false } } } ] }, { "plan_prefix": "LINEITEM,SUPPLIER,ORDERS", "table": "CUSTOMER", "rows_for_plan": 5810970, "cost_for_plan": 16596.97288, "pruned_by_cost": true, "current_cost": 16596.97288, "best_cost": 12856.35759 } ] } ] }, { "plan_prefix": "LINEITEM", "table": "n1", "rows_for_plan": 145274250, "cost_for_plan": 14845.3126, "pruned_by_cost": true, "current_cost": 14845.3126, "best_cost": 12856.35759 }, { "plan_prefix": "LINEITEM", "table": "n2", "rows_for_plan": 145274250, "cost_for_plan": 14845.3126, "pruned_by_cost": true, "current_cost": 14845.3126, "best_cost": 12856.35759 } ] } ] }, { "best_join_order": [ "ORDERS", "CUSTOMER", "n2", "LINEITEM", "SUPPLIER", "n1" ], "rows": 4472403, "cost": 12856.35759 }, { "substitute_best_equal": { "condition": "WHERE", "resulting_condition": "SUPPLIER.S_SUPPKEY = LINEITEM.L_SUPPKEY and LINEITEM.L_ORDERKEY = ORDERS.O_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": "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": "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": "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" } } ] } } ] }