explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Jj4q : Optimization for: plan #sieU

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.253 1,525.061 ↓ 0.0 958 1

Count (cost=5,536,202,243.49..5,536,206,271.73 rows=0 width=5,490) (actual time=1,524.184..1,525.061 rows=958 loops=1)

2. 0.448 1,524.808 ↑ 186.9 958 1

Subquery Scan on A (cost=5,536,202,243.49..5,536,204,481.40 rows=179,033 width=5,482) (actual time=1,524.181..1,524.808 rows=958 loops=1)

3. 10.786 1,524.360 ↑ 186.9 958 1

Sort (cost=5,536,202,243.49..5,536,202,691.07 rows=179,033 width=5,546) (actual time=1,524.178..1,524.360 rows=958 loops=1)

  • Sort Key: (DECODE((DECODE((T.BNSFDB)::NUMERIC, '1'::NUMERIC, (CASE WHEN ((TO_TIMESTAMP_TZ((TR.BLANK56)::TEXT, 'YYYY-MM-DD'::TEXT))::TIMESTAMP WITHOUT TIME ZONE >= (TO_TIMESTAMP_TZ((T.QFRQ)::TEXT, 'YYYY-MM-DD'::TEXT))::TIMESTAMP WITHOUT TIME ZONE) THEN '1'::TEXT WHEN ((T.QFRQ IS NULL) AND ((TO_TIMESTAMP_TZ((TR.BLANK56)::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.QFRQ 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.BLANK56)::TEXT, 'YYYY-MM-DD'::TEXT))::TIMESTAMP WITHOUT TIME ZONE)) THEN '3'::TEXT WHEN ((TO_TIMESTAMP_TZ((TR.BLANK56)::TEXT, 'YYYY-MM-DD'::TEXT))::TIMESTAMP WITHOUT TIME ZONE < (TO_TIMESTAMP_TZ((T.QFRQ)::TEXT, 'YYYY-MM-DD'::TEXT))::TIMESTAMP WITHOUT TIME ZONE) THEN '4'::TEXT ELSE NULL::TEXT END)::CHARACTER VARYING, (CASE WHEN ((TO_TIMESTAMP_TZ((CASE WHEN (((T.BNSFDB)::NUMERIC = '1'::NUMERIC) AND ((T.SNSFDB IS NULL) OR ((T.SNSFDB)::NUMERIC = '0'::NUMERIC))) THEN TR.BLANK56 WHEN (((T.BNSFDB)::NUMERIC = '1'::NUMERIC) AND ((T.SNSFDB)::NUMERIC = '1'::NUMERIC)) THEN TR.BLANK53 WHEN ((T.SNSFDB)::NUMERIC = '1'::NUMERIC) THEN TR.BLANK53 ELSE NULL::CHARACTER VARYING END)::TEXT, 'YYYY-MM-DD'::TEXT))::TIMESTAMP WITHOUT TIME ZONE >= (TO_TIMESTAMP_TZ((T.QFRQ)::TEXT, 'YYYY-MM-DD'::TEXT))::TIMESTAMP WITHOUT TIME ZONE) THEN '1'::TEXT WHEN ((TO_TIMESTAMP_TZ((CASE WHEN (((T.BNSFDB)::NUMERIC = '1'::NUMERIC) AND ((T.SNSFDB IS NULL) OR ((T.SNSFDB)::NUMERIC = '0'::NUMERIC))) THEN TR.BLANK56 WHEN (((T.BNSFDB)::NUMERIC = '1'::NUMERIC) AND ((T.SNSFDB)::NUMERIC = '1'::NUMERIC)) THEN TR.BLANK53 WHEN ((T.SNSFDB)::NUMERIC = '1'::NUMERIC) THEN TR.BLANK53 ELSE NULL::CHARACTER VARYING END)::TEXT, 'YYYY-MM-DD'::TEXT))::TIMESTAMP WITHOUT TIME ZONE < (TO_TIMESTAMP_TZ((T.QFRQ)::TEXT, 'YYYY-MM-DD'::TEXT))::TIMESTAMP WITHOUT TIME ZONE) THEN '4'::TEXT WHEN ((T.QFRQ IS NULL) AND ((TO_TIMESTAMP_TZ((CASE WHEN (((T.BNSFDB)::NUMERIC = '1'::NUMERIC) AND ((T.SNSFDB IS NULL) OR ((T.SNSFDB)::NUMERIC = '0'::NUMERIC))) THEN TR.BLANK56 WHEN (((T.BNSFDB)::NUMERIC = '1'::NUMERIC) AND ((T.SNSFDB)::NUMERIC = '1'::NUMERIC)) THEN TR.BLANK53 WHEN ((T.SNSFDB)::NUMERIC = '1'::NUMERIC) THEN TR.BLANK53 ELSE NULL::CHARACTER VARYING END)::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.QFRQ 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((CASE WHEN (((T.BNSFDB)::NUMERIC = '1'::NUMERIC) AND ((T.SNSFDB IS NULL) OR ((T.SNSFDB)::NUMERIC = '0'::NUMERIC))) THEN TR.BLANK56 WHEN (((T.BNSFDB)::NUMERIC = '1'::NUMERIC) AND ((T.SNSFDB)::NUMERIC = '1'::NUMERIC)) THEN TR.BLANK53 WHEN ((T.SNSFDB)::NUMERIC = '1'::NUMERIC) THEN TR.BLANK53 ELSE NULL::CHARACTER VARYING END)::TEXT, 'YYYY-MM-DD'::TEXT))::TIMESTAMP WITHOUT TIME ZONE)) THEN '3'::TEXT ELSE NULL::TEXT END)::CHARACTER VARYING))::NUMERIC, '1'::NUMERIC, '4'::NUMERIC, '3'::NUMERIC, '1'::NUMERIC, '2'::NUMERIC, '2'::NUMERIC, '4'::NUMERIC, '3'::NUMERIC, '5'::NUMERIC)), (ORA_DATE_MI(((TO_TIMESTAMP_TZ((CASE WHEN (((T.BNSFDB)::NUMERIC = '1'::NUMERIC) AND ((T.SNSFDB IS NULL) OR ((T.SNSFDB)::NUMERIC = '0'::NUMERIC))) THEN TR.BLANK56 WHEN (((T.BNSFDB)::NUMERIC = '1'::NUMERIC) AND ((T.SNSFDB)::NUMERIC = '1'::NUMERIC)) THEN TR.BLANK53 WHEN ((T.SNSFDB)::NUMERIC = '1'::NUMERIC) THEN TR.BLANK53 ELSE NULL::CHARACTER VARYING END)::TEXT, 'yyyy-mm-dd'::TEXT))::ORA_DATE)::TIMESTAMP WITHOUT TIME ZONE, (('NOW'::TEXT)::ORA_DATE)::TIMESTAMP WITHOUT TIME ZONE))
  • Sort Method: quicksort Memory: 325kB
4. 115.859 1,513.574 ↑ 186.9 958 1

Hash Semi Join (cost=1,283,432.48..5,535,760,105.98 rows=179,033 width=5,546) (actual time=1,279.884..1,513.574 rows=958 loops=1)

  • Hash Cond: (T.ZBDWID = T1.DEPT_ID)
5. 1.681 1,348.872 ↑ 354.4 1,010 1

Nested Loop (cost=1,280,336.77..1,325,593.14 rows=357,952 width=5,568) (actual time=1,278.065..1,348.872 rows=1,010 loops=1)

6. 12.592 1,325.981 ↑ 3.1 1,010 1

Subquery Scan on T (cost=1,280,336.35..1,315,651.22 rows=3,111 width=2,472) (actual time=1,278.022..1,325.981 rows=1,010 loops=1)

  • Filter: ((T.BNSFDB = '1'::CHARACTER VARYING) OR ((T.SNSFDB = '1'::CHARACTER VARYING) AND (hashed SubPlan 6)))
  • Rows Removed by Filter: 31,349
7. 28.541 1,313.108 ↑ 12.8 32,359 1

Unique (cost=1,280,317.86..1,308,362.01 rows=415,469 width=6,534) (actual time=1,271.358..1,313.108 rows=32,359 loops=1)

8. 215.004 1,284.567 ↑ 8.1 51,509 1

Sort (cost=1,280,317.86..1,281,356.53 rows=415,469 width=6,534) (actual time=1,271.355..1,284.567 rows=51,509 loops=1)

  • Sort Key: T_1.DATAKEY, T_1.SWRQ, T_1.SERIALNUMBER, T_1.DCYJ, EXT1.FIELD_VALUE_, T_1.FGSLD, T_1.FGSLDID, T_1.ZBCS, T_1.ZBCSID, T_1.CSFZR, T_1.CSFZRID, T_1.CBR, T_1.CBRID, T_1.TRANSFERUSERID, T_1.LXDH, T_1.BLQK, T_1.CSWBJYY, T_1.QFRQ, (DECODE((T_1.QFRQ)::TEXT, NULL::BOOLEAN, NULL::CHARACTER VARYING, '1'::CHARACTER VARYING)), T_1.SFLX, T_1.BJQK, T_1.BNSFDB, T_1.SNSFDB, T_1.BJSJ, T_1.SUPERVISIONDATE, TN.BUSINESS_ID
  • Sort Method: quicksort Memory: 9,143kB
9. 42.160 1,069.563 ↑ 8.1 51,509 1

Hash Left Join (cost=50,072.17..77,095.50 rows=415,469 width=6,534) (actual time=996.048..1,069.563 rows=51,509 loops=1)

  • Hash Cond: (T_1.DATAKEY = TN.BUSINESS_ID)
10. 15.965 100.011 ↑ 1.0 32,370 1

Hash Left Join (cost=18,249.09..30,637.94 rows=32,370 width=6,420) (actual time=68.612..100.011 rows=32,370 loops=1)

  • Hash Cond: (T_1.DATAKEY = EXT1.BIZ_ID_)
11. 15.498 53.746 ↑ 1.0 32,370 1

Hash Right Join (cost=1,204.75..4,348.84 rows=32,370 width=6,368) (actual time=38.276..53.746 rows=32,370 loops=1)

  • Hash Cond: (EXT.BIZ_ID_ = T_1.DATAKEY)
12. 0.037 0.037 ↑ 112.0 4 1

Index Scan using IDX_FID_1_1 on BPM_BUSINESS_FIELD_VALUE_EXT EXT (cost=0.43..1,330.35 rows=448 width=32) (actual time=0.029..0.037 rows=4 loops=1)

  • Index Cond: (FIELD_ID_ = 'blank47'::CHARACTER VARYING)
13. 14.595 38.211 ↑ 1.0 32,370 1

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

  • Buckets: 32,768 Batches: 1 Memory Usage: 3,109kB
14. 3.626 23.616 ↑ 1.0 32,370 1

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

15. 19.298 19.298 ↑ 1.0 31,455 1

Seq Scan on "P#CZOA_T_SUPERVISION_PARTITION_HISTORY" T_1 (cost=0.00..767.55 rows=31,455 width=1,460) (actual time=0.010..19.298 rows=31,455 loops=1)

16. 0.692 0.692 ↑ 1.0 915 1

Seq Scan on "P#CZOA_T_SUPERVISION_PARTITION_CURRENT" T_1_1 (cost=0.00..32.15 rows=915 width=1,410) (actual time=0.013..0.692 rows=915 loops=1)

17. 9.584 30.300 ↑ 1.2 22,650 1

Hash (cost=16,702.61..16,702.61 rows=27,338 width=84) (actual time=30.300..30.300 rows=22,650 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 2,171kB
18. 17.675 20.716 ↑ 1.2 22,650 1

Bitmap Heap Scan on BPM_BUSINESS_FIELD_VALUE_EXT EXT1 (cost=516.30..16,702.61 rows=27,338 width=84) (actual time=3.443..20.716 rows=22,650 loops=1)

  • Recheck Cond: (FIELD_ID_ = 'blank13'::CHARACTER VARYING)
  • Heap Blocks: exact=2,441
19. 3.041 3.041 ↑ 1.2 22,695 1

Bitmap Index Scan on IDX_FID_1_1 (cost=0.00..509.46 rows=27,338 width=0) (actual time=3.041..3.041 rows=22,695 loops=1)

  • Index Cond: (FIELD_ID_ = 'blank13'::CHARACTER VARYING)
20. 235.460 927.392 ↓ 198.5 509,514 1

Hash (cost=31,790.99..31,790.99 rows=2,567 width=82) (actual time=927.392..927.392 rows=509,514 loops=1)

  • Buckets: 524,288 (originally 4096) Batches: 1 (originally 1) Memory Usage: 36,439kB
21. 691.932 691.932 ↓ 198.5 509,514 1

Seq Scan on TBL_MANAGE_NO TN (cost=0.00..31,790.99 rows=2,567 width=82) (actual time=0.027..691.932 rows=509,514 loops=1)

  • Filter: ((RECORD_IDENTY)::NUMERIC = '0'::NUMERIC)
  • Rows Removed by Filter: 3,952
22.          

SubPlan (for Subquery Scan)

23. 0.281 0.281 ↑ 1.0 29 1

Seq Scan on TBL_SYS_DEPARTMENT D (cost=0.00..18.43 rows=29 width=32) (actual time=0.037..0.281 rows=29 loops=1)

  • Filter: (COMM_ORG_IDENTY = '1000'::CHARACTER VARYING)
  • Rows Removed by Filter: 325
24. 1.010 21.210 ↑ 2.0 1 1,010

Append (cost=0.41..3.18 rows=2 width=105) (actual time=0.016..0.021 rows=1 loops=1,010)

25. 13.130 13.130 ↑ 1.0 1 1,010

Index Scan using IDX_06_01_TBL_ARCHIVES_RECEIVE_PARTITION_HISTORY on TBL_ARCHIVES_RECEIVE_PARTITION_HISTORY TR (cost=0.41..2.79 rows=1 width=104) (actual time=0.012..0.013 rows=1 loops=1,010)

  • Index Cond: (RECEIVE_ID = T.DATAKEY)
26. 7.070 7.070 ↓ 0.0 0 1,010

Index Scan using IDX_06_01_TBL_ARCHIVES_RECEIVE_PARTITION_CURRENT on TBL_ARCHIVES_RECEIVE_PARTITION_CURRENT TR_1 (cost=0.28..0.38 rows=1 width=140) (actual time=0.007..0.007 rows=0 loops=1,010)

  • Index Cond: (RECEIVE_ID = T.DATAKEY)
27. 0.136 1.049 ↓ 1.2 354 1

Hash (cost=3,092.15..3,092.15 rows=285 width=32) (actual time=1.049..1.049 rows=354 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 31kB
28. 0.782 0.913 ↓ 1.2 354 1

Nested Loop Semi Join (cost=0.00..3,092.15 rows=285 width=32) (actual time=0.043..0.913 rows=354 loops=1)

  • Join Filter: ((T0.COMM_ORG_IDENTY = '1000'::CHARACTER VARYING) OR (((T0.COMM_ORG_IDENTY)::TEXT ~~ '1000%'::TEXT) AND (T0.COMM_ORG_IDENTY <> '1000'::CHARACTER VARYING) AND (T0.DEPT_ID = T1.DEPT_ID)))
  • Rows Removed by Join Filter: 705
29. 0.131 0.131 ↑ 1.0 354 1

Seq Scan on TBL_SYS_DEPARTMENT T1 (cost=0.00..17.54 rows=354 width=32) (actual time=0.013..0.131 rows=354 loops=1)

30. 0.000 0.000 ↑ 570.7 3 354

Materialize (cost=0.00..338.95 rows=1,712 width=37) (actual time=0.000..0.000 rows=3 loops=354)

31. 0.022 0.022 ↑ 570.7 3 1

Seq Scan on TBL_SYS_USER T0 (cost=0.00..330.39 rows=1,712 width=37) (actual time=0.017..0.022 rows=3 loops=1)

  • Filter: ((COMM_ORG_IDENTY = '1000'::CHARACTER VARYING) OR (((COMM_ORG_IDENTY)::TEXT ~~ '1000%'::TEXT) AND (COMM_ORG_IDENTY <> '1000'::CHARACTER VARYING)))
32.          

SubPlan (for Hash Semi Join)

33. 6.706 23.950 ↑ 1.0 1 958

Aggregate (cost=7,725.91..7,725.92 rows=1 width=32) (actual time=0.025..0.025 rows=1 loops=958)

34. 1.964 17.244 ↑ 13.0 1 958

Nested Loop (cost=156.08..7,725.87 rows=13 width=8) (actual time=0.016..0.018 rows=1 loops=958)

35. 2.874 9.580 ↑ 13.0 1 958

Bitmap Heap Scan on TBL_MANAGE_NO T1_1 (cost=155.80..7,625.91 rows=13 width=33) (actual time=0.010..0.010 rows=1 loops=958)

  • Recheck Cond: (BUSINESS_ID = T.TN_BUSINESS_ID)
  • Filter: ((RECORD_IDENTY)::NUMERIC = '0'::NUMERIC)
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=425
36. 6.706 6.706 ↑ 2,567.0 1 958

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

  • Index Cond: (BUSINESS_ID = T.TN_BUSINESS_ID)
37. 5.700 5.700 ↑ 1.0 1 570

Index Scan using TBL_SYS_USER_PKEY on TBL_SYS_USER U (cost=0.28..7.68 rows=1 width=41) (actual time=0.010..0.010 rows=1 loops=570)

  • Index Cond: (USER_ID = T1_1.MANAGER)
38. 2.926 17.138 ↑ 1.0 1 418

Aggregate (cost=7,725.91..7,725.92 rows=1 width=32) (actual time=0.041..0.041 rows=1 loops=418)

39. 0.988 14.212 ↑ 13.0 1 418

Nested Loop (cost=156.08..7,725.87 rows=13 width=8) (actual time=0.029..0.034 rows=1 loops=418)

40. 1.672 7.524 ↑ 13.0 1 418

Bitmap Heap Scan on TBL_MANAGE_NO T1_2 (cost=155.80..7,625.91 rows=13 width=33) (actual time=0.017..0.018 rows=1 loops=418)

  • Recheck Cond: (BUSINESS_ID = T.TN_BUSINESS_ID)
  • Filter: ((RECORD_IDENTY)::NUMERIC = '0'::NUMERIC)
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=425
41. 5.852 5.852 ↑ 2,567.0 1 418

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

  • Index Cond: (BUSINESS_ID = T.TN_BUSINESS_ID)
42. 5.700 5.700 ↑ 1.0 1 570

Index Scan using TBL_SYS_USER_PKEY on TBL_SYS_USER U_1 (cost=0.28..7.68 rows=1 width=41) (actual time=0.009..0.010 rows=1 loops=570)

  • Index Cond: (USER_ID = T1_2.MANAGER)
43. 0.000 6.706 ↓ 0.0 0 958

Index Scan using IDX_TBL_SYS_USER_01 on TBL_SYS_USER S (cost=15,452.11..15,460.13 rows=1 width=8) (actual time=0.007..0.007 rows=0 loops=958)

  • Index Cond: (USER_ID = DECODE($6, NULL::BOOLEAN, T.CBR, ($8)::CHARACTER VARYING))
  • Filter: (COMM_RECORD_IDENTY = '1'::NUMERIC)
44.          

Initplan (for Index Scan)

45. 4.790 20.118 ↑ 1.0 1 958

Aggregate (cost=7,725.91..7,725.92 rows=1 width=32) (actual time=0.021..0.021 rows=1 loops=958)

46. 1.576 15.328 ↑ 13.0 1 958

Nested Loop (cost=156.08..7,725.87 rows=13 width=8) (actual time=0.014..0.016 rows=1 loops=958)

47. 2.874 8.622 ↑ 13.0 1 958

Bitmap Heap Scan on TBL_MANAGE_NO T1_3 (cost=155.80..7,625.91 rows=13 width=33) (actual time=0.008..0.009 rows=1 loops=958)

  • Recheck Cond: (BUSINESS_ID = T.TN_BUSINESS_ID)
  • Filter: ((RECORD_IDENTY)::NUMERIC = '0'::NUMERIC)
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=425
48. 5.748 5.748 ↑ 2,567.0 1 958

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

  • Index Cond: (BUSINESS_ID = T.TN_BUSINESS_ID)
49. 5.130 5.130 ↑ 1.0 1 570

Index Scan using TBL_SYS_USER_PKEY on TBL_SYS_USER U_2 (cost=0.28..7.68 rows=1 width=41) (actual time=0.009..0.009 rows=1 loops=570)

  • Index Cond: (USER_ID = T1_3.MANAGER)
50. 2.926 17.138 ↑ 1.0 1 418

Aggregate (cost=7,725.91..7,725.92 rows=1 width=32) (actual time=0.040..0.041 rows=1 loops=418)

51. 1.558 14.212 ↑ 13.0 1 418

Nested Loop (cost=156.08..7,725.87 rows=13 width=8) (actual time=0.029..0.034 rows=1 loops=418)

52. 1.672 7.524 ↑ 13.0 1 418

Bitmap Heap Scan on TBL_MANAGE_NO T1_4 (cost=155.80..7,625.91 rows=13 width=33) (actual time=0.017..0.018 rows=1 loops=418)

  • Recheck Cond: (BUSINESS_ID = T.TN_BUSINESS_ID)
  • Filter: ((RECORD_IDENTY)::NUMERIC = '0'::NUMERIC)
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=425
53. 5.852 5.852 ↑ 2,567.0 1 418

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

  • Index Cond: (BUSINESS_ID = T.TN_BUSINESS_ID)
54. 5.130 5.130 ↑ 1.0 1 570

Index Scan using TBL_SYS_USER_PKEY on TBL_SYS_USER U_3 (cost=0.28..7.68 rows=1 width=41) (actual time=0.009..0.009 rows=1 loops=570)

  • Index Cond: (USER_ID = T1_4.MANAGER)
Planning time : 12.230 ms
Execution time : 1,526.854 ms