BEGIN;
BEGIN
EXPLAIN
select
	cntrycode,
	count(*) as numcust,
	sum(c_acctbal) as totacctbal
from
	(
		select
			substring(c_phone from 1 for 2) as cntrycode,
			c_acctbal
		from
			customer
		where
			substring(c_phone from 1 for 2) in
				('21', '33', '18', '10', '32', '28', '11')
			and c_acctbal > (
				select
					avg(c_acctbal)
				from
					customer
				where
					c_acctbal > 0.00
					and substring(c_phone from 1 for 2) in
						('21', '33', '18', '10', '32', '28', '11')
			)
			and not exists (
				select
					*
				from
					orders
				where
					o_custkey = c_custkey
			)
	) as vip
group by
	cntrycode
order by
	cntrycode;
                                                                     QUERY PLAN                                                                      
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize GroupAggregate  (cost=121532.35..121539.12 rows=25 width=72)
   Group Key: (SUBSTRING(customer.c_phone FROM 1 FOR 2))
   InitPlan 1 (returns $1)
     ->  Finalize Aggregate  (cost=49997.06..49997.07 rows=1 width=32)
           ->  Gather  (cost=49996.84..49997.05 rows=2 width=32)
                 Workers Planned: 2
                 ->  Partial Aggregate  (cost=48996.84..48996.85 rows=1 width=32)
                       ->  Parallel Bitmap Heap Scan on customer customer_1  (cost=8589.12..48601.91 rows=157973 width=6)
                             Recheck Cond: ((SUBSTRING(c_phone FROM 1 FOR 2) = ANY ('{21,33,18,10,32,28,11}'::text[])) AND (c_acctbal > 0.00))
                             ->  Bitmap Index Scan on customer_c_phone_c_acctbal_c_custkey_idx  (cost=0.00..8494.33 rows=379135 width=0)
                                   Index Cond: ((SUBSTRING(c_phone FROM 1 FOR 2) = ANY ('{21,33,18,10,32,28,11}'::text[])) AND (c_acctbal > 0.00))
   ->  Gather Merge  (cost=71535.28..71541.11 rows=50 width=72)
         Workers Planned: 2
         Params Evaluated: $1
         ->  Sort  (cost=70535.26..70535.32 rows=25 width=72)
               Sort Key: (SUBSTRING(customer.c_phone FROM 1 FOR 2))
               ->  Partial HashAggregate  (cost=70534.24..70534.68 rows=25 width=72)
                     Group Key: SUBSTRING(customer.c_phone FROM 1 FOR 2)
                     ->  Nested Loop Anti Join  (cost=3309.55..70348.55 rows=24759 width=38)
                           ->  Parallel Bitmap Heap Scan on customer  (cost=3309.11..40701.12 rows=58134 width=26)
                                 Recheck Cond: ((SUBSTRING(c_phone FROM 1 FOR 2) = ANY ('{21,33,18,10,32,28,11}'::text[])) AND (c_acctbal > $1))
                                 ->  Bitmap Index Scan on customer_c_phone_c_acctbal_c_custkey_idx  (cost=0.00..3274.23 rows=139521 width=0)
                                       Index Cond: ((SUBSTRING(c_phone FROM 1 FOR 2) = ANY ('{21,33,18,10,32,28,11}'::text[])) AND (c_acctbal > $1))
                           ->  Index Only Scan using orders_o_custkey_idx on orders  (cost=0.43..1.15 rows=17 width=4)
                                 Index Cond: (o_custkey = customer.c_custkey)
(25 rows)

COMMIT;
COMMIT