I am sending u the query along with execution plan. Please help
explain analyze select
s.*,a.actid,a.phone,d.domid,d.domname,d.domno,a.actno,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"
--
Thanks and Regards, Srikanth Kata
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
