explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fYAi : Optimization for: plan #o33B

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.016 1,939.660 ↓ 20.0 20 1

Subquery Scan on SYSINTERNAL-5-0 (cost=140,697,413,158,048.22..140,698,449,069,937.28 rows=1 width=3,479) (actual time=1,914.784..1,939.660 rows=20 loops=1)

  • Filter: ("SYSINTERNAL-5-0".RNUM >= 1)
2. 0.058 1,939.644 ↓ 0.0 20 1

Count (cost=140,697,413,158,048.22..140,698,449,069,937.28 rows=0 width=3,479) (actual time=1,914.777..1,939.644 rows=20 loops=1)

  • Stop Keys: (ROWNUM <= 20)
3. 0.084 1,913.306 ↑ 207,602,861.6 21 1

Hash Join (cost=140,697,413,158,048.22..140,698,405,473,336.34 rows=4,359,660,093 width=3,373) (actual time=1,913.218..1,913.306 rows=21 loops=1)

  • Hash Cond: (TR.UTDEPARTMENT = T1.DEPT_ID)
4. 0.029 1,911.980 ↑ 415,205,723.1 21 1

Unique (cost=140,687,413,157,534.61..140,688,285,089,553.22 rows=8,719,320,186 width=8,642) (actual time=1,911.939..1,911.980 rows=21 loops=1)

5. 105.996 1,911.951 ↑ 415,205,723.1 21 1

Sort (cost=140,687,413,157,534.61..140,687,434,955,835.08 rows=8,719,320,186 width=8,642) (actual time=1,911.937..1,911.951 rows=21 loops=1)

  • Sort Key: (DECODE((TR.STATE)::TEXT, '1'::TEXT, '4'::CHARACTER VARYING, '3'::TEXT, '1'::CHARACTER VARYING, '2'::TEXT, '2'::CHARACTER VARYING, '4'::TEXT, '3'::CHARACTER VARYING, '5'::CHARACTER VARYING)), (((TO_TIMESTAMP_TZ((T.XBSJ)::TEXT, 'YYYY-MM-DD'::TEXT))::TIMESTAMP WITHOUT TIME ZONE - ('NOW'::TEXT)::TIMESTAMP(0) without time zone)), T.DATAKEY, TR.FILENO, ((SubPlan 2)), TR.TYPE, TR.UTTYPE, TR.PROPOSER, TR.TITLE, D.DEPT_NAME, TR.UTDEPARTMENT, TR.LIMITTIME, T.FGSLD, T.FGSLDID, T.ZBCS, T.ZBCSID, T.CSFZR, T.CSFZRID, T.CBR, T.CBRID, T.TRANSFERUSERID, T.LXDH, T.BLQK, T.CSWBJYY, T.QFRQ, T.SFBJ, TN.CREATER, TR.REPLYID, ((SubPlan 3)), ((SubPlan 4)), T.BJQK, (CASE WHEN ((T.SFBJ = '1'::CHARACTER VARYING) AND ((TO_TIMESTAMP_TZ((TR.LIMITTIME)::TEXT, 'YYYY-MM-DD'::TEXT))::TIMESTAMP WITHOUT TIME ZONE >= (TO_TIMESTAMP_TZ((T.BJSJ)::TEXT, 'YYYY-MM-DD'::TEXT))::TIMESTAMP WITHOUT TIME ZONE)) THEN '1'::TEXT WHEN (((T.SFBJ <> '1'::CHARACTER VARYING) OR (T.SFBJ IS NULL)) AND ((TO_TIMESTAMP_TZ((TR.LIMITTIME)::TEXT, 'YYYY-MM-DD'::TEXT))::TIMESTAMP WITHOUT TIME ZONE >= (TO_TIMESTAMP_TZ(TO_CHAR(('NOW'::TEXT)::TIMESTAMP(0) without time zone, 'YYYY-MM-DD'::TEXT), 'YYYY-MM-DD'::TEXT))::TIMESTAMP WITHOUT TIME ZONE)) THEN '2'::TEXT WHEN (((T.SFBJ <> '1'::CHARACTER VARYING) OR (T.SFBJ IS NULL)) AND ((TO_TIMESTAMP_TZ(TO_CHAR(('NOW'::TEXT)::TIMESTAMP(0) without time zone, 'YYYY-MM-DD'::TEXT), 'YYYY-MM-DD'::TEXT))::TIMESTAMP WITHOUT TIME ZONE > (TO_TIMESTAMP_TZ((TR.LIMITTIME)::TEXT, 'YYYY-MM-DD'::TEXT))::TIMESTAMP WITHOUT TIME ZONE)) THEN '3'::TEXT WHEN ((T.SFBJ = '1'::CHARACTER VARYING) AND ((TO_TIMESTAMP_TZ((TR.LIMITTIME)::TEXT, 'YYYY-MM-DD'::TEXT))::TIMESTAMP WITHOUT TIME ZONE < (TO_TIMESTAMP_TZ((T.BJSJ)::TEXT, 'YYYY-MM-DD'::TEXT))::TIMESTAMP WITHOUT TIME ZONE)) THEN '4'::TEXT ELSE NULL::TEXT END), T.BJSJ, (CASE WHEN (((((TO_TIMESTAMP_TZ((TR.LIMITTIME)::TEXT, 'YYYY-MM-DD'::TEXT))::TIMESTAMP WITHOUT TIME ZONE - (TO_TIMESTAMP_TZ(TO_CHAR(('NOW'::TEXT)::TIMESTAMP(0) without time zone, 'YYYY-MM-DD'::TEXT), 'YYYY-MM-DD'::TEXT))::TIMESTAMP WITHOUT TIME ZONE))::TEXT >= '0'::TEXT) AND ((((TO_TIMESTAMP_TZ((TR.LIMITTIME)::TEXT, 'YYYY-MM-DD'::TEXT))::TIMESTAMP WITHOUT TIME ZONE - (TO_TIMESTAMP_TZ(TO_CHAR(('NOW'::TEXT)::TIMESTAMP(0) without time zone, 'YYYY-MM-DD'::TEXT), 'YYYY-MM-DD'::TEXT))::TIMESTAMP WITHOUT TIME ZONE))::TEXT <= '2'::TEXT)) THEN 1 ELSE 0 END), T.SERIALNUMBER, T.SUPERVISIONDATE, T.SATISFIED, T.COMMUNICATED, T.COPIED
  • Sort Method: quicksort Memory: 7,253kB
6. 429.726 1,805.955 ↑ 675,235.8 12,913 1

Hash Join (cost=4,231.10..140,621,362,004,412.12 rows=8,719,320,186 width=8,642) (actual time=94.465..1,805.955 rows=12,913 loops=1)

  • Hash Cond: (T.DATAKEY = TR.DATAKEY)
7. 359.568 560.751 ↑ 1,613.3 51,511 1

Hash Right Join (cost=1,204.33..2,940,368.17 rows=83,104,472 width=5,777) (actual time=38.600..560.751 rows=51,511 loops=1)

  • Hash Cond: (TN.BUSINESS_ID = T.DATAKEY)
8. 162.642 162.642 ↑ 1.0 513,466 1

Seq Scan on TBL_MANAGE_NO TN (cost=0.00..29,223.66 rows=513,466 width=115) (actual time=0.008..162.642 rows=513,466 loops=1)

9. 15.048 38.541 ↑ 1.0 32,370 1

Hash (cost=799.70..799.70 rows=32,370 width=1,406) (actual time=38.541..38.541 rows=32,370 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 3,038kB
10. 3.772 23.493 ↑ 1.0 32,370 1

Append (cost=0.00..799.70 rows=32,370 width=1,406) (actual time=0.011..23.493 rows=32,370 loops=1)

11. 18.994 18.994 ↑ 1.0 31,455 1

Seq Scan on "P#CZOA_T_SUPERVISION_PARTITION_HISTORY" T (cost=0.00..767.55 rows=31,455 width=1,407) (actual time=0.011..18.994 rows=31,455 loops=1)

12. 0.727 0.727 ↑ 1.0 915 1

Seq Scan on "P#CZOA_T_SUPERVISION_PARTITION_CURRENT" T_1 (cost=0.00..32.15 rows=915 width=1,378) (actual time=0.016..0.727 rows=915 loops=1)

13. 14.953 53.611 ↑ 1.0 20,930 1

Hash (cost=2,764.48..2,764.48 rows=20,984 width=2,447) (actual time=53.611..53.611 rows=20,930 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 6,747kB
14. 13.506 38.658 ↑ 1.0 20,930 1

Hash Join (cost=21.96..2,764.48 rows=20,984 width=2,447) (actual time=0.352..38.658 rows=20,930 loops=1)

  • Hash Cond: (TR.UTDEPARTMENT = D.DEPT_ID)
15. 2.362 24.827 ↑ 1.0 20,984 1

Append (cost=0.00..2,453.98 rows=20,984 width=295) (actual time=0.014..24.827 rows=20,984 loops=1)

16. 21.799 21.799 ↑ 1.0 20,348 1

Seq Scan on "P#CZOA_T_PROPOSAL_PARTITION_HISTORY" TR (cost=0.00..2,391.39 rows=20,348 width=296) (actual time=0.012..21.799 rows=20,348 loops=1)

  • Filter: (LIMITTIME IS NOT NULL)
  • Rows Removed by Filter: 1,891
17. 0.666 0.666 ↑ 1.0 636 1

Seq Scan on "P#CZOA_T_PROPOSAL_PARTITION_CURRENT" TR_1 (cost=0.00..62.59 rows=636 width=247) (actual time=0.014..0.666 rows=636 loops=1)

  • Filter: (LIMITTIME IS NOT NULL)
  • Rows Removed by Filter: 23
18. 0.133 0.325 ↑ 1.0 354 1

Hash (cost=17.54..17.54 rows=354 width=45) (actual time=0.325..0.325 rows=354 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 35kB
19. 0.192 0.192 ↑ 1.0 354 1

Seq Scan on TBL_SYS_DEPARTMENT D (cost=0.00..17.54 rows=354 width=45) (actual time=0.009..0.192 rows=354 loops=1)

20.          

SubPlan (for Hash Join)

21. 103.304 103.304 ↑ 1.0 1 12,913

Index Only Scan using IDX_CZOA_T_DATAITEM_02 on CZOA_T_DATAITEM M (cost=0.27..8.29 rows=1 width=13) (actual time=0.007..0.008 rows=1 loops=12,913)

  • Index Cond: ((GUID = TR.TYPE) AND (ELEMENTCODE = 'PRO_TYPE'::CHARACTER VARYING))
  • Heap Fetches: 12,913
22. 142.043 439.042 ↑ 1.0 1 12,913

Aggregate (cost=8,059.53..8,059.54 rows=1 width=32) (actual time=0.034..0.034 rows=1 loops=12,913)

23. 116.217 296.999 ↑ 2,567.0 1 12,913

HashAggregate (cost=8,001.77..8,027.44 rows=2,567 width=90) (actual time=0.020..0.023 rows=1 loops=12,913)

  • Group Key: T_2.BUSINESS_ID, U.USER_NAME
24. 36.871 180.782 ↑ 2,567.0 1 12,913

Hash Join (cost=496.37..7,988.94 rows=2,567 width=90) (actual time=0.013..0.014 rows=1 loops=12,913)

  • Hash Cond: (T_2.MANAGER = U.USER_ID)
25. 38.739 142.043 ↑ 2,567.0 1 12,913

Bitmap Heap Scan on TBL_MANAGE_NO T_2 (cost=156.44..7,613.71 rows=2,567 width=115) (actual time=0.011..0.011 rows=1 loops=12,913)

  • Recheck Cond: (BUSINESS_ID = TN.BUSINESS_ID)
  • Heap Blocks: exact=12,835
26. 103.304 103.304 ↑ 2,567.0 1 12,913

Bitmap Index Scan on TBL_MANAGE_NO_INDEX_1 (cost=0.00..155.80 rows=2,567 width=0) (actual time=0.008..0.008 rows=1 loops=12,913)

  • Index Cond: (BUSINESS_ID = TN.BUSINESS_ID)
27. 0.688 1.868 ↑ 1.0 1,908 1

Hash (cost=316.08..316.08 rows=1,908 width=41) (actual time=1.868..1.868 rows=1,908 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 154kB
28. 1.180 1.180 ↑ 1.0 1,908 1

Seq Scan on TBL_SYS_USER U (cost=0.00..316.08 rows=1,908 width=41) (actual time=0.008..1.180 rows=1,908 loops=1)

29. 103.304 219.521 ↑ 1.0 1 12,913

Aggregate (cost=8,059.53..8,059.54 rows=1 width=32) (actual time=0.017..0.017 rows=1 loops=12,913)

30. 77.478 116.217 ↓ 0.0 0 12,913

HashAggregate (cost=8,001.77..8,027.44 rows=2,567 width=90) (actual time=0.009..0.009 rows=0 loops=12,913)

  • Group Key: T_2_1.BUSINESS_ID, U_1.USER_NAME
31. 23.765 38.739 ↓ 0.0 0 12,913

Hash Join (cost=496.37..7,988.94 rows=2,567 width=90) (actual time=0.003..0.003 rows=0 loops=12,913)

  • Hash Cond: (T_2_1.MANAGER = U_1.USER_ID)
32. 0.000 12.913 ↓ 0.0 0 12,913

Bitmap Heap Scan on TBL_MANAGE_NO T_2_1 (cost=156.44..7,613.71 rows=2,567 width=115) (actual time=0.001..0.001 rows=0 loops=12,913)

  • Recheck Cond: (BUSINESS_ID = TR.REPLYID)
  • Heap Blocks: exact=49
33. 12.913 12.913 ↓ 0.0 0 12,913

Bitmap Index Scan on TBL_MANAGE_NO_INDEX_1 (cost=0.00..155.80 rows=2,567 width=0) (actual time=0.001..0.001 rows=0 loops=12,913)

  • Index Cond: (BUSINESS_ID = TR.REPLYID)
34. 0.707 2.061 ↑ 1.0 1,908 1

Hash (cost=316.08..316.08 rows=1,908 width=41) (actual time=2.061..2.061 rows=1,908 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 154kB
35. 1.354 1.354 ↑ 1.0 1,908 1

Seq Scan on TBL_SYS_USER U_1 (cost=0.00..316.08 rows=1,908 width=41) (actual time=0.014..1.354 rows=1,908 loops=1)

36. 0.115 1.242 ↓ 177.0 354 1

Hash (cost=10,000,000,513.58..10,000,000,513.58 rows=2 width=32) (actual time=1.242..1.242 rows=354 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 31kB
37. 0.099 1.127 ↓ 177.0 354 1

Unique (cost=10,000,000,513.57..10,000,000,513.58 rows=2 width=32) (actual time=0.979..1.127 rows=354 loops=1)

38. 0.401 1.028 ↓ 177.0 354 1

Sort (cost=10,000,000,513.57..10,000,000,513.57 rows=2 width=32) (actual time=0.978..1.028 rows=354 loops=1)

  • Sort Key: T1.DEPT_ID
  • Sort Method: quicksort Memory: 52kB
39. 0.310 0.627 ↓ 177.0 354 1

Nested Loop Semi Join (cost=10,000,000,000.27..10,000,000,513.56 rows=2 width=32) (actual time=0.070..0.627 rows=354 loops=1)

  • Join Filter: ((T0.DEPT_ID = '4028816c49795e8e014979b1f2a01740'::CHARACTER VARYING) OR (((T0.COMM_ORG_LEVEL)::TEXT ~~ '100010001010%'::TEXT) AND (T0.COMM_ORG_IDENTY = '1000'::CHARACTER VARYING) AND (T0.DEPT_ID <> '4028816c49795e8e014979b1f2a01740'::CHARACTER VARYING) AND (T0.DEPT_ID = T1.DEPT_ID)))
40. 0.317 0.317 ↑ 1.0 354 1

Index Only Scan using TBL_SYS_DEPARTMENT_PKEY on TBL_SYS_DEPARTMENT T1 (cost=0.27..81.54 rows=354 width=32) (actual time=0.022..0.317 rows=354 loops=1)

  • Heap Fetches: 354
41. 0.000 0.000 ↑ 11.0 1 354

Materialize (cost=0.00..335.22 rows=11 width=49) (actual time=0.000..0.000 rows=1 loops=354)

42. 0.040 0.040 ↑ 11.0 1 1

Seq Scan on TBL_SYS_USER T0 (cost=0.00..335.16 rows=11 width=49) (actual time=0.040..0.040 rows=1 loops=1)

  • Filter: ((DEPT_ID = '4028816c49795e8e014979b1f2a01740'::CHARACTER VARYING) OR (((COMM_ORG_LEVEL)::TEXT ~~ '100010001010%'::TEXT) AND (COMM_ORG_IDENTY = '1000'::CHARACTER VARYING) AND (DEPT_ID <> '4028816c49795e8e014979b1f2a01740'::CHARACTER VARYING)))
  • Rows Removed by Filter: 17
43.          

SubPlan (for Count)

44. 26.280 26.280 ↓ 0.0 0 20

Seq Scan on TBL_SYS_USER S (cost=0.00..325.62 rows=1 width=8) (actual time=1.314..1.314 rows=0 loops=20)

  • Filter: ((COMM_RECORD_IDENTY = '1'::NUMERIC) AND ((USER_ID)::TEXT = ((SubPlan 3))))
  • Rows Removed by Filter: 1,908
Planning time : 8.633 ms
Execution time : 1,940.436 ms