Hi Team,
Have a lovely Sunday.
I have a problem with PostgreSQL performance. I am using PostgreSQL 14.5 on Linux physical server. My query was taking 1 or 2 seconds yesterday, but today it is taking 5-6 seconds. Also, nothing has changed in the database. Also, when I run the query for the second time, the query runs for the same time, so I expect it to be more performant the second time because of the cache.
(the database cache hit ratio is 97,8)
Lastly, some other queries take milliseconds normally, but sometimes they take minutes. I didn't understand why this is happening. (There is no lock, no table bloating and up to date analyze)
I am adding the explain analyze output as an attachment.
EXPLAIN ANALYZE SELECT "Extent1"."StatusId", "Extent1"."DdoId", "Extent1"."SiteName", "Extent1"."SiteCode", "Extent1"."LeadCraftId", "Extent1"."LeadCraftName", "Extent1"."StatusName", "Extent1"."Region",
"Extent1"."TenantId", "Extent1"."Tenant", "Extent1"."INV_ORG_ID", "Extent1"."ProjectId", "Extent1"."CreationDate", "Extent1"."CreatedBy", "Extent1"."LastUpdatedBy", "Extent1"."LastUpdateDate", "Extent1"."SKKFlowID",
"Extent1"."DdoDescription", "Extent1"."DamageIndemnity", "Extent1"."DamageFormNo"
FROM "NEMS_INTEGRATION"."ERP_WORK_ORDER_DDO_INFO" AS "Extent1" WHERE "Extent1"."DdoId" = 1506074 OR "Extent1"."DdoId" IS NULL AND 1506074 IS NULL LIMIT 1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------
Limit (cost=3000161.94..3000161.96 rows=1 width=2952) (actual time=7125.162..7125.252 rows=1 loops=1)
-> Subquery Scan on "ERP_WORK_ORDER_DDO_INFO" (cost=3000161.94..3000162.02 rows=4 width=2952) (actual time=7125.161..7125.250 rows=1 loops=1)
-> HashAggregate (cost=3000161.94..3000161.98 rows=4 width=2952) (actual time=7125.158..7125.247 rows=1 loops=1)
Group Key: "D"."ID", "S"."NAME", "S"."CODE", "D"."LEADCRAFT", "CC"."NAME", "RWO"."CURRENT_STATE_ID", "SS"."NAME", mrod_region."NAME", (CASE mrod_region."ID" WHEN 71 THEN 2000 ELSE CASE "ST"."MAIN_SITE_TYPE_ID" WH
EN 5 THEN 44 ELSE CASE "S"."SITE_OWNER_COMPANY_ID" WHEN 2542 THEN 2542 WHEN 2543 THEN 2543 WHEN 2544 THEN 2544 ELSE 1 END END END), (CASE mrod_region."ID" WHEN 71 THEN $0 ELSE CASE "ST"."MAIN_SITE_TYPE_ID" WHEN 5 THEN $1 ELSE C
ASE "S"."SITE_OWNER_COMPANY_ID" WHEN 2542 THEN $2 WHEN 2543 THEN $3 WHEN 2544 THEN $4 ELSE $5 END END END), (CASE mrod_region."ID" WHEN 71 THEN $6 ELSE CASE "ST"."MAIN_SITE_TYPE_ID" WHEN 5 THEN $7 ELSE CASE "S"."SITE_OWNER_COMP
ANY_ID" WHEN 2542 THEN $8 WHEN 2543 THEN $9 WHEN 2544 THEN $10 ELSE $11 END END END), (NULL::bigint), "D"."CREATION_DATE", "CU"."LOGIN_NAME", "UU"."LOGIN_NAME", "D"."LAST_UPDATED_DATE", (NULL::bigint), (NULL::text), (NULL::bigi
nt), (NULL::bigint)
Batches: 1 Memory Usage: 60kB
-> Append (cost=36.10..3000161.74 rows=4 width=2952) (actual time=7100.944..7125.231 rows=1 loops=1)
-> Nested Loop (cost=36.10..50.59 rows=1 width=714) (actual time=0.098..0.122 rows=0 loops=1)
InitPlan 1 (returns $0)
-> Index Scan using "PK_COMPANY" on "COMPANY" (cost=0.27..2.49 rows=1 width=16) (never executed)
Index Cond: ("ID" = 2000)
InitPlan 2 (returns $1)
-> Index Scan using "PK_COMPANY" on "COMPANY" "COMPANY_1" (cost=0.27..2.49 rows=1 width=16) (never executed)
Index Cond: ("ID" = 44)
InitPlan 3 (returns $2)
-> Index Scan using "PK_COMPANY" on "COMPANY" "COMPANY_2" (cost=0.27..2.49 rows=1 width=16) (never executed)
Index Cond: ("ID" = 2542)
InitPlan 4 (returns $3)
-> Index Scan using "PK_COMPANY" on "COMPANY" "COMPANY_3" (cost=0.27..2.49 rows=1 width=16) (never executed)
Index Cond: ("ID" = 2543)
InitPlan 5 (returns $4)
-> Index Scan using "PK_COMPANY" on "COMPANY" "COMPANY_4" (cost=0.27..2.49 rows=1 width=16) (never executed)
Index Cond: ("ID" = 2544)
InitPlan 6 (returns $5)
-> Index Scan using "PK_COMPANY" on "COMPANY" "COMPANY_5" (cost=0.27..2.49 rows=1 width=16) (never executed)
Index Cond: ("ID" = 1)
InitPlan 7 (returns $6)
-> Index Scan using "PK_COMPANY" on "COMPANY" "COMPANY_6" (cost=0.27..2.49 rows=1 width=4) (never executed)
Index Cond: ("ID" = 2000)
InitPlan 8 (returns $7)
-> Index Scan using "PK_COMPANY" on "COMPANY" "COMPANY_7" (cost=0.27..2.49 rows=1 width=4) (never executed)
Index Cond: ("ID" = 44)
InitPlan 9 (returns $8)
-> Index Scan using "PK_COMPANY" on "COMPANY" "COMPANY_8" (cost=0.27..2.49 rows=1 width=4) (never executed)
Index Cond: ("ID" = 2542)
InitPlan 10 (returns $9)
-> Index Scan using "PK_COMPANY" on "COMPANY" "COMPANY_9" (cost=0.27..2.49 rows=1 width=4) (never executed)
Index Cond: ("ID" = 2543)
InitPlan 11 (returns $10)
-> Index Scan using "PK_COMPANY" on "COMPANY" "COMPANY_10" (cost=0.27..2.49 rows=1 width=4) (never executed)
Index Cond: ("ID" = 2544)
InitPlan 12 (returns $11)
-> Index Scan using "PK_COMPANY" on "COMPANY" "COMPANY_11" (cost=0.27..2.49 rows=1 width=4) (never executed)
Index Cond: ("ID" = 1)
-> Nested Loop (cost=5.95..20.38 rows=1 width=119) (actual time=0.098..0.110 rows=0 loops=1)
-> Nested Loop Left Join (cost=5.52..17.73 rows=1 width=119) (actual time=0.098..0.108 rows=0 loops=1)
-> Nested Loop Left Join (cost=5.25..15.23 rows=1 width=103) (actual time=0.097..0.108 rows=0 loops=1)
-> Nested Loop Left Join (cost=1.99..10.95 rows=1 width=95) (actual time=0.097..0.100 rows=0 loops=1)
-> Nested Loop Left Join (cost=1.57..10.46 rows=1 width=95) (actual time=0.097..0.099 rows=0 loops=1)
-> Nested Loop Left Join (cost=1.42..10.30 rows=1 width=95) (actual time=0.097..0.099 rows=0 loops=1)
-> Nested Loop (cost=1.00..7.65 rows=1 width=52) (actual time=0.097..0.098 rows=0 loops=1)
-> Nested Loop (cost=0.71..5.15 rows=1 width=46) (actual time=0.097..0.098 rows=0 loops=1)
-> Index Scan using "PK_DDO_WORK_ORDER" on "DDO_WORK_ORDER" "D" (cost=0.42..2.64 rows=1 width=40) (actual time=0.096..0.097 rows=0 loops=1)
Index Cond: ("ID" = 1506074)
-> Index Scan using "PK_USER_N" on "USER_N" "CU" (cost=0.29..2.51 rows=1 width=14) (never executed)
Index Cond: ("ID" = "D"."CREATED_BY_USER_ID")
-> Index Scan using "PK_USER_N" on "USER_N" "UU" (cost=0.29..2.51 rows=1 width=14) (never executed)
Index Cond: ("ID" = "D"."LAST_UPDATED_USER_ID")
-> Index Scan using "SITE_ID_NO_idx" on "SITE" "S" (cost=0.42..2.64 rows=1 width=51) (never executed)
Index Cond: ("ID" = "D"."SITE_ID")
-> Index Scan using "PK_SITE_TYPE" on "SITE_TYPE" "ST" (cost=0.14..0.16 rows=1 width=8) (never executed)
Index Cond: ("ID" = "S"."SITE_TYPE_ID")
-> Index Scan using "PK_ADDRESS" on "ADDRESS" "A" (cost=0.43..0.49 rows=1 width=8) (never executed)
Index Cond: ("ID" = "S"."ADDRESS_ID")
-> Nested Loop (cost=3.25..4.27 rows=1 width=16) (never executed)
-> Nested Loop (cost=3.11..4.06 rows=1 width=20) (never executed)
-> Nested Loop (cost=2.97..3.85 rows=1 width=24) (never executed)
-> Nested Loop (cost=2.83..3.65 rows=1 width=28) (never executed)
-> Nested Loop (cost=2.69..3.44 rows=1 width=32) (never executed)
-> Nested Loop (cost=2.55..3.23 rows=1 width=36) (never executed)
-> Nested Loop (cost=2.41..3.02 rows=1 width=40) (never executed)
-> Nested Loop (cost=2.27..2.82 rows=1 width=40) (never executed)
-> Nested Loop (cost=2.13..2.61 rows=1 width=40) (never executed)
-> Nested Loop (cost=1.98..2.41 rows=1 width=28) (never executed)
-> Nested Loop (cost=1.84..2.21 rows=1 width=28) (never executed)
-> Nested Loop (cost=1.70..2.01 rows=1 width=28) (never executed)
-> Nested Loop (cost=1.56..1.80 rows=1 width=28) (never executed)
-> Nested Loop (cost=1.41..1.63 rows=1 width=28) (never executed)
-> Nested Loop (cost=1.27..1.47 rows=1 width=28) (never executed)
-> Nested Loop (cost=1.13..1.30 rows=1 width=28) (never executed)
-> Nested Loop (cost=0.99..1.13 rows=1 width=28) (never executed)
-> Nested Loop (cost=0.84..0.97 rows=1 width=28) (never executed)
-> Nested Loop (cost=0.70..0.80 rows=1 width=28) (never executed)
-> Nested Loop (cost=0.56..0.63 rows=1 width=36) (never executed)
-> Index Scan using "PK_COUNTY" on "COUNTY" county (cost=0.2
8..0.30 rows=1 width=8) (never executed)
Index Cond: ("ID" = "A"."COUNTY_ID")
Filter: ("IS_DELETED" = 0)
-> Index Scan using "IX_COUNTY_RESPONSIBILITY_COUNTY_ID" on "
COUNTY_RESPONSIBILITY" active_county_responsibility (cost=0.28..0.32 rows=1 width=56) (never executed)
Index Cond: ("COUNTY_ID" = county."ID")
-> Index Scan using "PK_CITY" on "CITY" city (cost=0.14..0.17 rows
=1 width=8) (never executed)
Index Cond: ("ID" = county."CITY_ID")
Filter: ("IS_DELETED" = 0)
-> Index Scan using "PK_SUBREGION" on "SUBREGION" rn_subregion (cost=0.1
4..0.17 rows=1 width=8) (never executed)
Index Cond: ("ID" = active_county_responsibility."RN_SUBREGION_ID")
Filter: ("IS_DELETED" = 0)
-> Index Scan using "PK_SUBREGION" on "SUBREGION" fn_subregion (cost=0.14..0.1
7 rows=1 width=8) (never executed)
Index Cond: ("ID" = active_county_responsibility."FN_SUBREGION_ID")
Filter: ("IS_DELETED" = 0)
-> Index Scan using "PK_SUBREGION" on "SUBREGION" cn_subregion (cost=0.14..0.17 rows
=1 width=8) (never executed)
Index Cond: ("ID" = active_county_responsibility."CN_SUBREGION_ID")
Filter: ("IS_DELETED" = 0)
-> Index Scan using "PK_SUBREGION" on "SUBREGION" mrod_subregion (cost=0.14..0.17 rows=1 w
idth=8) (never executed)
Index Cond: ("ID" = active_county_responsibility."MROD_SUBREGION_ID")
Filter: ("IS_DELETED" = 0)
-> Index Scan using "PK_SUBREGION" on "SUBREGION" frod_subregion (cost=0.14..0.17 rows=1 width=8
) (never executed)
Index Cond: ("ID" = active_county_responsibility."FROD_SUBREGION_ID")
Filter: ("IS_DELETED" = 0)
-> Index Scan using "PK_SUBREGION" on "SUBREGION" lbs_subregion (cost=0.14..0.17 rows=1 width=8) (neve
r executed)
Index Cond: ("ID" = active_county_responsibility."LBS_SUBREGION_ID")
Filter: ("IS_DELETED" = 0)
-> Index Scan using "REGION_ID_DIVISION_ID_idx" on "REGION" rn_region (cost=0.14..0.20 rows=1 width=8) (neve
r executed)
Index Cond: ("ID" = rn_subregion."REGION_ID")
Filter: ("IS_DELETED" = 0)
-> Index Scan using "REGION_ID_DIVISION_ID_idx" on "REGION" fn_region (cost=0.14..0.20 rows=1 width=8) (never exec
uted)
Index Cond: ("ID" = fn_subregion."REGION_ID")
Filter: ("IS_DELETED" = 0)
-> Index Scan using "REGION_ID_DIVISION_ID_idx" on "REGION" cn_region (cost=0.14..0.20 rows=1 width=8) (never executed)
Index Cond: ("ID" = cn_subregion."REGION_ID")
-> Index Scan using "REGION_ID_DIVISION_ID_idx" on "REGION" mrod_region (cost=0.14..0.20 rows=1 width=16) (never executed)
Index Cond: ("ID" = mrod_subregion."REGION_ID")
-> Index Scan using "REGION_ID_DIVISION_ID_idx" on "REGION" frod_region (cost=0.14..0.20 rows=1 width=8) (never executed)
Index Cond: ("ID" = frod_subregion."REGION_ID")
-> Index Scan using "REGION_ID_DIVISION_ID_idx" on "REGION" lbs_region (cost=0.14..0.20 rows=1 width=8) (never executed)
Index Cond: ("ID" = lbs_subregion."REGION_ID")
-> Memoize (cost=0.14..0.20 rows=1 width=4) (never executed)
Cache Key: rn_region."MAIN_REGION_ID"
Cache Mode: logical
-> Index Scan using "PK_MAIN_REGION" on "MAIN_REGION" rn_main_region (cost=0.13..0.19 rows=1 width=4) (never executed)
Index Cond: ("ID" = rn_region."MAIN_REGION_ID")
Filter: ("IS_DELETED" = 0)
-> Memoize (cost=0.14..0.20 rows=1 width=4) (never executed)
Cache Key: fn_region."MAIN_REGION_ID"
Cache Mode: logical
-> Index Scan using "PK_MAIN_REGION" on "MAIN_REGION" fn_main_region (cost=0.13..0.19 rows=1 width=4) (never executed)
Index Cond: ("ID" = fn_region."MAIN_REGION_ID")
Filter: ("IS_DELETED" = 0)
-> Memoize (cost=0.14..0.20 rows=1 width=4) (never executed)
Cache Key: cn_region."MAIN_REGION_ID"
Cache Mode: logical
-> Index Scan using "PK_MAIN_REGION" on "MAIN_REGION" cn_main_region (cost=0.13..0.19 rows=1 width=4) (never executed)
Index Cond: ("ID" = cn_region."MAIN_REGION_ID")
Filter: ("IS_DELETED" = 0)
-> Memoize (cost=0.14..0.20 rows=1 width=4) (never executed)
Cache Key: mrod_region."MAIN_REGION_ID"
Cache Mode: logical
-> Index Scan using "PK_MAIN_REGION" on "MAIN_REGION" mrod_main_region (cost=0.13..0.19 rows=1 width=4) (never executed)
Index Cond: ("ID" = mrod_region."MAIN_REGION_ID")
Filter: ("IS_DELETED" = 0)
-> Memoize (cost=0.14..0.20 rows=1 width=4) (never executed)
Cache Key: frod_region."MAIN_REGION_ID"
Cache Mode: logical
-> Index Scan using "PK_MAIN_REGION" on "MAIN_REGION" frod_main_region (cost=0.13..0.19 rows=1 width=4) (never executed)
Index Cond: ("ID" = frod_region."MAIN_REGION_ID")
Filter: ("IS_DELETED" = 0)
-> Memoize (cost=0.14..0.20 rows=1 width=4) (never executed)
Cache Key: lbs_region."MAIN_REGION_ID"
Cache Mode: logical
-> Index Scan using "PK_MAIN_REGION" on "MAIN_REGION" lbs_main_region (cost=0.13..0.19 rows=1 width=4) (never executed)
Index Cond: ("ID" = lbs_region."MAIN_REGION_ID")
Filter: ("IS_DELETED" = 0)
-> Index Scan using "PK_COMPANY" on "COMPANY" "CC" (cost=0.27..2.49 rows=1 width=20) (never executed)
Index Cond: ("ID" = "D"."LEADCRAFT")
-> Index Only Scan using "WORK_ORDER_ID_CURRENT_STATE_ID_idx" on "WORK_ORDER" "RWO" (cost=0.43..2.65 rows=1 width=8) (never executed)
Index Cond: ("ID" = "D"."REF_WORK_ORDER_ID")
Heap Fetches: 0
-> Index Scan using "PK_SM_STATE" on "SM_STATE" "SS" (cost=0.28..0.29 rows=1 width=23) (never executed)
Index Cond: ("ID" = "RWO"."CURRENT_STATE_ID")
-> Subquery Scan on "*SELECT* 2" (cost=10.93..30.31 rows=1 width=230) (actual time=0.096..0.111 rows=0 loops=1)
-> Nested Loop Left Join (cost=10.93..30.30 rows=1 width=226) (actual time=0.095..0.111 rows=0 loops=1)
InitPlan 13 (returns $12)
-> Limit (cost=0.27..2.49 rows=1 width=4) (never executed)
-> Index Scan using "IX_COMPANY_NAME" on "COMPANY" c (cost=0.27..2.49 rows=1 width=4) (never executed)
Index Cond: (("NAME")::text = 'GLOBALTOWER'::text)
InitPlan 14 (returns $13)
-> Limit (cost=0.27..2.49 rows=1 width=4) (never executed)
-> Index Scan using "IX_COMPANY_NAME" on "COMPANY" c_1 (cost=0.27..2.49 rows=1 width=4) (never executed)
Index Cond: (("NAME")::text = 'GLOBALTOWER'::text)
-> Nested Loop Left Join (cost=5.67..20.10 rows=1 width=111) (actual time=0.095..0.104 rows=0 loops=1)
-> Nested Loop Left Join (cost=5.40..17.60 rows=1 width=95) (actual time=0.095..0.104 rows=0 loops=1)
-> Nested Loop Left Join (cost=2.15..13.31 rows=1 width=91) (actual time=0.095..0.098 rows=0 loops=1)
-> Nested Loop Left Join (cost=1.72..12.82 rows=1 width=91) (actual time=0.095..0.097 rows=0 loops=1)
-> Nested Loop (cost=1.29..10.18 rows=1 width=56) (actual time=0.095..0.097 rows=0 loops=1)
-> Nested Loop (cost=1.01..7.67 rows=1 width=50) (actual time=0.095..0.096 rows=0 loops=1)
-> Nested Loop (cost=0.72..5.17 rows=1 width=44) (actual time=0.094..0.095 rows=0 loops=1)
-> Index Only Scan using "WORK_ORDER_ID_CURRENT_STATE_ID_idx" on "WORK_ORDER" "WO" (cost=0.43..2.65 rows=1 width=8) (actual time=0.042..0.043 rows=1 l
oops=1)
Index Cond: ("ID" = 1506074)
Heap Fetches: 0
-> Index Scan using "IX_WORK_ORDER_ID_TOWER_MANAGEMENT" on "TOWER_MANAGEMENT" "TM" (cost=0.29..2.51 rows=1 width=40) (actual time=0.051..0.052 rows=0
loops=1)
Index Cond: ("WORK_ORDER_ID" = 1506074)
-> Index Scan using "PK_USER_N" on "USER_N" "CU_1" (cost=0.29..2.51 rows=1 width=14) (never executed)
Index Cond: ("ID" = "TM"."CREATED_BY_USER_ID")
-> Index Scan using "PK_USER_N" on "USER_N" "UU_1" (cost=0.29..2.51 rows=1 width=14) (never executed)
Index Cond: ("ID" = "TM"."LAST_UPDATED_USER_ID")
-> Index Scan using "SITE_ID_NO_idx" on "SITE" "S_1" (cost=0.42..2.64 rows=1 width=43) (never executed)
Index Cond: ("ID" = "TM"."SITE_ID")
-> Index Scan using "PK_ADDRESS" on "ADDRESS" "A_1" (cost=0.43..0.49 rows=1 width=8) (never executed)
Index Cond: ("ID" = "S_1"."ADDRESS_ID")
-> Nested Loop (cost=3.25..4.27 rows=1 width=12) (never executed)
-> Nested Loop (cost=3.11..4.06 rows=1 width=16) (never executed)
-> Nested Loop (cost=2.97..3.85 rows=1 width=20) (never executed)
-> Nested Loop (cost=2.83..3.65 rows=1 width=24) (never executed)
-> Nested Loop (cost=2.69..3.44 rows=1 width=28) (never executed)
-> Nested Loop (cost=2.55..3.23 rows=1 width=32) (never executed)
-> Nested Loop (cost=2.41..3.02 rows=1 width=36) (never executed)
-> Nested Loop (cost=2.27..2.82 rows=1 width=36) (never executed)
-> Nested Loop (cost=2.13..2.61 rows=1 width=36) (never executed)
-> Nested Loop (cost=1.98..2.41 rows=1 width=28) (never executed)
-> Nested Loop (cost=1.84..2.21 rows=1 width=28) (never executed)
-> Nested Loop (cost=1.70..2.01 rows=1 width=28) (never executed)
-> Nested Loop (cost=1.56..1.80 rows=1 width=28) (never executed)
-> Nested Loop (cost=1.41..1.63 rows=1 width=28) (never executed)
-> Nested Loop (cost=1.27..1.47 rows=1 width=28) (never executed)
-> Nested Loop (cost=1.13..1.30 rows=1 width=28) (never executed)
-> Nested Loop (cost=0.99..1.13 rows=1 width=28) (never executed)
-> Nested Loop (cost=0.84..0.97 rows=1 width=28) (never executed)
-> Nested Loop (cost=0.70..0.80 rows=1 width=28) (never executed)
-> Nested Loop (cost=0.56..0.63 rows=1 width=36) (never executed)
-> Index Scan using "PK_COUNTY" on "COUNTY" county_1 (cost=0
.28..0.30 rows=1 width=8) (never executed)
Index Cond: ("ID" = "A_1"."COUNTY_ID")
Filter: ("IS_DELETED" = 0)
-> Index Scan using "IX_COUNTY_RESPONSIBILITY_COUNTY_ID" on "
COUNTY_RESPONSIBILITY" active_county_responsibility_1 (cost=0.28..0.32 rows=1 width=56) (never executed)
Index Cond: ("COUNTY_ID" = county_1."ID")
-> Index Scan using "PK_CITY" on "CITY" city_1 (cost=0.14..0.17 ro
ws=1 width=8) (never executed)
Index Cond: ("ID" = county_1."CITY_ID")
Filter: ("IS_DELETED" = 0)
-> Index Scan using "PK_SUBREGION" on "SUBREGION" rn_subregion_1 (cost=0
.14..0.17 rows=1 width=8) (never executed)
Index Cond: ("ID" = active_county_responsibility_1."RN_SUBREGION_ID"
)
Filter: ("IS_DELETED" = 0)
-> Index Scan using "PK_SUBREGION" on "SUBREGION" fn_subregion_1 (cost=0.14..0
.17 rows=1 width=8) (never executed)
Index Cond: ("ID" = active_county_responsibility_1."FN_SUBREGION_ID")
Filter: ("IS_DELETED" = 0)
-> Index Scan using "PK_SUBREGION" on "SUBREGION" cn_subregion_1 (cost=0.14..0.17 ro
ws=1 width=8) (never executed)
Index Cond: ("ID" = active_county_responsibility_1."CN_SUBREGION_ID")
Filter: ("IS_DELETED" = 0)
-> Index Scan using "PK_SUBREGION" on "SUBREGION" mrod_subregion_1 (cost=0.14..0.17 rows=1
width=8) (never executed)
Index Cond: ("ID" = active_county_responsibility_1."MROD_SUBREGION_ID")
Filter: ("IS_DELETED" = 0)
-> Index Scan using "PK_SUBREGION" on "SUBREGION" frod_subregion_1 (cost=0.14..0.17 rows=1 width
=8) (never executed)
Index Cond: ("ID" = active_county_responsibility_1."FROD_SUBREGION_ID")
Filter: ("IS_DELETED" = 0)
-> Index Scan using "PK_SUBREGION" on "SUBREGION" lbs_subregion_1 (cost=0.14..0.17 rows=1 width=8) (ne
ver executed)
Index Cond: ("ID" = active_county_responsibility_1."LBS_SUBREGION_ID")
Filter: ("IS_DELETED" = 0)
-> Index Scan using "REGION_ID_DIVISION_ID_idx" on "REGION" rn_region_1 (cost=0.14..0.20 rows=1 width=8) (ne
ver executed)
Index Cond: ("ID" = rn_subregion_1."REGION_ID")
Filter: ("IS_DELETED" = 0)
-> Index Scan using "REGION_ID_DIVISION_ID_idx" on "REGION" fn_region_1 (cost=0.14..0.20 rows=1 width=8) (never ex
ecuted)
Index Cond: ("ID" = fn_subregion_1."REGION_ID")
Filter: ("IS_DELETED" = 0)
-> Index Scan using "REGION_ID_DIVISION_ID_idx" on "REGION" cn_region_1 (cost=0.14..0.20 rows=1 width=8) (never executed
)
Index Cond: ("ID" = cn_subregion_1."REGION_ID")
-> Index Scan using "REGION_ID_DIVISION_ID_idx" on "REGION" mrod_region_1 (cost=0.14..0.20 rows=1 width=16) (never executed)
Index Cond: ("ID" = mrod_subregion_1."REGION_ID")
-> Index Scan using "REGION_ID_DIVISION_ID_idx" on "REGION" frod_region_1 (cost=0.14..0.20 rows=1 width=8) (never executed)
Index Cond: ("ID" = frod_subregion_1."REGION_ID")
-> Index Scan using "REGION_ID_DIVISION_ID_idx" on "REGION" lbs_region_1 (cost=0.14..0.20 rows=1 width=8) (never executed)
Index Cond: ("ID" = lbs_subregion_1."REGION_ID")
-> Memoize (cost=0.14..0.20 rows=1 width=4) (never executed)
Cache Key: rn_region_1."MAIN_REGION_ID"
Cache Mode: logical
-> Index Scan using "PK_MAIN_REGION" on "MAIN_REGION" rn_main_region_1 (cost=0.13..0.19 rows=1 width=4) (never executed)
Index Cond: ("ID" = rn_region_1."MAIN_REGION_ID")
Filter: ("IS_DELETED" = 0)
-> Memoize (cost=0.14..0.20 rows=1 width=4) (never executed)
Cache Key: fn_region_1."MAIN_REGION_ID"
Cache Mode: logical
-> Index Scan using "PK_MAIN_REGION" on "MAIN_REGION" fn_main_region_1 (cost=0.13..0.19 rows=1 width=4) (never executed)
Index Cond: ("ID" = fn_region_1."MAIN_REGION_ID")
Filter: ("IS_DELETED" = 0)
-> Memoize (cost=0.14..0.20 rows=1 width=4) (never executed)
Cache Key: cn_region_1."MAIN_REGION_ID"
Cache Mode: logical
-> Index Scan using "PK_MAIN_REGION" on "MAIN_REGION" cn_main_region_1 (cost=0.13..0.19 rows=1 width=4) (never executed)
Index Cond: ("ID" = cn_region_1."MAIN_REGION_ID")
Filter: ("IS_DELETED" = 0)
-> Memoize (cost=0.14..0.20 rows=1 width=4) (never executed)
Cache Key: mrod_region_1."MAIN_REGION_ID"
Cache Mode: logical
-> Index Scan using "PK_MAIN_REGION" on "MAIN_REGION" mrod_main_region_1 (cost=0.13..0.19 rows=1 width=4) (never executed)
Index Cond: ("ID" = mrod_region_1."MAIN_REGION_ID")
Filter: ("IS_DELETED" = 0)
-> Memoize (cost=0.14..0.20 rows=1 width=4) (never executed)
Cache Key: frod_region_1."MAIN_REGION_ID"
Cache Mode: logical
-> Index Scan using "PK_MAIN_REGION" on "MAIN_REGION" frod_main_region_1 (cost=0.13..0.19 rows=1 width=4) (never executed)
Index Cond: ("ID" = frod_region_1."MAIN_REGION_ID")
Filter: ("IS_DELETED" = 0)
-> Memoize (cost=0.14..0.20 rows=1 width=4) (never executed)
Cache Key: lbs_region_1."MAIN_REGION_ID"
Cache Mode: logical
-> Index Scan using "PK_MAIN_REGION" on "MAIN_REGION" lbs_main_region_1 (cost=0.13..0.19 rows=1 width=4) (never executed)
Index Cond: ("ID" = lbs_region_1."MAIN_REGION_ID")
Filter: ("IS_DELETED" = 0)
-> Index Scan using "PK_COMPANY" on "COMPANY" "CC_1" (cost=0.27..2.49 rows=1 width=20) (never executed)
Index Cond: ("ID" = "TM"."COMPANY_ID")
-> Index Scan using "PK_SM_STATE" on "SM_STATE" "SS_1" (cost=0.28..2.49 rows=1 width=23) (never executed)
Index Cond: ("ID" = "WO"."CURRENT_STATE_ID")
SubPlan 15
-> Limit (cost=0.42..2.73 rows=1 width=4) (never executed)
-> Index Scan using "IX_STANDARD_TASK_TOWER_MANAGEMENT_ID" on "STANDARD_TASK" st (cost=0.42..2.73 rows=1 width=4) (never executed)
Index Cond: ("TOWER_MANAGEMENT_ID" = "TM"."ID")
Filter: (("PROJECT_TYPE_ID" IS NOT NULL) AND ("MAIN_TASK_ID" = 0) AND ("IS_DELETED" = 0))
-> Subquery Scan on "*SELECT* 3" (cost=364376.65..1477159.13 rows=1 width=207) (actual time=7100.748..7124.903 rows=1 loops=1)
-> Nested Loop (cost=364376.65..1477159.11 rows=1 width=189) (actual time=7100.744..7124.897 rows=1 loops=1)
InitPlan 16 (returns $15)
-> Limit (cost=0.27..2.49 rows=1 width=4) (actual time=0.040..0.041 rows=1 loops=1)
-> Index Scan using "IX_COMPANY_NAME" on "COMPANY" c_2 (cost=0.27..2.49 rows=1 width=4) (actual time=0.039..0.039 rows=1 loops=1)
Index Cond: (("NAME")::text = 'SUPERONLINE'::text)
-> Hash Join (cost=364374.02..1477156.45 rows=1 width=186) (actual time=7100.675..7124.825 rows=1 loops=1)
Hash Cond: (wf_ins."InstanceId" = "DP"."INSTANCE_ID")
-> Nested Loop Left Join (cost=364355.41..1475341.43 rows=479040 width=38) (actual time=2190.053..7081.265 rows=474910 loops=1)
-> Hash Right Join (cost=364355.26..1463359.36 rows=479040 width=19) (actual time=2190.036..6856.246 rows=474910 loops=1)
Hash Cond: (wf_cstp."InstanceId" = wf_ins."InstanceId")
Join Filter: ((wf_ins."StatusCd")::text = 'CMP'::text)
-> Index Scan using "IX_T_WF_STEP_StepDefTypeCd" on "T_WF_STEP" wf_cstp (cost=0.43..1098968.54 rows=4019 width=8) (actual time=0.096..4278.668 rows=802420 loops=1)
Index Cond: (("StepDefTypeCd")::text = 'End'::text)
Filter: ("StepId" = (SubPlan 20))
Rows Removed by Filter: 625
SubPlan 20
-> Result (cost=1.06..1.07 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=803045)
InitPlan 19 (returns $19)
-> Limit (cost=0.43..1.06 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=803045)
-> Index Only Scan Backward using "T_WF_STEP_InstanceId_StepId_idx" on "T_WF_STEP" "T_WF_STEP_1" (cost=0.43..37.05 rows=59 width=8) (actual time=0.003..
0.003 rows=1 loops=803045)
Index Cond: (("InstanceId" = wf_cstp."InstanceId") AND ("StepId" IS NOT NULL))
Heap Fetches: 803045
-> Hash (cost=358366.83..358366.83 rows=479040 width=22) (actual time=2187.777..2187.782 rows=474910 loops=1)
Buckets: 524288 Batches: 1 Memory Usage: 29324kB
-> Merge Left Join (cost=292842.33..358366.83 rows=479040 width=22) (actual time=1474.752..2059.546 rows=474910 loops=1)
Merge Cond: (wf_ins."InstanceId" = wf_stp."InstanceId")
-> Index Scan using "T_WF_INSTANCE_InstanceId_ApplicationCd_idx" on "T_WF_INSTANCE" wf_ins (cost=0.43..64316.34 rows=479040 width=38) (actual time=12.423..410.421
rows=474910 loops=1)
Index Cond: (("ApplicationCd")::text = 'ONENT_SabitKurulum'::text)
-> Sort (cost=292841.90..292844.90 rows=1198 width=8) (actual time=1461.453..1486.572 rows=213375 loops=1)
Sort Key: wf_stp."InstanceId"
Sort Method: quicksort Memory: 16146kB
-> Index Scan using "IX_T_WF_STEP_StatusCd" on "T_WF_STEP" wf_stp (cost=0.43..292780.65 rows=1198 width=8) (actual time=0.139..1402.221 rows=213375 loops=1)
Index Cond: (("StatusCd")::text = ANY ('{P,Q,S,T,U,W}'::text[]))
Filter: ("StepId" = (SubPlan 18))
Rows Removed by Filter: 21069
SubPlan 18
-> Result (cost=1.06..1.07 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=234444)
InitPlan 17 (returns $17)
-> Limit (cost=0.43..1.06 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=234444)
-> Index Only Scan Backward using "T_WF_STEP_InstanceId_StepId_idx" on "T_WF_STEP" (cost=0.43..37.05 rows=59 width=8) (actual time=0.0
03..0.003 rows=1 loops=234444)
Index Cond: (("InstanceId" = wf_stp."InstanceId") AND ("StepId" IS NOT NULL))
Heap Fetches: 234470
-> Memoize (cost=0.15..0.17 rows=1 width=31) (actual time=0.000..0.000 rows=1 loops=474910)
Cache Key: (wf_ins."EntityStatusCd")::text
Cache Mode: logical
Hits: 474871 Misses: 39 Evictions: 0 Overflows: 0 Memory Usage: 6kB
-> Index Scan using "IX_T_WF_WORKFLOWSTATUS_Code" on "T_WF_WORKFLOWSTATUS" wf_sts (cost=0.14..0.16 rows=1 width=31) (actual time=0.004..0.004 rows=1 loops=39)
Index Cond: (("Code")::text = (wf_ins."EntityStatusCd")::text)
-> Hash (cost=18.59..18.59 rows=1 width=164) (actual time=0.562..0.586 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Nested Loop Left Join (cost=5.37..18.59 rows=1 width=164) (actual time=0.553..0.580 rows=1 loops=1)
-> Nested Loop Left Join (cost=5.10..16.09 rows=1 width=148) (actual time=0.526..0.551 rows=1 loops=1)
-> Nested Loop Left Join (cost=1.85..11.81 rows=1 width=144) (actual time=0.268..0.273 rows=1 loops=1)
-> Nested Loop Left Join (cost=1.42..11.32 rows=1 width=144) (actual time=0.228..0.232 rows=1 loops=1)
-> Nested Loop (cost=1.00..8.67 rows=1 width=109) (actual time=0.158..0.162 rows=1 loops=1)
-> Nested Loop (cost=0.71..6.17 rows=1 width=103) (actual time=0.119..0.122 rows=1 loops=1)
Join Filter: ("DP"."TENANT_ID" = "T"."ID")
-> Nested Loop (cost=0.71..5.15 rows=1 width=94) (actual time=0.102..0.104 rows=1 loops=1)
-> Index Scan using "DDO_PACKAGE_pkey" on "DDO_PACKAGE" "DP" (cost=0.42..2.64 rows=1 width=88) (actual time=0.067..0.068 rows=1 loops=1)
Index Cond: ("ID" = 1506074)
-> Index Scan using "PK_USER_N" on "USER_N" "CU_2" (cost=0.29..2.51 rows=1 width=14) (actual time=0.032..0.032 rows=1 loops=1)
Index Cond: ("ID" = "DP"."CREATED_BY_USER_ID")
-> Seq Scan on "TENANT" "T" (cost=0.00..1.01 rows=1 width=13) (actual time=0.016..0.016 rows=1 loops=1)
-> Index Scan using "PK_USER_N" on "USER_N" "UU_2" (cost=0.29..2.51 rows=1 width=14) (actual time=0.038..0.038 rows=1 loops=1)
Index Cond: ("ID" = "DP"."LAST_UPDATED_USER_ID")
-> Index Scan using "SITE_ID_NO_idx" on "SITE" "S_2" (cost=0.42..2.64 rows=1 width=43) (actual time=0.069..0.069 rows=1 loops=1)
Index Cond: ("ID" = "DP"."SITE_ID")
-> Index Scan using "PK_ADDRESS" on "ADDRESS" "A_2" (cost=0.43..0.49 rows=1 width=8) (actual time=0.039..0.039 rows=1 loops=1)
Index Cond: ("ID" = "S_2"."ADDRESS_ID")
-> Nested Loop (cost=3.25..4.27 rows=1 width=12) (actual time=0.256..0.276 rows=1 loops=1)
-> Nested Loop (cost=3.11..4.06 rows=1 width=16) (actual time=0.249..0.267 rows=1 loops=1)
-> Nested Loop (cost=2.97..3.85 rows=1 width=20) (actual time=0.241..0.258 rows=1 loops=1)
-> Nested Loop (cost=2.83..3.65 rows=1 width=24) (actual time=0.235..0.251 rows=1 loops=1)
-> Nested Loop (cost=2.69..3.44 rows=1 width=28) (actual time=0.224..0.239 rows=1 loops=1)
-> Nested Loop (cost=2.55..3.23 rows=1 width=32) (actual time=0.217..0.231 rows=1 loops=1)
-> Nested Loop (cost=2.41..3.02 rows=1 width=36) (actual time=0.197..0.209 rows=1 loops=1)
-> Nested Loop (cost=2.27..2.82 rows=1 width=36) (actual time=0.189..0.201 rows=1 loops=1)
-> Nested Loop (cost=2.13..2.61 rows=1 width=36) (actual time=0.183..0.194 rows=1 loops=1)
-> Nested Loop (cost=1.98..2.41 rows=1 width=28) (actual time=0.174..0.184 rows=1 loops=1)
-> Nested Loop (cost=1.84..2.21 rows=1 width=28) (actual time=0.168..0.177 rows=1 loops=1)
-> Nested Loop (cost=1.70..2.01 rows=1 width=28) (actual time=0.161..0.169 rows=1 loops=1)
-> Nested Loop (cost=1.56..1.80 rows=1 width=28) (actual time=0.148..0.155 rows=1 loops=1)
-> Nested Loop (cost=1.41..1.63 rows=1 width=28) (actual time=0.129..0.135 rows=1 loops=1)
-> Nested Loop (cost=1.27..1.47 rows=1 width=28) (actual time=0.119..0.125 rows=1 lo
ops=1)
-> Nested Loop (cost=1.13..1.30 rows=1 width=28) (actual time=0.113..0.118 row
s=1 loops=1)
-> Nested Loop (cost=0.99..1.13 rows=1 width=28) (actual time=0.105..0.1
09 rows=1 loops=1)
-> Nested Loop (cost=0.84..0.97 rows=1 width=28) (actual time=0.09
7..0.100 rows=1 loops=1)
-> Nested Loop (cost=0.70..0.80 rows=1 width=28) (actual tim
e=0.070..0.073 rows=1 loops=1)
-> Nested Loop (cost=0.56..0.63 rows=1 width=36) (actu
al time=0.044..0.046 rows=1 loops=1)
-> Index Scan using "PK_COUNTY" on "COUNTY" count
y_2 (cost=0.28..0.30 rows=1 width=8) (actual time=0.021..0.021 rows=1 loops=1)
Index Cond: ("ID" = "A_2"."COUNTY_ID")
Filter: ("IS_DELETED" = 0)
-> Index Scan using "IX_COUNTY_RESPONSIBILITY_COU
NTY_ID" on "COUNTY_RESPONSIBILITY" active_county_responsibility_2 (cost=0.28..0.32 rows=1 width=56) (actual time=0.021..0.022 rows=1 loops=1)
Index Cond: ("COUNTY_ID" = county_2."ID")
-> Index Scan using "PK_CITY" on "CITY" city_2 (cost=0
.14..0.17 rows=1 width=8) (actual time=0.026..0.026 rows=1 loops=1)
Index Cond: ("ID" = county_2."CITY_ID")
Filter: ("IS_DELETED" = 0)
-> Index Scan using "PK_SUBREGION" on "SUBREGION" rn_subregio
n_2 (cost=0.14..0.17 rows=1 width=8) (actual time=0.026..0.026 rows=1 loops=1)
Index Cond: ("ID" = active_county_responsibility_2."RN_S
UBREGION_ID")
Filter: ("IS_DELETED" = 0)
-> Index Scan using "PK_SUBREGION" on "SUBREGION" fn_subregion_2 (
cost=0.14..0.17 rows=1 width=8) (actual time=0.007..0.008 rows=1 loops=1)
Index Cond: ("ID" = active_county_responsibility_2."FN_SUBREGI
ON_ID")
Filter: ("IS_DELETED" = 0)
-> Index Scan using "PK_SUBREGION" on "SUBREGION" cn_subregion_2 (cost=0
.14..0.17 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=1)
Index Cond: ("ID" = active_county_responsibility_2."CN_SUBREGION_ID"
)
Filter: ("IS_DELETED" = 0)
-> Index Scan using "PK_SUBREGION" on "SUBREGION" mrod_subregion_2 (cost=0.14.
.0.17 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=1)
Index Cond: ("ID" = active_county_responsibility_2."MROD_SUBREGION_ID")
Filter: ("IS_DELETED" = 0)
-> Index Scan using "PK_SUBREGION" on "SUBREGION" frod_subregion_2 (cost=0.14..0.17
rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=1)
Index Cond: ("ID" = active_county_responsibility_2."FROD_SUBREGION_ID")
Filter: ("IS_DELETED" = 0)
-> Index Scan using "PK_SUBREGION" on "SUBREGION" lbs_subregion_2 (cost=0.14..0.17 rows=1
width=8) (actual time=0.018..0.019 rows=1 loops=1)
Index Cond: ("ID" = active_county_responsibility_2."LBS_SUBREGION_ID")
Filter: ("IS_DELETED" = 0)
-> Index Scan using "REGION_ID_DIVISION_ID_idx" on "REGION" rn_region_2 (cost=0.14..0.20 rows=1
width=8) (actual time=0.012..0.013 rows=1 loops=1)
Index Cond: ("ID" = rn_subregion_2."REGION_ID")
Filter: ("IS_DELETED" = 0)
-> Index Scan using "REGION_ID_DIVISION_ID_idx" on "REGION" fn_region_2 (cost=0.14..0.20 rows=1 width=
8) (actual time=0.006..0.006 rows=1 loops=1)
Index Cond: ("ID" = fn_subregion_2."REGION_ID")
Filter: ("IS_DELETED" = 0)
-> Index Scan using "REGION_ID_DIVISION_ID_idx" on "REGION" cn_region_2 (cost=0.14..0.20 rows=1 width=8) (ac
tual time=0.005..0.005 rows=1 loops=1)
Index Cond: ("ID" = cn_subregion_2."REGION_ID")
-> Index Scan using "REGION_ID_DIVISION_ID_idx" on "REGION" mrod_region_2 (cost=0.14..0.20 rows=1 width=16) (actua
l time=0.008..0.009 rows=1 loops=1)
Index Cond: ("ID" = mrod_subregion_2."REGION_ID")
-> Index Scan using "REGION_ID_DIVISION_ID_idx" on "REGION" frod_region_2 (cost=0.14..0.20 rows=1 width=8) (actual time=
0.005..0.005 rows=1 loops=1)
Index Cond: ("ID" = frod_subregion_2."REGION_ID")
-> Index Scan using "REGION_ID_DIVISION_ID_idx" on "REGION" lbs_region_2 (cost=0.14..0.20 rows=1 width=8) (actual time=0.007..
0.007 rows=1 loops=1)
Index Cond: ("ID" = lbs_subregion_2."REGION_ID")
-> Memoize (cost=0.14..0.20 rows=1 width=4) (actual time=0.019..0.020 rows=1 loops=1)
Cache Key: rn_region_2."MAIN_REGION_ID"
Cache Mode: logical
Hits: 0 Misses: 1 Evictions: 0 Overflows: 0 Memory Usage: 1kB
-> Index Scan using "PK_MAIN_REGION" on "MAIN_REGION" rn_main_region_2 (cost=0.13..0.19 rows=1 width=4) (actual time=0.017..0.
018 rows=1 loops=1)
Index Cond: ("ID" = rn_region_2."MAIN_REGION_ID")
Filter: ("IS_DELETED" = 0)
-> Memoize (cost=0.14..0.20 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=1)
Cache Key: fn_region_2."MAIN_REGION_ID"
Cache Mode: logical
Hits: 0 Misses: 1 Evictions: 0 Overflows: 0 Memory Usage: 1kB
-> Index Scan using "PK_MAIN_REGION" on "MAIN_REGION" fn_main_region_2 (cost=0.13..0.19 rows=1 width=4) (actual time=0.005..0.005 ro
ws=1 loops=1)
Index Cond: ("ID" = fn_region_2."MAIN_REGION_ID")
Filter: ("IS_DELETED" = 0)
-> Memoize (cost=0.14..0.20 rows=1 width=4) (actual time=0.010..0.011 rows=1 loops=1)
Cache Key: cn_region_2."MAIN_REGION_ID"
Cache Mode: logical
Hits: 0 Misses: 1 Evictions: 0 Overflows: 0 Memory Usage: 1kB
-> Index Scan using "PK_MAIN_REGION" on "MAIN_REGION" cn_main_region_2 (cost=0.13..0.19 rows=1 width=4) (actual time=0.009..0.009 rows=1 l
oops=1)
Index Cond: ("ID" = cn_region_2."MAIN_REGION_ID")
Filter: ("IS_DELETED" = 0)
-> Memoize (cost=0.14..0.20 rows=1 width=4) (actual time=0.005..0.006 rows=1 loops=1)
Cache Key: mrod_region_2."MAIN_REGION_ID"
Cache Mode: logical
Hits: 0 Misses: 1 Evictions: 0 Overflows: 0 Memory Usage: 1kB
-> Index Scan using "PK_MAIN_REGION" on "MAIN_REGION" mrod_main_region_2 (cost=0.13..0.19 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops
=1)
Index Cond: ("ID" = mrod_region_2."MAIN_REGION_ID")
Filter: ("IS_DELETED" = 0)
-> Memoize (cost=0.14..0.20 rows=1 width=4) (actual time=0.007..0.008 rows=1 loops=1)
Cache Key: frod_region_2."MAIN_REGION_ID"
Cache Mode: logical
Hits: 0 Misses: 1 Evictions: 0 Overflows: 0 Memory Usage: 1kB
-> Index Scan using "PK_MAIN_REGION" on "MAIN_REGION" frod_main_region_2 (cost=0.13..0.19 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=1)
Index Cond: ("ID" = frod_region_2."MAIN_REGION_ID")
Filter: ("IS_DELETED" = 0)
-> Memoize (cost=0.14..0.20 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=1)
Cache Key: lbs_region_2."MAIN_REGION_ID"
Cache Mode: logical
Hits: 0 Misses: 1 Evictions: 0 Overflows: 0 Memory Usage: 1kB
-> Index Scan using "PK_MAIN_REGION" on "MAIN_REGION" lbs_main_region_2 (cost=0.13..0.19 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=1)
Index Cond: ("ID" = lbs_region_2."MAIN_REGION_ID")
Filter: ("IS_DELETED" = 0)
-> Index Scan using "PK_COMPANY" on "COMPANY" "CC_2" (cost=0.27..2.49 rows=1 width=20) (actual time=0.027..0.027 rows=1 loops=1)
Index Cond: ("ID" = "DP"."TEAM_ID")
-> Index Scan using "IX_T_WF_WORKFLOWSTATUS_Code" on "T_WF_WORKFLOWSTATUS" (cost=0.14..0.16 rows=1 width=14) (actual time=0.017..0.018 rows=1 loops=1)
Index Cond: (("Code")::text = (wf_ins."EntityStatusCd")::text)
Filter: ("GCRecId" IS NULL)
-> Subquery Scan on "*SELECT* 4" (cost=390807.37..1522921.68 rows=1 width=207) (actual time=0.076..0.089 rows=0 loops=1)
-> Nested Loop (cost=390807.37..1522921.66 rows=1 width=205) (actual time=0.075..0.088 rows=0 loops=1)
InitPlan 21 (returns $20)
-> Limit (cost=0.27..2.49 rows=1 width=4) (never executed)
-> Index Scan using "IX_COMPANY_NAME" on "COMPANY" c_3 (cost=0.27..2.49 rows=1 width=4) (never executed)
Index Cond: (("NAME")::text = 'DBS'::text)
-> Hash Join (cost=390804.74..1522919.00 rows=1 width=186) (actual time=0.075..0.087 rows=0 loops=1)
Hash Cond: ("WF_INS"."InstanceId" = "DP_1"."INSTANCE_ID")
-> Nested Loop Left Join (cost=390785.54..1518585.67 rows=1150432 width=38) (never executed)
-> Hash Right Join (cost=390785.39..1489818.80 rows=1150432 width=19) (never executed)
Hash Cond: ("WF_CSTP"."InstanceId" = "WF_INS"."InstanceId")
Join Filter: (("WF_INS"."StatusCd")::text = 'CMP'::text)
-> Index Scan using "IX_T_WF_STEP_StepDefTypeCd" on "T_WF_STEP" "WF_CSTP" (cost=0.43..1098968.54 rows=4019 width=8) (never executed)
Index Cond: (("StepDefTypeCd")::text = 'End'::text)
Filter: ("StepId" = (SubPlan 25))
SubPlan 25
-> Result (cost=1.06..1.07 rows=1 width=8) (never executed)
InitPlan 24 (returns $24)
-> Limit (cost=0.43..1.06 rows=1 width=8) (never executed)
-> Index Only Scan Backward using "T_WF_STEP_InstanceId_StepId_idx" on "T_WF_STEP" "T_WF_STEP_3" (cost=0.43..37.05 rows=59 width=8) (never executed)
Index Cond: (("InstanceId" = "WF_CSTP"."InstanceId") AND ("StepId" IS NOT NULL))
Heap Fetches: 0
-> Hash (cost=376404.55..376404.55 rows=1150432 width=22) (never executed)
-> Merge Left Join (cost=292842.33..376404.55 rows=1150432 width=22) (never executed)
Merge Cond: ("WF_INS"."InstanceId" = "WF_STP"."InstanceId")
-> Index Scan using "T_WF_INSTANCE_pkey" on "T_WF_INSTANCE" "WF_INS" (cost=0.43..80668.60 rows=1150432 width=38) (never executed)
-> Sort (cost=292841.90..292844.90 rows=1198 width=8) (never executed)
Sort Key: "WF_STP"."InstanceId"
-> Index Scan using "IX_T_WF_STEP_StatusCd" on "T_WF_STEP" "WF_STP" (cost=0.43..292780.65 rows=1198 width=8) (never executed)
Index Cond: (("StatusCd")::text = ANY ('{P,Q,S,T,U,W}'::text[]))
Filter: ("StepId" = (SubPlan 23))
SubPlan 23
-> Result (cost=1.06..1.07 rows=1 width=8) (never executed)
InitPlan 22 (returns $22)
-> Limit (cost=0.43..1.06 rows=1 width=8) (never executed)
-> Index Only Scan Backward using "T_WF_STEP_InstanceId_StepId_idx" on "T_WF_STEP" "T_WF_STEP_2" (cost=0.43..37.05 rows=59 width=8) (n
ever executed)
Index Cond: (("InstanceId" = "WF_STP"."InstanceId") AND ("StepId" IS NOT NULL))
Heap Fetches: 0
-> Memoize (cost=0.15..0.17 rows=1 width=31) (never executed)
Cache Key: ("WF_INS"."EntityStatusCd")::text
Cache Mode: logical
-> Index Scan using "IX_T_WF_WORKFLOWSTATUS_Code" on "T_WF_WORKFLOWSTATUS" "WF_STS" (cost=0.14..0.16 rows=1 width=31) (never executed)
Index Cond: (("Code")::text = ("WF_INS"."EntityStatusCd")::text)
-> Hash (cost=19.19..19.19 rows=1 width=164) (actual time=0.034..0.043 rows=0 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 8kB
-> Nested Loop Left Join (cost=4.95..19.19 rows=1 width=164) (actual time=0.034..0.042 rows=0 loops=1)
-> Nested Loop Left Join (cost=4.68..14.48 rows=1 width=148) (actual time=0.034..0.042 rows=0 loops=1)
-> Nested Loop Left Join (cost=1.43..10.20 rows=1 width=144) (actual time=0.033..0.036 rows=0 loops=1)
-> Nested Loop Left Join (cost=1.00..9.70 rows=1 width=144) (actual time=0.033..0.035 rows=0 loops=1)
-> Nested Loop (cost=0.57..7.06 rows=1 width=109) (actual time=0.033..0.035 rows=0 loops=1)
-> Nested Loop (cost=0.29..4.55 rows=1 width=103) (actual time=0.033..0.034 rows=0 loops=1)
Join Filter: ("DP_1"."TENANT_ID" = "T_1"."ID")
-> Nested Loop (cost=0.29..3.53 rows=1 width=94) (actual time=0.033..0.034 rows=0 loops=1)
-> Seq Scan on "DDO" "DP_1" (cost=0.00..1.02 rows=1 width=88) (actual time=0.032..0.032 rows=0 loops=1)
Filter: ("ID" = 1506074)
Rows Removed by Filter: 2
-> Index Scan using "PK_USER_N" on "USER_N" "CU_3" (cost=0.29..2.51 rows=1 width=14) (never executed)
Index Cond: ("ID" = "DP_1"."CREATED_BY_USER_ID")
-> Seq Scan on "TENANT" "T_1" (cost=0.00..1.01 rows=1 width=13) (never executed)
-> Index Scan using "PK_USER_N" on "USER_N" "UU_3" (cost=0.29..2.51 rows=1 width=14) (never executed)
Index Cond: ("ID" = "DP_1"."LAST_UPDATED_USER_ID")
-> Index Scan using "SITE_ID_NO_idx" on "SITE" "S_3" (cost=0.42..2.64 rows=1 width=43) (never executed)
Index Cond: ("ID" = "DP_1"."SITE_ID")
-> Index Scan using "PK_ADDRESS" on "ADDRESS" "A_3" (cost=0.43..0.49 rows=1 width=8) (never executed)
Index Cond: ("ID" = "S_3"."ADDRESS_ID")
-> Nested Loop (cost=3.25..4.27 rows=1 width=12) (never executed)
-> Nested Loop (cost=3.11..4.06 rows=1 width=16) (never executed)
-> Nested Loop (cost=2.97..3.85 rows=1 width=20) (never executed)
-> Nested Loop (cost=2.83..3.65 rows=1 width=24) (never executed)
-> Nested Loop (cost=2.69..3.44 rows=1 width=28) (never executed)
-> Nested Loop (cost=2.55..3.23 rows=1 width=32) (never executed)
-> Nested Loop (cost=2.41..3.02 rows=1 width=36) (never executed)
-> Nested Loop (cost=2.27..2.82 rows=1 width=36) (never executed)
-> Nested Loop (cost=2.13..2.61 rows=1 width=36) (never executed)
-> Nested Loop (cost=1.98..2.41 rows=1 width=28) (never executed)
-> Nested Loop (cost=1.84..2.21 rows=1 width=28) (never executed)
-> Nested Loop (cost=1.70..2.01 rows=1 width=28) (never executed)
-> Nested Loop (cost=1.56..1.80 rows=1 width=28) (never executed)
-> Nested Loop (cost=1.41..1.63 rows=1 width=28) (never executed)
-> Nested Loop (cost=1.27..1.47 rows=1 width=28) (never executed)
-> Nested Loop (cost=1.13..1.30 rows=1 width=28) (never executed)
-> Nested Loop (cost=0.99..1.13 rows=1 width=28) (never executed)
-> Nested Loop (cost=0.84..0.97 rows=1 width=28) (never executed)
-> Nested Loop (cost=0.70..0.80 rows=1 width=28) (never exec
uted)
-> Nested Loop (cost=0.56..0.63 rows=1 width=36) (neve
r executed)
-> Index Scan using "PK_COUNTY" on "COUNTY" count
y_3 (cost=0.28..0.30 rows=1 width=8) (never executed)
Index Cond: ("ID" = "A_3"."COUNTY_ID")
Filter: ("IS_DELETED" = 0)
-> Index Scan using "IX_COUNTY_RESPONSIBILITY_COU
NTY_ID" on "COUNTY_RESPONSIBILITY" active_county_responsibility_3 (cost=0.28..0.32 rows=1 width=56) (never executed)
Index Cond: ("COUNTY_ID" = county_3."ID")
-> Index Scan using "PK_CITY" on "CITY" city_3 (cost=0
.14..0.17 rows=1 width=8) (never executed)
Index Cond: ("ID" = county_3."CITY_ID")
Filter: ("IS_DELETED" = 0)
-> Index Scan using "PK_SUBREGION" on "SUBREGION" rn_subregio
n_3 (cost=0.14..0.17 rows=1 width=8) (never executed)
Index Cond: ("ID" = active_county_responsibility_3."RN_S
UBREGION_ID")
Filter: ("IS_DELETED" = 0)
-> Index Scan using "PK_SUBREGION" on "SUBREGION" fn_subregion_3 (
cost=0.14..0.17 rows=1 width=8) (never executed)
Index Cond: ("ID" = active_county_responsibility_3."FN_SUBREGI
ON_ID")
Filter: ("IS_DELETED" = 0)
-> Index Scan using "PK_SUBREGION" on "SUBREGION" cn_subregion_3 (cost=0
.14..0.17 rows=1 width=8) (never executed)
Index Cond: ("ID" = active_county_responsibility_3."CN_SUBREGION_ID"
)
Filter: ("IS_DELETED" = 0)
-> Index Scan using "PK_SUBREGION" on "SUBREGION" mrod_subregion_3 (cost=0.14.
.0.17 rows=1 width=8) (never executed)
Index Cond: ("ID" = active_county_responsibility_3."MROD_SUBREGION_ID")
Filter: ("IS_DELETED" = 0)
-> Index Scan using "PK_SUBREGION" on "SUBREGION" frod_subregion_3 (cost=0.14..0.17
rows=1 width=8) (never executed)
Index Cond: ("ID" = active_county_responsibility_3."FROD_SUBREGION_ID")
Filter: ("IS_DELETED" = 0)
-> Index Scan using "PK_SUBREGION" on "SUBREGION" lbs_subregion_3 (cost=0.14..0.17 rows=1
width=8) (never executed)
Index Cond: ("ID" = active_county_responsibility_3."LBS_SUBREGION_ID")
Filter: ("IS_DELETED" = 0)
-> Index Scan using "REGION_ID_DIVISION_ID_idx" on "REGION" rn_region_3 (cost=0.14..0.20 rows=1
width=8) (never executed)
Index Cond: ("ID" = rn_subregion_3."REGION_ID")
Filter: ("IS_DELETED" = 0)
-> Index Scan using "REGION_ID_DIVISION_ID_idx" on "REGION" fn_region_3 (cost=0.14..0.20 rows=1 width=
8) (never executed)
Index Cond: ("ID" = fn_subregion_3."REGION_ID")
Filter: ("IS_DELETED" = 0)
-> Index Scan using "REGION_ID_DIVISION_ID_idx" on "REGION" cn_region_3 (cost=0.14..0.20 rows=1 width=8) (ne
ver executed)
Index Cond: ("ID" = cn_subregion_3."REGION_ID")
-> Index Scan using "REGION_ID_DIVISION_ID_idx" on "REGION" mrod_region_3 (cost=0.14..0.20 rows=1 width=16) (never
executed)
Index Cond: ("ID" = mrod_subregion_3."REGION_ID")
-> Index Scan using "REGION_ID_DIVISION_ID_idx" on "REGION" frod_region_3 (cost=0.14..0.20 rows=1 width=8) (never execut
ed)
Index Cond: ("ID" = frod_subregion_3."REGION_ID")
-> Index Scan using "REGION_ID_DIVISION_ID_idx" on "REGION" lbs_region_3 (cost=0.14..0.20 rows=1 width=8) (never executed)
Index Cond: ("ID" = lbs_subregion_3."REGION_ID")
-> Memoize (cost=0.14..0.20 rows=1 width=4) (never executed)
Cache Key: rn_region_3."MAIN_REGION_ID"
Cache Mode: logical
-> Index Scan using "PK_MAIN_REGION" on "MAIN_REGION" rn_main_region_3 (cost=0.13..0.19 rows=1 width=4) (never executed)
Index Cond: ("ID" = rn_region_3."MAIN_REGION_ID")
Filter: ("IS_DELETED" = 0)
-> Memoize (cost=0.14..0.20 rows=1 width=4) (never executed)
Cache Key: fn_region_3."MAIN_REGION_ID"
Cache Mode: logical
-> Index Scan using "PK_MAIN_REGION" on "MAIN_REGION" fn_main_region_3 (cost=0.13..0.19 rows=1 width=4) (never executed)
Index Cond: ("ID" = fn_region_3."MAIN_REGION_ID")
Filter: ("IS_DELETED" = 0)
-> Memoize (cost=0.14..0.20 rows=1 width=4) (never executed)
Cache Key: cn_region_3."MAIN_REGION_ID"
Cache Mode: logical
-> Index Scan using "PK_MAIN_REGION" on "MAIN_REGION" cn_main_region_3 (cost=0.13..0.19 rows=1 width=4) (never executed)
Index Cond: ("ID" = cn_region_3."MAIN_REGION_ID")
Filter: ("IS_DELETED" = 0)
-> Memoize (cost=0.14..0.20 rows=1 width=4) (never executed)
Cache Key: mrod_region_3."MAIN_REGION_ID"
Cache Mode: logical
-> Index Scan using "PK_MAIN_REGION" on "MAIN_REGION" mrod_main_region_3 (cost=0.13..0.19 rows=1 width=4) (never executed)
Index Cond: ("ID" = mrod_region_3."MAIN_REGION_ID")
Filter: ("IS_DELETED" = 0)
-> Memoize (cost=0.14..0.20 rows=1 width=4) (never executed)
Cache Key: frod_region_3."MAIN_REGION_ID"
Cache Mode: logical
-> Index Scan using "PK_MAIN_REGION" on "MAIN_REGION" frod_main_region_3 (cost=0.13..0.19 rows=1 width=4) (never executed)
Index Cond: ("ID" = frod_region_3."MAIN_REGION_ID")
Filter: ("IS_DELETED" = 0)
-> Memoize (cost=0.14..0.20 rows=1 width=4) (never executed)
Cache Key: lbs_region_3."MAIN_REGION_ID"
Cache Mode: logical
-> Index Scan using "PK_MAIN_REGION" on "MAIN_REGION" lbs_main_region_3 (cost=0.13..0.19 rows=1 width=4) (never executed)
Index Cond: ("ID" = lbs_region_3."MAIN_REGION_ID")
Filter: ("IS_DELETED" = 0)
-> Index Scan using "PK_COMPANY" on "COMPANY" "CC_3" (cost=0.27..2.49 rows=1 width=20) (never executed)
Index Cond: ("ID" = "DP_1"."TEAM_ID")
-> Index Scan using "IX_T_WF_WORKFLOWSTATUS_Code" on "T_WF_WORKFLOWSTATUS" "T_WF_WORKFLOWSTATUS_1" (cost=0.14..0.16 rows=1 width=14) (never executed)
Index Cond: (("Code")::text = ("WF_INS"."EntityStatusCd")::text)
Filter: ("GCRecId" IS NULL)
Planning Time: 595.452 ms
Execution Time: 7130.235 ms
