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
				('31', '11', '22', '10', '30', '15', '25')
			and c_acctbal > (
				select
					avg(c_acctbal)
				from
					customer
				where
					c_acctbal > 0.00
					and substring(c_phone from 1 for 2) in
						('31', '11', '22', '10', '30', '15', '25')
			)
			and not exists (
				select
					*
				from
					orders
				where
					o_custkey = c_custkey
			)
	) as vip
group by
	cntrycode
order by
	cntrycode;
                                                                     QUERY PLAN                                                                      
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize GroupAggregate  (cost=121903.27..121910.04 rows=25 width=72)
   Group Key: (SUBSTRING(customer.c_phone FROM 1 FOR 2))
   InitPlan 1 (returns $1)
     ->  Finalize Aggregate  (cost=50111.54..50111.55 rows=1 width=32)
           ->  Gather  (cost=50111.32..50111.53 rows=2 width=32)
                 Workers Planned: 2
                 ->  Partial Aggregate  (cost=49111.32..49111.33 rows=1 width=32)
                       ->  Parallel Bitmap Heap Scan on customer customer_1  (cost=8665.62..48713.08 rows=159294 width=6)
                             Recheck Cond: ((SUBSTRING(c_phone FROM 1 FOR 2) = ANY ('{31,11,22,10,30,15,25}'::text[])) AND (c_acctbal > 0.00))
                             ->  Bitmap Index Scan on customer_c_phone_c_acctbal_c_custkey_idx  (cost=0.00..8570.04 rows=382305 width=0)
                                   Index Cond: ((SUBSTRING(c_phone FROM 1 FOR 2) = ANY ('{31,11,22,10,30,15,25}'::text[])) AND (c_acctbal > 0.00))
   ->  Gather Merge  (cost=71791.72..71797.55 rows=50 width=72)
         Workers Planned: 2
         Params Evaluated: $1
         ->  Sort  (cost=70791.69..70791.76 rows=25 width=72)
               Sort Key: (SUBSTRING(customer.c_phone FROM 1 FOR 2))
               ->  Partial HashAggregate  (cost=70790.68..70791.11 rows=25 width=72)
                     Group Key: SUBSTRING(customer.c_phone FROM 1 FOR 2)
                     ->  Nested Loop Anti Join  (cost=3321.46..70603.43 rows=24966 width=38)
                           ->  Parallel Bitmap Heap Scan on customer  (cost=3321.02..40725.80 rows=58620 width=26)
                                 Recheck Cond: ((SUBSTRING(c_phone FROM 1 FOR 2) = ANY ('{31,11,22,10,30,15,25}'::text[])) AND (c_acctbal > $1))
                                 ->  Bitmap Index Scan on customer_c_phone_c_acctbal_c_custkey_idx  (cost=0.00..3285.85 rows=140688 width=0)
                                       Index Cond: ((SUBSTRING(c_phone FROM 1 FOR 2) = ANY ('{31,11,22,10,30,15,25}'::text[])) AND (c_acctbal > $1))
                           ->  Index Only Scan using orders_o_custkey_idx on orders  (cost=0.43..1.14 rows=17 width=4)
                                 Index Cond: (o_custkey = customer.c_custkey)
(25 rows)

COMMIT;
COMMIT