explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kgZB

Settings
# exclusive inclusive rows x rows loops node
1. 17,817.046 85,484.906 ↓ 0.0 0 1

Update on tmp_fact_accrual (cost=1,603,685.78..1,938,464.74 rows=3,574,614 width=644) (actual time=85,484.906..85,484.906 rows=0 loops=1)

  • Buffers: shared hit=51360 read=282272 dirtied=414, local hit=7692855 read=390271 dirtied=575592 written=805707, temp read=126654 written=126654
2. 1,889.684 67,667.860 ↑ 1.0 3,574,274 1

Hash Join (cost=1,603,685.78..1,938,464.74 rows=3,574,614 width=644) (actual time=49,413.296..67,667.86 rows=3,574,274 loops=1)

  • Buffers: shared hit=51360 read=282272 dirtied=414, local hit=45208 read=390212 dirtied=127710 written=365086, temp read=126654 written=126654
3. 3,054.456 65,629.769 ↑ 1.0 3,574,274 1

Hash Join (cost=1,596,292.53..1,921,687.91 rows=3,574,614 width=671) (actual time=49,263.311..65,629.769 rows=3,574,274 loops=1)

  • Buffers: shared hit=47914 read=282272 dirtied=414, local hit=45208 read=390212 dirtied=127710 written=365086, temp read=126654 written=126654
4. 5,061.370 62,299.134 ↑ 1.0 3,574,274 1

Hash Join (cost=1,581,025.16..1,897,037.05 rows=3,574,614 width=698) (actual time=48,983.972..62,299.134 rows=3,574,274 loops=1)

  • Buffers: shared hit=39218 read=282272 dirtied=414, local hit=45208 read=390212 dirtied=127710 written=365086, temp read=126654 written=126654
5. 9,631.296 9,631.296 ↑ 1.0 3,574,274 1

Seq Scan on tmp_fact_accrual tmp_fact_accrual (cost=0..253,456.14 rows=3,574,614 width=558) (actual time=1,354.87..9,631.296 rows=3,574,274 loops=1)

  • Buffers: local read=217710 dirtied=1 written=199060
6. 3,470.478 47,606.468 ↑ 1.0 3,574,274 1

Hash (cost=1,536,342.49..1,536,342.49 rows=3,574,614 width=156) (actual time=47,606.468..47,606.468 rows=3,574,274 loops=1)

  • Buffers: shared hit=39218 read=282272 dirtied=414, local hit=45208 read=172502 dirtied=127709 written=166026, temp read=126654 written=126654
7. 8,471.898 44,135.990 ↑ 1.0 3,574,274 1

Hash Join (cost=848,404.34..1,536,342.49 rows=3,574,614 width=156) (actual time=23,933.06..44,135.99 rows=3,574,274 loops=1)

  • Buffers: shared hit=39218 read=282272 dirtied=414, local hit=45208 read=172502 dirtied=127709 written=166026, temp read=126654 written=126654
8. 1,361.560 12,208.358 ↑ 1.0 3,574,274 1

Hash Join (cost=18,703.87..319,413.83 rows=3,574,614 width=183) (actual time=428.181..12,208.358 rows=3,574,274 loops=1)

  • Buffers: shared hit=5181 read=4834, local hit=45208 read=172502 dirtied=127709 written=166026
9. 1,673.054 10,846.706 ↑ 1.0 3,574,274 1

Hash Join (cost=18,699.22..309,690.53 rows=3,574,614 width=210) (actual time=428.056..10,846.706 rows=3,574,274 loops=1)

  • Buffers: shared hit=5179 read=4834, local hit=45208 read=172502 dirtied=127709 written=166026
10. 841.116 8,882.352 ↑ 1.0 3,574,274 1

Hash Join (cost=9,740.39..291,348.07 rows=3,574,614 width=237) (actual time=135.338..8,882.352 rows=3,574,274 loops=1)

  • Buffers: shared hit=4399, local hit=45208 read=172502 dirtied=127709 written=166026
11. 2,162.153 8,041.039 ↑ 1.0 3,574,274 1

Hash Join (cost=9,727.83..281,951.54 rows=3,574,614 width=264) (actual time=135.117..8,041.039 rows=3,574,274 loops=1)

  • Buffers: shared hit=4394, local hit=45208 read=172502 dirtied=127709 written=166026
12. 1,702.915 5,781.237 ↑ 1.0 3,574,274 1

Hash Join (cost=3,739.36..266,579.47 rows=3,574,614 width=291) (actual time=36.956..5,781.237 rows=3,574,274 loops=1)

  • Buffers: shared hit=2272, local hit=45208 read=172502 dirtied=127709 written=166026
13. 4,041.575 4,041.575 ↑ 1.0 3,574,274 1

Seq Scan on tmp_fact_accrual tfa (cost=0..253,456.14 rows=3,574,614 width=318) (actual time=0.05..4,041.575 rows=3,574,274 loops=1)

  • Buffers: local hit=45208 read=172502 dirtied=127709 written=166026
14. 20.137 36.747 ↑ 1.0 65,216 1

Hash (cost=2,924.16..2,924.16 rows=65,216 width=46) (actual time=36.747..36.747 rows=65,216 loops=1)

  • Buffers: shared hit=2272
15. 16.610 16.610 ↑ 1.0 65,216 1

Seq Scan on dim_contract_family dcf (cost=0..2,924.16 rows=65,216 width=46) (actual time=0.012..16.61 rows=65,216 loops=1)

  • Buffers: shared hit=2272
16. 64.671 97.649 ↑ 1.0 171,843 1

Hash (cost=3,840.43..3,840.43 rows=171,843 width=47) (actual time=97.649..97.649 rows=171,843 loops=1)

  • Buffers: shared hit=2122
17. 32.978 32.978 ↑ 1.0 171,843 1

Seq Scan on dim_contract_renew_version dcrv (cost=0..3,840.43 rows=171,843 width=47) (actual time=0.024..32.978 rows=171,843 loops=1)

  • Buffers: shared hit=2122
18. 0.122 0.197 ↓ 1.0 346 1

Hash (cost=8.36..8.36 rows=336 width=46) (actual time=0.197..0.197 rows=346 loops=1)

  • Buffers: shared hit=5
19. 0.075 0.075 ↓ 1.0 346 1

Seq Scan on dim_lumpsum dl (cost=0..8.36 rows=336 width=46) (actual time=0.016..0.075 rows=346 loops=1)

  • Buffers: shared hit=5
20. 103.176 291.300 ↓ 1.0 148,668 1

Hash (cost=7,100.59..7,100.59 rows=148,659 width=46) (actual time=291.3..291.3 rows=148,668 loops=1)

  • Buffers: shared hit=780 read=4834
21. 188.124 188.124 ↓ 1.0 148,668 1

Seq Scan on dim_company dc (cost=0..7,100.59 rows=148,659 width=46) (actual time=0.016..188.124 rows=148,668 loops=1)

  • Buffers: shared hit=780 read=4834
22. 0.054 0.092 ↓ 1.0 121 1

Hash (cost=3.18..3.18 rows=118 width=46) (actual time=0.092..0.092 rows=121 loops=1)

  • Buffers: shared hit=2
23. 0.038 0.038 ↓ 1.0 121 1

Seq Scan on dim_uom du (cost=0..3.18 rows=118 width=46) (actual time=0.015..0.038 rows=121 loops=1)

  • Buffers: shared hit=2
24. 12,209.405 23,455.734 ↑ 1.0 14,271,213 1

Hash (cost=454,742.32..454,742.32 rows=14,326,732 width=82) (actual time=23,455.734..23,455.734 rows=14,271,213 loops=1)

  • Buffers: shared hit=34037 read=277438 dirtied=414, temp written=89507
25. 11,246.329 11,246.329 ↑ 1.0 14,271,213 1

Seq Scan on fact_contract fc (cost=0..454,742.32 rows=14,326,732 width=82) (actual time=0.053..11,246.329 rows=14,271,213 loops=1)

  • Buffers: shared hit=34037 read=277438 dirtied=414
26. 163.358 276.179 ↑ 1.0 292,061 1

Hash (cost=11,616.61..11,616.61 rows=292,061 width=46) (actual time=276.179..276.179 rows=292,061 loops=1)

  • Buffers: shared hit=8696
27. 112.821 112.821 ↑ 1.0 292,061 1

Seq Scan on dim_contract_amend_version dcav (cost=0..11,616.61 rows=292,061 width=46) (actual time=0.071..112.821 rows=292,061 loops=1)

  • Buffers: shared hit=8696
28. 98.803 148.407 ↑ 1.0 175,433 1

Hash (cost=5,200.33..5,200.33 rows=175,433 width=46) (actual time=148.407..148.407 rows=175,433 loops=1)

  • Buffers: shared hit=3446
29. 49.604 49.604 ↑ 1.0 175,433 1

Seq Scan on dim_product dp (cost=0..5,200.33 rows=175,433 width=46) (actual time=0.06..49.604 rows=175,433 loops=1)

  • Buffers: shared hit=3446
Planning time : 29.062 ms
Execution time : 85,520.828 ms