explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wXRm

Settings
# exclusive inclusive rows x rows loops node
1. 286.412 4,045.606 ↓ 1.1 362,831 1

Hash Join (cost=549,447.84..578,664.93 rows=335,989 width=42) (actual time=3,088.154..4,045.606 rows=362,831 loops=1)

  • Hash Cond: (dcpscase_t.dcps_case_uid = caseclm_t.dcps_case_uid)
2. 244.182 2,474.146 ↓ 1.1 286,731 1

Hash Right Join (cost=291,366.74..311,347.56 rows=270,768 width=21) (actual time=1,800.242..2,474.146 rows=286,731 loops=1)

  • Hash Cond: (dcpscase_t_1.fldr_num = dcpscase_t.fldr_num)
  • Join Filter: ((dcpscase_t.adjulvl_cd)::text = '2'::text)
  • Rows Removed by Join Filter: 286,499
3. 157.576 1,559.863 ↑ 1.0 197,984 1

Hash Left Join (cost=261,960.94..274,652.05 rows=198,176 width=18) (actual time=1,127.640..1,559.863 rows=197,984 loops=1)

  • Hash Cond: (caseclm_t_1.dcps_clm_uid = initialdetrm."dcpsClaimUid")
4. 172.822 541.133 ↑ 1.0 197,984 1

Hash Right Join (cost=17,506.93..27,373.82 rows=198,176 width=8) (actual time=266.046..541.133 rows=197,984 loops=1)

  • Hash Cond: (caseclm_t_1.dcps_case_uid = dcpscase_t_1.dcps_case_uid)
5. 102.779 102.779 ↓ 1.0 247,863 1

Index Only Scan using ixpk_caseclm_t on caseclm_t caseclm_t_1 (cost=0.42..6,667.70 rows=246,703 width=8) (actual time=0.023..102.779 rows=247,863 loops=1)

  • Heap Fetches: 92,714
6. 56.638 265.532 ↑ 1.0 158,207 1

Hash (cost=14,886.17..14,886.17 rows=159,707 width=8) (actual time=265.532..265.532 rows=158,207 loops=1)

  • Buckets: 131,072 Batches: 4 Memory Usage: 2,570kB
7. 208.894 208.894 ↑ 1.0 158,207 1

Seq Scan on dcpscase_t dcpscase_t_1 (cost=0.00..14,886.17 rows=159,707 width=8) (actual time=0.018..208.894 rows=158,207 loops=1)

  • Filter: ((adjulvl_cd)::text = '1'::text)
  • Rows Removed by Filter: 38,921
8. 70.473 861.154 ↓ 1.1 175,065 1

Hash (cost=241,771.18..241,771.18 rows=154,306 width=14) (actual time=861.153..861.154 rows=175,065 loops=1)

  • Buckets: 131,072 Batches: 4 Memory Usage: 3,251kB
9. 98.370 790.681 ↓ 1.1 175,065 1

Subquery Scan on initialdetrm (cost=239,456.59..241,771.18 rows=154,306 width=14) (actual time=545.451..790.681 rows=175,065 loops=1)

10. 86.169 692.311 ↓ 1.1 175,065 1

Unique (cost=239,456.59..240,228.12 rows=154,306 width=1,494) (actual time=545.447..692.311 rows=175,065 loops=1)

11. 203.209 606.142 ↓ 1.2 178,813 1

Sort (cost=239,456.59..239,842.36 rows=154,306 width=1,494) (actual time=545.445..606.142 rows=178,813 loops=1)

  • Sort Key: clmdetn_t.dcps_clm_uid, clmdetn_t.insrt_ts DESC
  • Sort Method: external merge Disk: 7,368kB
12. 179.025 402.933 ↓ 1.2 178,813 1

Hash Left Join (cost=15,222.66..25,741.94 rows=154,306 width=1,494) (actual time=127.346..402.933 rows=178,813 loops=1)

  • 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,577
13. 97.101 97.101 ↑ 1.0 185,390 1

Seq Scan on clmdetn_t (cost=0.00..6,596.60 rows=185,776 width=26) (actual time=0.012..97.101 rows=185,390 loops=1)

  • Filter: (detn_typ_cd IS NOT NULL)
  • Rows Removed by Filter: 1,129
14. 62.783 126.807 ↑ 1.0 182,856 1

Hash (cost=12,038.96..12,038.96 rows=183,096 width=9) (actual time=126.806..126.807 rows=182,856 loops=1)

  • Buckets: 131,072 Batches: 4 Memory Usage: 2,856kB
15. 64.024 64.024 ↑ 1.0 182,856 1

Seq Scan on caseanly_t (cost=0.00..12,038.96 rows=183,096 width=9) (actual time=0.005..64.024 rows=182,856 loops=1)

16. 104.740 670.101 ↓ 1.3 279,831 1

Hash (cost=25,612.20..25,612.20 rows=218,208 width=13) (actual time=670.101..670.101 rows=279,831 loops=1)

  • Buckets: 131,072 Batches: 4 Memory Usage: 4,040kB
17. 192.369 565.361 ↓ 1.3 279,831 1

Hash Right Join (cost=17,845.31..25,612.20 rows=218,208 width=13) (actual time=311.793..565.361 rows=279,831 loops=1)

  • Hash Cond: (cssplhdg_t.dcps_case_uid = dcpscase_t.dcps_case_uid)
18. 61.520 61.520 ↑ 1.0 217,207 1

Seq Scan on cssplhdg_t (cost=0.00..4,517.08 rows=218,208 width=7) (actual time=0.012..61.520 rows=217,207 loops=1)

19. 71.784 311.472 ↑ 1.0 197,128 1

Hash (cost=14,389.14..14,389.14 rows=198,814 width=10) (actual time=311.472..311.472 rows=197,128 loops=1)

  • Buckets: 131,072 Batches: 4 Memory Usage: 3,049kB
20. 239.688 239.688 ↑ 1.0 197,128 1

Seq Scan on dcpscase_t (cost=0.00..14,389.14 rows=198,814 width=10) (actual time=0.013..239.688 rows=197,128 loops=1)

21. 103.478 1,285.048 ↓ 1.0 247,863 1

Hash (cost=253,310.31..253,310.31 rows=246,703 width=29) (actual time=1,285.047..1,285.048 rows=247,863 loops=1)

  • Buckets: 65,536 Batches: 8 Memory Usage: 2,206kB
22. 193.741 1,181.570 ↓ 1.0 247,863 1

Hash Left Join (cost=243,012.43..253,310.31 rows=246,703 width=29) (actual time=882.861..1,181.570 rows=247,863 loops=1)

  • Hash Cond: (caseclm_t.dcps_clm_uid = currentdetrm."dcpsClaimUid")
23. 105.278 105.278 ↓ 1.0 247,863 1

Index Only Scan using ixpk_caseclm_t on caseclm_t (cost=0.42..6,667.70 rows=246,703 width=8) (actual time=0.020..105.278 rows=247,863 loops=1)

  • Heap Fetches: 93,103
24. 75.149 882.551 ↓ 1.1 175,065 1

Hash (cost=240,028.18..240,028.18 rows=154,306 width=25) (actual time=882.551..882.551 rows=175,065 loops=1)

  • Buckets: 65,536 Batches: 4 Memory Usage: 3,016kB
25. 96.307 807.402 ↓ 1.1 175,065 1

Subquery Scan on currentdetrm (cost=237,713.59..240,028.18 rows=154,306 width=25) (actual time=568.710..807.402 rows=175,065 loops=1)

26. 81.805 711.095 ↓ 1.1 175,065 1

Unique (cost=237,713.59..238,485.12 rows=154,306 width=1,473) (actual time=568.706..711.095 rows=175,065 loops=1)

27. 210.264 629.290 ↓ 1.2 178,813 1

Sort (cost=237,713.59..238,099.36 rows=154,306 width=1,473) (actual time=568.704..629.290 rows=178,813 loops=1)

  • Sort Key: clmdetn_t_1.dcps_clm_uid, clmdetn_t_1.insrt_ts DESC
  • Sort Method: external merge Disk: 9,880kB
28. 189.313 419.026 ↓ 1.2 178,813 1

Hash Left Join (cost=15,222.66..26,105.94 rows=154,306 width=1,473) (actual time=128.977..419.026 rows=178,813 loops=1)

  • 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,577
29. 101.257 101.257 ↑ 1.0 185,390 1

Seq Scan on clmdetn_t clmdetn_t_1 (cost=0.00..6,596.60 rows=185,776 width=33) (actual time=0.007..101.257 rows=185,390 loops=1)

  • Filter: (detn_typ_cd IS NOT NULL)
  • Rows Removed by Filter: 1,129
30. 62.884 128.456 ↑ 1.0 182,856 1

Hash (cost=12,038.96..12,038.96 rows=183,096 width=9) (actual time=128.455..128.456 rows=182,856 loops=1)

  • Buckets: 131,072 Batches: 4 Memory Usage: 2,856kB
31. 65.572 65.572 ↑ 1.0 182,856 1

Seq Scan on caseanly_t caseanly_t_1 (cost=0.00..12,038.96 rows=183,096 width=9) (actual time=0.003..65.572 rows=182,856 loops=1)

Planning time : 4.567 ms
Execution time : 4,119.898 ms