Problem with a query

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