Skip site navigation (1) Skip section navigation (2)

What is the best way to optimize the query.

From: Srikanth <srikanth(at)inventum(dot)net>
To: pgsql-performance(at)postgresql(dot)org
Subject: What is the best way to optimize the query.
Date: 2010-07-17 08:50:26
Message-ID: 4C416ED2.4000008@inventum.net (view raw or flat)
Thread:
Lists: pgsql-performance
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

Responses

pgsql-performance by date

Next:From: Heikki LinnakangasDate: 2010-07-18 08:54:05
Subject: Re: now() gives same time within the session
Previous:From: Greg SmithDate: 2010-07-16 16:55:29
Subject: Re: performance on new linux box

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group