Problem with a query

[Siraj G <tosiraj.g@xxxxxxxxx>]

Hello!

We have a couple of queries that all of a sudden became rather slow. I took explain analyze from one of the SQLs as bdlow. Can you please check and suggest if anything can be done?

'-> Table scan on <temporary>  (actual time=0.019..71.526 rows=38622 loops=1)\n    
-> Aggregate using temporary table  (actual time=33891741.611..33891815.749 rows=38622 loops=1)\n        
-> Nested loop inner join  (cost=155705096.99 rows=0) (actual time=33872496.362..33891210.133 rows=38622 loops=1)\n            
-> Filter: (SES.IS_SERVER_ID <> <cache>(-(1)))  (cost=371424.15 rows=1803523) (actual time=0.036..3921.142 rows=3651493 loops=1)\n                
-> Index scan on SES using IS_SESSION_IDX4  (cost=371424.15 rows=3607044) (actual time=0.034..3600.114 rows=3651493 loops=1)\n            
-> Index lookup on DST using <auto_key0> (IS_SESSION_ID=SES.IS_SESSION_ID)  (actual time=0.004..0.004 rows=0 loops=3651493)\n              
-> Materialize  (cost=0.00..0.00 rows=0) (actual time=33886497.639..33886608.008 rows=38622 loops=1)\n                    
-> Table scan on <temporary>  (actual time=0.018..51.715 rows=38622 loops=1)\n                        
-> Aggregate using temporary table  (actual time=33872191.430..33872246.080 rows=38622 loops=1)\n                            
-> Nested loop left join  (cost=1025850971.42 rows=1242668643) (actual time=910.618..33869299.956 rows=38622 loops=1)\n                                
-> Nested loop left join  (cost=124916205.44 rows=1242668643) (actual time=910.606..33868869.982 rows=38622 loops=1)\n                                    
-> Nested loop left join  (cost=649328.22 rows=949) (actual time=21.155..4387.994 rows=38622 loops=1)\n                                        
-> Nested loop left join  (cost=648884.46 rows=949) (actual time=21.144..4189.892 rows=38622 loops=1)\n                                          
-> Nested loop inner join  (cost=648552.34 rows=949) (actual time=21.127..3847.460 rows=38622 loops=1)\n                                                
-> Inner hash join (MD.REP_ID = P.IS_REPOSITORY_ID)  (cost=0.96 rows=0) (actual time=0.045..0.054 rows=1 loops=1)\n                                                    
-> Table scan on MD  (cost=7.00 rows=2) (actual time=0.007..0.013 rows=2 loops=1)\n                                                    
-> Hash\n                                                        
-> Nested loop inner join  (cost=0.70 rows=0) (actual time=0.024..0.027 rows=1 loops=1)\n                                                            
-> Filter: (LU.IS_PROJ_ID is not null)  (cost=0.35 rows=1) (actual time=0.010..0.012 rows=1 loops=1)\n                                                                
-> Table scan on LU  (cost=0.35 rows=1) (actual time=0.009..0.011 rows=1 loops=1)\n                                                            
-> Filter: (P.IS_PROJ_GUID = LU.IS_PROJ_GUID)  (cost=0.26 rows=0) (actual time=0.014..0.014 rows=1 loops=1)\n                                                                
-> Single-row index lookup on P using PRIMARY (IS_PROJ_ID=LU.IS_PROJ_ID)  (cost=0.26 rows=1) (actual time=0.012..0.012 rows=1 loops=1)\n                                                
-> Filter: ((DS.REPOSITORYID = MD.REP_GUID) and (DS.PROJECTID = LU.IS_PROJ_GUID) and (DS.RECORDTIME >= LU.IS_WIN_BEGIN) and (DS.RECORDTIME < LU.IS_WIN_END))  (cost=11300581.57 rows=18978) (actual time=21.080..3837.717 rows=38622 loops=1)\n                                                    
-> Index range scan on DS (re-planned for each iteration)  (cost=11300581.57 rows=17084027) (actual time=21.071..3653.945 rows=39790 loops=1)\n                                            
-> Filter: (MD.REP_ID = U.IS_REPOSITORY_ID)  (cost=0.25 rows=1) (actual time=0.008..0.008 rows=1 loops=38622)\n                                                
-> Single-row index lookup on U using EM_USER_PK (EM_USER_GUID=DS.USERID, IS_REPOSITORY_ID=MD.REP_ID)  (cost=0.25 rows=1) (actual time=0.007..0.007 rows=1 loops=38622)\n                                        
-> Single-row index lookup on D using IS_DOC_PK (IS_DOC_GUID=DS.DOCUMENTID, IS_PROJ_ID=LU.IS_PROJ_ID)  (cost=0.37 rows=1) (actual time=0.004..0.004 rows=1 loops=38622)\n                                    
-> Nested loop inner join  (cost=23163414.56 rows=1309557) (actual time=714.186..876.817 rows=1 loops=38622)\n                                        
-> Table scan on LU1  (cost=0.01 rows=1) (actual time=0.007..0.011 rows=1 loops=38622)\n                                        
-> Filter: ((SS01.PROJECTID = LU1.IS_PROJ_GUID) and (SS01.SCHEDULEID = DS.JOBID) and (SS01.SESSIONID = DS.SESSIONID) and (SS01.RECORDTIME >= LU1.IS_WIN_BEGIN) and (SS01.RECORDTIME < LU1.IS_WIN_END))  (cost=24410.22 rows=1309557) (actual time=714.176..876.804 rows=1 loops=38622)\n                                            
-> Index lookup on SS01 using IS_SCHEDULE_STATS_IDX1 (SCHEDULETYPE=1)  (cost=24410.22 rows=1309557) (actual time=0.035..522.644 rows=1360349 loops=38622)\n                                
-> Index lookup on S using IS_SCHED_PK (IS_SCHED_GUID=SS01.TRIGGERID)  (cost=0.63 rows=1) (actual time=0.009..0.010 rows=1 loops=38622)\n'

Regards

Siraj