explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bX12

Settings
# exclusive inclusive rows x rows loops node
1. 102.491 4,422.113 ↓ 15,070.0 15,070 1

Nested Loop (cost=1,017.77..549,503 rows=1 width=4,334) (actual time=2,534.973..4,422.113 rows=15,070 loops=1)

  • Buffers: shared hit=760774 read=479953
2. 79.890 4,244.272 ↓ 15,070.0 15,070 1

Nested Loop (cost=1,017.35..549,499.54 rows=1 width=3,151) (actual time=2,534.943..4,244.272 rows=15,070 loops=1)

  • Buffers: shared hit=700504 read=479943
3. 82.132 4,119.172 ↓ 15,070.0 15,070 1

Nested Loop (cost=1,017.21..549,499.36 rows=1 width=2,993) (actual time=2,534.921..4,119.172 rows=15,070 loops=1)

  • Buffers: shared hit=670364 read=479943
4. 74.598 3,991.830 ↓ 15,070.0 15,070 1

Nested Loop (cost=1,016.92..549,496.06 rows=1 width=2,779) (actual time=2,534.899..3,991.83 rows=15,070 loops=1)

  • Buffers: shared hit=625154 read=479943
5. 71.731 3,856.952 ↓ 15,070.0 15,070 1

Nested Loop (cost=1,016.5..549,492.62 rows=1 width=2,718) (actual time=2,534.877..3,856.952 rows=15,070 loops=1)

  • Buffers: shared hit=564885 read=479929
6. 52.298 3,694.801 ↓ 15,070.0 15,070 1

Nested Loop (cost=1,016.08..549,489.18 rows=1 width=1,198) (actual time=2,534.853..3,694.801 rows=15,070 loops=1)

  • Buffers: shared hit=504606 read=479928
7. 68.962 3,521.943 ↓ 15,070.0 15,070 1

Nested Loop (cost=1,016.08..549,487.86 rows=1 width=1,093) (actual time=2,534.83..3,521.943 rows=15,070 loops=1)

  • Buffers: shared hit=489536 read=479928
8. 0.000 3,377.631 ↓ 15,070.0 15,070 1

Gather (cost=1,015.66..549,484.42 rows=1 width=887) (actual time=2,534.798..3,377.631 rows=15,070 loops=1)

  • Buffers: shared hit=429290 read=479894
9. 1,542.264 3,600.652 ↓ 5,023.0 5,023 3

Hash Join (cost=15.66..548,484.32 rows=1 width=887) (actual time=2,522.199..3,600.652 rows=5,023 loops=3)

  • Buffers: shared hit=429290 read=479894
10. 1,737.377 1,737.377 ↑ 1.3 3,958,819 3

Seq Scan on fact_contract_view fact_contract_view (cost=0..529,911.57 rows=4,948,557 width=498) (actual time=0.049..1,737.377 rows=3,958,819 loops=3)

  • Buffers: shared hit=532 read=479894
11. 26.283 321.011 ↓ 15,070.0 15,070 3

Hash (cost=15.65..15.65 rows=1 width=389) (actual time=321.011..321.011 rows=15,070 loops=3)

  • Buffers: shared hit=428666
12. 104.877 294.728 ↓ 15,070.0 15,070 3

Nested Loop (cost=7.28..15.65 rows=1 width=389) (actual time=105.144..294.728 rows=15,070 loops=3)

  • Buffers: shared hit=428666
13. 64.945 189.847 ↓ 15,070.0 15,070 3

Nested Loop (cost=6.86..12.2 rows=1 width=236) (actual time=105.11..189.847 rows=15,070 loops=3)

  • Buffers: shared hit=247824
14. 19.827 124.899 ↓ 15,070.0 15,070 3

Hash Join (cost=6.72..9.02 rows=1 width=166) (actual time=105.078..124.899 rows=15,070 loops=3)

  • Buffers: shared hit=157402
15. 0.036 0.036 ↓ 1.0 96 3

Seq Scan on dim_accrual_run dim_accrual_run (cost=0..1.94 rows=94 width=17) (actual time=0.016..0.036 rows=96 loops=3)

  • Buffers: shared hit=3
16. 13.230 105.036 ↓ 15,070.0 15,070 3

Hash (cost=6.7..6.7 rows=1 width=149) (actual time=105.036..105.036 rows=15,070 loops=3)

  • Buffers: shared hit=157399
17. 75.411 91.806 ↓ 15,070.0 15,070 3

Nested Loop (cost=0.85..6.7 rows=1 width=149) (actual time=0.069..91.806 rows=15,070 loops=3)

  • Buffers: shared hit=157399
18. 16.392 16.392 ↓ 15,070.0 15,070 3

Index Scan using fact_accrual_client_sk_idx on fact_accrual fa (cost=0.43..3.26 rows=1 width=97) (actual time=0.044..16.392 rows=15,070 loops=3)

  • Index Cond: (fa.client_sk = 27)
  • Buffers: shared hit=2147
19. 0.003 0.003 ↓ 0.0 0 45,210

Index Scan using product_uom_conversion_pkey on fact_product_uom_conversion fpuc (cost=0.42..3.44 rows=1 width=60) (actual time=0.003..0.003 rows=0 loops=45,210)

  • Index Cond: ((fa.product_sk = fpuc.product_sk) AND (fa.uom_sk = fpuc.uom_sk))
  • Buffers: shared hit=155252
20. 0.003 0.003 ↑ 1.0 1 45,210

Index Scan using dim_lumpsum_pkey on dim_lumpsum dim_lumpsum (cost=0.15..3.17 rows=1 width=70) (actual time=0.003..0.003 rows=1 loops=45,210)

  • Index Cond: (dim_lumpsum.lumpsum_sk = fa.lumpsum_sk)
  • Buffers: shared hit=90422
21. 0.004 0.004 ↑ 1.0 1 45,210

Index Scan using idx_dim_date_pkey on dim_date dim_date (cost=0.42..3.44 rows=1 width=153) (actual time=0.004..0.004 rows=1 loops=45,210)

  • Index Cond: ((dim_date.date = fa.accrual_date) AND (dim_date.client_sk = 27))
  • Buffers: shared hit=180842
22. 75.350 75.350 ↑ 1.0 1 15,070

Index Scan using dim_contract_amend_version_contract_amend_version_sk_idx on dim_contract_amend_version dim_contract_amend_version (cost=0.42..3.44 rows=1 width=206) (actual time=0.005..0.005 rows=1 loops=15,070)

  • Index Cond: (dim_contract_amend_version.contract_amend_version_sk = fa.contract_amend_version_sk)
  • Buffers: shared hit=60246 read=34
23. 120.560 120.560 ↑ 1.0 1 15,070

Seq Scan on dim_client dim_client (cost=0..1.31 rows=1 width=105) (actual time=0.007..0.008 rows=1 loops=15,070)

  • Filter: (dim_client.client_sk = 27)
  • Buffers: shared hit=15070
24. 90.420 90.420 ↑ 1.0 1 15,070

Index Scan using dim_company_company_sk_idx on dim_company dim_company (cost=0.42..3.44 rows=1 width=1,520) (actual time=0.006..0.006 rows=1 loops=15,070)

  • Index Cond: (dim_company.company_sk = fa.accrual_company_sk)
  • Buffers: shared hit=60279 read=1
25. 60.280 60.280 ↑ 1.0 1 15,070

Index Scan using dim_contract_renew_version_contract_renew_version_sk_idx on dim_contract_renew_version dim_contract_renew_version (cost=0.42..3.44 rows=1 width=61) (actual time=0.004..0.004 rows=1 loops=15,070)

  • Index Cond: (dim_contract_renew_version.contract_renew_version_sk = fa.contract_renew_version_sk)
  • Buffers: shared hit=60269 read=14
26. 45.210 45.210 ↑ 1.0 1 15,070

Index Scan using dim_contract_family_contract_family_sk_idx on dim_contract_family dim_contract_family (cost=0.29..3.31 rows=1 width=214) (actual time=0.003..0.003 rows=1 loops=15,070)

  • Index Cond: (dim_contract_family.contract_family_sk = fa.contract_family_sk)
  • Buffers: shared hit=45210
27. 45.210 45.210 ↑ 1.0 1 15,070

Index Scan using dim_contract_template_pkey on dim_contract_template dim_contract_template (cost=0.14..0.17 rows=1 width=158) (actual time=0.003..0.003 rows=1 loops=15,070)

  • Index Cond: (dim_contract_template.contract_template_sk = dim_contract_family.contract_template_sk)
  • Filter: (dim_contract_family.client_sk = dim_contract_template.client_sk)
  • Buffers: shared hit=30140
28. 75.350 75.350 ↑ 1.0 1 15,070

Index Scan using dim_product_pkey on dim_product dim_product (cost=0.42..3.44 rows=1 width=927) (actual time=0.005..0.005 rows=1 loops=15,070)

  • Index Cond: (dim_product.product_sk = fa.product_sk)
  • Buffers: shared hit=60270 read=10