explain analyze select s.*,a.actid,a.phone,d.domid,d.domname,d.domno,a.ac tno,a.actname,p.descr as svcdescr
from vwsubsmin s
inner join packages p on s.svcno=p.pkgno
inner join account a on a.actno=s.actno
inner join ssgdom d on a.domno=d.domno
inner join (select subsno from getexpiringsubs(1,cast(2 as integer),cast(3 as double precision),
'4') as (subsno int,expirydt timestamp without time zone,balcpt double precision)) as e
on s.subsno=e.subsno
where s.status <=15 and d.domno=273
order by d.domname,s.expirydt,a.actname
"Sort (cost=79056.66..79056.67 rows=1 width=330) (actual time=220244.497..220244.497 rows=0 loops=1)"
Sort Key: d.domname, CASE WHEN (v.expirydt IS NULL) THEN b.expirydt ELSE v.expirydt END, a.actname"
-> Nested Loop (cost=78354.14..79056.65 rows=1 width=330) (actual time=220244.457..220244.457 rows=0 loops=1)"
-> Nested Loop (cost=78354.14..79051.44 rows=1 width=296) (actual time=220244.422..220244.422 rows=0 loops=1)"
-> Hash Join (cost=78354.14..79047.51 rows=1 width=268) (actual time=220244.389..220244.389 rows=0 loops=1)"
Hash Cond: ("outer".actno = "inner".actno)"
-> Merge Join (cost=77605.44..78297.14 rows=333 width=221) (actual time=216573.695..216573.695 rows=0 loops=1)"
Merge Cond: ("outer".subsno = "inner".subsno)"
-> Merge Left Join (cost=77543.11..78080.70 rows=58313 width=225) (actual time=207017.909..207017.909 rows=1 loops=1)"
Merge Cond: (("outer".subsno = "inner".subsno) AND ("outer".actno = "inner".actno))"
-> Sort (cost=36864.71..37010.49 rows=58313 width=144) (actual time=182412.046..182412.046 rows=1 loops=1)"
Sort Key: s.subsno, b.actno"
-> Hash Left Join (cost=10628.10..27483.78 rows=58313 width=144) (actual time=155815.373..180210.411 rows=146953 loops=1)"
Hash Cond: ("outer".subsno = "inner".subsno)"
-> Hash Join (cost=6486.20..18594.41 rows=58313 width=136) (actual time=154276.012..171743.982 rows=146953 loops=1)"
Hash Cond: ("outer".subsno = "inner".subsno)"
-> Seq Scan on actbal b (cost=0.00..4155.37 rows=174937 width=67) (actual time=15.862..853.287 rows=174937 loops=1)"
-> Hash (cost=5599.42..5599.42 rows=58313 width=69) (actual time=154252.586..154252.586 rows=146954 loops=1)"
-> Seq Scan on subs s (cost=0.00..5599.42 rows=58313 width=69) (actual time=409.370..153354.835 rows=146954 loops=1)"
Filter: (CASE WHEN ((status = 0) AND issubsexpired(subsno)) THEN 15 ELSE status END <= 15)"
-> Hash (cost=2795.32..2795.32 rows=161032 width=12) (actual time=1539.306..1539.306 rows=161032 loops=1)"
-> Seq Scan on cpnsubs c (cost=0.00..2795.32 rows=161032 width=12) (actual time=445.696..1202.186 rows=161032 loops=1)"
-> Sort (cost=40678.41..40711.82 rows=13364 width=93) (actual time=24604.798..24604.798 rows=1 loops=1)"
Sort Key: v.subsno, v.actno"
-> Subquery Scan v (cost=36763.41..39330.87 rows=13364 width=93) (actual time=23786.875..24304.328 rows=67576 loops=1)"
-> GroupAggregate (cost=36763.41..39197.23 rows=13364 width=61) (actual time=23786.791..24241.895 rows=67576 loops=1)"
-> Sort (cost=36763.41..36942.35 rows=71576 width=61) (actual time=23785.939..23849.227 rows=72402 loops=1)"
Sort Key: u.actno, u.subsno"
-> Hash Join (cost=5141.67..28427.93 rows=71576 width=61) (actual time=7397.590..21721.903 rows=72402 loops=1)"
Hash Cond: ("outer".ctno = "inner".ctno)"
-> Hash Join (cost=5061.16..27273.78 rows=71576 width=32) (actual time=6002.278..20257.764 rows=72402 loops=1)"
Hash Cond: ("outer".cpno = "inner".cpno)"
-> Seq Scan on cpn c (cost=0.00..10132.94 rows=443194 width=12) (actual time=1038.150..9313.905 rows=443194 loops=1)"
-> Hash (cost=4252.22..4252.22 rows=71576 width=36) (actual time=3524.715..3524.715 rows=72402 loops=1)"
-> Bitmap Heap Scan on cpnusage u (cost=448.52..4252.22 rows=71576 width=36) (actual time=832.658..3474.318 rows=72402 loops=1)"
Recheck Cond: (status < 15)"
-> Bitmap Index Scan on cpnusage_status (cost=0.00..448.52 rows=71576 width=0) (actual time=465.807..465.807 rows=72402 loops=1)"
Index Cond: (status < 15)"
-> Hash (cost=79.75..79.75 rows=304 width=41) (actual time=1395.192..1395.192 rows=304 loops=1)"
-> Hash Join (cost=40.60..79.75 rows=304 width=41) (actual time=1394.251..1395.072 rows=304 loops=1)"
Hash Cond: ("outer".ctno = "inner".ctno)"
-> Hash Left Join (cost=26.80..61.39 rows=304 width=37) (actual time=932.963..933.672 rows=304 loops=1)"
Hash Cond: (("outer".price_class_id)::text = ("inner".price_class_id)::text)"
-> Hash Left Join (cost=18.34..49.62 rows=304 width=52) (actual time=97.380..97.935 rows=304 loops=1)"
Hash Cond: ("outer".validprduom = "inner".uomno)"
-> Hash Left Join (cost=17.26..43.98 rows=304 width=56) (actual time=97.356..97.818 rows=304 loops=1)"
Hash Cond: ("outer".timelimituom = "inner".uomno)"
-> Hash Left Join (cost=16.19..38.35 rows=304 width=51) (actual time=51.738..52.119 rows=304 loops=1)"
Hash Cond: ("outer".stno = "inner".svccat)"
-> Hash Left Join (cost=15.16..32.76 rows=304 width=55) (actual time=2.668..2.953 rows=304 loops=1)"
Hash Cond: ("outer".domno = "inner".domno)"
-> Seq Scan on cpntype q (cost=0.00..13.04 rows=304 width=59) (actual time=0.001..0.099 rows=304 loops=1)"
-> Hash (cost=14.13..14.13 rows=413 width=4) (actual time=2.599..2.599 rows=413 loops=1)"
-> Seq Scan on ssgdom d (cost=0.00..14.13 rows=413 width=4) (actual time=0.696..2.447 rows=413 loops=1)"
-> Hash (cost=1.02..1.02 rows=2 width=4) (actual time=49.041..49.041 rows=2 loops=1)"
-> Seq Scan on svccat s (cost=0.00..1.02 rows=2 width=4) (actual time=48.997..48.999 rows=2 loops=1)"
-> Hash (cost=1.06..1.06 rows=6 width=13) (actual time=45.606..45.606 rows=6 loops=1)"
-> Seq Scan on timeuom u1 (cost=0.00..1.06 rows=6 width=13) (actual time=45.593..45.599 rows=6 loops=1)"
-> Hash (cost=1.06..1.06 rows=6 width=4) (actual time=0.006..0.006 rows=6 loops=1)"
-> Seq Scan on timeuom u2 (cost=0.00..1.06 rows=6 width=4) (actual time=0.002..0.002 rows=6 loops=1)"
-> Hash (cost=7.77..7.77 rows=277 width=15) (actual time=835.538..835.538 rows=277 loops=1)"
-> Seq Scan on price_class l (cost=0.00..7.77 rows=277 width=15) (actual time=732.953..835.436 rows=277 loops=1)"
-> Hash (cost=13.04..13.04 rows=304 width=4) (actual time=461.270..461.270 rows=304 loops=1)"
-> Seq Scan on cpntype t (cost=0.00..13.04 rows=304 width=4) (actual time=234.548..461.194 rows=304 loops=1)"
-> Sort (cost=62.33..64.83 rows=1000 width=4) (actual time=9554.783..9554.783 rows=0 loops=1)"
Sort Key: getexpiringsubs.subsno"
-> Function Scan on getexpiringsubs (cost=0.00..12.50 rows=1000 width=4) (actual time=9554.086..9554.086 rows=0 loops=1)"
-> Hash (cost=748.00..748.00 rows=280 width=47) (actual time=3670.649..3670.649 rows=646 loops=1)"
-> Index Scan using account_domno on account a (cost=0.00..748.00 rows=280 width=47) (actual time=455.439..3670.133 rows=646 loops=1)"
Index Cond: (273 = domno)"
-> Index Scan using packages_pkey on packages p (cost=0.00..3.91 rows=1 width=32) (never executed)"
Index Cond: ("outer".svcno = p.pkgno)"
-> Index Scan using ssgdom_pkey on ssgdom d (cost=0.00..5.19 rows=1 width=38) (never executed)"
Index Cond: (domno = 273)"
"Total runtime: 220481.780 ms"