-------------- explain analyze select ref_0.ol_d_id AS c0, ref_0.ol_supply_w_id AS c1, ref_0.ol_amount AS c2, ref_0.ol_quantity AS c3, ref_0.ol_o_id AS c4, ref_0.ol_o_id AS c5 FROM order_line AS ref_0 WHERE (EXISTS ( SELECT ref_0.ol_amount AS c0, 38 AS c1, ref_1.w_id AS c2, (SELECT w_street_2 FROM warehouse LIMIT 1 OFFSET 4) AS c3, ref_1.w_street_2 AS c4, (SELECT o_entry_d FROM orders LIMIT 1 OFFSET 6) AS c5, ref_1.w_street_2 AS c6, ref_1.w_id AS c7, ref_1.w_street_2 AS c8, ref_0.ol_d_id AS c9 FROM warehouse AS ref_1 WHERE ((ref_1.w_state IS NOT NULL) AND (ref_1.w_zip IS NOT NULL)) OR (ref_1.w_state IS NOT NULL) )) AND (EXISTS ( SELECT ref_2.i_price AS c0, ref_2.i_price AS c1 FROM item AS ref_2 WHERE ref_0.ol_number IS NULL )) -------------- +-----------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------+ | -> Zero rows (Impossible WHERE) (actual time=0.000..0.000 rows=0 loops=1) | +-----------------------------------------------------------------------------+ 1 row in set, 3 warnings (0.00 sec) Note (Code 1276): Field or reference 'test.ref_0.ol_amount' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.ol_d_id' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.ol_number' of SELECT #5 was resolved in SELECT #1 -------------- explain analyze select subq_1.c0 as c0, subq_1.c2 as c1, subq_1.c2 as c2, subq_1.c0 as c3, subq_1.c2 as c4, (select o_id from orders limit 1 offset 3) as c5, 19 as c6, subq_1.c2 as c7, subq_1.c1 as c8, subq_1.c2 as c9 from (select distinct cast(nullif(subq_0.c3, subq_0.c1) as character(24)) as c0, subq_0.c3 as c1, subq_0.c4 as c2 from (select ref_0.s_dist_10 as c0, ref_0.s_dist_08 as c1, (select w_name from warehouse limit 1 offset 2) as c2, (select s_dist_10 from stock limit 1 offset 4) as c3, ref_0.s_dist_02 as c4, ref_0.s_dist_01 as c5, 91 as c6, ref_0.s_dist_07 as c7 from stock as ref_0 where ref_0.s_ytd is not NULL) as subq_0 where subq_0.c7 is not NULL) as subq_1 where subq_1.c1 is NULL -------------- +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Filter: (subq_1.c1 is null) (actual time=285.327..285.327 rows=0 loops=1) -> Index lookup on subq_1 using (c1=NULL) (actual time=0.005..0.005 rows=0 loops=1) -> Materialize (actual time=285.326..285.326 rows=0 loops=1) -> Table scan on (actual time=0.001..6.010 rows=100000 loops=1) -> Temporary table with deduplication (actual time=189.830..200.444 rows=100000 loops=1) -> Table scan on ref_0 (cost=10497.20 rows=97990) (actual time=0.496..83.715 rows=100000 loops=1) -> Select #6 (subquery in projection; run only once) -> Limit/Offset: 1/4 row(s) (actual time=0.091..0.091 rows=1 loops=1) -> Table scan on stock (cost=10497.20 rows=97990) (actual time=0.028..0.088 rows=5 loops=1) -> Select #6 (subquery in projection; run only once) -> Limit/Offset: 1/4 row(s) (actual time=0.091..0.091 rows=1 loops=1) -> Table scan on stock (cost=10497.20 rows=97990) (actual time=0.028..0.088 rows=5 loops=1) -> Select #2 (subquery in projection; run only once) -> Limit/Offset: 1/3 row(s) (never executed) -> Table scan on orders (cost=3049.45 rows=30092) (never executed) | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.30 sec) -------------- explain analyze select 98 as c0, ref_0.d_ytd as c1, ref_0.d_state as c2, ref_0.d_name as c3 from district as ref_0 where (ref_0.d_next_o_id is not NULL) and (EXISTS ( select ref_1.i_price as c0, 53 as c1, ref_0.d_tax as c2, ref_1.i_im_id as c3, cast(nullif(ref_0.d_zip, ref_0.d_zip) as character(9)) as c4, ref_0.d_name as c5, ref_1.i_data as c6, ref_2.o_ol_cnt as c7 from item as ref_1 left join orders as ref_2 on (ref_1.i_im_id = ref_2.o_w_id ) where EXISTS ( select ref_0.d_id as c0, ref_0.d_state as c1, ref_0.d_street_2 as c2 from new_order as ref_3 where (ref_1.i_price is not NULL) and (ref_1.i_name is NULL) ) )) -------------- +-----------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------+ | -> Zero rows (Impossible WHERE) (actual time=0.000..0.000 rows=0 loops=1) | +-----------------------------------------------------------------------------+ 1 row in set, 9 warnings (0.00 sec) Note (Code 1276): Field or reference 'test.ref_0.d_tax' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.d_zip' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.d_zip' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.d_name' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.d_id' of SELECT #3 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.d_state' of SELECT #3 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.d_street_2' of SELECT #3 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_1.i_price' of SELECT #3 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_1.i_name' of SELECT #3 was resolved in SELECT #2 -------------- explain analyze select ref_0.ol_quantity as c0 from order_line as ref_0 where EXISTS ( select ref_0.ol_d_id as c0, ref_0.ol_w_id as c1, ref_0.ol_delivery_d as c2, ref_1.no_o_id as c3, ref_1.no_d_id as c4, ref_0.ol_amount as c5, ref_1.no_w_id as c6, ref_1.no_o_id as c7, ref_0.ol_dist_info as c8 from new_order as ref_1 where ref_0.ol_d_id is not NULL) -------------- ^C^C -- sending "KILL QUERY 11" to server ... ^C -- query aborted +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Remove duplicate ref_0 rows using temporary table (weedout) (cost=238957090.71 rows=2389557268) (actual time=3.310..98854.567 rows=218527 loops=1) -> Inner hash join (no condition) (cost=238957090.71 rows=2389557268) (actual time=3.306..22155.962 rows=502173536 loops=1) -> Table scan on ref_0 (cost=30431.96 rows=298732) (actual time=0.025..402.070 rows=218527 loops=1) -> Hash -> Table scan on ref_1 (cost=805.15 rows=7999) (actual time=0.016..2.519 rows=8019 loops=1) | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set, 7 warnings (1 min 38.96 sec) Note (Code 1276): Field or reference 'test.ref_0.ol_d_id' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.ol_w_id' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.ol_delivery_d' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.ol_amount' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.ol_dist_info' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.ol_d_id' of SELECT #2 was resolved in SELECT #1 Warning (Code 1317): Query execution was interrupted -------------- explain analyze select ref_0.d_street_2 as c0 from district as ref_0 where EXISTS ( select ref_1.s_quantity as c0, ref_1.s_ytd as c1, ref_0.d_name as c2 from stock as ref_1 where EXISTS ( select ref_2.i_data as c0 from item as ref_2 left join order_line as ref_3 on (4 is not NULL) where ref_1.s_remote_cnt is NULL )) -------------- +-----------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------+ | -> Zero rows (Impossible WHERE) (actual time=0.000..0.000 rows=0 loops=1) | +-----------------------------------------------------------------------------+ 1 row in set, 2 warnings (0.00 sec) Note (Code 1276): Field or reference 'test.ref_0.d_name' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_1.s_remote_cnt' of SELECT #3 was resolved in SELECT #2 -------------- explain analyze select subq_0.c24 as c0, subq_0.c12 as c1, subq_0.c12 as c2 from (select distinct ref_0.c_credit as c0, ref_0.c_zip as c1, ref_0.c_d_id as c2, ref_0.c_state as c3, ref_0.c_delivery_cnt as c4, ref_0.c_credit_lim as c5, ref_0.c_last as c6, ref_0.c_data as c7, ref_0.c_data as c8, cast(nullif(ref_0.c_payment_cnt, ref_0.c_delivery_cnt) as SIGNED) as c9, ref_0.c_state as c10, ref_0.c_since as c11, ref_0.c_credit_lim as c12, 50 as c13, ref_0.c_credit as c14, ref_0.c_last as c15, ref_0.c_w_id as c16, ref_0.c_ytd_payment as c17, ref_0.c_middle as c18, 90 as c19, ref_0.c_city as c20, ref_0.c_zip as c21, ref_0.c_payment_cnt as c22, ref_0.c_credit as c23, ref_0.c_credit as c24 from customer as ref_0 where ref_0.c_city is not NULL ) as subq_0 where subq_0.c22 is not NULL -------------- +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Table scan on subq_0 (actual time=0.001..7.255 rows=30000 loops=1) -> Materialize (actual time=186.300..194.833 rows=30000 loops=1) -> Table scan on (actual time=0.002..6.737 rows=30000 loops=1) -> Temporary table with deduplication (actual time=131.999..140.018 rows=30000 loops=1) -> Table scan on ref_0 (cost=3154.45 rows=28577) (actual time=0.125..36.455 rows=30000 loops=1) | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.21 sec) -------------- explain analyze select distinct ref_0.s_i_id as c0, ref_0.s_i_id as c1, ref_0.s_data as c2, ref_0.s_dist_03 as c3 from stock as ref_0 where ref_0.s_dist_04 is not NULL -------------- +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Table scan on (actual time=0.001..5.455 rows=100000 loops=1) -> Temporary table with deduplication (actual time=112.518..121.898 rows=100000 loops=1) -> Table scan on ref_0 (cost=10497.20 rows=97990) (actual time=0.017..39.826 rows=100000 loops=1) | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.14 sec) -------------- explain analyze select (select s_dist_01 from stock limit 1 offset 8) as c0, ref_0.s_remote_cnt as c1, ref_0.s_quantity as c2, (select no_o_id from new_order limit 1 offset 6) as c3, cast(nullif(ref_0.s_dist_10, cast(nullif(ref_0.s_dist_07, ref_0.s_dist_08) as character(24))) as character(24)) as c4, ref_0.s_dist_03 as c5, ref_0.s_data as c6, (select s_order_cnt from stock limit 1 offset 4) as c7, ref_0.s_dist_03 as c8, ref_0.s_i_id as c9, ref_0.s_dist_08 as c10, ref_0.s_dist_03 as c11 from stock as ref_0 where EXISTS ( select ref_1.c_middle as c0, ref_1.c_balance as c1, 30 as c2, ref_0.s_dist_10 as c3, ref_1.c_balance as c4 from customer as ref_1 where ref_0.s_quantity is not NULL) -------------- ^C^C -- sending "KILL QUERY 11" to server ... ^C -- query aborted +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Remove duplicate ref_0 rows using temporary table (weedout) (cost=280030484.56 rows=2800260230) (actual time=13.500..4628.987 rows=10146 loops=1) -> Inner hash join (no condition) (cost=280030484.56 rows=2800260230) (actual time=13.495..1258.064 rows=23315062 loops=1) -> Table scan on ref_0 (cost=11106.11 rows=97990) (actual time=0.014..17.965 rows=10146 loops=1) -> Hash -> Table scan on ref_1 (cost=3154.45 rows=28577) (actual time=0.015..10.760 rows=30000 loops=1) -> Select #2 (subquery in projection; run only once) -> Limit/Offset: 1/8 row(s) (actual time=0.008..0.008 rows=1 loops=1) -> Table scan on stock (cost=10497.20 rows=97990) (actual time=0.003..0.007 rows=9 loops=1) -> Select #3 (subquery in projection; run only once) -> Limit/Offset: 1/6 row(s) (actual time=0.005..0.006 rows=1 loops=1) -> Table scan on new_order (cost=805.15 rows=7999) (actual time=0.003..0.005 rows=7 loops=1) -> Select #4 (subquery in projection; run only once) -> Limit/Offset: 1/4 row(s) (actual time=0.007..0.007 rows=1 loops=1) -> Table scan on stock (cost=10497.20 rows=97990) (actual time=0.006..0.007 rows=5 loops=1) | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set, 3 warnings (4.65 sec) Note (Code 1276): Field or reference 'test.ref_0.s_dist_10' of SELECT #5 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.s_quantity' of SELECT #5 was resolved in SELECT #1 Warning (Code 1317): Query execution was interrupted -------------- explain analyze select 14 as c0, subq_0.c4 as c1, ref_0.s_ytd as c2 from stock as ref_0 left join (select ref_1.s_dist_09 as c0, ref_1.s_remote_cnt as c1, ref_1.s_dist_06 as c2, ref_1.s_dist_09 as c3, ref_1.s_data as c4, ref_1.s_quantity as c5, ref_1.s_dist_04 as c6, ref_1.s_dist_02 as c7 from stock as ref_1 where ref_1.s_i_id is NULL) as subq_0 on (ref_0.s_dist_05 = subq_0.c0 ) where subq_0.c0 is not NULL -------------- +-----------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------+ | -> Zero rows (Impossible WHERE) (actual time=0.000..0.000 rows=0 loops=1) | +-----------------------------------------------------------------------------+ 1 row in set (0.00 sec) -------------- explain analyze select subq_0.c4 as c0, subq_0.c0 as c1, cast(nullif(subq_0.c4, subq_0.c4) as SIGNED) as c2, subq_0.c0 as c3, subq_0.c3 as c4 from (select ref_1.ol_number as c0, ref_1.ol_amount as c1, ref_1.ol_i_id as c2, ref_1.ol_number as c3, ref_0.d_id as c4 from district as ref_0 left join order_line as ref_1 on (ref_1.ol_amount is not NULL) where EXISTS ( select ref_1.ol_supply_w_id as c0, ref_1.ol_i_id as c1, ref_3.ol_amount as c2, ref_3.ol_number as c3 from district as ref_2 inner join order_line as ref_3 on (EXISTS ( select ref_4.ol_o_id as c0, ref_2.d_zip as c1, ref_3.ol_number as c2, ref_3.ol_w_id as c3, ref_2.d_zip as c4, 30 as c5, ref_0.d_tax as c6, ref_3.ol_w_id as c7, ref_1.ol_number as c8, ref_2.d_street_2 as c9 from order_line as ref_4 where 61 is NULL )) where ref_2.d_tax is NULL) ) as subq_0 where subq_0.c1 is not NULL -------------- +-----------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------+ | -> Zero rows (Impossible WHERE) (actual time=0.000..0.000 rows=0 loops=1) | +-----------------------------------------------------------------------------+ 1 row in set, 10 warnings (0.00 sec) Note (Code 1276): Field or reference 'test.ref_1.ol_supply_w_id' of SELECT #3 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_1.ol_i_id' of SELECT #3 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_2.d_zip' of SELECT #4 was resolved in SELECT #3 Note (Code 1276): Field or reference 'test.ref_3.ol_number' of SELECT #4 was resolved in SELECT #3 Note (Code 1276): Field or reference 'test.ref_3.ol_w_id' of SELECT #4 was resolved in SELECT #3 Note (Code 1276): Field or reference 'test.ref_2.d_zip' of SELECT #4 was resolved in SELECT #3 Note (Code 1276): Field or reference 'test.ref_0.d_tax' of SELECT #4 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_3.ol_w_id' of SELECT #4 was resolved in SELECT #3 Note (Code 1276): Field or reference 'test.ref_1.ol_number' of SELECT #4 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_2.d_street_2' of SELECT #4 was resolved in SELECT #3 -------------- explain analyze select ref_0.ol_i_id as c0, ref_1.d_state as c1, 91 as c2, ref_0.ol_o_id as c3, ref_1.d_city as c4, ref_0.ol_o_id as c5, ref_0.ol_number as c6, ref_0.ol_amount as c7 from order_line as ref_0 inner join district as ref_1 on (EXISTS ( select ref_2.h_amount as c0, ref_1.d_name as c1, ref_2.h_data as c2, ref_2.h_d_id as c3, ref_1.d_next_o_id as c4, ref_0.ol_delivery_d as c5, ref_1.d_street_1 as c6 from history as ref_2 where ref_0.ol_supply_w_id is NULL)) where (ref_1.d_city is not NULL) or (cast(coalesce(ref_0.ol_o_id, ref_0.ol_i_id) as SIGNED) is not NULL) -------------- +-----------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------+ | -> Zero rows (Impossible WHERE) (actual time=0.000..0.000 rows=0 loops=1) | +-----------------------------------------------------------------------------+ 1 row in set, 5 warnings (0.00 sec) Note (Code 1276): Field or reference 'test.ref_1.d_name' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_1.d_next_o_id' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.ol_delivery_d' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_1.d_street_1' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.ol_supply_w_id' of SELECT #2 was resolved in SELECT #1 -------------- explain analyze select distinct ref_0.s_data as c0 from stock as ref_0 where ref_0.s_dist_10 is not NULL -------------- +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Table scan on (actual time=0.001..4.151 rows=100000 loops=1) -> Temporary table with deduplication (actual time=98.174..106.173 rows=100000 loops=1) -> Table scan on ref_0 (cost=10497.20 rows=97990) (actual time=0.013..38.491 rows=100000 loops=1) | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.12 sec) -------------- explain analyze select distinct ref_0.i_id as c0, ref_0.i_name as c1, ref_0.i_name as c2, ref_0.i_price as c3, (select o_carrier_id from orders limit 1 offset 4) as c4, ref_0.i_im_id as c5 from item as ref_0 where ((select o_w_id from orders limit 1 offset 1) is not NULL) and (ref_0.i_id is not NULL) -------------- +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Table scan on (actual time=0.001..6.502 rows=100000 loops=1) -> Temporary table with deduplication (actual time=135.836..146.266 rows=100000 loops=1) -> Table scan on ref_0 (cost=10114.25 rows=99620) (actual time=0.067..47.406 rows=100000 loops=1) -> Select #2 (subquery in projection; run only once) -> Limit/Offset: 1/4 row(s) (actual time=0.005..0.005 rows=1 loops=1) -> Table scan on orders (cost=3049.45 rows=30092) (actual time=0.003..0.005 rows=5 loops=1) | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.17 sec) -------------- explain analyze select ref_0.ol_i_id as c0, ref_1.d_state as c1, 91 as c2, ref_0.ol_o_id as c3, ref_1.d_city as c4, ref_0.ol_o_id as c5, ref_0.ol_number as c6, ref_0.ol_amount as c7 from order_line as ref_0 inner join district as ref_1 on (EXISTS ( select ref_2.h_amount as c0, ref_1.d_name as c1, ref_2.h_data as c2, ref_2.h_d_id as c3, ref_1.d_next_o_id as c4, ref_0.ol_delivery_d as c5, ref_1.d_street_1 as c6 from history as ref_2 where ref_0.ol_supply_w_id is NULL)) where (ref_1.d_city is not NULL) or (cast(coalesce(ref_0.ol_o_id, ref_0.ol_i_id) as SIGNED) is not NULL) -------------- +-----------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------+ | -> Zero rows (Impossible WHERE) (actual time=0.000..0.000 rows=0 loops=1) | +-----------------------------------------------------------------------------+ 1 row in set, 5 warnings (0.00 sec) Note (Code 1276): Field or reference 'test.ref_1.d_name' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_1.d_next_o_id' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.ol_delivery_d' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_1.d_street_1' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.ol_supply_w_id' of SELECT #2 was resolved in SELECT #1 -------------- explain analyze select distinct ref_0.i_price as c0, ref_0.i_price as c1, ref_0.i_data as c2, ref_0.i_id as c3 from item as ref_0 where ref_0.i_data is not NULL -------------- +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Table scan on (actual time=0.001..5.540 rows=100000 loops=1) -> Temporary table with deduplication (actual time=117.805..127.231 rows=100000 loops=1) -> Table scan on ref_0 (cost=10114.25 rows=99620) (actual time=0.014..36.367 rows=100000 loops=1) | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.15 sec) -------------- explain analyze select subq_0.c0 as c0, 66 as c1, subq_0.c0 as c2, subq_0.c0 as c3, subq_0.c0 as c4, 52 as c5 from (select ref_0.c_data as c0 from customer as ref_0 where 92 is not NULL) as subq_0 where (EXISTS ( select ref_2.no_w_id as c0, ref_1.d_city as c1, ref_2.no_w_id as c2, subq_0.c0 as c3, 100 as c4 from district as ref_1 inner join new_order as ref_2 on (ref_1.d_id = ref_2.no_w_id ) where (subq_0.c0 is not NULL) or ((ref_1.d_w_id is not NULL) or (ref_1.d_state is not NULL)))) and (subq_0.c0 is not NULL) -------------- ^C^C -- sending "KILL QUERY 11" to server ... ^C -- query aborted +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Remove duplicate ref_0 rows using temporary table (weedout) (cost=22867361.82 rows=228587426) (actual time=5.206..4307.602 rows=11493 loops=1) -> Inner hash join (no condition) (cost=22867361.82 rows=228587426) (actual time=5.201..1288.201 rows=20801464 loops=1) -> Table scan on ref_0 (cost=3471.37 rows=28577) (actual time=0.006..13.545 rows=11493 loops=1) -> Hash -> Inner hash join (ref_2.no_w_id = ref_1.d_id) (cost=8005.51 rows=7999) (actual time=0.114..4.065 rows=8019 loops=1) -> Table scan on ref_2 (cost=8.52 rows=7999) (actual time=0.003..2.656 rows=8019 loops=1) -> Hash -> Table scan on ref_1 (cost=1.25 rows=10) (actual time=0.094..0.099 rows=10 loops=1) | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set, 3 warnings (4.31 sec) Note (Code 1276): Field or reference 'subq_0.c0' of SELECT #3 was resolved in SELECT #1 Note (Code 1276): Field or reference 'subq_0.c0' of SELECT #3 was resolved in SELECT #1 Warning (Code 1317): Query execution was interrupted -------------- explain analyze select distinct ref_0.i_id as c0, ref_0.i_im_id as c1, ref_0.i_price as c2, ref_0.i_data as c3, cast(coalesce(ref_0.i_im_id, ref_0.i_id) as SIGNED) as c4 from item as ref_0 where ref_0.i_price is not NULL -------------- +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Table scan on (actual time=0.001..6.489 rows=100000 loops=1) -> Temporary table with deduplication (actual time=143.912..154.615 rows=100000 loops=1) -> Table scan on ref_0 (cost=10114.25 rows=99620) (actual time=0.013..44.152 rows=100000 loops=1) | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.18 sec) -------------- explain analyze select ref_0.d_street_1 as c0, ref_0.d_street_1 as c1, ref_0.d_street_1 as c2 from district as ref_0 where EXISTS ( select subq_0.c0 as c0, subq_0.c0 as c1, ref_0.d_w_id as c2 from (select ref_1.s_dist_08 as c0 from stock as ref_1 where ref_1.s_dist_09 is not NULL) as subq_0 where EXISTS ( select ref_0.d_w_id as c0, subq_0.c0 as c1, ref_2.c_data as c2, subq_0.c0 as c3, ref_0.d_state as c4 from customer as ref_2 where ref_0.d_city is NULL ) ) -------------- +-----------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------+ | -> Zero rows (Impossible WHERE) (actual time=0.000..0.000 rows=0 loops=1) | +-----------------------------------------------------------------------------+ 1 row in set, 6 warnings (0.00 sec) Note (Code 1276): Field or reference 'test.ref_0.d_w_id' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.d_w_id' of SELECT #4 was resolved in SELECT #1 Note (Code 1276): Field or reference 'subq_0.c0' of SELECT #4 was resolved in SELECT #2 Note (Code 1276): Field or reference 'subq_0.c0' of SELECT #4 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_0.d_state' of SELECT #4 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.d_city' of SELECT #4 was resolved in SELECT #1 -------------- explain analyze select subq_0.c0 as c0, subq_0.c0 as c1, subq_0.c0 as c2, subq_0.c0 as c3, subq_0.c0 as c4, subq_0.c0 as c5 from (select ref_0.ol_number as c0 from order_line as ref_0 where EXISTS ( select ref_0.ol_w_id as c0, ref_0.ol_amount as c1, ref_1.h_c_id as c2, ref_1.h_date as c3, 4 as c4, ref_0.ol_i_id as c5, ref_0.ol_o_id as c6, ref_0.ol_supply_w_id as c7, 41 as c8, ref_0.ol_quantity as c9, ref_1.h_c_id as c10, ref_0.ol_i_id as c11, ref_0.ol_number as c12, 51 as c13 from history as ref_1 where 22 is NULL ) ) as subq_0 where EXISTS ( select ref_2.c_balance as c0, ref_2.c_discount as c1, subq_0.c0 as c2, ref_2.c_street_1 as c3, subq_0.c0 as c4, subq_0.c0 as c5, ref_2.c_d_id as c6, ref_2.c_street_1 as c7 from customer as ref_2 where ref_2.c_state is not NULL) -------------- +-----------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------+ | -> Zero rows (Impossible WHERE) (actual time=0.000..0.000 rows=0 loops=1) | +-----------------------------------------------------------------------------+ 1 row in set, 11 warnings (0.00 sec) Note (Code 1276): Field or reference 'test.ref_0.ol_w_id' of SELECT #3 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_0.ol_amount' of SELECT #3 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_0.ol_i_id' of SELECT #3 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_0.ol_o_id' of SELECT #3 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_0.ol_supply_w_id' of SELECT #3 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_0.ol_quantity' of SELECT #3 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_0.ol_i_id' of SELECT #3 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_0.ol_number' of SELECT #3 was resolved in SELECT #2 Note (Code 1276): Field or reference 'subq_0.c0' of SELECT #4 was resolved in SELECT #1 Note (Code 1276): Field or reference 'subq_0.c0' of SELECT #4 was resolved in SELECT #1 Note (Code 1276): Field or reference 'subq_0.c0' of SELECT #4 was resolved in SELECT #1 -------------- explain analyze select (select i_price from item limit 1 offset 1) as c0, subq_0.c6 as c1, subq_0.c4 as c2, subq_0.c1 as c3, subq_0.c2 as c4, subq_0.c4 as c5, subq_0.c2 as c6 from (select distinct ref_0.s_dist_02 as c0, ref_0.s_i_id as c1, ref_0.s_dist_05 as c2, ref_0.s_dist_07 as c3, ref_0.s_dist_04 as c4, (select o_d_id from orders limit 1 offset 4) as c5, ref_0.s_w_id as c6 from stock as ref_0 where ref_0.s_dist_08 is not NULL) as subq_0 where subq_0.c5 is NULL -------------- +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Filter: (subq_0.c5 is null) (actual time=256.411..256.411 rows=0 loops=1) -> Index lookup on subq_0 using (c5=NULL) (actual time=0.005..0.005 rows=0 loops=1) -> Materialize (actual time=256.410..256.410 rows=0 loops=1) -> Table scan on (actual time=0.001..5.120 rows=100000 loops=1) -> Temporary table with deduplication (actual time=148.221..158.042 rows=100000 loops=1) -> Table scan on ref_0 (cost=10497.20 rows=97990) (actual time=0.016..54.541 rows=100000 loops=1) -> Select #4 (subquery in projection; run only once) -> Limit/Offset: 1/4 row(s) (actual time=0.007..0.008 rows=1 loops=1) -> Table scan on orders (cost=3049.45 rows=30092) (actual time=0.004..0.007 rows=5 loops=1) -> Select #2 (subquery in projection; run only once) -> Limit/Offset: 1/1 row(s) (never executed) -> Table scan on item (cost=10114.25 rows=99620) (never executed) | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.27 sec) -------------- explain analyze select (select ol_quantity from order_line limit 1 offset 2) as c0, subq_0.c1 as c1, subq_0.c1 as c2, subq_0.c1 as c3, subq_0.c3 as c4, subq_0.c0 as c5 from (select distinct ref_0.s_dist_07 as c0, (select h_data from history limit 1 offset 1) as c1, cast(coalesce(ref_0.s_dist_06, ref_0.s_dist_08) as character(24)) as c2, ref_0.s_dist_02 as c3, ref_0.s_data as c4 from stock as ref_0 where ref_0.s_dist_02 is not NULL) as subq_0 where EXISTS ( select subq_0.c4 as c0, ref_1.d_id as c1, subq_0.c3 as c2 from district as ref_1 where subq_0.c1 is NULL ) -------------- +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Remove duplicate subq_0 rows using temporary table (weedout) (actual time=332.556..332.556 rows=0 loops=1) -> Nested loop inner join (actual time=332.554..332.554 rows=0 loops=1) -> Table scan on ref_1 (cost=1.25 rows=10) (actual time=0.033..0.045 rows=10 loops=1) -> Filter: (subq_0.c1 is null) (actual time=33.250..33.250 rows=0 loops=10) -> Index lookup on subq_0 using (c1=NULL) (actual time=0.001..0.001 rows=0 loops=10) -> Materialize (actual time=33.250..33.250 rows=0 loops=10) -> Table scan on (actual time=0.001..8.731 rows=100000 loops=1) -> Temporary table with deduplication (actual time=213.372..226.827 rows=100000 loops=1) -> Table scan on ref_0 (cost=10497.20 rows=97990) (actual time=0.007..60.894 rows=100000 loops=1) -> Select #4 (subquery in projection; run only once) -> Limit/Offset: 1/1 row(s) (actual time=0.125..0.125 rows=1 loops=1) -> Table scan on history (cost=2966.95 rows=29267) (actual time=0.122..0.124 rows=2 loops=1) -> Select #2 (subquery in projection; run only once) -> Limit/Offset: 1/2 row(s) (never executed) -> Table scan on order_line (cost=30282.20 rows=298732) (never executed) | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set, 3 warnings (0.35 sec) Note (Code 1276): Field or reference 'subq_0.c4' of SELECT #5 was resolved in SELECT #1 Note (Code 1276): Field or reference 'subq_0.c3' of SELECT #5 was resolved in SELECT #1 Note (Code 1276): Field or reference 'subq_0.c1' of SELECT #5 was resolved in SELECT #1 -------------- explain analyze select subq_0.c1 as c0 from (select distinct ref_0.s_data as c0, ref_0.s_order_cnt as c1, ref_0.s_dist_10 as c2, ref_0.s_dist_07 as c3 from stock as ref_0 where (ref_0.s_order_cnt is not NULL) and (32 is not NULL) ) as subq_0 where subq_0.c3 is not NULL -------------- +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Table scan on subq_0 (actual time=0.001..6.061 rows=100000 loops=1) -> Materialize (actual time=194.515..204.821 rows=100000 loops=1) -> Table scan on (actual time=0.002..6.646 rows=100000 loops=1) -> Temporary table with deduplication (actual time=160.487..171.311 rows=100000 loops=1) -> Table scan on ref_0 (cost=10497.20 rows=97990) (actual time=0.015..52.626 rows=100000 loops=1) | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.23 sec) -------------- explain analyze select distinct ref_0.s_dist_03 as c0, ref_0.s_data as c1 from stock as ref_0 where ref_0.s_remote_cnt is not NULL -------------- +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Table scan on (actual time=0.001..4.521 rows=100000 loops=1) -> Temporary table with deduplication (actual time=134.864..143.441 rows=100000 loops=1) -> Table scan on ref_0 (cost=10497.20 rows=97990) (actual time=0.018..48.051 rows=100000 loops=1) | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.15 sec) -------------- explain analyze select distinct 57 as c0, ref_0.i_id as c1, ref_0.i_data as c2, ref_0.i_im_id as c3, ref_0.i_data as c4, ref_0.i_im_id as c5, ref_0.i_name as c6 from item as ref_0 where ref_0.i_id is not NULL -------------- +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Table scan on (actual time=0.001..6.699 rows=100000 loops=1) -> Temporary table with deduplication (actual time=126.545..137.144 rows=100000 loops=1) -> Table scan on ref_0 (cost=10114.25 rows=99620) (actual time=0.029..39.012 rows=100000 loops=1) | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.16 sec) -------------- explain analyze select 97 as c0, subq_0.c1 as c1, subq_0.c1 as c2, subq_0.c1 as c3 from (select distinct ref_0.i_data as c0, ref_0.i_im_id as c1 from item as ref_0 where EXISTS ( select ref_0.i_price as c0, ref_1.s_dist_09 as c1 from stock as ref_1 where EXISTS ( select ref_0.i_im_id as c0, ref_1.s_quantity as c1, ref_0.i_name as c2, ref_1.s_dist_08 as c3, 86 as c4, ref_0.i_data as c5, ref_0.i_im_id as c6, 23 as c7, ref_0.i_price as c8, 69 as c9, ref_2.h_c_w_id as c10 from history as ref_2 where ref_2.h_date is not NULL)) ) as subq_0 where 25 is not NULL -------------- +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Table scan on subq_0 (actual time=0.001..4.699 rows=100000 loops=1) -> Materialize (actual time=136.425..145.127 rows=100000 loops=1) -> Table scan on (actual time=0.001..5.558 rows=100000 loops=1) -> Temporary table with deduplication (actual time=109.563..119.240 rows=100000 loops=1) -> Inner hash join (no condition) (actual time=0.028..49.629 rows=100000 loops=1) -> Table scan on ref_0 (cost=10114.28 rows=99620) (actual time=0.002..38.164 rows=100000 loops=1) -> Hash -> Limit: 1 row(s) (actual time=0.018..0.018 rows=1 loops=1) -> Nested loop inner join (cost=307224607.24 rows=2867873330) (actual time=0.018..0.018 rows=1 loops=1) -> Table scan on ref_2 (cost=2966.95 rows=29267) (actual time=0.013..0.013 rows=1 loops=1) -> Table scan on ref_1 (cost=2003317975037.96 rows=97990) (actual time=0.004..0.004 rows=1 loops=1) | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set, 8 warnings (0.16 sec) Note (Code 1276): Field or reference 'test.ref_0.i_price' of SELECT #3 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_0.i_im_id' of SELECT #4 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_1.s_quantity' of SELECT #4 was resolved in SELECT #3 Note (Code 1276): Field or reference 'test.ref_0.i_name' of SELECT #4 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_1.s_dist_08' of SELECT #4 was resolved in SELECT #3 Note (Code 1276): Field or reference 'test.ref_0.i_data' of SELECT #4 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_0.i_im_id' of SELECT #4 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_0.i_price' of SELECT #4 was resolved in SELECT #2 -------------- explain analyze select ref_0.d_w_id as c0 from district as ref_0 where EXISTS ( select ref_0.d_id as c0, ref_0.d_street_2 as c1, ref_1.s_dist_08 as c2, ref_1.s_dist_10 as c3, ref_0.d_city as c4 from stock as ref_1 where (ref_0.d_name is not NULL) and ((EXISTS ( select ref_1.s_dist_01 as c0, ref_1.s_dist_02 as c1 from item as ref_2 where ref_1.s_dist_06 is NULL )) and (ref_1.s_i_id is not NULL))) -------------- +-----------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------+ | -> Zero rows (Impossible WHERE) (actual time=0.000..0.000 rows=0 loops=1) | +-----------------------------------------------------------------------------+ 1 row in set, 7 warnings (0.00 sec) Note (Code 1276): Field or reference 'test.ref_0.d_id' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.d_street_2' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.d_city' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.d_name' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_1.s_dist_01' of SELECT #3 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_1.s_dist_02' of SELECT #3 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_1.s_dist_06' of SELECT #3 was resolved in SELECT #2 -------------- explain analyze select ref_0.d_street_1 as c0, ref_0.d_street_1 as c1, ref_0.d_street_1 as c2 from district as ref_0 where EXISTS ( select subq_0.c0 as c0, subq_0.c0 as c1, ref_0.d_w_id as c2 from (select ref_1.s_dist_08 as c0 from stock as ref_1 where ref_1.s_dist_09 is not NULL) as subq_0 where EXISTS ( select ref_0.d_w_id as c0, subq_0.c0 as c1, ref_2.c_data as c2, subq_0.c0 as c3, ref_0.d_state as c4 from customer as ref_2 where ref_0.d_city is NULL ) ) -------------- +-----------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------+ | -> Zero rows (Impossible WHERE) (actual time=0.000..0.000 rows=0 loops=1) | +-----------------------------------------------------------------------------+ 1 row in set, 6 warnings (0.01 sec) Note (Code 1276): Field or reference 'test.ref_0.d_w_id' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.d_w_id' of SELECT #4 was resolved in SELECT #1 Note (Code 1276): Field or reference 'subq_0.c0' of SELECT #4 was resolved in SELECT #2 Note (Code 1276): Field or reference 'subq_0.c0' of SELECT #4 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_0.d_state' of SELECT #4 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.d_city' of SELECT #4 was resolved in SELECT #1 -------------- explain analyze select distinct ref_0.i_im_id as c0, ref_0.i_data as c1, ref_0.i_im_id as c2, ref_0.i_name as c3, case when ref_0.i_id is not NULL then ref_0.i_name else ref_0.i_name end as c4 from item as ref_0 where ref_0.i_id is not NULL -------------- +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Table scan on (actual time=0.001..6.777 rows=100000 loops=1) -> Temporary table with deduplication (actual time=133.454..144.490 rows=100000 loops=1) -> Table scan on ref_0 (cost=10114.25 rows=99620) (actual time=0.014..40.944 rows=100000 loops=1) | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.16 sec) -------------- explain analyze select distinct ref_0.s_data as c0, ref_0.s_dist_05 as c1 from stock as ref_0 where ref_0.s_dist_07 is not NULL -------------- +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Table scan on (actual time=0.001..4.569 rows=100000 loops=1) -> Temporary table with deduplication (actual time=105.143..113.463 rows=100000 loops=1) -> Table scan on ref_0 (cost=10497.20 rows=97990) (actual time=0.014..38.735 rows=100000 loops=1) | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.13 sec) -------------- explain analyze select (select ol_quantity from order_line limit 1 offset 2) as c0, subq_0.c1 as c1, subq_0.c1 as c2, subq_0.c1 as c3, subq_0.c3 as c4, subq_0.c0 as c5 from (select distinct ref_0.s_dist_07 as c0, (select h_data from history limit 1 offset 1) as c1, cast(coalesce(ref_0.s_dist_06, ref_0.s_dist_08) as character(24)) as c2, ref_0.s_dist_02 as c3, ref_0.s_data as c4 from stock as ref_0 where ref_0.s_dist_02 is not NULL) as subq_0 where EXISTS ( select subq_0.c4 as c0, ref_1.d_id as c1, subq_0.c3 as c2 from district as ref_1 where subq_0.c1 is NULL ) -------------- +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Remove duplicate subq_0 rows using temporary table (weedout) (actual time=267.056..267.056 rows=0 loops=1) -> Nested loop inner join (actual time=267.055..267.055 rows=0 loops=1) -> Table scan on ref_1 (cost=1.25 rows=10) (actual time=0.012..0.022 rows=10 loops=1) -> Filter: (subq_0.c1 is null) (actual time=26.703..26.703 rows=0 loops=10) -> Index lookup on subq_0 using (c1=NULL) (actual time=0.001..0.001 rows=0 loops=10) -> Materialize (actual time=26.703..26.703 rows=0 loops=10) -> Table scan on (actual time=0.001..7.831 rows=100000 loops=1) -> Temporary table with deduplication (actual time=161.896..173.997 rows=100000 loops=1) -> Table scan on ref_0 (cost=10497.20 rows=97990) (actual time=0.005..47.500 rows=100000 loops=1) -> Select #4 (subquery in projection; run only once) -> Limit/Offset: 1/1 row(s) (actual time=0.004..0.004 rows=1 loops=1) -> Table scan on history (cost=2966.95 rows=29267) (actual time=0.002..0.003 rows=2 loops=1) -> Select #2 (subquery in projection; run only once) -> Limit/Offset: 1/2 row(s) (never executed) -> Table scan on order_line (cost=30282.20 rows=298732) (never executed) | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set, 3 warnings (0.28 sec) Note (Code 1276): Field or reference 'subq_0.c4' of SELECT #5 was resolved in SELECT #1 Note (Code 1276): Field or reference 'subq_0.c3' of SELECT #5 was resolved in SELECT #1 Note (Code 1276): Field or reference 'subq_0.c1' of SELECT #5 was resolved in SELECT #1 -------------- explain analyze select ref_0.ol_delivery_d as c0, ref_0.ol_dist_info as c1, ref_0.ol_dist_info as c2 from order_line as ref_0 where EXISTS ( select ref_1.ol_supply_w_id as c0, ref_1.ol_supply_w_id as c1, ref_0.ol_amount as c2, ref_0.ol_amount as c3, ref_1.ol_supply_w_id as c4 from order_line as ref_1 where ref_0.ol_supply_w_id is not NULL ) -------------- +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Hash semijoin (no condition) (actual time=0.026..132.271 rows=300148 loops=1) -> Table scan on ref_0 (cost=30282.20 rows=298732) (actual time=0.003..111.180 rows=300148 loops=1) -> Hash -> Limit: 1 row(s) (actual time=0.017..0.017 rows=1 loops=1) -> Table scan on ref_1 (cost=46595.18 rows=298732) (actual time=0.017..0.017 rows=1 loops=1) | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set, 3 warnings (0.18 sec) Note (Code 1276): Field or reference 'test.ref_0.ol_amount' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.ol_amount' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.ol_supply_w_id' of SELECT #2 was resolved in SELECT #1 -------------- explain analyze select distinct ref_0.i_data as c0, ref_0.i_price as c1, ref_0.i_price as c2, ref_0.i_price as c3, ref_0.i_price as c4 from item as ref_0 where EXISTS ( select ref_1.w_name as c0, ref_0.i_price as c1, ref_1.w_zip as c2, ref_1.w_city as c3, ref_0.i_id as c4, ref_1.w_street_1 as c5, ref_1.w_zip as c6, ref_1.w_zip as c7, ref_1.w_id as c8, ref_0.i_price as c9, ref_0.i_data as c10, (select w_id from warehouse limit 1 offset 2) as c11, 17 as c12 from warehouse as ref_1 where ref_1.w_id is not NULL ) -------------- +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Table scan on (actual time=0.001..6.267 rows=100000 loops=1) -> Temporary table with deduplication (actual time=143.248..153.520 rows=100000 loops=1) -> Inner hash join (no condition) (actual time=0.147..47.111 rows=100000 loops=1) -> Table scan on ref_0 (cost=10114.29 rows=99620) (actual time=0.005..36.475 rows=100000 loops=1) -> Hash -> Limit: 1 row(s) (actual time=0.133..0.133 rows=1 loops=1) -> Table scan on ref_1 (cost=0.35 rows=1) (actual time=0.133..0.133 rows=1 loops=1) | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set, 4 warnings (0.18 sec) Note (Code 1276): Field or reference 'test.ref_0.i_price' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.i_id' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.i_price' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.i_data' of SELECT #2 was resolved in SELECT #1 -------------- explain analyze select subq_0.c0 as c0, subq_0.c0 as c1 from (select distinct ref_0.i_name as c0, ref_0.i_name as c1 from item as ref_0 where (ref_0.i_data is not NULL) or (ref_0.i_id is not NULL) ) as subq_0 where subq_0.c1 is not NULL -------------- +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Table scan on subq_0 (actual time=0.000..4.269 rows=100000 loops=1) -> Materialize (actual time=122.678..130.833 rows=100000 loops=1) -> Table scan on (actual time=0.001..4.844 rows=100000 loops=1) -> Temporary table with deduplication (actual time=95.399..104.157 rows=100000 loops=1) -> Table scan on ref_0 (cost=10114.25 rows=99620) (actual time=0.015..36.210 rows=100000 loops=1) | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.15 sec) -------------- explain analyze select subq_1.c0 as c0, subq_1.c0 as c1, subq_1.c0 as c2, 16 as c3 from (select subq_0.c0 as c0 from stock as ref_0 inner join (select ref_1.s_ytd as c0, ref_1.s_dist_04 as c1 from stock as ref_1 where EXISTS ( select ref_1.s_dist_09 as c0 from stock as ref_2 where ref_1.s_dist_08 is NULL ) ) as subq_0 on (ref_0.s_dist_02 = subq_0.c1 ) where ref_0.s_ytd is not NULL ) as subq_1 where ((subq_1.c0 is not NULL) and (subq_1.c0 is not NULL)) and (subq_1.c0 is not NULL) -------------- +-----------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------+ | -> Zero rows (Impossible WHERE) (actual time=0.000..0.000 rows=0 loops=1) | +-----------------------------------------------------------------------------+ 1 row in set, 2 warnings (0.00 sec) Note (Code 1276): Field or reference 'test.ref_1.s_dist_09' of SELECT #4 was resolved in SELECT #3 Note (Code 1276): Field or reference 'test.ref_1.s_dist_08' of SELECT #4 was resolved in SELECT #3 -------------- explain analyze select distinct ref_0.i_price as c0, ref_0.i_name as c1, ref_0.i_data as c2, ref_0.i_id as c3 from item as ref_0 where ref_0.i_price is not NULL -------------- +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Table scan on (actual time=0.001..5.694 rows=100000 loops=1) -> Temporary table with deduplication (actual time=123.521..133.153 rows=100000 loops=1) -> Table scan on ref_0 (cost=10114.25 rows=99620) (actual time=0.014..39.578 rows=100000 loops=1) | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.15 sec) -------------- explain analyze select ref_0.ol_w_id as c0, ref_0.ol_i_id as c1, ref_0.ol_dist_info as c2 from order_line as ref_0 where EXISTS ( select 84 as c0, ref_0.ol_supply_w_id as c1, ref_1.i_price as c2, ref_1.i_name as c3, ref_1.i_name as c4 from item as ref_1 where ref_0.ol_amount is not NULL) -------------- ^C^C -- sending "KILL QUERY 11" to server ... ^C -- query aborted +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Remove duplicate ref_0 rows using temporary table (weedout) (cost=2975983059.24 rows=29759681840) (actual time=41.165..5706.230 rows=13901 loops=1) -> Inner hash join (no condition) (cost=2975983059.24 rows=29759681840) (actual time=41.160..1545.441 rows=29064902 loops=1) -> Table scan on ref_0 (cost=34634.19 rows=298732) (actual time=0.017..18.363 rows=13901 loops=1) -> Hash -> Table scan on ref_1 (cost=10114.25 rows=99620) (actual time=0.013..31.637 rows=100000 loops=1) | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set, 3 warnings (5.71 sec) Note (Code 1276): Field or reference 'test.ref_0.ol_supply_w_id' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.ol_amount' of SELECT #2 was resolved in SELECT #1 Warning (Code 1317): Query execution was interrupted -------------- explain analyze select ref_0.i_data as c0 from item as ref_0 inner join (select (select i_name from item limit 1 offset 1) as c0 from stock as ref_1 where ref_1.s_dist_06 is not NULL) as subq_0 on (ref_0.i_name = subq_0.c0 ) where (ref_0.i_im_id is not NULL) or (ref_0.i_im_id is not NULL) -------------- +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Inner hash join (no condition) (cost=97630574.56 rows=976176395) (actual time=41.996..83.581 rows=100000 loops=1) -> Table scan on ref_1 (cost=1.27 rows=97990) (actual time=0.009..33.103 rows=100000 loops=1) -> Hash -> Filter: (ref_0.i_name = (select #3)) (cost=10114.25 rows=9962) (actual time=0.023..41.980 rows=1 loops=1) -> Table scan on ref_0 (cost=10114.25 rows=99620) (actual time=0.012..35.490 rows=100000 loops=1) -> Select #3 (subquery in condition; run only once) -> Limit/Offset: 1/1 row(s) (actual time=0.006..0.006 rows=1 loops=1) -> Table scan on item (cost=10114.25 rows=99620) (actual time=0.004..0.006 rows=2 loops=1) | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.09 sec) -------------- explain analyze select subq_0.c0 as c0, subq_0.c2 as c1, subq_0.c0 as c2 from (select ref_0.w_state as c0, ref_0.w_ytd as c1, ref_0.w_city as c2, ref_0.w_tax as c3, ref_0.w_ytd as c4, case when (ref_0.w_city is not NULL) and (ref_0.w_street_1 is NULL) then ref_0.w_name else ref_0.w_name end as c5, ref_0.w_ytd as c6 from warehouse as ref_0 where (ref_0.w_city is not NULL) and (EXISTS ( select 16 as c0, ref_0.w_street_2 as c1, ref_1.d_state as c2, ref_1.d_zip as c3, ref_1.d_street_2 as c4, ref_1.d_id as c5, ref_2.ol_o_id as c6, ref_0.w_city as c7, ref_1.d_zip as c8, ref_1.d_ytd as c9, ref_0.w_street_2 as c10, ref_1.d_tax as c11, ref_1.d_w_id as c12, ref_1.d_street_1 as c13 from district as ref_1 inner join order_line as ref_2 on (EXISTS ( select ref_1.d_street_1 as c0, 36 as c1, ref_2.ol_w_id as c2, ref_3.h_c_d_id as c3, ref_1.d_ytd as c4, ref_2.ol_d_id as c5, ref_1.d_city as c6, ref_3.h_w_id as c7, ref_3.h_date as c8, 1 as c9, (select i_id from item limit 1 offset 48) as c10 from history as ref_3 where ref_1.d_zip is NULL)) where ref_2.ol_i_id is not NULL ))) as subq_0 where case when subq_0.c0 is NULL then subq_0.c6 else subq_0.c6 end is not NULL -------------- +-----------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------+ | -> Zero rows (Impossible WHERE) (actual time=0.000..0.000 rows=0 loops=1) | +-----------------------------------------------------------------------------+ 1 row in set, 9 warnings (0.00 sec) Note (Code 1276): Field or reference 'test.ref_0.w_street_2' of SELECT #3 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_0.w_city' of SELECT #3 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_0.w_street_2' of SELECT #3 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_1.d_street_1' of SELECT #4 was resolved in SELECT #3 Note (Code 1276): Field or reference 'test.ref_2.ol_w_id' of SELECT #4 was resolved in SELECT #3 Note (Code 1276): Field or reference 'test.ref_1.d_ytd' of SELECT #4 was resolved in SELECT #3 Note (Code 1276): Field or reference 'test.ref_2.ol_d_id' of SELECT #4 was resolved in SELECT #3 Note (Code 1276): Field or reference 'test.ref_1.d_city' of SELECT #4 was resolved in SELECT #3 Note (Code 1276): Field or reference 'test.ref_1.d_zip' of SELECT #4 was resolved in SELECT #3 -------------- explain analyze select ref_1.ol_number as c0, ref_1.ol_dist_info as c1, ref_0.c_last as c2 from customer as ref_0 inner join order_line as ref_1 on (((ref_0.c_delivery_cnt is NULL) and (ref_0.c_middle is NULL)) or (ref_1.ol_o_id is not NULL)) where cast(nullif((select i_im_id from item limit 1 offset 3) , ref_1.ol_d_id) as SIGNED) is NULL -------------- ^C^C -- sending "KILL QUERY 11" to server ... ^C -- query aborted +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Filter: (cast(nullif((select #2),ref_1.ol_d_id) as signed) is null) (cost=853691025.33 rows=8536864364) (actual time=5388.727..5388.727 rows=0 loops=1) -> Inner hash join (no condition) (cost=853691025.33 rows=8536864364) (actual time=14.395..2035.234 rows=41108544 loops=1) -> Table scan on ref_1 (cost=1.10 rows=298732) (actual time=0.016..27.091 rows=19264 loops=1) -> Hash -> Table scan on ref_0 (cost=3154.45 rows=28577) (actual time=0.012..11.377 rows=30000 loops=1) -> Select #2 (subquery in condition; run only once) -> Limit/Offset: 1/3 row(s) (actual time=0.007..0.007 rows=1 loops=1) -> Table scan on item (cost=10114.25 rows=99620) (actual time=0.004..0.006 rows=4 loops=1) | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set, 1 warning (5.39 sec) Warning (Code 1317): Query execution was interrupted -------------- explain analyze select subq_0.c0 as c0, subq_0.c2 as c1, subq_0.c0 as c2 from (select ref_0.w_state as c0, ref_0.w_ytd as c1, ref_0.w_city as c2, ref_0.w_tax as c3, ref_0.w_ytd as c4, case when (ref_0.w_city is not NULL) and (ref_0.w_street_1 is NULL) then ref_0.w_name else ref_0.w_name end as c5, ref_0.w_ytd as c6 from warehouse as ref_0 where (ref_0.w_city is not NULL) and (EXISTS ( select 16 as c0, ref_0.w_street_2 as c1, ref_1.d_state as c2, ref_1.d_zip as c3, ref_1.d_street_2 as c4, ref_1.d_id as c5, ref_2.ol_o_id as c6, ref_0.w_city as c7, ref_1.d_zip as c8, ref_1.d_ytd as c9, ref_0.w_street_2 as c10, ref_1.d_tax as c11, ref_1.d_w_id as c12, ref_1.d_street_1 as c13 from district as ref_1 inner join order_line as ref_2 on (EXISTS ( select ref_1.d_street_1 as c0, 36 as c1, ref_2.ol_w_id as c2, ref_3.h_c_d_id as c3, ref_1.d_ytd as c4, ref_2.ol_d_id as c5, ref_1.d_city as c6, ref_3.h_w_id as c7, ref_3.h_date as c8, 1 as c9, (select i_id from item limit 1 offset 48) as c10 from history as ref_3 where ref_1.d_zip is NULL)) where ref_2.ol_i_id is not NULL ))) as subq_0 where case when subq_0.c0 is NULL then subq_0.c6 else subq_0.c6 end is not NULL -------------- +-----------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------+ | -> Zero rows (Impossible WHERE) (actual time=0.000..0.000 rows=0 loops=1) | +-----------------------------------------------------------------------------+ 1 row in set, 9 warnings (0.00 sec) Note (Code 1276): Field or reference 'test.ref_0.w_street_2' of SELECT #3 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_0.w_city' of SELECT #3 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_0.w_street_2' of SELECT #3 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_1.d_street_1' of SELECT #4 was resolved in SELECT #3 Note (Code 1276): Field or reference 'test.ref_2.ol_w_id' of SELECT #4 was resolved in SELECT #3 Note (Code 1276): Field or reference 'test.ref_1.d_ytd' of SELECT #4 was resolved in SELECT #3 Note (Code 1276): Field or reference 'test.ref_2.ol_d_id' of SELECT #4 was resolved in SELECT #3 Note (Code 1276): Field or reference 'test.ref_1.d_city' of SELECT #4 was resolved in SELECT #3 Note (Code 1276): Field or reference 'test.ref_1.d_zip' of SELECT #4 was resolved in SELECT #3 -------------- explain analyze select subq_0.c3 as c0, subq_0.c2 as c1, subq_0.c8 as c2 from (select distinct ref_0.i_data as c0, ref_0.i_data as c1, ref_0.i_name as c2, ref_0.i_name as c3, ref_0.i_id as c4, ref_0.i_name as c5, 65 as c6, ref_0.i_data as c7, ref_0.i_data as c8, ref_0.i_im_id as c9, ref_0.i_im_id as c10, ref_0.i_id as c11 from item as ref_0 where ref_0.i_id is not NULL ) as subq_0 where (subq_0.c0 is not NULL) and ((select i_data from item limit 1 offset 2) is not NULL) -------------- +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Table scan on subq_0 (actual time=0.001..10.000 rows=100000 loops=1) -> Materialize (actual time=238.261..252.060 rows=100000 loops=1) -> Table scan on (actual time=0.001..10.301 rows=100000 loops=1) -> Temporary table with deduplication (actual time=161.373..175.766 rows=100000 loops=1) -> Table scan on ref_0 (cost=10114.25 rows=99620) (actual time=0.003..39.118 rows=100000 loops=1) | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.27 sec) -------------- explain analyze select distinct ref_0.s_data as c0, ref_0.s_dist_02 as c1, ref_0.s_data as c2 from stock as ref_0 where ref_0.s_w_id is not NULL -------------- +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Table scan on (actual time=0.001..5.474 rows=100000 loops=1) -> Temporary table with deduplication (actual time=114.826..124.257 rows=100000 loops=1) -> Table scan on ref_0 (cost=10497.20 rows=97990) (actual time=0.019..39.260 rows=100000 loops=1) | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.14 sec) -------------- explain analyze select subq_0.c1 as c0 from (select distinct ref_0.s_data as c0, ref_0.s_order_cnt as c1, ref_0.s_dist_10 as c2, ref_0.s_dist_07 as c3 from stock as ref_0 where (ref_0.s_order_cnt is not NULL) and (32 is not NULL) ) as subq_0 where subq_0.c3 is not NULL -------------- +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Table scan on subq_0 (actual time=0.001..5.645 rows=100000 loops=1) -> Materialize (actual time=151.930..161.376 rows=100000 loops=1) -> Table scan on (actual time=0.001..6.127 rows=100000 loops=1) -> Temporary table with deduplication (actual time=121.395..131.487 rows=100000 loops=1) -> Table scan on ref_0 (cost=10497.20 rows=97990) (actual time=0.014..42.656 rows=100000 loops=1) | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.18 sec) -------------- explain analyze select distinct ref_0.i_data as c0, ref_0.i_data as c1, ref_0.i_price as c2 from item as ref_0 where ref_0.i_id is not NULL -------------- +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Table scan on (actual time=0.001..5.773 rows=100000 loops=1) -> Temporary table with deduplication (actual time=107.954..117.859 rows=100000 loops=1) -> Table scan on ref_0 (cost=10114.25 rows=99620) (actual time=0.013..34.697 rows=100000 loops=1) | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.14 sec) -------------- explain analyze select subq_0.c2 as c0, 22 as c1, subq_0.c2 as c2, subq_0.c3 as c3, subq_0.c3 as c4, subq_0.c3 as c5, subq_0.c1 as c6, subq_0.c3 as c7, subq_0.c3 as c8, (select ol_number from order_line limit 1 offset 2) as c9, subq_0.c2 as c10 from (select ref_1.i_im_id as c0, ref_1.i_price as c1, ref_0.i_data as c2, ref_1.i_price as c3 from item as ref_0 left join item as ref_1 on (ref_0.i_name = ref_1.i_name ) where ref_1.i_data is not NULL ) as subq_0 where subq_0.c1 is NULL -------------- +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Inner hash join (ref_0.i_name = ref_1.i_name) (cost=89327978.83 rows=89317297) (actual time=38.889..38.889 rows=0 loops=1) -> Table scan on ref_0 (cost=0.17 rows=99620) (never executed) -> Hash -> Filter: ((ref_1.i_price is null) and (ref_1.i_data is not null)) (cost=10114.25 rows=8966) (actual time=38.883..38.883 rows=0 loops=1) -> Table scan on ref_1 (cost=10114.25 rows=99620) (actual time=0.014..34.704 rows=100000 loops=1) -> Select #2 (subquery in projection; run only once) -> Limit/Offset: 1/2 row(s) (never executed) -> Table scan on order_line (cost=30282.20 rows=298732) (never executed) | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.04 sec) -------------- explain analyze select ref_0.s_dist_02 as c0, ref_0.s_remote_cnt as c1, ref_0.s_remote_cnt as c2 from stock as ref_0 where EXISTS ( select ref_0.s_dist_07 as c0, ref_0.s_dist_10 as c1 from district as ref_1 inner join warehouse as ref_2 on (ref_1.d_tax = ref_2.w_tax ) where ref_2.w_ytd is not NULL) -------------- +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Inner hash join (no condition) (actual time=0.032..0.032 rows=0 loops=1) -> Table scan on ref_0 (cost=10497.23 rows=97990) (never executed) -> Hash -> Limit: 1 row(s) (actual time=0.028..0.028 rows=0 loops=1) -> Nested loop inner join (cost=1.60 rows=1) (actual time=0.028..0.028 rows=0 loops=1) -> Table scan on ref_2 (cost=0.35 rows=1) (actual time=0.017..0.018 rows=1 loops=1) -> Filter: (ref_1.d_tax = ref_2.w_tax) (cost=0.35 rows=1) (actual time=0.009..0.009 rows=0 loops=1) -> Table scan on ref_1 (cost=0.35 rows=10) (actual time=0.003..0.007 rows=10 loops=1) | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set, 2 warnings (0.00 sec) Note (Code 1276): Field or reference 'test.ref_0.s_dist_07' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.s_dist_10' of SELECT #2 was resolved in SELECT #1 -------------- explain analyze select distinct ref_0.i_data as c0, ref_0.i_name as c1, ref_0.i_name as c2 from item as ref_0 where ref_0.i_im_id is not NULL -------------- +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Table scan on (actual time=0.001..5.486 rows=100000 loops=1) -> Temporary table with deduplication (actual time=109.277..118.662 rows=100000 loops=1) -> Table scan on ref_0 (cost=10114.25 rows=99620) (actual time=0.009..35.916 rows=100000 loops=1) | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.13 sec) -------------- explain analyze select subq_0.c2 as c0, subq_0.c2 as c1, subq_0.c0 as c2 from (select distinct ref_0.i_id as c0, cast(nullif(ref_0.i_im_id, ref_0.i_im_id) as SIGNED) as c1, ref_0.i_name as c2, ref_0.i_data as c3 from item as ref_0 where ref_0.i_im_id is not NULL ) as subq_0 where subq_0.c2 is not NULL -------------- +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Table scan on subq_0 (actual time=0.001..5.606 rows=100000 loops=1) -> Materialize (actual time=160.044..169.521 rows=100000 loops=1) -> Table scan on (actual time=0.001..6.344 rows=100000 loops=1) -> Temporary table with deduplication (actual time=125.838..136.134 rows=100000 loops=1) -> Table scan on ref_0 (cost=10114.25 rows=99620) (actual time=0.014..42.120 rows=100000 loops=1) | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.19 sec) -------------- explain analyze select subq_0.c2 as c0, subq_0.c0 as c1, subq_0.c1 as c2, subq_0.c1 as c3, subq_0.c1 as c4 from (select (select d_street_2 from district limit 1 offset 5) as c0, ref_0.w_state as c1, ref_0.w_street_1 as c2, ref_0.w_zip as c3 from warehouse as ref_0 where EXISTS ( select ref_1.i_id as c0, ref_1.i_price as c1 from item as ref_1 where ref_0.w_city is not NULL ) ) as subq_0 where EXISTS ( select ref_2.o_ol_cnt as c0, ref_3.no_d_id as c1 from orders as ref_2 inner join new_order as ref_3 on (ref_2.o_c_id = ref_3.no_w_id ) where ref_2.o_d_id is not NULL) -------------- +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Nested loop semijoin (actual time=1.106..1.107 rows=1 loops=1) -> Hash semijoin (no condition) (actual time=0.023..0.025 rows=1 loops=1) -> Table scan on ref_0 (cost=0.35 rows=1) (actual time=0.004..0.005 rows=1 loops=1) -> Hash -> Limit: 1 row(s) (actual time=0.014..0.014 rows=1 loops=1) -> Table scan on ref_1 (cost=1007584651.48 rows=99620) (actual time=0.013..0.013 rows=1 loops=1) -> Nested loop inner join (cost=24393355.70 rows=24070591) (actual time=1.082..1.082 rows=1 loops=1) -> Table scan on ref_3 (cost=805.15 rows=7999) (actual time=0.002..0.002 rows=1 loops=1) -> Filter: (ref_2.o_c_id = ref_3.no_w_id) (cost=969746822.63 rows=3009) (actual time=1.079..1.079 rows=1 loops=1) -> Table scan on ref_2 (cost=969746822.63 rows=30092) (actual time=0.002..0.973 rows=2964 loops=1) -> Select #3 (subquery in projection; run only once) -> Limit/Offset: 1/5 row(s) (actual time=0.005..0.005 rows=1 loops=1) -> Table scan on district (cost=1.25 rows=10) (actual time=0.003..0.004 rows=6 loops=1) | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set, 1 warning (0.00 sec) Note (Code 1276): Field or reference 'test.ref_0.w_city' of SELECT #4 was resolved in SELECT #2 -------------- explain analyze select ref_0.d_zip as c0, ref_0.d_w_id as c1 from district as ref_0 where ((ref_0.d_next_o_id is not NULL) or (ref_0.d_tax is not NULL)) and (EXISTS ( select ref_1.ol_number as c0, (select s_dist_02 from stock limit 1 offset 2) as c1, ref_0.d_w_id as c2 from order_line as ref_1 where EXISTS ( select ref_2.w_zip as c0, ref_1.ol_w_id as c1, ref_1.ol_amount as c2, ref_0.d_city as c3, ref_0.d_street_1 as c4 from warehouse as ref_2 where ref_0.d_state is NULL))) -------------- +-----------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------+ | -> Zero rows (Impossible WHERE) (actual time=0.000..0.000 rows=0 loops=1) | +-----------------------------------------------------------------------------+ 1 row in set, 6 warnings (0.00 sec) Note (Code 1276): Field or reference 'test.ref_0.d_w_id' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_1.ol_w_id' of SELECT #4 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_1.ol_amount' of SELECT #4 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_0.d_city' of SELECT #4 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.d_street_1' of SELECT #4 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.d_state' of SELECT #4 was resolved in SELECT #1 -------------- explain analyze select subq_0.c1 as c0 from (select distinct ref_0.s_dist_07 as c0, ref_0.s_i_id as c1, ref_0.s_order_cnt as c2, ref_0.s_dist_04 as c3, ref_0.s_dist_09 as c4, ref_0.s_quantity as c5 from stock as ref_0 where ref_0.s_remote_cnt is not NULL ) as subq_0 where 37 is not NULL -------------- +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Table scan on subq_0 (actual time=0.001..3.255 rows=100000 loops=1) -> Materialize (actual time=160.560..167.775 rows=100000 loops=1) -> Table scan on (actual time=0.002..4.290 rows=100000 loops=1) -> Temporary table with deduplication (actual time=131.670..140.303 rows=100000 loops=1) -> Table scan on ref_0 (cost=10497.20 rows=97990) (actual time=0.009..52.733 rows=100000 loops=1) | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.18 sec) -------------- explain analyze select 25 as c0, subq_0.c13 as c1 from (select distinct ref_0.i_im_id as c0, ref_0.i_id as c1, 68 as c2, ref_0.i_im_id as c3, ref_0.i_data as c4, ref_0.i_im_id as c5, ref_0.i_im_id as c6, ref_0.i_im_id as c7, ref_0.i_im_id as c8, ref_0.i_name as c9, ref_0.i_price as c10, ref_0.i_price as c11, ref_0.i_price as c12, ref_0.i_price as c13, ref_0.i_im_id as c14, ref_0.i_price as c15, ref_0.i_id as c16 from item as ref_0 where ref_0.i_im_id is not NULL ) as subq_0 where subq_0.c0 is not NULL -------------- +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Table scan on subq_0 (actual time=0.001..13.511 rows=100000 loops=1) -> Materialize (actual time=344.859..362.593 rows=100000 loops=1) -> Table scan on (actual time=0.002..14.726 rows=100000 loops=1) -> Temporary table with deduplication (actual time=229.416..248.884 rows=100000 loops=1) -> Table scan on ref_0 (cost=10114.25 rows=99620) (actual time=0.015..52.949 rows=100000 loops=1) | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.39 sec) -------------- explain analyze select (select c_payment_cnt from customer limit 1 offset 48) as c0, ref_1.d_state as c1, ref_0.ol_w_id as c2 from order_line as ref_0 inner join district as ref_1 on (EXISTS ( select ref_0.ol_number as c0 from customer as ref_2 where ref_1.d_next_o_id is NULL )) where ref_0.ol_amount is not NULL -------------- +-----------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------+ | -> Zero rows (Impossible WHERE) (actual time=0.000..0.000 rows=0 loops=1) | +-----------------------------------------------------------------------------+ 1 row in set, 2 warnings (0.00 sec) Note (Code 1276): Field or reference 'test.ref_0.ol_number' of SELECT #3 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_1.d_next_o_id' of SELECT #3 was resolved in SELECT #1 -------------- explain analyze select cast(coalesce(ref_0.ol_number, ref_0.ol_o_id) as SIGNED) as c0, ref_0.ol_delivery_d as c1, ref_1.d_street_1 as c2, ref_0.ol_dist_info as c3, ref_0.ol_i_id as c4, ref_0.ol_supply_w_id as c5, ref_0.ol_i_id as c6, ref_1.d_id as c7 from order_line as ref_0 left join district as ref_1 on (ref_0.ol_w_id = ref_1.d_w_id ) where EXISTS ( select ref_0.ol_number as c0, ref_1.d_w_id as c1, ref_1.d_name as c2, ref_1.d_w_id as c3, ref_0.ol_d_id as c4 from history as ref_2 where (ref_2.h_w_id is not NULL) and (ref_1.d_name is NULL) ) -------------- +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Hash semijoin (no condition) (actual time=195.073..195.073 rows=0 loops=1) -> Filter: (ref_1.d_name is null) (cost=298801.30 rows=298732) (actual time=195.052..195.052 rows=0 loops=1) -> Hash antijoin (ref_1.d_w_id = ref_0.ol_w_id) (cost=298801.30 rows=298732) (actual time=195.051..195.051 rows=0 loops=1) -> Table scan on ref_0 (cost=30282.20 rows=298732) (actual time=0.004..156.833 rows=300148 loops=1) -> Hash -> Table scan on ref_1 (cost=0.00 rows=10) (actual time=0.003..0.008 rows=10 loops=1) -> Hash -> Limit: 1 row(s) (actual time=0.014..0.014 rows=1 loops=1) -> Table scan on ref_2 (cost=708.59 rows=29267) (actual time=0.013..0.013 rows=1 loops=1) | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set, 6 warnings (0.19 sec) Note (Code 1276): Field or reference 'test.ref_0.ol_number' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_1.d_w_id' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_1.d_name' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_1.d_w_id' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.ol_d_id' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_1.d_name' of SELECT #2 was resolved in SELECT #1 -------------- explain analyze select ref_0.ol_amount as c0, ref_0.ol_w_id as c1, ref_0.ol_w_id as c2, ref_0.ol_w_id as c3, ref_0.ol_o_id as c4, ref_0.ol_dist_info as c5, ref_0.ol_amount as c6 from order_line as ref_0 where (EXISTS ( select ref_0.ol_w_id as c0, 50 as c1, ref_1.s_w_id as c2, ref_0.ol_o_id as c3, ref_1.s_remote_cnt as c4, ref_1.s_dist_08 as c5, ref_1.s_dist_04 as c6 from stock as ref_1 where ref_0.ol_delivery_d is not NULL)) and (ref_0.ol_supply_w_id is not NULL) -------------- ^C^C -- sending "KILL QUERY 11" to server ... ^C -- query aborted +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Remove duplicate ref_0 rows using temporary table (weedout) (cost=2927294335.77 rows=29272748680) (actual time=62.917..5183.845 rows=14725 loops=1) -> Inner hash join (no condition) (cost=2927294335.77 rows=29272748680) (actual time=62.911..1523.122 rows=25634668 loops=1) -> Table scan on ref_0 (cost=38843.77 rows=298732) (actual time=0.017..15.745 rows=14725 loops=1) -> Hash -> Table scan on ref_1 (cost=10497.20 rows=97990) (actual time=0.015..45.973 rows=100000 loops=1) | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set, 4 warnings (5.20 sec) Note (Code 1276): Field or reference 'test.ref_0.ol_w_id' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.ol_o_id' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.ol_delivery_d' of SELECT #2 was resolved in SELECT #1 Warning (Code 1317): Query execution was interrupted -------------- explain analyze select ref_0.s_dist_09 as c0, ref_0.s_order_cnt as c1 from stock as ref_0 where (EXISTS ( select ref_0.s_dist_05 as c0, ref_0.s_dist_06 as c1, ref_1.d_city as c2, ref_0.s_dist_04 as c3, ref_0.s_dist_01 as c4, ref_1.d_ytd as c5, ref_1.d_tax as c6, ref_1.d_street_2 as c7, ref_0.s_dist_06 as c8, ref_1.d_state as c9 from district as ref_1 where cast(nullif(ref_1.d_w_id, ref_1.d_next_o_id) as SIGNED) is NULL )) and (ref_0.s_dist_02 is not NULL) -------------- +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Inner hash join (no condition) (actual time=0.024..0.024 rows=0 loops=1) -> Table scan on ref_0 (cost=10497.36 rows=97990) (never executed) -> Hash -> Limit: 1 row(s) (actual time=0.020..0.020 rows=0 loops=1) -> Filter: (cast(nullif(ref_1.d_w_id,ref_1.d_next_o_id) as signed) is null) (cost=12.50 rows=10) (actual time=0.019..0.019 rows=0 loops=1) -> Table scan on ref_1 (cost=12.50 rows=10) (actual time=0.012..0.017 rows=10 loops=1) | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set, 5 warnings (0.00 sec) Note (Code 1276): Field or reference 'test.ref_0.s_dist_05' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.s_dist_06' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.s_dist_04' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.s_dist_01' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.s_dist_06' of SELECT #2 was resolved in SELECT #1 -------------- explain analyze select distinct subq_0.c1 as c0, subq_0.c0 as c1, subq_0.c1 as c2, 60 as c3, subq_0.c0 as c4, subq_0.c0 as c5, subq_0.c0 as c6 from (select ref_0.i_name as c0, ref_0.i_im_id as c1 from item as ref_0 where ref_0.i_name is not NULL) as subq_0 where cast(coalesce(subq_0.c1, subq_0.c1) as SIGNED) is not NULL -------------- +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Table scan on (actual time=0.001..7.252 rows=100000 loops=1) -> Temporary table with deduplication (actual time=127.541..139.136 rows=100000 loops=1) -> Table scan on ref_0 (cost=10114.25 rows=99620) (actual time=0.007..36.996 rows=100000 loops=1) | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.16 sec) -------------- explain analyze select subq_0.c0 as c0, subq_0.c0 as c1 from (select ref_0.s_dist_10 as c0 from stock as ref_0 where ref_0.s_remote_cnt is not NULL) as subq_0 where EXISTS ( select subq_0.c0 as c0, ref_1.d_w_id as c1, subq_0.c0 as c2, subq_0.c0 as c3, ref_1.d_id as c4, ref_1.d_zip as c5, subq_0.c0 as c6, subq_0.c0 as c7, subq_0.c0 as c8, 99 as c9, 18 as c10, ref_1.d_state as c11 from district as ref_1 where EXISTS ( select ref_2.w_id as c0, subq_0.c0 as c1, ref_1.d_ytd as c2, 26 as c3, ref_1.d_name as c4, ref_1.d_state as c5, ref_1.d_id as c6, subq_0.c0 as c7, ref_2.w_id as c8, ref_2.w_street_2 as c9, ref_2.w_tax as c10, subq_0.c0 as c11, ref_1.d_tax as c12, ref_2.w_street_2 as c13, ref_1.d_street_1 as c14 from warehouse as ref_2 where 39 is NULL ) ) -------------- +-----------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------+ | -> Zero rows (Impossible WHERE) (actual time=0.000..0.000 rows=0 loops=1) | +-----------------------------------------------------------------------------+ 1 row in set, 15 warnings (0.00 sec) Note (Code 1276): Field or reference 'subq_0.c0' of SELECT #3 was resolved in SELECT #1 Note (Code 1276): Field or reference 'subq_0.c0' of SELECT #3 was resolved in SELECT #1 Note (Code 1276): Field or reference 'subq_0.c0' of SELECT #3 was resolved in SELECT #1 Note (Code 1276): Field or reference 'subq_0.c0' of SELECT #3 was resolved in SELECT #1 Note (Code 1276): Field or reference 'subq_0.c0' of SELECT #3 was resolved in SELECT #1 Note (Code 1276): Field or reference 'subq_0.c0' of SELECT #3 was resolved in SELECT #1 Note (Code 1276): Field or reference 'subq_0.c0' of SELECT #4 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_1.d_ytd' of SELECT #4 was resolved in SELECT #3 Note (Code 1276): Field or reference 'test.ref_1.d_name' of SELECT #4 was resolved in SELECT #3 Note (Code 1276): Field or reference 'test.ref_1.d_state' of SELECT #4 was resolved in SELECT #3 Note (Code 1276): Field or reference 'test.ref_1.d_id' of SELECT #4 was resolved in SELECT #3 Note (Code 1276): Field or reference 'subq_0.c0' of SELECT #4 was resolved in SELECT #1 Note (Code 1276): Field or reference 'subq_0.c0' of SELECT #4 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_1.d_tax' of SELECT #4 was resolved in SELECT #3 Note (Code 1276): Field or reference 'test.ref_1.d_street_1' of SELECT #4 was resolved in SELECT #3 -------------- explain analyze select subq_0.c0 as c0 from (select ref_1.ol_supply_w_id as c0, (select w_tax from warehouse limit 1 offset 3) as c1, ref_0.s_dist_09 as c2, ref_1.ol_i_id as c3 from stock as ref_0 left join order_line as ref_1 on (ref_0.s_dist_09 = ref_1.ol_dist_info ) where ref_1.ol_supply_w_id is not NULL ) as subq_0 where subq_0.c3 is NULL -------------- +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Inner hash join (ref_0.s_dist_09 = ref_1.ol_dist_info) (cost=263488070.27 rows=263454731) (actual time=128.164..128.164 rows=0 loops=1) -> Table scan on ref_0 (cost=0.15 rows=97990) (never executed) -> Hash -> Filter: ((ref_1.ol_i_id is null) and (ref_1.ol_supply_w_id is not null)) (cost=30282.20 rows=26886) (actual time=128.158..128.158 rows=0 loops=1) -> Table scan on ref_1 (cost=30282.20 rows=298732) (actual time=0.014..113.271 rows=300148 loops=1) | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.13 sec) -------------- explain analyze select ref_0.d_street_2 as c0 from district as ref_0 where EXISTS ( select ref_1.s_quantity as c0, ref_1.s_ytd as c1, ref_0.d_name as c2 from stock as ref_1 where EXISTS ( select ref_2.i_data as c0 from item as ref_2 left join order_line as ref_3 on (4 is not NULL) where ref_1.s_remote_cnt is NULL )) -------------- +-----------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------+ | -> Zero rows (Impossible WHERE) (actual time=0.000..0.000 rows=0 loops=1) | +-----------------------------------------------------------------------------+ 1 row in set, 2 warnings (0.00 sec) Note (Code 1276): Field or reference 'test.ref_0.d_name' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_1.s_remote_cnt' of SELECT #3 was resolved in SELECT #2 -------------- explain analyze select ref_0.ol_number as c0, ref_0.ol_i_id as c1, (select i_data from item limit 1 offset 5) as c2, ref_0.ol_o_id as c3, ref_0.ol_i_id as c4, ref_0.ol_quantity as c5, ref_0.ol_dist_info as c6 from order_line as ref_0 where (((EXISTS ( select ref_1.c_d_id as c0, ref_0.ol_dist_info as c1, ref_0.ol_dist_info as c2, ref_1.c_balance as c3, ref_0.ol_dist_info as c4, ref_1.c_credit as c5, ref_1.c_street_2 as c6, 75 as c7, ref_1.c_payment_cnt as c8, ref_0.ol_dist_info as c9, ref_0.ol_w_id as c10 from customer as ref_1 where ref_0.ol_quantity is not NULL)) and (ref_0.ol_number is not NULL)) and ((ref_0.ol_quantity is not NULL) and (ref_0.ol_w_id is not NULL))) and ((ref_0.ol_i_id is not NULL) or (ref_0.ol_w_id is not NULL)) -------------- ^C^C -- sending "KILL QUERY 11" to server ... ^C -- query aborted +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Remove duplicate ref_0 rows using temporary table (weedout) (cost=853691649.54 rows=8536864364) (actual time=18.492..3819.347 rows=9297 loops=1) -> Inner hash join (no condition) (cost=853691649.54 rows=8536864364) (actual time=18.486..1219.370 rows=17672842 loops=1) -> Table scan on ref_0 (cost=31931.89 rows=298732) (actual time=0.011..14.526 rows=9297 loops=1) -> Hash -> Table scan on ref_1 (cost=3154.45 rows=28577) (actual time=0.017..14.278 rows=30000 loops=1) -> Select #2 (subquery in projection; run only once) -> Limit/Offset: 1/5 row(s) (actual time=0.007..0.007 rows=1 loops=1) -> Table scan on item (cost=10114.25 rows=99620) (actual time=0.004..0.005 rows=6 loops=1) | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set, 7 warnings (3.83 sec) Note (Code 1276): Field or reference 'test.ref_0.ol_dist_info' of SELECT #3 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.ol_dist_info' of SELECT #3 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.ol_dist_info' of SELECT #3 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.ol_dist_info' of SELECT #3 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.ol_w_id' of SELECT #3 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.ol_quantity' of SELECT #3 was resolved in SELECT #1 Warning (Code 1317): Query execution was interrupted -------------- explain analyze select subq_0.c2 as c0, subq_0.c0 as c1, subq_0.c1 as c2, subq_0.c1 as c3, subq_0.c1 as c4 from (select (select d_street_2 from district limit 1 offset 5) as c0, ref_0.w_state as c1, ref_0.w_street_1 as c2, ref_0.w_zip as c3 from warehouse as ref_0 where EXISTS ( select ref_1.i_id as c0, ref_1.i_price as c1 from item as ref_1 where ref_0.w_city is NULL ) ) as subq_0 where EXISTS ( select ref_2.o_ol_cnt as c0, ref_3.no_d_id as c1 from orders as ref_2 inner join new_order as ref_3 on (ref_2.o_c_id = ref_3.no_w_id ) where ref_2.o_d_id is not NULL) -------------- +-----------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------+ | -> Zero rows (Impossible WHERE) (actual time=0.000..0.000 rows=0 loops=1) | +-----------------------------------------------------------------------------+ 1 row in set, 1 warning (0.00 sec) Note (Code 1276): Field or reference 'test.ref_0.w_city' of SELECT #4 was resolved in SELECT #2 -------------- explain analyze select distinct subq_0.c2 as c0, (select w_ytd from warehouse limit 1 offset 3) as c1, subq_0.c2 as c2, subq_0.c2 as c3, subq_0.c2 as c4, subq_0.c1 as c5, cast(coalesce(subq_0.c1, subq_0.c0) as character(24)) as c6, subq_0.c0 as c7, subq_0.c1 as c8, subq_0.c1 as c9, subq_0.c1 as c10, subq_0.c2 as c11, (select no_o_id from new_order limit 1 offset 1) as c12 from (select ref_0.s_dist_10 as c0, ref_0.s_dist_07 as c1, ref_0.s_w_id as c2 from stock as ref_0 where (select ol_o_id from order_line limit 1 offset 6) is not NULL) as subq_0 where subq_0.c2 is not NULL -------------- +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Table scan on (actual time=0.001..9.569 rows=100000 loops=1) -> Temporary table with deduplication (actual time=183.368..196.810 rows=100000 loops=1) -> Table scan on ref_0 (cost=10497.20 rows=97990) (actual time=0.005..47.342 rows=100000 loops=1) -> Select #2 (subquery in projection; run only once) -> Limit/Offset: 1/3 row(s) (actual time=0.004..0.004 rows=0 loops=1) -> Table scan on warehouse (cost=0.35 rows=1) (actual time=0.003..0.004 rows=1 loops=1) -> Select #3 (subquery in projection; run only once) -> Limit/Offset: 1/1 row(s) (actual time=0.002..0.002 rows=1 loops=1) -> Table scan on new_order (cost=805.15 rows=7999) (actual time=0.002..0.002 rows=2 loops=1) | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.23 sec) -------------- explain analyze select subq_0.c1 as c0, (select ol_amount from order_line limit 1 offset 5) as c1, case when subq_0.c11 is not NULL then subq_0.c4 else subq_0.c4 end as c2, subq_0.c3 as c3 from (select distinct (select s_ytd from stock limit 1 offset 6) as c0, ref_0.s_dist_06 as c1, 82 as c2, ref_0.s_dist_07 as c3, ref_0.s_dist_10 as c4, ref_0.s_data as c5, ref_0.s_dist_03 as c6, ref_0.s_w_id as c7, ref_0.s_i_id as c8, ref_0.s_ytd as c9, ref_0.s_dist_06 as c10, ref_0.s_dist_08 as c11, ref_0.s_order_cnt as c12, ref_0.s_dist_08 as c13, ref_0.s_dist_08 as c14, ref_0.s_dist_02 as c15, ref_0.s_dist_05 as c16, ref_0.s_dist_07 as c17 from stock as ref_0 where ref_0.s_dist_10 is not NULL ) as subq_0 where subq_0.c12 is not NULL -------------- +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Table scan on subq_0 (actual time=0.001..12.774 rows=100000 loops=1) -> Materialize (actual time=300.186..317.124 rows=100000 loops=1) -> Table scan on (actual time=0.001..13.014 rows=100000 loops=1) -> Temporary table with deduplication (actual time=215.757..232.828 rows=100000 loops=1) -> Table scan on ref_0 (cost=10497.20 rows=97990) (actual time=0.015..57.104 rows=100000 loops=1) -> Select #4 (subquery in projection; run only once) -> Limit/Offset: 1/6 row(s) (actual time=0.006..0.006 rows=1 loops=1) -> Table scan on stock (cost=10497.20 rows=97990) (actual time=0.002..0.005 rows=7 loops=1) -> Select #2 (subquery in projection; run only once) -> Limit/Offset: 1/5 row(s) (actual time=0.013..0.013 rows=1 loops=1) -> Table scan on order_line (cost=30282.20 rows=298732) (actual time=0.010..0.012 rows=6 loops=1) | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.35 sec) -------------- explain analyze select distinct ref_0.s_dist_04 as c0, ref_0.s_remote_cnt as c1, ref_0.s_dist_09 as c2, ref_0.s_dist_08 as c3, ref_0.s_data as c4, ref_0.s_dist_03 as c5 from stock as ref_0 where ref_0.s_order_cnt is not NULL -------------- +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Table scan on (actual time=0.001..6.431 rows=100000 loops=1) -> Temporary table with deduplication (actual time=135.773..146.073 rows=100000 loops=1) -> Table scan on ref_0 (cost=10497.20 rows=97990) (actual time=0.015..43.208 rows=100000 loops=1) | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.16 sec) -------------- explain analyze select distinct 91 as c0, subq_0.c1 as c1, subq_0.c2 as c2, subq_0.c2 as c3, subq_0.c1 as c4, (select s_dist_07 from stock limit 1 offset 5) as c5, subq_0.c1 as c6, subq_0.c2 as c7, subq_0.c0 as c8, subq_0.c1 as c9, subq_0.c2 as c10 from (select ref_0.s_order_cnt as c0, ref_0.s_dist_09 as c1, ref_0.s_dist_06 as c2 from stock as ref_0 where ref_0.s_data is not NULL) as subq_0 where subq_0.c1 is not NULL -------------- +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Table scan on (actual time=0.001..8.260 rows=100000 loops=1) -> Temporary table with deduplication (actual time=141.620..153.664 rows=100000 loops=1) -> Table scan on ref_0 (cost=10497.20 rows=97990) (actual time=0.014..45.038 rows=100000 loops=1) -> Select #2 (subquery in projection; run only once) -> Limit/Offset: 1/5 row(s) (actual time=0.006..0.006 rows=1 loops=1) -> Table scan on stock (cost=10497.20 rows=97990) (actual time=0.002..0.005 rows=6 loops=1) | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.18 sec) -------------- explain analyze select distinct subq_0.c0 as c0, subq_0.c1 as c1, 1 as c2, subq_0.c0 as c3, (select c_phone from customer limit 1 offset 4) as c4, case when subq_0.c0 is not NULL then subq_0.c0 else subq_0.c0 end as c5, subq_0.c0 as c6, subq_0.c0 as c7, subq_0.c0 as c8, subq_0.c1 as c9 from (select ref_0.s_dist_02 as c0, ref_0.s_i_id as c1 from stock as ref_0 where cast(nullif(ref_0.s_dist_01, ref_0.s_dist_10) as character(24)) is not NULL) as subq_0 where subq_0.c0 is not NULL -------------- +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Table scan on (actual time=0.001..7.710 rows=100000 loops=1) -> Temporary table with deduplication (actual time=164.759..176.416 rows=100000 loops=1) -> Filter: (cast(nullif(ref_0.s_dist_01,ref_0.s_dist_10) as char(24) charset utf8mb4) is not null) (cost=10497.20 rows=97990) (actual time=0.017..61.361 rows=100000 loops=1) -> Table scan on ref_0 (cost=10497.20 rows=97990) (actual time=0.014..46.263 rows=100000 loops=1) -> Select #2 (subquery in projection; run only once) -> Limit/Offset: 1/4 row(s) (actual time=0.008..0.008 rows=1 loops=1) -> Table scan on customer (cost=3154.45 rows=28577) (actual time=0.004..0.007 rows=5 loops=1) | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.20 sec) -------------- explain analyze select subq_0.c2 as c0, case when subq_0.c6 is not NULL then subq_0.c3 else subq_0.c3 end as c1, (select d_tax from district limit 1 offset 4) as c2 from (select ref_0.ol_d_id as c0, ref_0.ol_i_id as c1, ref_0.ol_i_id as c2, ref_0.ol_dist_info as c3, ref_0.ol_supply_w_id as c4, ref_0.ol_delivery_d as c5, ref_0.ol_quantity as c6 from order_line as ref_0 where EXISTS ( select ref_0.ol_i_id as c0, ref_1.c_phone as c1, ref_1.c_ytd_payment as c2, ref_0.ol_o_id as c3 from customer as ref_1 where ref_0.ol_dist_info is not NULL)) as subq_0 where 29 is not NULL -------------- ^C^C -- sending "KILL QUERY 11" to server ... ^C -- query aborted +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Remove duplicate ref_0 rows using temporary table (weedout) (cost=853691069.92 rows=8536864364) (actual time=13.874..5223.621 rows=13271 loops=1) -> Inner hash join (no condition) (cost=853691069.92 rows=8536864364) (actual time=13.868..1446.054 rows=26117284 loops=1) -> Table scan on ref_0 (cost=31352.27 rows=298732) (actual time=0.011..18.228 rows=13271 loops=1) -> Hash -> Table scan on ref_1 (cost=3154.45 rows=28577) (actual time=0.014..10.778 rows=30000 loops=1) -> Select #2 (subquery in projection; run only once) -> Limit/Offset: 1/4 row(s) (actual time=0.006..0.006 rows=1 loops=1) -> Table scan on district (cost=1.25 rows=10) (actual time=0.004..0.005 rows=5 loops=1) | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set, 4 warnings (5.24 sec) Note (Code 1276): Field or reference 'test.ref_0.ol_i_id' of SELECT #4 was resolved in SELECT #3 Note (Code 1276): Field or reference 'test.ref_0.ol_o_id' of SELECT #4 was resolved in SELECT #3 Note (Code 1276): Field or reference 'test.ref_0.ol_dist_info' of SELECT #4 was resolved in SELECT #3 Warning (Code 1317): Query execution was interrupted -------------- explain analyze select subq_0.c3 as c0, subq_0.c2 as c1, subq_0.c1 as c2, subq_0.c3 as c3, 26 as c4, subq_0.c1 as c5, subq_0.c0 as c6, subq_0.c3 as c7, subq_0.c1 as c8, subq_0.c1 as c9, subq_0.c3 as c10 from (select distinct ref_0.s_dist_04 as c0, ref_0.s_dist_08 as c1, ref_0.s_w_id as c2, ref_0.s_dist_10 as c3 from stock as ref_0 where ref_0.s_w_id is not NULL) as subq_0 where subq_0.c2 is not NULL -------------- +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Table scan on subq_0 (actual time=0.000..3.723 rows=100000 loops=1) -> Materialize (actual time=116.795..124.643 rows=100000 loops=1) -> Table scan on (actual time=0.001..3.465 rows=100000 loops=1) -> Temporary table with deduplication (actual time=98.724..106.075 rows=100000 loops=1) -> Table scan on ref_0 (cost=10497.20 rows=97990) (actual time=0.013..40.316 rows=100000 loops=1) | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.14 sec) -------------- explain analyze select distinct 44 as c0, ref_0.i_data as c1, ref_0.i_price as c2, ref_0.i_name as c3, ref_0.i_name as c4, ref_0.i_name as c5, ref_0.i_id as c6, ref_0.i_im_id as c7, ref_0.i_name as c8, 58 as c9 from item as ref_0 where (ref_0.i_data is not NULL) and (ref_0.i_data is not NULL) -------------- +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Table scan on (actual time=0.001..7.907 rows=100000 loops=1) -> Temporary table with deduplication (actual time=147.976..159.731 rows=100000 loops=1) -> Table scan on ref_0 (cost=10114.25 rows=99620) (actual time=0.030..40.255 rows=100000 loops=1) | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.19 sec) -------------- explain analyze select subq_0.c4 as c0 from (select ref_0.s_quantity as c0, ref_0.s_ytd as c1, (select o_w_id from orders limit 1 offset 5) as c2, ref_1.s_remote_cnt as c3, ref_1.s_dist_03 as c4 from stock as ref_0 left join stock as ref_1 on (ref_0.s_dist_02 is not NULL) where ref_1.s_data is not NULL ) as subq_0 where subq_0.c3 is NULL -------------- +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Inner hash join (no condition) (cost=86431431.71 rows=864183572) (actual time=42.161..42.161 rows=0 loops=1) -> Table scan on ref_0 (cost=1.40 rows=97990) (never executed) -> Hash -> Filter: ((ref_1.s_remote_cnt is null) and (ref_1.s_data is not null)) (cost=10497.20 rows=8819) (actual time=42.155..42.155 rows=0 loops=1) -> Table scan on ref_1 (cost=10497.20 rows=97990) (actual time=0.015..38.018 rows=100000 loops=1) | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.04 sec) -------------- explain analyze select ref_0.i_im_id as c0, ref_0.i_data as c1, ref_0.i_price as c2 from item as ref_0 where EXISTS ( select ref_1.d_tax as c0, ref_0.i_price as c1, ref_1.d_w_id as c2, ref_0.i_data as c3, ref_1.d_zip as c4, ref_0.i_name as c5, ref_1.d_city as c6, ref_1.d_city as c7, (select s_quantity from stock limit 1 offset 2) as c8, ref_1.d_city as c9, ref_1.d_next_o_id as c10, ref_0.i_im_id as c11, ref_1.d_tax as c12, ref_1.d_street_1 as c13, ref_0.i_data as c14, ref_1.d_name as c15, ref_0.i_id as c16, ref_0.i_im_id as c17, ref_1.d_id as c18, ref_0.i_name as c19, ref_1.d_w_id as c20, ref_1.d_tax as c21, ref_1.d_ytd as c22 from district as ref_1 where EXISTS ( select ref_1.d_state as c0 from orders as ref_2 where ref_1.d_city is NULL ) ) -------------- +-----------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------+ | -> Zero rows (Impossible WHERE) (actual time=0.000..0.000 rows=0 loops=1) | +-----------------------------------------------------------------------------+ 1 row in set, 10 warnings (0.00 sec) Note (Code 1276): Field or reference 'test.ref_0.i_price' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.i_data' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.i_name' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.i_im_id' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.i_data' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.i_id' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.i_im_id' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.i_name' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_1.d_state' of SELECT #4 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_1.d_city' of SELECT #4 was resolved in SELECT #2 -------------- explain analyze select subq_0.c4 as c0, subq_0.c2 as c1, subq_0.c1 as c2, subq_0.c4 as c3, subq_0.c1 as c4, subq_0.c2 as c5 from (select distinct cast(coalesce(ref_0.s_dist_02, ref_0.s_dist_01) as character(24)) as c0, ref_0.s_dist_06 as c1, ref_0.s_dist_03 as c2, ref_0.s_dist_04 as c3, ref_0.s_dist_03 as c4, ref_0.s_dist_08 as c5, ref_0.s_dist_01 as c6, ref_0.s_w_id as c7, ref_0.s_dist_09 as c8 from stock as ref_0 where ref_0.s_dist_01 is not NULL ) as subq_0 where (subq_0.c7 is not NULL) and (subq_0.c6 is not NULL) -------------- +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Table scan on subq_0 (actual time=0.001..8.237 rows=100000 loops=1) -> Materialize (actual time=224.153..236.504 rows=100000 loops=1) -> Table scan on (actual time=0.001..8.633 rows=100000 loops=1) -> Temporary table with deduplication (actual time=175.216..187.969 rows=100000 loops=1) -> Table scan on ref_0 (cost=10497.20 rows=97990) (actual time=0.020..50.228 rows=100000 loops=1) | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.26 sec) -------------- explain analyze select subq_0.c0 as c0, subq_0.c0 as c1, 87 as c2, subq_0.c0 as c3, subq_0.c0 as c4, subq_0.c0 as c5 from (select 46 as c0 from district as ref_0 where EXISTS ( select ref_0.d_street_2 as c0, (select ol_d_id from order_line limit 1 offset 4) as c1, ref_1.ol_w_id as c2, (select h_data from history limit 1 offset 3) as c3, 79 as c4 from order_line as ref_1 where EXISTS ( select ref_0.d_w_id as c0, ref_2.w_id as c1, ref_2.w_state as c2, ref_1.ol_amount as c3, ref_1.ol_supply_w_id as c4 from warehouse as ref_2 where ref_1.ol_quantity is NULL ) ) ) as subq_0 where (select i_id from item limit 1 offset 6) is not NULL -------------- +-----------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------+ | -> Zero rows (Impossible WHERE) (actual time=0.000..0.000 rows=0 loops=1) | +-----------------------------------------------------------------------------+ 1 row in set, 5 warnings (0.00 sec) Note (Code 1276): Field or reference 'test.ref_0.d_street_2' of SELECT #3 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_0.d_w_id' of SELECT #6 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_1.ol_amount' of SELECT #6 was resolved in SELECT #3 Note (Code 1276): Field or reference 'test.ref_1.ol_supply_w_id' of SELECT #6 was resolved in SELECT #3 Note (Code 1276): Field or reference 'test.ref_1.ol_quantity' of SELECT #6 was resolved in SELECT #3 -------------- explain analyze select (select c_city from customer limit 1 offset 3) as c0 from stock as ref_0 left join stock as ref_1 on (ref_0.s_dist_10 = ref_1.s_dist_01 ) where ((((ref_0.s_dist_03 is not NULL) and (ref_1.s_i_id is NULL)) and (ref_0.s_i_id is not NULL)) and ((EXISTS ( select (select i_id from item limit 1 offset 3) as c0, ref_1.s_dist_07 as c1, ref_2.i_im_id as c2 from item as ref_2 where ref_1.s_dist_08 is not NULL )) or (ref_1.s_w_id is not NULL))) and ((ref_0.s_w_id is NULL) or (ref_1.s_dist_04 is not NULL)) -------------- ^C^C -- sending "KILL QUERY 11" to server ... ^C -- query aborted +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Filter: ((ref_1.s_i_id is null) and (exists(select #3) or (ref_1.s_w_id is not null)) and (ref_1.s_dist_04 is not null)) (cost=960414903.64 rows=864183572) (actual time=4665.595..4665.595 rows=0 loops=1) -> Hash antijoin (ref_1.s_dist_01 = ref_0.s_dist_10) (cost=960414903.64 rows=864183572) (actual time=142.402..142.808 rows=139 loops=1) -> Table scan on ref_0 (cost=10497.20 rows=97990) (actual time=0.028..39.152 rows=100000 loops=1) -> Hash -> Table scan on ref_1 (cost=2.16 rows=97990) (actual time=0.008..41.952 rows=100000 loops=1) -> Select #3 (subquery in condition; dependent) -> Limit: 1 row(s) (actual time=32.534..32.534 rows=0 loops=139) -> Filter: (ref_1.s_dist_08 is not null) (cost=10114.25 rows=99620) (actual time=32.534..32.534 rows=0 loops=139) -> Table scan on ref_2 (cost=10114.25 rows=99620) (actual time=0.004..29.472 rows=99956 loops=139) -> Select #4 (subquery in projection; run only once) -> Limit/Offset: 1/3 row(s) (never executed) -> Table scan on item (cost=10114.25 rows=99620) (never executed) -> Select #2 (subquery in projection; run only once) -> Limit/Offset: 1/3 row(s) (never executed) -> Table scan on customer (cost=3154.45 rows=28577) (never executed) | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set, 3 warnings (4.67 sec) Note (Code 1276): Field or reference 'test.ref_1.s_dist_07' of SELECT #3 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_1.s_dist_08' of SELECT #3 was resolved in SELECT #1 Warning (Code 1317): Query execution was interrupted -------------- explain analyze select distinct ref_0.s_dist_07 as c0, (select s_dist_03 from stock limit 1 offset 93) as c1, ref_0.s_data as c2, ref_0.s_dist_09 as c3, ref_0.s_remote_cnt as c4, case when ref_0.s_i_id is not NULL then ref_0.s_dist_08 else ref_0.s_dist_08 end as c5, ref_0.s_remote_cnt as c6, ref_0.s_dist_01 as c7, ref_0.s_data as c8, ref_0.s_dist_03 as c9, 7 as c10, ref_0.s_dist_02 as c11, ref_0.s_dist_08 as c12 from stock as ref_0 where ref_0.s_quantity is not NULL -------------- +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Table scan on (actual time=0.001..11.107 rows=100000 loops=1) -> Temporary table with deduplication (actual time=267.461..282.965 rows=100000 loops=1) -> Table scan on ref_0 (cost=10497.20 rows=97990) (actual time=0.015..69.139 rows=100000 loops=1) -> Select #2 (subquery in projection; run only once) -> Limit/Offset: 1/93 row(s) (actual time=0.034..0.034 rows=1 loops=1) -> Table scan on stock (cost=10497.20 rows=97990) (actual time=0.002..0.031 rows=94 loops=1) | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.32 sec) -------------- explain analyze select subq_0.c1 as c0, subq_0.c0 as c1, subq_0.c5 as c2, subq_0.c5 as c3, subq_0.c6 as c4, subq_0.c2 as c5, subq_0.c3 as c6, subq_0.c0 as c7, subq_0.c1 as c8, subq_0.c7 as c9, subq_0.c0 as c10, subq_0.c7 as c11, subq_0.c2 as c12, subq_0.c2 as c13, subq_0.c6 as c14, subq_0.c7 as c15 from (select distinct ref_0.i_id as c0, ref_0.i_data as c1, ref_0.i_name as c2, ref_0.i_data as c3, ref_0.i_im_id as c4, ref_0.i_im_id as c5, ref_0.i_im_id as c6, ref_0.i_price as c7 from item as ref_0 where ref_0.i_data is not NULL ) as subq_0 where subq_0.c5 is not NULL -------------- +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Table scan on subq_0 (actual time=0.001..7.871 rows=100000 loops=1) -> Materialize (actual time=211.661..223.351 rows=100000 loops=1) -> Table scan on (actual time=0.001..8.606 rows=100000 loops=1) -> Temporary table with deduplication (actual time=153.449..166.136 rows=100000 loops=1) -> Table scan on ref_0 (cost=10114.25 rows=99620) (actual time=0.016..43.897 rows=100000 loops=1) | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.27 sec) -------------- explain analyze select cast(nullif(98, 71) as SIGNED) as c0, (select s_dist_08 from stock limit 1 offset 4) as c1 from district as ref_0 where ((ref_0.d_street_2 is NULL) or (((ref_0.d_tax is NULL) and (ref_0.d_ytd is NULL)) and ((ref_0.d_street_2 is NULL) and (ref_0.d_state is NULL)))) or (EXISTS ( select ref_0.d_zip as c0 from order_line as ref_1 where EXISTS ( select ref_0.d_w_id as c0, ref_0.d_street_2 as c1, ref_2.h_c_id as c2, ref_1.ol_dist_info as c3, ref_0.d_state as c4, ref_1.ol_dist_info as c5, ref_2.h_c_d_id as c6, ref_2.h_amount as c7, ref_2.h_c_w_id as c8, 87 as c9 from history as ref_2 where (ref_1.ol_i_id is NULL) and (ref_1.ol_i_id is NULL) ))) -------------- +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Filter: exists(select #3) (cost=1.25 rows=10) (actual time=0.022..0.022 rows=0 loops=1) -> Table scan on ref_0 (cost=1.25 rows=10) (actual time=0.013..0.018 rows=10 loops=1) -> Select #3 (subquery in condition; dependent) -> Zero rows (Impossible WHERE) (actual time=0.000..0.000 rows=0 loops=10) -> Select #2 (subquery in projection; run only once) -> Limit/Offset: 1/4 row(s) (never executed) -> Table scan on stock (cost=10497.20 rows=97990) (never executed) | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set, 8 warnings (0.00 sec) Note (Code 1276): Field or reference 'test.ref_0.d_zip' of SELECT #3 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.d_w_id' of SELECT #4 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.d_street_2' of SELECT #4 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_1.ol_dist_info' of SELECT #4 was resolved in SELECT #3 Note (Code 1276): Field or reference 'test.ref_0.d_state' of SELECT #4 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_1.ol_dist_info' of SELECT #4 was resolved in SELECT #3 Note (Code 1276): Field or reference 'test.ref_1.ol_i_id' of SELECT #4 was resolved in SELECT #3 Note (Code 1276): Field or reference 'test.ref_1.ol_i_id' of SELECT #4 was resolved in SELECT #3 -------------- explain analyze select 34 as c0, ref_0.no_o_id as c1, ref_1.no_d_id as c2, (select w_ytd from warehouse limit 1 offset 5) as c3, ref_1.no_w_id as c4 from new_order as ref_0 inner join new_order as ref_1 on (ref_0.no_o_id = ref_1.no_w_id ) where ref_1.no_o_id is not NULL -------------- +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Inner hash join (ref_1.no_w_id = ref_0.no_o_id) (cost=6399211.14 rows=6398400) (actual time=9.185..9.185 rows=0 loops=1) -> Table scan on ref_1 (cost=0.01 rows=7999) (actual time=0.006..2.551 rows=8019 loops=1) -> Hash -> Table scan on ref_0 (cost=805.15 rows=7999) (actual time=0.006..2.235 rows=8019 loops=1) -> Select #2 (subquery in projection; run only once) -> Limit/Offset: 1/5 row(s) (never executed) -> Table scan on warehouse (cost=0.35 rows=1) (never executed) | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) -------------- explain analyze select subq_0.c0 as c0 from (select distinct ref_0.s_dist_08 as c0, ref_0.s_i_id as c1, ref_0.s_ytd as c2, ref_0.s_w_id as c3, ref_0.s_dist_04 as c4, ref_0.s_quantity as c5, ref_0.s_dist_01 as c6, ref_0.s_data as c7, ref_0.s_dist_07 as c8, ref_0.s_dist_08 as c9, ref_0.s_order_cnt as c10, ref_0.s_dist_07 as c11, 19 as c12, ref_0.s_data as c13, ref_0.s_ytd as c14 from stock as ref_0 where ref_0.s_dist_07 is not NULL) as subq_0 where subq_0.c13 is not NULL -------------- +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Table scan on subq_0 (actual time=0.001..11.303 rows=100000 loops=1) -> Materialize (actual time=270.886..286.135 rows=100000 loops=1) -> Table scan on (actual time=0.001..11.295 rows=100000 loops=1) -> Temporary table with deduplication (actual time=190.103..205.451 rows=100000 loops=1) -> Table scan on ref_0 (cost=10497.20 rows=97990) (actual time=0.024..51.803 rows=100000 loops=1) | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.31 sec) -------------- explain analyze select subq_0.c0 as c0, subq_0.c0 as c1, 87 as c2, subq_0.c0 as c3, subq_0.c0 as c4, subq_0.c0 as c5 from (select 46 as c0 from district as ref_0 where EXISTS ( select ref_0.d_street_2 as c0, (select ol_d_id from order_line limit 1 offset 4) as c1, ref_1.ol_w_id as c2, (select h_data from history limit 1 offset 3) as c3, 79 as c4 from order_line as ref_1 where EXISTS ( select ref_0.d_w_id as c0, ref_2.w_id as c1, ref_2.w_state as c2, ref_1.ol_amount as c3, ref_1.ol_supply_w_id as c4 from warehouse as ref_2 where ref_1.ol_quantity is NULL ) ) ) as subq_0 where (select i_id from item limit 1 offset 6) is not NULL -------------- +-----------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------+ | -> Zero rows (Impossible WHERE) (actual time=0.000..0.000 rows=0 loops=1) | +-----------------------------------------------------------------------------+ 1 row in set, 5 warnings (0.00 sec) Note (Code 1276): Field or reference 'test.ref_0.d_street_2' of SELECT #3 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_0.d_w_id' of SELECT #6 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_1.ol_amount' of SELECT #6 was resolved in SELECT #3 Note (Code 1276): Field or reference 'test.ref_1.ol_supply_w_id' of SELECT #6 was resolved in SELECT #3 Note (Code 1276): Field or reference 'test.ref_1.ol_quantity' of SELECT #6 was resolved in SELECT #3 -------------- explain analyze select distinct subq_0.c0 as c0 from (select ref_0.ol_dist_info as c0 from order_line as ref_0 where ref_0.ol_i_id is not NULL) as subq_0 where subq_0.c0 is not NULL -------------- +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Table scan on (actual time=0.001..8.610 rows=300144 loops=1) -> Temporary table with deduplication (actual time=218.632..239.143 rows=300144 loops=1) -> Table scan on ref_0 (cost=30282.20 rows=298732) (actual time=0.007..97.347 rows=300148 loops=1) | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.26 sec) -------------- explain analyze select cast(nullif(98, 71) as SIGNED) as c0, (select s_dist_08 from stock limit 1 offset 4) as c1 from district as ref_0 where ((ref_0.d_street_2 is NULL) or (((ref_0.d_tax is NULL) and (ref_0.d_ytd is NULL)) and ((ref_0.d_street_2 is NULL) and (ref_0.d_state is NULL)))) or (EXISTS ( select ref_0.d_zip as c0 from order_line as ref_1 where EXISTS ( select ref_0.d_w_id as c0, ref_0.d_street_2 as c1, ref_2.h_c_id as c2, ref_1.ol_dist_info as c3, ref_0.d_state as c4, ref_1.ol_dist_info as c5, ref_2.h_c_d_id as c6, ref_2.h_amount as c7, ref_2.h_c_w_id as c8, 87 as c9 from history as ref_2 where (ref_1.ol_i_id is NULL) and (ref_1.ol_i_id is NULL) ))) -------------- +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Filter: exists(select #3) (cost=1.25 rows=10) (actual time=0.022..0.022 rows=0 loops=1) -> Table scan on ref_0 (cost=1.25 rows=10) (actual time=0.013..0.018 rows=10 loops=1) -> Select #3 (subquery in condition; dependent) -> Zero rows (Impossible WHERE) (actual time=0.000..0.000 rows=0 loops=10) -> Select #2 (subquery in projection; run only once) -> Limit/Offset: 1/4 row(s) (never executed) -> Table scan on stock (cost=10497.20 rows=97990) (never executed) | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set, 8 warnings (0.00 sec) Note (Code 1276): Field or reference 'test.ref_0.d_zip' of SELECT #3 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.d_w_id' of SELECT #4 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.d_street_2' of SELECT #4 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_1.ol_dist_info' of SELECT #4 was resolved in SELECT #3 Note (Code 1276): Field or reference 'test.ref_0.d_state' of SELECT #4 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_1.ol_dist_info' of SELECT #4 was resolved in SELECT #3 Note (Code 1276): Field or reference 'test.ref_1.ol_i_id' of SELECT #4 was resolved in SELECT #3 Note (Code 1276): Field or reference 'test.ref_1.ol_i_id' of SELECT #4 was resolved in SELECT #3 -------------- explain analyze select ref_0.d_w_id as c0, ref_0.d_next_o_id as c1 from district as ref_0 where EXISTS ( select ref_1.ol_w_id as c0 from order_line as ref_1 where EXISTS ( select ref_2.h_d_id as c0, ref_2.h_data as c1, ref_1.ol_i_id as c2, ref_0.d_street_1 as c3 from history as ref_2 where 18 is NULL ) ) -------------- +-----------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------+ | -> Zero rows (Impossible WHERE) (actual time=0.000..0.000 rows=0 loops=1) | +-----------------------------------------------------------------------------+ 1 row in set, 2 warnings (0.01 sec) Note (Code 1276): Field or reference 'test.ref_1.ol_i_id' of SELECT #3 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_0.d_street_1' of SELECT #3 was resolved in SELECT #1 -------------- explain analyze select subq_0.c2 as c0 from (select ref_1.ol_supply_w_id as c0, 32 as c1, ref_1.ol_amount as c2 from stock as ref_0 left join order_line as ref_1 on (ref_0.s_dist_03 = ref_1.ol_dist_info ) where ref_1.ol_w_id is not NULL ) as subq_0 where subq_0.c2 is NULL -------------- +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Inner hash join (ref_0.s_dist_03 = ref_1.ol_dist_info) (cost=263487998.66 rows=263454731) (actual time=110.057..110.057 rows=0 loops=1) -> Table scan on ref_0 (cost=0.15 rows=97990) (never executed) -> Hash -> Filter: ((ref_1.ol_amount is null) and (ref_1.ol_w_id is not null)) (cost=30282.20 rows=26886) (actual time=110.052..110.052 rows=0 loops=1) -> Table scan on ref_1 (cost=30282.20 rows=298732) (actual time=0.008..97.119 rows=300148 loops=1) | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.10 sec) -------------- explain analyze select ref_0.d_street_2 as c0, ref_0.d_name as c1, ref_0.d_id as c2, ref_0.d_w_id as c3, ref_0.d_state as c4 from district as ref_0 where EXISTS ( select ref_1.ol_i_id as c0, ref_1.ol_delivery_d as c1, ref_0.d_street_1 as c2, ref_0.d_street_2 as c3, ref_0.d_tax as c4 from order_line as ref_1 where EXISTS ( select ref_3.i_im_id as c0, ref_0.d_zip as c1, ref_2.w_id as c2, ref_0.d_w_id as c3, ref_1.ol_w_id as c4 from warehouse as ref_2 inner join item as ref_3 on (ref_2.w_id = ref_3.i_id ) where ref_2.w_ytd is NULL ) ) -------------- +-----------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------+ | -> Zero rows (Impossible WHERE) (actual time=0.000..0.000 rows=0 loops=1) | +-----------------------------------------------------------------------------+ 1 row in set, 6 warnings (0.00 sec) Note (Code 1276): Field or reference 'test.ref_0.d_street_1' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.d_street_2' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.d_tax' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.d_zip' of SELECT #3 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.d_w_id' of SELECT #3 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_1.ol_w_id' of SELECT #3 was resolved in SELECT #2 -------------- explain analyze select subq_0.c6 as c0, subq_0.c0 as c1, subq_0.c2 as c2, subq_0.c3 as c3, cast(nullif(cast(nullif(subq_0.c0, subq_0.c3) as character(24)), subq_0.c7) as character(24)) as c4, subq_0.c7 as c5 from (select case when EXISTS ( select ref_0.s_dist_06 as c0, ref_1.h_c_d_id as c1, ref_0.s_dist_06 as c2, 82 as c3, ref_0.s_i_id as c4 from history as ref_1 where EXISTS ( select ref_1.h_c_d_id as c0 from orders as ref_2 where ref_2.o_entry_d is not NULL ) ) then ref_0.s_dist_06 else ref_0.s_dist_06 end as c0, ref_0.s_data as c1, case when (ref_0.s_dist_09 is not NULL) or ((select i_id from item limit 1 offset 5) is not NULL) then ref_0.s_dist_07 else ref_0.s_dist_07 end as c2, ref_0.s_dist_10 as c3, ref_0.s_w_id as c4, ref_0.s_ytd as c5, ref_0.s_dist_10 as c6, ref_0.s_dist_08 as c7, 20 as c8, ref_0.s_dist_06 as c9 from stock as ref_0 where EXISTS ( select 74 as c0, ref_3.w_id as c1, 85 as c2, ref_0.s_dist_02 as c3, ref_0.s_remote_cnt as c4, ref_3.w_street_1 as c5, ref_3.w_street_2 as c6, ref_0.s_dist_08 as c7 from warehouse as ref_3 where 58 is not NULL )) as subq_0 where subq_0.c9 is not NULL -------------- +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Table scan on subq_0 (actual time=0.001..8.761 rows=100000 loops=1) -> Materialize (actual time=490.537..503.224 rows=100000 loops=1) -> Inner hash join (no condition) (actual time=0.027..77.934 rows=100000 loops=1) -> Table scan on ref_0 (cost=10497.33 rows=97990) (actual time=0.005..65.106 rows=100000 loops=1) -> Hash -> Limit: 1 row(s) (actual time=0.013..0.014 rows=1 loops=1) -> Table scan on ref_3 (cost=0.35 rows=1) (actual time=0.013..0.013 rows=1 loops=1) -> Select #3 (subquery in projection; dependent) -> Limit: 1 row(s) (actual time=0.003..0.003 rows=1 loops=100000) -> Inner hash join (no condition) (actual time=0.003..0.003 rows=1 loops=100000) -> Table scan on ref_1 (cost=2966.95 rows=29267) (actual time=0.001..0.001 rows=1 loops=100000) -> Hash -> Limit: 1 row(s) (actual time=0.001..0.001 rows=1 loops=100000) -> Table scan on ref_2 (cost=91764049.40 rows=30092) (actual time=0.001..0.001 rows=1 loops=100000) -> Select #5 (subquery in projection; run only once) -> Limit/Offset: 1/5 row(s) (never executed) -> Table scan on item (cost=10114.25 rows=99620) (never executed) | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set, 7 warnings (0.54 sec) Note (Code 1276): Field or reference 'test.ref_0.s_dist_06' of SELECT #3 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_0.s_dist_06' of SELECT #3 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_0.s_i_id' of SELECT #3 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_1.h_c_d_id' of SELECT #4 was resolved in SELECT #3 Note (Code 1276): Field or reference 'test.ref_0.s_dist_02' of SELECT #6 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_0.s_remote_cnt' of SELECT #6 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_0.s_dist_08' of SELECT #6 was resolved in SELECT #2 -------------- explain analyze select ref_0.w_street_1 as c0, ref_0.w_street_2 as c1, ref_0.w_street_2 as c2, ref_0.w_state as c3, ref_0.w_zip as c4 from warehouse as ref_0 where EXISTS ( select 33 as c0 from new_order as ref_1 left join new_order as ref_2 on (ref_1.no_o_id = ref_2.no_w_id ) where ref_2.no_o_id is not NULL ) -------------- ^C^C -- sending "KILL QUERY 11" to server ... ^C -- query aborted +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Nested loop semijoin (cost=639925.61 rows=5758560) (actual time=3698.403..3698.403 rows=0 loops=1) -> Table scan on ref_0 (cost=0.35 rows=1) (actual time=0.013..0.013 rows=1 loops=1) -> Nested loop inner join (cost=5797160.36 rows=5758560) (actual time=3698.389..3698.389 rows=0 loops=1) -> Filter: (ref_2.no_o_id is not null) (cost=805.15 rows=7199) (actual time=0.004..1.513 rows=1414 loops=1) -> Table scan on ref_2 (cost=805.15 rows=7999) (actual time=0.003..1.185 rows=1414 loops=1) -> Filter: (ref_1.no_o_id = ref_2.no_w_id) (cost=30296424.13 rows=800) (actual time=2.614..2.614 rows=0 loops=1414) -> Table scan on ref_1 (cost=30296424.13 rows=7999) (actual time=0.002..2.286 rows=8014 loops=1414) | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set, 1 warning (3.70 sec) Warning (Code 1317): Query execution was interrupted -------------- explain analyze select ref_0.no_d_id as c0 from new_order as ref_0 left join new_order as ref_1 on (ref_0.no_o_id = ref_1.no_w_id ) where ref_1.no_w_id is not NULL -------------- +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Inner hash join (ref_0.no_o_id = ref_1.no_w_id) (cost=5759371.06 rows=5758560) (actual time=9.535..9.535 rows=0 loops=1) -> Table scan on ref_0 (cost=0.01 rows=7999) (actual time=0.009..2.500 rows=8019 loops=1) -> Hash -> Filter: (ref_1.no_w_id is not null) (cost=805.15 rows=7199) (actual time=0.016..2.853 rows=8019 loops=1) -> Table scan on ref_1 (cost=805.15 rows=7999) (actual time=0.015..2.489 rows=8019 loops=1) | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) -------------- explain analyze select ref_0.i_im_id as c0, ref_0.i_data as c1, ref_0.i_price as c2 from item as ref_0 where EXISTS ( select ref_1.d_tax as c0, ref_0.i_price as c1, ref_1.d_w_id as c2, ref_0.i_data as c3, ref_1.d_zip as c4, ref_0.i_name as c5, ref_1.d_city as c6, ref_1.d_city as c7, (select s_quantity from stock limit 1 offset 2) as c8, ref_1.d_city as c9, ref_1.d_next_o_id as c10, ref_0.i_im_id as c11, ref_1.d_tax as c12, ref_1.d_street_1 as c13, ref_0.i_data as c14, ref_1.d_name as c15, ref_0.i_id as c16, ref_0.i_im_id as c17, ref_1.d_id as c18, ref_0.i_name as c19, ref_1.d_w_id as c20, ref_1.d_tax as c21, ref_1.d_ytd as c22 from district as ref_1 where EXISTS ( select ref_1.d_state as c0 from orders as ref_2 where ref_1.d_city is NULL ) ) -------------- +-----------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------+ | -> Zero rows (Impossible WHERE) (actual time=0.000..0.000 rows=0 loops=1) | +-----------------------------------------------------------------------------+ 1 row in set, 10 warnings (0.00 sec) Note (Code 1276): Field or reference 'test.ref_0.i_price' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.i_data' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.i_name' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.i_im_id' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.i_data' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.i_id' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.i_im_id' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.i_name' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_1.d_state' of SELECT #4 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_1.d_city' of SELECT #4 was resolved in SELECT #2 -------------- explain analyze select subq_0.c0 as c0, subq_0.c0 as c1, subq_0.c0 as c2, subq_0.c0 as c3, subq_0.c0 as c4, subq_0.c0 as c5, subq_0.c0 as c6, subq_0.c0 as c7, subq_0.c0 as c8, subq_0.c0 as c9, 82 as c10, subq_0.c0 as c11 from (select ref_0.ol_delivery_d as c0 from order_line as ref_0 where EXISTS ( select ref_0.ol_w_id as c0, ref_0.ol_supply_w_id as c1, ref_0.ol_i_id as c2 from new_order as ref_1 where ref_0.ol_amount is not NULL )) as subq_0 where subq_0.c0 is not NULL -------------- ^C^C -- sending "KILL QUERY 11" to server ... ^C -- query aborted +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Remove duplicate ref_0 rows using temporary table (weedout) (cost=238956940.95 rows=2389557268) (actual time=3.781..4076.818 rows=7908 loops=1) -> Inner hash join (no condition) (cost=238956940.95 rows=2389557268) (actual time=3.773..858.009 rows=22504376 loops=1) -> Table scan on ref_0 (cost=30282.20 rows=298732) (actual time=0.018..12.916 rows=7908 loops=1) -> Hash -> Table scan on ref_1 (cost=805.15 rows=7999) (actual time=0.011..2.911 rows=8019 loops=1) | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set, 5 warnings (4.10 sec) Note (Code 1276): Field or reference 'test.ref_0.ol_w_id' of SELECT #3 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_0.ol_supply_w_id' of SELECT #3 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_0.ol_i_id' of SELECT #3 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_0.ol_amount' of SELECT #3 was resolved in SELECT #2 Warning (Code 1317): Query execution was interrupted -------------- explain analyze select ref_0.no_o_id as c0, ref_1.no_w_id as c1, ref_0.no_d_id as c2, ref_0.no_o_id as c3, ref_1.no_w_id as c4, cast(coalesce(ref_1.no_d_id, ref_1.no_w_id) as SIGNED) as c5, ref_1.no_o_id as c6, 36 as c7, ref_1.no_d_id as c8, ref_1.no_w_id as c9 from new_order as ref_0 inner join new_order as ref_1 on (ref_0.no_o_id = ref_1.no_w_id ) where ref_0.no_o_id is not NULL -------------- +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Inner hash join (ref_1.no_w_id = ref_0.no_o_id) (cost=6399211.78 rows=6398400) (actual time=9.301..9.301 rows=0 loops=1) -> Table scan on ref_1 (cost=0.01 rows=7999) (actual time=0.004..2.533 rows=8019 loops=1) -> Hash -> Table scan on ref_0 (cost=805.15 rows=7999) (actual time=0.011..2.355 rows=8019 loops=1) | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) -------------- explain analyze select ref_0.d_name as c0 from district as ref_0 left join item as ref_1 on ((ref_0.d_w_id is not NULL) and (EXISTS ( select ref_0.d_w_id as c0, ref_2.no_w_id as c1, ref_0.d_w_id as c2, ref_1.i_id as c3, ref_2.no_d_id as c4, ref_2.no_d_id as c5, ref_2.no_d_id as c6, ref_2.no_w_id as c7, ref_0.d_tax as c8 from new_order as ref_2 where ref_2.no_d_id is not NULL ))) where (ref_0.d_id is not NULL) and (ref_1.i_price is not NULL) -------------- +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Inner hash join (no condition) (actual time=0.033..107.455 rows=1000000 loops=1) -> Filter: (ref_1.i_price is not null) (cost=1011.56 rows=89658) (actual time=0.003..39.985 rows=100000 loops=1) -> Table scan on ref_1 (cost=1011.56 rows=99620) (actual time=0.002..34.848 rows=100000 loops=1) -> Hash -> Inner hash join (no condition) (actual time=0.019..0.024 rows=10 loops=1) -> Table scan on ref_0 (cost=1.25 rows=10) (actual time=0.003..0.008 rows=10 loops=1) -> Hash -> Limit: 1 row(s) (actual time=0.010..0.010 rows=1 loops=1) -> Table scan on ref_2 (cost=6440394.85 rows=7999) (actual time=0.009..0.009 rows=1 loops=1) | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set, 4 warnings (0.14 sec) Note (Code 1276): Field or reference 'test.ref_0.d_w_id' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.d_w_id' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_1.i_id' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.d_tax' of SELECT #2 was resolved in SELECT #1 -------------- explain analyze select distinct ref_0.ol_o_id as c0, ref_0.ol_delivery_d as c1, ref_0.ol_dist_info as c2 from order_line as ref_0 where ref_0.ol_supply_w_id is not NULL -------------- +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Table scan on (actual time=0.001..9.660 rows=300147 loops=1) -> Temporary table with deduplication (actual time=347.709..369.136 rows=300147 loops=1) -> Table scan on ref_0 (cost=30282.20 rows=298732) (actual time=0.010..114.374 rows=300148 loops=1) | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.42 sec) -------------- explain analyze select subq_0.c0 as c0, subq_0.c0 as c1, 87 as c2, subq_0.c0 as c3, subq_0.c0 as c4, subq_0.c0 as c5 from (select 46 as c0 from district as ref_0 where EXISTS ( select ref_0.d_street_2 as c0, (select ol_d_id from order_line limit 1 offset 4) as c1, ref_1.ol_w_id as c2, (select h_data from history limit 1 offset 3) as c3, 79 as c4 from order_line as ref_1 where EXISTS ( select ref_0.d_w_id as c0, ref_2.w_id as c1, ref_2.w_state as c2, ref_1.ol_amount as c3, ref_1.ol_supply_w_id as c4 from warehouse as ref_2 where ref_1.ol_quantity is NULL ) ) ) as subq_0 where (select i_id from item limit 1 offset 6) is not NULL -------------- +-----------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------+ | -> Zero rows (Impossible WHERE) (actual time=0.000..0.000 rows=0 loops=1) | +-----------------------------------------------------------------------------+ 1 row in set, 5 warnings (0.00 sec) Note (Code 1276): Field or reference 'test.ref_0.d_street_2' of SELECT #3 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_0.d_w_id' of SELECT #6 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_1.ol_amount' of SELECT #6 was resolved in SELECT #3 Note (Code 1276): Field or reference 'test.ref_1.ol_supply_w_id' of SELECT #6 was resolved in SELECT #3 Note (Code 1276): Field or reference 'test.ref_1.ol_quantity' of SELECT #6 was resolved in SELECT #3 -------------- explain analyze select cast(coalesce(subq_0.c1, case when subq_0.c3 is not NULL then subq_0.c0 else subq_0.c0 end ) as character(24)) as c0 from (select ref_0.s_dist_03 as c0, ref_0.s_dist_01 as c1, ref_0.s_data as c2, ref_1.s_dist_01 as c3, ref_1.s_dist_09 as c4 from stock as ref_0 left join stock as ref_1 on (ref_0.s_remote_cnt = ref_1.s_w_id ) where (ref_1.s_order_cnt is not NULL) and (ref_0.s_order_cnt is not NULL) ) as subq_0 where subq_0.c4 is NULL -------------- +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Inner hash join (ref_0.s_remote_cnt = ref_1.s_w_id) (cost=86430891.46 rows=86418358) (actual time=41.995..41.995 rows=0 loops=1) -> Table scan on ref_0 (cost=0.34 rows=97990) (never executed) -> Hash -> Filter: ((ref_1.s_dist_09 is null) and (ref_1.s_order_cnt is not null)) (cost=10497.20 rows=8819) (actual time=41.989..41.989 rows=0 loops=1) -> Table scan on ref_1 (cost=10497.20 rows=97990) (actual time=0.008..38.094 rows=100000 loops=1) | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.05 sec) -------------- explain analyze select subq_0.c3 as c0 from (select ref_1.no_o_id as c0, 48 as c1, ref_1.no_d_id as c2, ref_0.no_d_id as c3, ref_1.no_o_id as c4, ref_0.no_o_id as c5, ref_1.no_w_id as c6, ref_0.no_o_id as c7, ref_1.no_w_id as c8, ref_0.no_o_id as c9, ref_0.no_d_id as c10, ref_0.no_w_id as c11, case when ref_1.no_d_id is not NULL then ref_1.no_w_id else ref_1.no_w_id end as c12 from new_order as ref_0 inner join new_order as ref_1 on (ref_0.no_o_id = ref_1.no_w_id ) where ref_1.no_w_id is not NULL ) as subq_0 where 12 is not NULL -------------- +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Inner hash join (ref_1.no_w_id = ref_0.no_o_id) (cost=6399211.78 rows=6398400) (actual time=9.991..9.991 rows=0 loops=1) -> Table scan on ref_1 (cost=0.01 rows=7999) (actual time=0.004..2.641 rows=8019 loops=1) -> Hash -> Table scan on ref_0 (cost=805.15 rows=7999) (actual time=0.010..2.600 rows=8019 loops=1) | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) -------------- explain analyze select cast(nullif(98, 71) as SIGNED) as c0, (select s_dist_08 from stock limit 1 offset 4) as c1 from district as ref_0 where ((ref_0.d_street_2 is NULL) or (((ref_0.d_tax is NULL) and (ref_0.d_ytd is NULL)) and ((ref_0.d_street_2 is NULL) and (ref_0.d_state is NULL)))) or (EXISTS ( select ref_0.d_zip as c0 from order_line as ref_1 where EXISTS ( select ref_0.d_w_id as c0, ref_0.d_street_2 as c1, ref_2.h_c_id as c2, ref_1.ol_dist_info as c3, ref_0.d_state as c4, ref_1.ol_dist_info as c5, ref_2.h_c_d_id as c6, ref_2.h_amount as c7, ref_2.h_c_w_id as c8, 87 as c9 from history as ref_2 where (ref_1.ol_i_id is NULL) and (ref_1.ol_i_id is NULL) ))) -------------- +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Filter: exists(select #3) (cost=1.25 rows=10) (actual time=0.027..0.027 rows=0 loops=1) -> Table scan on ref_0 (cost=1.25 rows=10) (actual time=0.017..0.023 rows=10 loops=1) -> Select #3 (subquery in condition; dependent) -> Zero rows (Impossible WHERE) (actual time=0.000..0.000 rows=0 loops=10) -> Select #2 (subquery in projection; run only once) -> Limit/Offset: 1/4 row(s) (never executed) -> Table scan on stock (cost=10497.20 rows=97990) (never executed) | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set, 8 warnings (0.00 sec) Note (Code 1276): Field or reference 'test.ref_0.d_zip' of SELECT #3 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.d_w_id' of SELECT #4 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.d_street_2' of SELECT #4 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_1.ol_dist_info' of SELECT #4 was resolved in SELECT #3 Note (Code 1276): Field or reference 'test.ref_0.d_state' of SELECT #4 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_1.ol_dist_info' of SELECT #4 was resolved in SELECT #3 Note (Code 1276): Field or reference 'test.ref_1.ol_i_id' of SELECT #4 was resolved in SELECT #3 Note (Code 1276): Field or reference 'test.ref_1.ol_i_id' of SELECT #4 was resolved in SELECT #3 -------------- explain analyze select subq_0.c1 as c0, subq_0.c1 as c1 from (select ref_0.ol_delivery_d as c0, ref_0.ol_w_id as c1, 73 as c2 from order_line as ref_0 where EXISTS ( select ref_0.ol_supply_w_id as c0, ref_0.ol_w_id as c1 from district as ref_1 where EXISTS ( select (select w_zip from warehouse limit 1 offset 5) as c0, ref_0.ol_number as c1, ref_1.d_tax as c2, ref_1.d_w_id as c3, ref_0.ol_amount as c4, ref_0.ol_w_id as c5, ref_0.ol_quantity as c6 from district as ref_2 where (select w_tax from warehouse limit 1 offset 3) is not NULL ) )) as subq_0 where (subq_0.c0 is not NULL) and (subq_0.c1 is not NULL) -------------- +-----------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------+ | -> Zero rows (Impossible WHERE) (actual time=0.000..0.000 rows=0 loops=1) | +-----------------------------------------------------------------------------+ 1 row in set, 8 warnings (0.00 sec) Note (Code 1276): Field or reference 'test.ref_0.ol_supply_w_id' of SELECT #3 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_0.ol_w_id' of SELECT #3 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_0.ol_number' of SELECT #4 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_1.d_tax' of SELECT #4 was resolved in SELECT #3 Note (Code 1276): Field or reference 'test.ref_1.d_w_id' of SELECT #4 was resolved in SELECT #3 Note (Code 1276): Field or reference 'test.ref_0.ol_amount' of SELECT #4 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_0.ol_w_id' of SELECT #4 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_0.ol_quantity' of SELECT #4 was resolved in SELECT #2 -------------- explain analyze select subq_0.c1 as c0, subq_0.c1 as c1, subq_0.c0 as c2, subq_0.c0 as c3, subq_0.c0 as c4, subq_0.c1 as c5, subq_0.c0 as c6 from (select ref_0.i_name as c0, ref_1.ol_d_id as c1 from item as ref_0 left join order_line as ref_1 on (ref_0.i_im_id = ref_1.ol_w_id ) where ref_1.ol_amount is not NULL) as subq_0 where subq_0.c1 is NULL -------------- +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Inner hash join (ref_0.i_im_id = ref_1.ol_w_id) (cost=267867746.86 rows=267837129) (actual time=108.662..108.662 rows=0 loops=1) -> Table scan on ref_0 (cost=0.05 rows=99620) (never executed) -> Hash -> Filter: ((ref_1.ol_d_id is null) and (ref_1.ol_amount is not null)) (cost=30282.20 rows=26886) (actual time=108.657..108.657 rows=0 loops=1) -> Table scan on ref_1 (cost=30282.20 rows=298732) (actual time=0.008..96.829 rows=300148 loops=1) | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.11 sec) -------------- explain analyze select ref_0.no_w_id as c0, cast(nullif(subq_0.c4, ref_0.no_w_id) as SIGNED) as c1, subq_0.c1 as c2, subq_0.c1 as c3, cast(coalesce(case when subq_0.c6 is not NULL then subq_0.c0 else subq_0.c0 end , ref_0.no_o_id) as SIGNED) as c4 from new_order as ref_0 left join (select ref_1.no_w_id as c0, ref_1.no_o_id as c1, 33 as c2, ref_1.no_d_id as c3, ref_1.no_o_id as c4, ref_1.no_w_id as c5, ref_1.no_o_id as c6 from new_order as ref_1 where ref_1.no_o_id is not NULL) as subq_0 on (ref_0.no_o_id = subq_0.c0 ) where subq_0.c0 is not NULL -------------- +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Inner hash join (ref_0.no_o_id = ref_1.no_w_id) (cost=5759371.64 rows=5758560) (actual time=9.316..9.316 rows=0 loops=1) -> Table scan on ref_0 (cost=0.01 rows=7999) (actual time=0.003..2.423 rows=8019 loops=1) -> Hash -> Filter: (ref_1.no_w_id is not null) (cost=805.15 rows=7199) (actual time=0.011..2.875 rows=8019 loops=1) -> Table scan on ref_1 (cost=805.15 rows=7999) (actual time=0.010..2.448 rows=8019 loops=1) | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) -------------- explain analyze select subq_1.c1 as c0, subq_1.c6 as c1, 5 as c2 from (select ref_2.d_tax as c0, 3 as c1, ref_2.d_id as c2, (select no_w_id from new_order limit 1 offset 2) as c3, subq_0.c10 as c4, subq_0.c15 as c5, subq_0.c15 as c6, subq_0.c6 as c7, (select s_data from stock limit 1 offset 1) as c8, ref_2.d_street_2 as c9, ref_2.d_w_id as c10 from (select ref_0.ol_delivery_d as c0, ref_0.ol_dist_info as c1, ref_0.ol_dist_info as c2, ref_0.ol_quantity as c3, ref_0.ol_number as c4, ref_0.ol_d_id as c5, ref_0.ol_w_id as c6, ref_0.ol_d_id as c7, ref_0.ol_w_id as c8, ref_0.ol_d_id as c9, ref_0.ol_quantity as c10, ref_0.ol_supply_w_id as c11, ref_0.ol_dist_info as c12, ref_0.ol_quantity as c13, ref_0.ol_dist_info as c14, ref_0.ol_supply_w_id as c15 from order_line as ref_0 where EXISTS ( select (select w_zip from warehouse limit 1 offset 5) as c0, ref_1.ol_number as c1, ref_0.ol_delivery_d as c2, ref_0.ol_number as c3, ref_1.ol_supply_w_id as c4, ref_1.ol_i_id as c5, ref_1.ol_amount as c6 from order_line as ref_1 where ref_0.ol_i_id is not NULL ) ) as subq_0 left join district as ref_2 on (subq_0.c15 = ref_2.d_w_id ) where EXISTS ( select ref_2.d_street_1 as c0, ref_3.s_order_cnt as c1, subq_0.c5 as c2, ref_2.d_city as c3, ref_3.s_remote_cnt as c4, subq_0.c13 as c5, subq_0.c6 as c6, subq_0.c13 as c7, subq_0.c15 as c8, ref_2.d_ytd as c9, ref_2.d_city as c10, subq_0.c6 as c11, subq_0.c1 as c12, 86 as c13, ref_2.d_next_o_id as c14, ref_3.s_dist_06 as c15, subq_0.c7 as c16, ref_3.s_dist_02 as c17, subq_0.c3 as c18, subq_0.c11 as c19 from stock as ref_3 where ref_3.s_dist_07 is NULL ) ) as subq_1 where subq_1.c6 is not NULL -------------- +-----------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------+ | -> Zero rows (Impossible WHERE) (actual time=0.000..0.000 rows=0 loops=1) | +-----------------------------------------------------------------------------+ 1 row in set, 18 warnings (0.00 sec) Note (Code 1276): Field or reference 'test.ref_0.ol_delivery_d' of SELECT #6 was resolved in SELECT #5 Note (Code 1276): Field or reference 'test.ref_0.ol_number' of SELECT #6 was resolved in SELECT #5 Note (Code 1276): Field or reference 'test.ref_0.ol_i_id' of SELECT #6 was resolved in SELECT #5 Note (Code 1276): Field or reference 'test.ref_2.d_street_1' of SELECT #8 was resolved in SELECT #2 Note (Code 1276): Field or reference 'subq_0.c5' of SELECT #8 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_2.d_city' of SELECT #8 was resolved in SELECT #2 Note (Code 1276): Field or reference 'subq_0.c13' of SELECT #8 was resolved in SELECT #2 Note (Code 1276): Field or reference 'subq_0.c6' of SELECT #8 was resolved in SELECT #2 Note (Code 1276): Field or reference 'subq_0.c13' of SELECT #8 was resolved in SELECT #2 Note (Code 1276): Field or reference 'subq_0.c15' of SELECT #8 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_2.d_ytd' of SELECT #8 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_2.d_city' of SELECT #8 was resolved in SELECT #2 Note (Code 1276): Field or reference 'subq_0.c6' of SELECT #8 was resolved in SELECT #2 Note (Code 1276): Field or reference 'subq_0.c1' of SELECT #8 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_2.d_next_o_id' of SELECT #8 was resolved in SELECT #2 Note (Code 1276): Field or reference 'subq_0.c7' of SELECT #8 was resolved in SELECT #2 Note (Code 1276): Field or reference 'subq_0.c3' of SELECT #8 was resolved in SELECT #2 Note (Code 1276): Field or reference 'subq_0.c11' of SELECT #8 was resolved in SELECT #2 -------------- explain analyze select distinct ref_0.ol_number as c0, ref_0.ol_dist_info as c1, ref_0.ol_quantity as c2, ref_0.ol_number as c3, ref_0.ol_i_id as c4, ref_0.ol_d_id as c5 from order_line as ref_0 where ref_0.ol_o_id is not NULL -------------- +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Table scan on (actual time=0.001..9.263 rows=300148 loops=1) -> Temporary table with deduplication (actual time=282.335..303.586 rows=300148 loops=1) -> Table scan on ref_0 (cost=30282.20 rows=298732) (actual time=0.008..118.818 rows=300148 loops=1) | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.35 sec) -------------- explain analyze select ref_0.d_id as c0, ref_0.d_state as c1, ref_0.d_street_1 as c2 from district as ref_0 where EXISTS ( select ref_1.ol_supply_w_id as c0, ref_0.d_city as c1, ref_0.d_street_1 as c2, case when 67 is not NULL then ref_1.ol_amount else ref_1.ol_amount end as c3, ref_0.d_state as c4 from order_line as ref_1 where EXISTS ( select ref_0.d_ytd as c0, (select c_w_id from customer limit 1 offset 1) as c1, ref_1.ol_dist_info as c2, ref_2.ol_w_id as c3, ref_1.ol_amount as c4, ref_1.ol_number as c5, ref_2.ol_number as c6, ref_2.ol_amount as c7, ref_2.ol_d_id as c8 from order_line as ref_2 where ref_0.d_state is NULL )) -------------- +-----------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------+ | -> Zero rows (Impossible WHERE) (actual time=0.000..0.000 rows=0 loops=1) | +-----------------------------------------------------------------------------+ 1 row in set, 8 warnings (0.00 sec) Note (Code 1276): Field or reference 'test.ref_0.d_city' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.d_street_1' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.d_state' of SELECT #2 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_0.d_ytd' of SELECT #3 was resolved in SELECT #1 Note (Code 1276): Field or reference 'test.ref_1.ol_dist_info' of SELECT #3 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_1.ol_amount' of SELECT #3 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_1.ol_number' of SELECT #3 was resolved in SELECT #2 Note (Code 1276): Field or reference 'test.ref_0.d_state' of SELECT #3 was resolved in SELECT #1 -------------- explain analyze select subq_0.c2 as c0 from (select ref_1.ol_supply_w_id as c0, 32 as c1, ref_1.ol_amount as c2 from stock as ref_0 left join order_line as ref_1 on (ref_0.s_dist_03 = ref_1.ol_dist_info ) where ref_1.ol_w_id is not NULL ) as subq_0 where subq_0.c2 is NULL -------------- +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Inner hash join (ref_0.s_dist_03 = ref_1.ol_dist_info) (cost=263487998.66 rows=263454731) (actual time=110.463..110.463 rows=0 loops=1) -> Table scan on ref_0 (cost=0.15 rows=97990) (never executed) -> Hash -> Filter: ((ref_1.ol_amount is null) and (ref_1.ol_w_id is not null)) (cost=30282.20 rows=26886) (actual time=110.457..110.457 rows=0 loops=1) -> Table scan on ref_1 (cost=30282.20 rows=298732) (actual time=0.014..97.366 rows=300148 loops=1) | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.11 sec)