accouting subquery's cost into total cost

[zhouqingqing]

PhysicOrder  (inccost=24.1, cost=0.1, rows=1) (actual rows=0)
    Output: supplier.s_name[0],supplier.s_address[1]
    Order by: supplier.s_name[0]
    -> PhysicHashJoin  (inccost=24, cost=13, rows=1) (actual rows=0)
        Output: supplier.s_name[1],supplier.s_address[2]
        Filter: supplier.s_nationkey[3]=nation.n_nationkey[0]
        -> PhysicScanTable nation (inccost=1, cost=1, rows=1) (actual rows=1)
            Output: nation.n_nationkey[0]
            Filter: nation.n_name[1]='CANADA'
        -> PhysicScanTable supplier (inccost=10, cost=10, rows=10) (actual rows=10)
            Output: supplier.s_name[1],supplier.s_address[2],supplier.s_nationkey[3]
            Filter: supplier.s_suppkey[0] in @1
            <InSubqueryExpr> cached 1
                -> PhysicFilter  (inccost=14410, cost=753, rows=753) (actual rows=47)

Though this does not affect the correctness since subquery is optimized separately but better to fix for consistency.

[9DemonFox]

SQL

select	(select count(O.o_orderkey) 
	 	from orders O 
	 	where O.o_custkey = 37) as OrderCount, 
	c_name,
	(select count(O2.o_custkey) 
		from orders O2
		where O2.o_custkey = 26) as CustPhone
from customer C
where exists
	(
		select *
		from nation N
		where N.n_name like '%A%'
		and exists(
			select * 
			from region R
			where N.n_regionkey = R.r_regionkey
			and R.r_regionkey<2
		)
	)

physic plan

order by c_name
Total cost: 3136.59, memory=4950
PhysicOrder  (inccost=3136.59, cost=766.59, rows=150, memory=4950) (actual rows=150)
    Output: c_name[0],{@1}[1],{@2}[2]
    <ScalarSubqueryExpr> cached 1
        -> PhysicHashAgg  (inccost=1541, cost=3, rows=1, memory=2) (actual rows=1)
            Output: {sum({count(o_orderkey)})}[0]
            Aggregates: sum({count(o_orderkey)}[0])
            -> PhysicGather Threads: 10 (inccost=1538, cost=10, rows=1) (actual rows=10)
                Output: {count(o_orderkey)}[0]
                -> PhysicHashAgg  (inccost=1528, cost=28, rows=1, memory=8) (actual rows=1, loops=10)
                    Output: {count(o_orderkey)}[0]
                    Aggregates: count(o_orderkey[0])
                    -> PhysicScanTable orders as o (inccost=1500, cost=1500, rows=26) (actual rows=2, loops=10)
                        Output: o_orderkey[0]
                        Filter: o_custkey[1]=37

    <ScalarSubqueryExpr> cached 2
        -> PhysicHashAgg  (inccost=1525, cost=3, rows=1, memory=2) (actual rows=1)
            Output: {sum({count(o_custkey)})}[0]
            Aggregates: sum({count(o_custkey)}[0])
            -> PhysicGather Threads: 10 (inccost=1522, cost=10, rows=1) (actual rows=10)
                Output: {count(o_custkey)}[0]
                -> PhysicHashAgg  (inccost=1512, cost=12, rows=1, memory=8) (actual rows=1, loops=10)
                    Output: {count(o_custkey)}[0]
                    Aggregates: count(o_custkey[0])
                    -> PhysicScanTable orders as o2 (inccost=1500, cost=1500, rows=10) (actual rows=1, loops=10)
                        Output: o_custkey[1]
                        Filter: o_custkey[1]=26

    Order by: c_name[0]
    -> PhysicGather Threads: 10 (inccost=2370, cost=1500, rows=150) (actual rows=150)
        Output: c_name[1],@1,@2
        <ScalarSubqueryExpr> cached 1
            -> PhysicHashAgg  (inccost=1541, cost=3, rows=1, memory=2) (actual rows=1)
                Output: {sum({count(o_orderkey)})}[0]
                Aggregates: sum({count(o_orderkey)}[0])
                -> PhysicGather Threads: 10 (inccost=1538, cost=10, rows=1) (actual rows=10)
                    Output: {count(o_orderkey)}[0]
                    -> PhysicHashAgg  (inccost=1528, cost=28, rows=1, memory=8) (actual rows=1, loops=10)
                        Output: {count(o_orderkey)}[0]
                        Aggregates: count(o_orderkey[0])
                        -> PhysicScanTable orders as o (inccost=1500, cost=1500, rows=26) (actual rows=2, loops=10)
                            Output: o_orderkey[0]
                            Filter: o_custkey[1]=37

        <ScalarSubqueryExpr> cached 2
            -> PhysicHashAgg  (inccost=1525, cost=3, rows=1, memory=2) (actual rows=1)
                Output: {sum({count(o_custkey)})}[0]
                Aggregates: sum({count(o_custkey)}[0])
                -> PhysicGather Threads: 10 (inccost=1522, cost=10, rows=1) (actual rows=10)
                    Output: {count(o_custkey)}[0]
                    -> PhysicHashAgg  (inccost=1512, cost=12, rows=1, memory=8) (actual rows=1, loops=10)
                        Output: {count(o_custkey)}[0]
                        Aggregates: count(o_custkey[0])
                        -> PhysicScanTable orders as o2 (inccost=1500, cost=1500, rows=10) (actual rows=1, loops=10)
                            Output: o_custkey[1]
                            Filter: o_custkey[1]=26

        -> PhysicScanTable customer as c (inccost=510, cost=150, rows=150) (actual rows=15, loops=10)
            Output: c_name[1],@1,@2
            <ScalarSubqueryExpr> cached 1
                -> PhysicHashAgg  (inccost=1541, cost=3, rows=1, memory=2) (actual rows=1)
                    Output: {sum({count(o_orderkey)})}[0]
                    Aggregates: sum({count(o_orderkey)}[0])
                    -> PhysicGather Threads: 10 (inccost=1538, cost=10, rows=1) (actual rows=10)
                        Output: {count(o_orderkey)}[0]
                        -> PhysicHashAgg  (inccost=1528, cost=28, rows=1, memory=8) (actual rows=1, loops=10)
                            Output: {count(o_orderkey)}[0]
                            Aggregates: count(o_orderkey[0])
                            -> PhysicScanTable orders as o (inccost=1500, cost=1500, rows=26) (actual rows=2, loops=10)
                                Output: o_orderkey[0]
                                Filter: o_custkey[1]=37

            <ScalarSubqueryExpr> cached 2
                -> PhysicHashAgg  (inccost=1525, cost=3, rows=1, memory=2) (actual rows=1)
                    Output: {sum({count(o_custkey)})}[0]
                    Aggregates: sum({count(o_custkey)}[0])
                    -> PhysicGather Threads: 10 (inccost=1522, cost=10, rows=1) (actual rows=10)
                        Output: {count(o_custkey)}[0]
                        -> PhysicHashAgg  (inccost=1512, cost=12, rows=1, memory=8) (actual rows=1, loops=10)
                            Output: {count(o_custkey)}[0]
                            Aggregates: count(o_custkey[0])
                            -> PhysicScanTable orders as o2 (inccost=1500, cost=1500, rows=10) (actual rows=1, loops=10)
                                Output: o_custkey[1]
                                Filter: o_custkey[1]=26

            Filter: @3
            <ExistSubqueryExpr> cached 3
                -> PhysicFilter  (inccost=360, cost=10, rows=10) (actual rows=8)
                    Output: n_nationkey[0],n_name[1],n_regionkey[2],n_comment[3]
                    Filter: {#marker@4}[4]
                    -> PhysicMarkJoin Left (inccost=350, cost=50, rows=10) (actual rows=21)
                        Output: n_nationkey[0],n_name[1],n_regionkey[2],n_comment[3],{#marker@4}[4]
                        Filter: n_regionkey[2]=r_regionkey[4]
                        -> PhysicGather Threads: 10 (inccost=275, cost=250, rows=25) (actual rows=21)
                            Output: n_nationkey[0],n_name[1],n_regionkey[2],n_comment[3]
                            -> PhysicScanTable nation as n (inccost=25, cost=25, rows=25) (actual rows=21)
                                Output: n_nationkey[0],n_name[1],n_regionkey[2],n_comment[3]
                                Filter: n_name[1] like '%A%'
                        -> PhysicGather Threads: 10 (inccost=25, cost=20, rows=2) (actual rows=2, loops=21)
                            Output: r_regionkey[0]
                            -> PhysicScanTable region as r (inccost=5, cost=5, rows=2) (actual rows=2)
                                Output: r_regionkey[0]
                                Filter: r_regionkey[0]<2

[9DemonFox]

What cause this is the non-corelated subqueries in selection clause are always pushed down.