explain.depesz.com

PostgreSQL's explain analyze made readable

Result: S3po0 : Optimization for: plan #sieU

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.001 10,459.991 ↓ 0.0 0 1

Subquery Scan on A (cost=616,367.56..644,606.24 rows=1 width=12,783) (actual time=10,459.991..10,459.991 rows=0 loops=1)

  • Filter: (A.RN >= 1)
2. 0.001 10,459.990 ↓ 0.0 0 1

Count (cost=616,367.56..644,606.24 rows=0 width=12,783) (actual time=10,459.990..10,459.990 rows=0 loops=1)

  • Stop Keys: ((ROWNUM)::NUMERIC <= (TO_NUMBER('1'::TEXT, '99999999999999999999999999999999999999D99999999999999999999999999999999999999'::TEXT) * '20'::NUMERIC))
3. 0.000 10,459.989 ↓ 0.0 0 1

Count (cost=616,367.56..644,606.23 rows=0 width=12,775) (actual time=10,459.989..10,459.989 rows=0 loops=1)

4. 0.004 10,459.989 ↓ 0.0 0 1

Nested Loop (cost=616,367.56..638,113.40 rows=649,283 width=12,607) (actual time=10,459.989..10,459.989 rows=0 loops=1)

  • Join Filter: ("SYSINTERNAL-41-0".BUSINESS_ID = P.BUSINESSID_)
5. 0.062 10,459.945 ↑ 245.0 1 1

Nested Loop (cost=616,367.14..635,598.79 rows=245 width=12,689) (actual time=10,407.491..10,459.945 rows=1 loops=1)

6. 186.413 10,457.628 ↓ 15.8 205 1

Subquery Scan on SYSINTERNAL-41-0 (cost=616,366.86..635,468.61 rows=13 width=153) (actual time=8,840.555..10,457.628 rows=205 loops=1)

  • Filter: (("SYSINTERNAL-41-0".MM = 1) AND ("SYSINTERNAL-41-0".MANAGER = '4028816c49795e8e014979c51fd917f2'::CHARACTER VARYING))
  • Rows Removed by Filter: 509,215
7. 892.686 10,271.215 ↓ 1.0 509,420 1

WindowAgg (cost=616,366.86..627,827.91 rows=509,380 width=1,668) (actual time=8,824.332..10,271.215 rows=509,420 loops=1)

8. 7,698.285 9,378.529 ↓ 1.0 509,420 1

Sort (cost=616,366.86..617,640.31 rows=509,380 width=1,596) (actual time=8,824.314..9,378.529 rows=509,420 loops=1)

  • Sort Key: T1.BUSINESS_ID, T1.TRANSFERUSERID, T1.CREATE_TIME DESC
  • Sort Method: external merge Disk: 153,568kB
9. 599.593 1,680.244 ↓ 1.0 509,420 1

GroupAggregate (cost=0.67..214,648.26 rows=509,380 width=1,596) (actual time=0.042..1,680.244 rows=509,420 loops=1)

  • Group Key: T1.TITLE, T1.CREATER, T1.MANAGER, T1.MANAGE_TIME, T1.MANAGE_URL, T1.BUSINESS_ID, T1.TASK_ID, T1.STYLE, T1.RECORD_IDENTY, T1.PRIORITY, T1.PROCESS_INSTANCE_ID, T1.TYPE, T1.BLANK0, T1.BLANK1, T1.SUB_PROCESS_FLAG, T1.TRANSFERUSERID, T1.CREATE_TIME
10. 1,080.651 1,080.651 ↑ 1.0 509,522 1

Index Only Scan using IDX002 on TBL_MANAGE_NO T1 (cost=0.67..187,898.97 rows=509,541 width=1,596) (actual time=0.029..1,080.651 rows=509,522 loops=1)

  • Filter: ((RECORD_IDENTY = '0'::BPCHAR) OR (RECORD_IDENTY = '2'::BPCHAR))
  • Rows Removed by Filter: 3,944
  • Heap Fetches: 513,466
11. 0.410 2.255 ↓ 0.0 0 205

Append (cost=0.28..9.99 rows=2 width=1,879) (actual time=0.011..0.011 rows=0 loops=205)

12. 1.435 1.435 ↓ 0.0 0 205

Index Scan using ID_10_01_CZOA_T_COMMUNICATION_INFO_PARTITION_HISTORY on CZOA_T_COMMUNICATION_INFO_PARTITION_HISTORY T (cost=0.28..7.68 rows=1 width=1,730) (actual time=0.007..0.007 rows=0 loops=205)

  • Index Cond: (DATAKEY = "SYSINTERNAL-41-0".BUSINESS_ID)
13. 0.410 0.410 ↓ 0.0 0 205

Index Scan using ID_10_01_CZOA_T_COMMUNICATION_INFO_PARTITION_CURRENT on CZOA_T_COMMUNICATION_INFO_PARTITION_CURRENT T_1 (cost=0.14..2.31 rows=1 width=12,536) (actual time=0.002..0.002 rows=0 loops=205)

  • Index Cond: (DATAKEY = "SYSINTERNAL-41-0".BUSINESS_ID)
14. 0.013 0.040 ↓ 0.0 0 1

Append (cost=0.42..10.24 rows=2 width=33) (actual time=0.040..0.040 rows=0 loops=1)

15. 0.015 0.015 ↓ 0.0 0 1

Index Only Scan using JBPM_1_08_JBPM_PROCESSINSTANCE_PARTITION_HISTORY on JBPM_PROCESSINSTANCE_PARTITION_HISTORY P (cost=0.42..3.87 rows=1 width=32) (actual time=0.015..0.015 rows=0 loops=1)

  • Index Cond: (BUSINESSID_ = T.DATAKEY)
  • Heap Fetches: 0
16. 0.012 0.012 ↓ 0.0 0 1

Index Only Scan using JBPM_1_08_JBPM_PROCESSINSTANCE_PARTITION_CURRENT on JBPM_PROCESSINSTANCE_PARTITION_CURRENT P_1 (cost=0.42..6.36 rows=1 width=33) (actual time=0.012..0.012 rows=0 loops=1)

  • Index Cond: (BUSINESSID_ = T.DATAKEY)
  • Heap Fetches: 0
17.          

SubPlan (for Count)

18. 0.000 0.000 ↓ 0.0 0

Count (cost=0.42..16.89 rows=0 width=32) (never executed)

  • Stop Keys: (ROWNUM = 1)
19. 0.000 0.000 ↓ 0.0 0

Append (cost=0.42..16.88 rows=2 width=8) (never executed)

20. 0.000 0.000 ↓ 0.0 0

Index Scan using JBPM_1_08_JBPM_PROCESSINSTANCE_PARTITION_HISTORY on JBPM_PROCESSINSTANCE_PARTITION_HISTORY P_2 (cost=0.42..8.44 rows=1 width=8) (never executed)

  • Index Cond: (BUSINESSID_ = T.DATAKEY)
21. 0.000 0.000 ↓ 0.0 0

Index Scan using JBPM_1_08_JBPM_PROCESSINSTANCE_PARTITION_CURRENT on JBPM_PROCESSINSTANCE_PARTITION_CURRENT P_3 (cost=0.42..8.44 rows=1 width=8) (never executed)

  • Index Cond: (BUSINESSID_ = T.DATAKEY)
Planning time : 3.272 ms
Execution time : 10,479.162 ms