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
				('27', '14', '29', '30', '26', '12', '28')
			and c_acctbal > (
				select
					avg(c_acctbal)
				from
					customer
				where
					c_acctbal > 0.00
					and substring(c_phone from 1 for 2) in
						('27', '14', '29', '30', '26', '12', '28')
			)
			and not exists (
				select
					*
				from
					orders
				where
					o_custkey = c_custkey
			)
	) as vip
group by
	cntrycode
order by
	cntrycode;
                                                                     QUERY PLAN                                                                      
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize GroupAggregate  (cost=122495.97..122502.74 rows=25 width=72)
   Group Key: (SUBSTRING(customer.c_phone FROM 1 FOR 2))
   InitPlan 1 (returns $1)
     ->  Finalize Aggregate  (cost=50276.32..50276.33 rows=1 width=32)
           ->  Gather  (cost=50276.10..50276.31 rows=2 width=32)
                 Workers Planned: 2
                 ->  Partial Aggregate  (cost=49276.10..49276.11 rows=1 width=32)
                       ->  Parallel Bitmap Heap Scan on customer customer_1  (cost=8765.30..48872.20 rows=161558 width=6)
                             Recheck Cond: ((SUBSTRING(c_phone FROM 1 FOR 2) = ANY ('{27,14,29,30,26,12,28}'::text[])) AND (c_acctbal > 0.00))
                             ->  Bitmap Index Scan on customer_c_phone_c_acctbal_c_custkey_idx  (cost=0.00..8668.36 rows=387740 width=0)
                                   Index Cond: ((SUBSTRING(c_phone FROM 1 FOR 2) = ANY ('{27,14,29,30,26,12,28}'::text[])) AND (c_acctbal > 0.00))
   ->  Gather Merge  (cost=72219.64..72225.47 rows=50 width=72)
         Workers Planned: 2
         Params Evaluated: $1
         ->  Sort  (cost=71219.61..71219.68 rows=25 width=72)
               Sort Key: (SUBSTRING(customer.c_phone FROM 1 FOR 2))
               ->  Partial HashAggregate  (cost=71218.60..71219.03 rows=25 width=72)
                     Group Key: SUBSTRING(customer.c_phone FROM 1 FOR 2)
                     ->  Nested Loop Anti Join  (cost=3365.98..71028.69 rows=25321 width=38)
                           ->  Parallel Bitmap Heap Scan on customer  (cost=3365.54..40792.19 rows=59453 width=26)
                                 Recheck Cond: ((SUBSTRING(c_phone FROM 1 FOR 2) = ANY ('{27,14,29,30,26,12,28}'::text[])) AND (c_acctbal > $1))
                                 ->  Bitmap Index Scan on customer_c_phone_c_acctbal_c_custkey_idx  (cost=0.00..3329.87 rows=142688 width=0)
                                       Index Cond: ((SUBSTRING(c_phone FROM 1 FOR 2) = ANY ('{27,14,29,30,26,12,28}'::text[])) AND (c_acctbal > $1))
                           ->  Index Only Scan using orders_o_custkey_idx on orders  (cost=0.43..1.13 rows=17 width=4)
                                 Index Cond: (o_custkey = customer.c_custkey)
(25 rows)

COMMIT;
COMMIT