explain.depesz.com

PostgreSQL's explain analyze made readable

Result: G95 : ArrearsForNHG - cte 1

Settings
# exclusive inclusive rows x rows loops node
1. 0.016 619.890 ↓ 20.0 20 1

Limit (cost=23,112.92..26,248.86 rows=1 width=12) (actual time=310.971..619.890 rows=20 loops=1)

2. 0.817 619.874 ↓ 20.0 20 1

Nested Loop (cost=23,112.92..26,248.86 rows=1 width=12) (actual time=310.969..619.874 rows=20 loops=1)

  • Join Filter: (c.nextduedatenr = am.duedatenr)
  • Rows Removed by Join Filter: 5843
3. 0.019 617.937 ↓ 20.0 20 1

Nested Loop (cost=23,112.48..26,205.38 rows=1 width=68) (actual time=310.949..617.937 rows=20 loops=1)

4. 0.031 617.818 ↓ 20.0 20 1

Nested Loop (cost=23,112.06..26,204.73 rows=1 width=60) (actual time=310.935..617.818 rows=20 loops=1)

5. 0.059 617.707 ↓ 20.0 20 1

Nested Loop (cost=23,111.64..26,204.18 rows=1 width=40) (actual time=310.922..617.707 rows=20 loops=1)

6. 0.082 617.468 ↓ 20.0 20 1

Nested Loop Left Join (cost=23,111.23..26,195.74 rows=1 width=1,296) (actual time=310.895..617.468 rows=20 loops=1)

  • Join Filter: (rn4.entityid = rn1.entityid)
7.          

CTE guaranteesprep

8. 38.084 278.807 ↑ 1.1 30,000 1

WindowAgg (cost=18,785.09..23,111.23 rows=33,278 width=123) (actual time=175.219..278.807 rows=30,000 loops=1)

9. 34.026 180.723 ↑ 1.1 30,000 1

Sort (cost=18,785.09..18,868.28 rows=33,278 width=143) (actual time=175.190..180.723 rows=30,000 loops=1)

  • Sort Key: c_1.pkey, (CASE WHEN (gt.codeid = 3) THEN 0 ELSE gt.codeid END), coll.pkey
  • Sort Method: external merge Disk: 4432kB
10. 6.670 146.697 ↑ 1.1 30,000 1

Hash Left Join (cost=4,665.52..13,895.03 rows=33,278 width=143) (actual time=55.376..146.697 rows=30,000 loops=1)

  • Hash Cond: (coll.guaranteetypecd = gt.codeid)
11. 33.102 140.016 ↑ 1.1 30,000 1

Hash Join (cost=4,664.15..13,693.00 rows=33,278 width=122) (actual time=55.348..140.016 rows=30,000 loops=1)

  • Hash Cond: (c_1.pkey = c2c.creditfk)
12. 51.867 51.867 ↓ 1.0 99,343 1

Seq Scan on credit c_1 (cost=0.00..6,555.38 rows=96,938 width=24) (actual time=0.106..51.867 rows=99,343 loops=1)

13. 11.087 55.047 ↑ 1.1 30,000 1

Hash (cost=3,728.17..3,728.17 rows=33,278 width=102) (actual time=55.047..55.047 rows=30,000 loops=1)

  • Buckets: 32768 Batches: 2 Memory Usage: 2414kB
14. 17.609 43.960 ↑ 1.1 30,000 1

Hash Join (cost=2,294.11..3,728.17 rows=33,278 width=102) (actual time=21.934..43.960 rows=30,000 loops=1)

  • Hash Cond: (c2c.collateralfk = coll.pkey)
15. 4.651 4.651 ↓ 1.0 68,205 1

Seq Scan on collateral2credit c2c (cost=0.00..1,255.02 rows=68,202 width=24) (actual time=0.097..4.651 rows=68,205 loops=1)

16. 8.960 21.700 ↑ 1.0 29,601 1

Hash (cost=1,922.54..1,922.54 rows=29,726 width=82) (actual time=21.700..21.700 rows=29,601 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 4022kB
17. 12.740 12.740 ↑ 1.0 29,601 1

Seq Scan on collateral coll (cost=0.00..1,922.54 rows=29,726 width=82) (actual time=0.104..12.740 rows=29,601 loops=1)

  • Filter: ((classname)::text = 'Guarantee'::text)
  • Rows Removed by Filter: 31324
18. 0.004 0.011 ↑ 1.0 6 1

Hash (cost=1.30..1.30 rows=6 width=21) (actual time=0.011..0.011 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
19. 0.007 0.007 ↑ 1.0 6 1

Seq Scan on par_guaranteetypecaption gt (cost=0.00..1.30 rows=6 width=21) (actual time=0.004..0.007 rows=6 loops=1)

  • Filter: (languageid = 4)
  • Rows Removed by Filter: 18
20.          

SubPlan (forWindowAgg)

21. 30.000 60.000 ↑ 1.0 1 30,000

Aggregate (cost=0.09..0.10 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=30,000)

22. 30.000 30.000 ↑ 1.0 6 30,000

Values Scan on "*VALUES*" (cost=0.00..0.08 rows=6 width=8) (actual time=0.000..0.001 rows=6 loops=30,000)

23. 0.010 512.606 ↓ 20.0 20 1

Nested Loop Left Join (cost=0.00..2,333.61 rows=1 width=32) (actual time=306.035..512.606 rows=20 loops=1)

  • Join Filter: (rn3.entityid = rn1.entityid)
24. 0.028 405.056 ↓ 20.0 20 1

Nested Loop Left Join (cost=0.00..1,582.78 rows=1 width=24) (actual time=301.256..405.056 rows=20 loops=1)

  • Join Filter: (rn2.entityid = rn1.entityid)
  • Rows Removed by Join Filter: 60
25. 175.308 175.308 ↓ 20.0 20 1

CTE Scan on guaranteesprep rn1 (cost=0.00..831.95 rows=1 width=16) (actual time=175.239..175.308 rows=20 loops=1)

  • Filter: ((rn = 1) AND (typecd = 3))
  • Rows Removed by Filter: 3
26. 229.720 229.720 ↑ 55.3 3 20

CTE Scan on guaranteesprep rn2 (cost=0.00..748.75 rows=166 width=12) (actual time=5.401..11.486 rows=3 loops=20)

  • Filter: (rn = 2)
  • Rows Removed by Filter: 29997
27. 107.540 107.540 ↓ 0.0 0 20

CTE Scan on guaranteesprep rn3 (cost=0.00..748.75 rows=166 width=12) (actual time=5.377..5.377 rows=0 loops=20)

  • Filter: (rn = 3)
  • Rows Removed by Filter: 30000
28. 104.700 104.700 ↓ 0.0 0 20

CTE Scan on guaranteesprep rn4 (cost=0.00..748.75 rows=166 width=12) (actual time=5.235..5.235 rows=0 loops=20)

  • Filter: (rn = 4)
  • Rows Removed by Filter: 30000
29.          

SubPlan (forNested Loop Left Join)

30. 0.040 0.080 ↑ 1.0 1 20

Aggregate (cost=0.06..0.07 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=20)

31. 0.040 0.040 ↑ 1.0 4 20

Values Scan on "*VALUES*_1" (cost=0.00..0.05 rows=4 width=8) (actual time=0.001..0.002 rows=4 loops=20)

32. 0.180 0.180 ↑ 1.0 1 20

Index Scan using pk__credit__5e190d187b016d94 on credit c (cost=0.42..8.44 rows=1 width=28) (actual time=0.009..0.009 rows=1 loops=20)

  • Index Cond: (pkey = rn1.loanpartentityid)
33. 0.080 0.080 ↑ 1.0 1 20

Index Scan using ix_creditbalances_creditfk on creditbalances cb (cost=0.42..0.54 rows=1 width=20) (actual time=0.004..0.004 rows=1 loops=20)

  • Index Cond: (creditfk = c.pkey)
34. 0.100 0.100 ↑ 1.0 1 20

Index Scan using ix_amortizationversion_creditprecomputedfk on amortizationversion av (cost=0.42..0.64 rows=1 width=24) (actual time=0.005..0.005 rows=1 loops=20)

  • Index Cond: (creditprecomputedfk = cb.creditfk)
  • Filter: isactive
  • Rows Removed by Filter: 0
35. 1.120 1.120 ↑ 2.9 293 20

Index Scan using ix_amortization_amortizationversionfk on amortization am (cost=0.44..32.88 rows=848 width=24) (actual time=0.009..0.056 rows=293 loops=20)

  • Index Cond: (amortizationversionfk = av.pkey)
Planning time : 3.160 ms
Execution time : 622.563 ms