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
				('16', '13', '26', '19', '28', '22', '17')
			and c_acctbal > (
				select
					avg(c_acctbal)
				from
					customer
				where
					c_acctbal > 0.00
					and substring(c_phone from 1 for 2) in
						('16', '13', '26', '19', '28', '22', '17')
			)
			and not exists (
				select
					*
				from
					orders
				where
					o_custkey = c_custkey
			)
	) as vip
group by
	cntrycode
order by
	cntrycode;
                                                                     QUERY PLAN                                                                      
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize GroupAggregate  (cost=121462.33..121469.10 rows=25 width=72)
   Group Key: (SUBSTRING(customer.c_phone FROM 1 FOR 2))
   InitPlan 1 (returns $1)
     ->  Finalize Aggregate  (cost=49981.97..49981.98 rows=1 width=32)
           ->  Gather  (cost=49981.75..49981.96 rows=2 width=32)
                 Workers Planned: 2
                 ->  Partial Aggregate  (cost=48981.75..48981.76 rows=1 width=32)
                       ->  Parallel Bitmap Heap Scan on customer customer_1  (cost=8582.16..48587.52 rows=157690 width=6)
                             Recheck Cond: ((SUBSTRING(c_phone FROM 1 FOR 2) = ANY ('{16,13,26,19,28,22,17}'::text[])) AND (c_acctbal > 0.00))
                             ->  Bitmap Index Scan on customer_c_phone_c_acctbal_c_custkey_idx  (cost=0.00..8487.54 rows=378456 width=0)
                                   Index Cond: ((SUBSTRING(c_phone FROM 1 FOR 2) = ANY ('{16,13,26,19,28,22,17}'::text[])) AND (c_acctbal > 0.00))
   ->  Gather Merge  (cost=71480.35..71486.18 rows=50 width=72)
         Workers Planned: 2
         Params Evaluated: $1
         ->  Sort  (cost=70480.32..70480.39 rows=25 width=72)
               Sort Key: (SUBSTRING(customer.c_phone FROM 1 FOR 2))
               ->  Partial HashAggregate  (cost=70479.31..70479.74 rows=25 width=72)
                     Group Key: SUBSTRING(customer.c_phone FROM 1 FOR 2)
                     ->  Nested Loop Anti Join  (cost=3306.97..70293.95 rows=24714 width=38)
                           ->  Parallel Bitmap Heap Scan on customer  (cost=3306.53..40695.81 rows=58030 width=26)
                                 Recheck Cond: ((SUBSTRING(c_phone FROM 1 FOR 2) = ANY ('{16,13,26,19,28,22,17}'::text[])) AND (c_acctbal > $1))
                                 ->  Bitmap Index Scan on customer_c_phone_c_acctbal_c_custkey_idx  (cost=0.00..3271.71 rows=139271 width=0)
                                       Index Cond: ((SUBSTRING(c_phone FROM 1 FOR 2) = ANY ('{16,13,26,19,28,22,17}'::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