explain.depesz.com

PostgreSQL's explain analyze made readable

Result: thEh

Settings
# exclusive inclusive rows x rows loops node
1. 184.883 3,268.337 ↓ 1.1 362,549 1

Gather (cost=140,365.38..187,644.26 rows=336,734 width=42) (actual time=2,535.498..3,268.337 rows=362,549 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
2. 104.571 3,083.454 ↑ 1.2 120,850 3 / 3

Hash Left Join (cost=139,365.38..152,970.86 rows=140,306 width=42) (actual time=2,465.319..3,083.454 rows=120,850 loops=3)

  • Hash Cond: (caseclm_t_1.dcps_clm_uid = initialdetrm."dcpsClaimUid")
3. 89.886 1,947.014 ↑ 1.2 120,850 3 / 3

Parallel Hash Left Join (cost=89,597.41..100,161.59 rows=140,306 width=32) (actual time=1,433.330..1,947.014 rows=120,850 loops=3)

  • Hash Cond: (dcpscase_t_1.dcps_case_uid = caseclm_t_1.dcps_case_uid)
4. 100.937 1,769.979 ↓ 1.0 116,295 3 / 3

Parallel Hash Left Join (cost=83,087.90..92,644.10 rows=112,897 width=32) (actual time=1,345.647..1,769.979 rows=116,295 loops=3)

  • Hash Cond: (dcpscase_t.dcps_case_uid = cssplhdg_t.dcps_case_uid)
5. 88.687 1,603.195 ↑ 1.2 82,550 3 / 3

Parallel Hash Left Join (cost=77,869.33..86,375.95 rows=102,793 width=33) (actual time=1,279.299..1,603.195 rows=82,550 loops=3)

  • Hash Cond: (dcpscase_t.fldr_num = dcpscase_t_1.fldr_num)
  • Join Filter: ((dcpscase_t.adjulvl_cd)::text = '2'::text)
  • Rows Removed by Join Filter: 66,073
6. 87.273 1,412.577 ↑ 1.2 82,550 3 / 3

Parallel Hash Join (cost=63,622.90..71,244.75 rows=102,793 width=35) (actual time=1,176.838..1,412.577 rows=82,550 loops=3)

  • Hash Cond: (caseclm_t.dcps_case_uid = dcpscase_t.dcps_case_uid)
7. 102.342 1,205.913 ↑ 1.2 82,550 3 / 3

Hash Left Join (cost=49,379.88..56,731.89 rows=102,793 width=29) (actual time=1,057.082..1,205.913 rows=82,550 loops=3)

  • Hash Cond: (caseclm_t.dcps_clm_uid = currentdetrm."dcpsClaimUid")
8. 46.819 46.819 ↑ 1.2 82,550 3 / 3

Parallel Index Only Scan using ixpk_caseclm_t on caseclm_t (cost=0.42..5,224.60 rows=102,793 width=8) (actual time=0.026..46.819 rows=82,550 loops=3)

  • Heap Fetches: 78,608
9. 94.488 1,056.752 ↓ 1.1 174,947 3 / 3

Hash (cost=46,398.65..46,398.65 rows=154,145 width=25) (actual time=1,056.751..1,056.752 rows=174,947 loops=3)

  • Buckets: 65,536 Batches: 4 Memory Usage: 3,014kB
10. 128.229 962.264 ↓ 1.1 174,947 3 / 3

Subquery Scan on currentdetrm (cost=44,086.47..46,398.65 rows=154,145 width=25) (actual time=617.726..962.264 rows=174,947 loops=3)

11. 129.769 834.035 ↓ 1.1 174,947 3 / 3

Unique (cost=44,086.47..44,857.20 rows=154,145 width=41) (actual time=617.723..834.035 rows=174,947 loops=3)

12. 226.604 704.266 ↓ 1.2 178,693 3 / 3

Sort (cost=44,086.47..44,471.84 rows=154,145 width=41) (actual time=617.721..704.266 rows=178,693 loops=3)

  • Sort Key: clmdetn_t.dcps_clm_uid, clmdetn_t.insrt_ts DESC
  • Sort Method: external merge Disk: 9,872kB
  • Worker 0: Sort Method: external merge Disk: 9,872kB
  • Worker 1: Sort Method: external merge Disk: 9,872kB
13. 196.477 477.662 ↓ 1.2 178,693 3 / 3

Hash Left Join (cost=15,187.08..26,061.35 rows=154,145 width=41) (actual time=166.353..477.662 rows=178,693 loops=3)

  • Hash Cond: (clmdetn_t.case_anl_uid = caseanly_t.case_anl_uid)
  • Filter: ((clmdetn_t.case_anl_uid IS NULL) OR ((caseanly_t.anl_stus_cd)::text = 'CLSD'::text))
  • Rows Removed by Filter: 6,556
14. 115.420 115.420 ↑ 1.0 185,249 3 / 3

Seq Scan on clmdetn_t (cost=0.00..6,591.01 rows=185,618 width=33) (actual time=0.018..115.420 rows=185,249 loops=3)

  • Filter: (detn_typ_cd IS NOT NULL)
  • Rows Removed by Filter: 1,133
15. 82.380 165.765 ↑ 1.0 182,684 3 / 3

Hash (cost=12,006.37..12,006.37 rows=182,937 width=9) (actual time=165.765..165.765 rows=182,684 loops=3)

  • Buckets: 131,072 Batches: 4 Memory Usage: 2,854kB
16. 83.385 83.385 ↑ 1.0 182,684 3 / 3

Seq Scan on caseanly_t (cost=0.00..12,006.37 rows=182,937 width=9) (actual time=0.007..83.385 rows=182,684 loops=3)

17. 33.901 119.391 ↑ 1.3 65,653 3 / 3

Parallel Hash (cost=13,209.12..13,209.12 rows=82,712 width=10) (actual time=119.390..119.391 rows=65,653 loops=3)

  • Buckets: 262,144 Batches: 1 Memory Usage: 11,360kB
18. 85.490 85.490 ↑ 1.3 65,653 3 / 3

Parallel Seq Scan on dcpscase_t (cost=0.00..13,209.12 rows=82,712 width=10) (actual time=0.008..85.490 rows=65,653 loops=3)

19. 26.259 101.931 ↑ 1.3 52,685 3 / 3

Parallel Hash (cost=13,415.90..13,415.90 rows=66,442 width=8) (actual time=101.931..101.931 rows=52,685 loops=3)

  • Buckets: 262,144 Batches: 1 Memory Usage: 8,288kB
20. 75.672 75.672 ↑ 1.3 52,685 3 / 3

Parallel Seq Scan on dcpscase_t dcpscase_t_1 (cost=0.00..13,415.90 rows=66,442 width=8) (actual time=0.013..75.672 rows=52,685 loops=3)

  • Filter: ((adjulvl_cd)::text = '1'::text)
  • Rows Removed by Filter: 12,968
21. 36.406 65.847 ↑ 1.8 72,353 3 / 3

Parallel Hash (cost=3,615.48..3,615.48 rows=128,248 width=7) (actual time=65.847..65.847 rows=72,353 loops=3)

  • Buckets: 262,144 Batches: 1 Memory Usage: 10,592kB
22. 29.441 29.441 ↑ 1.8 72,353 3 / 3

Parallel Seq Scan on cssplhdg_t (cost=0.00..3,615.48 rows=128,248 width=7) (actual time=0.009..29.441 rows=72,353 loops=3)

23. 41.150 87.149 ↑ 1.2 82,550 3 / 3

Parallel Hash (cost=5,224.60..5,224.60 rows=102,793 width=8) (actual time=87.149..87.149 rows=82,550 loops=3)

  • Buckets: 262,144 Batches: 1 Memory Usage: 11,776kB
24. 45.999 45.999 ↑ 1.2 82,550 3 / 3

Parallel Index Only Scan using ixpk_caseclm_t on caseclm_t caseclm_t_1 (cost=0.42..5,224.60 rows=102,793 width=8) (actual time=0.033..45.999 rows=82,550 loops=3)

  • Heap Fetches: 80,135
25. 86.131 1,031.869 ↓ 1.1 174,947 3 / 3

Hash (cost=47,088.15..47,088.15 rows=154,145 width=14) (actual time=1,031.868..1,031.869 rows=174,947 loops=3)

  • Buckets: 131,072 Batches: 4 Memory Usage: 3,249kB
26. 117.069 945.738 ↓ 1.1 174,947 3 / 3

Subquery Scan on initialdetrm (cost=44,775.97..47,088.15 rows=154,145 width=14) (actual time=628.463..945.738 rows=174,947 loops=3)

27. 120.679 828.669 ↓ 1.1 174,947 3 / 3

Unique (cost=44,775.97..45,546.70 rows=154,145 width=62) (actual time=628.460..828.669 rows=174,947 loops=3)

28. 219.732 707.990 ↓ 1.2 178,693 3 / 3

Sort (cost=44,775.97..45,161.34 rows=154,145 width=62) (actual time=628.458..707.990 rows=178,693 loops=3)

  • Sort Key: clmdetn_t_1.dcps_clm_uid, clmdetn_t_1.insrt_ts DESC
  • Sort Method: external merge Disk: 7,360kB
  • Worker 0: Sort Method: external merge Disk: 7,360kB
  • Worker 1: Sort Method: external merge Disk: 7,360kB
29. 199.228 488.258 ↓ 1.2 178,693 3 / 3

Hash Left Join (cost=15,187.08..25,697.35 rows=154,145 width=62) (actual time=169.227..488.258 rows=178,693 loops=3)

  • Hash Cond: (clmdetn_t_1.case_anl_uid = caseanly_t_1.case_anl_uid)
  • Filter: ((clmdetn_t_1.case_anl_uid IS NULL) OR ((caseanly_t_1.anl_stus_cd)::text = 'CLSD'::text))
  • Rows Removed by Filter: 6,556
30. 120.023 120.023 ↑ 1.0 185,249 3 / 3

Seq Scan on clmdetn_t clmdetn_t_1 (cost=0.00..6,591.01 rows=185,618 width=26) (actual time=0.010..120.023 rows=185,249 loops=3)

  • Filter: (detn_typ_cd IS NOT NULL)
  • Rows Removed by Filter: 1,133
31. 85.107 169.007 ↑ 1.0 182,684 3 / 3

Hash (cost=12,006.37..12,006.37 rows=182,937 width=9) (actual time=169.007..169.007 rows=182,684 loops=3)

  • Buckets: 131,072 Batches: 4 Memory Usage: 2,854kB
32. 83.900 83.900 ↑ 1.0 182,684 3 / 3

Seq Scan on caseanly_t caseanly_t_1 (cost=0.00..12,006.37 rows=182,937 width=9) (actual time=0.004..83.900 rows=182,684 loops=3)

Planning time : 3.362 ms
Execution time : 3,380.658 ms