Qualified Subquery Rewrite

Copyright © 2024 PawSQL

explain analyze 
select * from orders where o_orderdate < all(select o_orderdate from orders where o_custkey > 100)
;

-> Filter: <not>((orders.O_ORDERDATE >= (select #2)))  (cost=13846.04 rows=133181) (actual time=672.536..672.536 rows=0 loops=1)
    -> Table scan on orders  (cost=13846.04 rows=199762) (actual time=2.460..408.819 rows=201800 loops=1)
    -> Select #2 (subquery in condition; run only once)
        -> Aggregate: min(orders.O_ORDERDATE)  (cost=30281.45 rows=1) (actual time=214.765..214.765 rows=1 loops=1)
            -> Filter: (orders.O_CUSTKEY > 100)  (cost=20293.35 rows=99881) (actual time=0.237..202.460 rows=199252 loops=1)
                -> Covering index range scan on orders using comp_idx over (100 < O_CUSTKEY)  (cost=20293.35 rows=99881) (actual time=0.138..189.628 rows=199252 loops=1)

explain analyze 
select * from orders where o_orderdate < (select min(o_orderdate) from orders where o_custkey > 100) 
and not exists(select 1 from orders where o_custkey > 100 and o_orderdate is null)
;

-> Filter: (orders.O_ORDERDATE < (select #2))  (cost=0.77 rows=1) (actual time=0.774..0.774 rows=0 loops=1)
    -> Index range scan on orders using orders_date_idx over (O_ORDERDATE < '1900-01-01')  (cost=0.77 rows=1) (actual time=0.774..0.774 rows=0 loops=1)
    -> Select #2 (subquery in condition; run only once)
        -> Aggregate: min(orders.O_ORDERDATE)  (cost=30185.53 rows=1) (actual time=106.362..106.362 rows=1 loops=1)
            -> Filter: (orders.O_CUSTKEY > 100)  (cost=20197.43 rows=99881) (actual time=0.010..93.979 rows=199252 loops=1)
                -> Covering index range scan on orders using comp_idx over (100 < O_CUSTKEY)  (cost=20197.43 rows=99881) (actual time=0.009..81.032 rows=199252 loops=1)

​

explain analyze 
select * from orders where o_orderdate < (select o_orderdate from orders where o_custkey > 100 order by o_orderdate asc limit 1) ;

-> Filter: (orders.O_ORDERDATE < (select #2))  (cost=0.77 rows=1) (actual time=0.008..0.008 rows=0 loops=1)
    -> Index range scan on orders using orders_date_idx over (O_ORDERDATE < '1900-01-01')  (cost=0.77 rows=1) (actual time=0.008..0.008 rows=0 loops=1)
    -> Select #2 (subquery in condition; run only once)
        -> Limit: 1 row(s)  (cost=0.10 rows=1) (actual time=0.022..0.022 rows=1 loops=1)
            -> Filter: (orders.O_CUSTKEY > 100)  (cost=0.10 rows=1) (actual time=0.021..0.021 rows=1 loops=1)
                -> Index scan on orders using orders_date_idx  (cost=0.10 rows=2) (actual time=0.020..0.020 rows=1 loops=1)