explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vPqR

Settings
# exclusive inclusive rows x rows loops node
1. 102.202 1,235.711 ↓ 15,070.0 15,070 1

Nested Loop (cost=15.89..37.69 rows=1 width=4,334) (actual time=205.998..1,235.711 rows=15,070 loops=1)

  • Buffers: shared hit=534564 read=199
2. 88.620 1,103.369 ↓ 15,070.0 15,070 1

Nested Loop (cost=15.74..37.49 rows=1 width=3,920) (actual time=205.964..1,103.369 rows=15,070 loops=1)

  • Buffers: shared hit=504424 read=199
3. 84.003 954.469 ↓ 15,070.0 15,070 1

Nested Loop (cost=15.32..34.05 rows=1 width=2,993) (actual time=205.923..954.469 rows=15,070 loops=1)

  • Buffers: shared hit=444144 read=199
4. 56.578 810.186 ↓ 15,070.0 15,070 1

Nested Loop (cost=14.89..30.59 rows=1 width=2,495) (actual time=205.658..810.186 rows=15,070 loops=1)

  • Buffers: shared hit=384009
5. 51.780 708.398 ↓ 15,070.0 15,070 1

Nested Loop (cost=14.6..27.28 rows=1 width=2,281) (actual time=205.631..708.398 rows=15,070 loops=1)

  • Buffers: shared hit=338799
6. 50.015 596.338 ↓ 15,070.0 15,070 1

Nested Loop (cost=14.18..23.84 rows=1 width=2,128) (actual time=205.602..596.338 rows=15,070 loops=1)

  • Buffers: shared hit=278519
7. 40.093 440.833 ↓ 15,070.0 15,070 1

Nested Loop (cost=14.18..22.51 rows=1 width=2,023) (actual time=205.569..440.833 rows=15,070 loops=1)

  • Buffers: shared hit=263449
8. 37.459 340.460 ↓ 15,070.0 15,070 1

Nested Loop (cost=13.76..19.08 rows=1 width=1,817) (actual time=205.543..340.46 rows=15,070 loops=1)

  • Buffers: shared hit=203169
9. 22.152 227.651 ↓ 15,070.0 15,070 1

Hash Join (cost=13.34..15.64 rows=1 width=297) (actual time=205.504..227.651 rows=15,070 loops=1)

  • Buffers: shared hit=142889
10. 0.038 0.038 ↓ 1.0 96 1

Seq Scan on dim_accrual_run dim_accrual_run (cost=0..1.94 rows=94 width=17) (actual time=0.019..0.038 rows=96 loops=1)

  • Buffers: shared hit=1
11. 14.993 205.461 ↓ 15,070.0 15,070 1

Hash (cost=13.32..13.32 rows=1 width=280) (actual time=205.461..205.461 rows=15,070 loops=1)

  • Buffers: shared hit=142888
12. 18.859 190.468 ↓ 15,070.0 15,070 1

Nested Loop (cost=1.42..13.32 rows=1 width=280) (actual time=0.067..190.468 rows=15,070 loops=1)

  • Buffers: shared hit=142888
13. 17.573 141.469 ↓ 15,070.0 15,070 1

Nested Loop (cost=1.27..10.14 rows=1 width=210) (actual time=0.059..141.469 rows=15,070 loops=1)

  • Buffers: shared hit=112748
14. 18.735 78.686 ↓ 15,070.0 15,070 1

Nested Loop (cost=0.85..6.7 rows=1 width=158) (actual time=0.046..78.686 rows=15,070 loops=1)

  • Buffers: shared hit=60998
15. 14.741 14.741 ↓ 15,070.0 15,070 1

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

  • Index Cond: (fa.client_sk = 27)
  • Buffers: shared hit=715
16. 45.210 45.210 ↑ 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.003..0.003 rows=1 loops=15,070)

  • Index Cond: (dim_contract_renew_version.contract_renew_version_sk = fa.contract_renew_version_sk)
  • Buffers: shared hit=60283
17. 45.210 45.210 ↓ 0.0 0 15,070

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=15,070)

  • Index Cond: ((fa.product_sk = fpuc.product_sk) AND (fa.uom_sk = fpuc.uom_sk))
  • Buffers: shared hit=51750
18. 30.140 30.140 ↑ 1.0 1 15,070

Index Scan using dim_lumpsum_pkey on dim_lumpsum dim_lumpsum (cost=0.15..3.17 rows=1 width=70) (actual time=0.002..0.002 rows=1 loops=15,070)

  • Index Cond: (dim_lumpsum.lumpsum_sk = fa.lumpsum_sk)
  • Buffers: shared hit=30140
19. 75.350 75.350 ↑ 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.005..0.005 rows=1 loops=15,070)

  • Index Cond: (dim_company.company_sk = fa.accrual_company_sk)
  • Buffers: shared hit=60280
20. 60.280 60.280 ↑ 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.004..0.004 rows=1 loops=15,070)

  • Index Cond: (dim_contract_amend_version.contract_amend_version_sk = fa.contract_amend_version_sk)
  • Buffers: shared hit=60280
21. 105.490 105.490 ↑ 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.007 rows=1 loops=15,070)

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

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=15,070)

  • Index Cond: ((dim_date.date = fa.accrual_date) AND (dim_date.client_sk = 27))
  • Buffers: shared hit=60280
23. 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
24. 60.280 60.280 ↑ 1.0 1 15,070

Index Scan using fact_contract_view_contract_sk_idx on fact_contract_view fact_contract_view (cost=0.43..3.45 rows=1 width=498) (actual time=0.004..0.004 rows=1 loops=15,070)

  • Index Cond: (fact_contract_view.contract_sk = fa.contract_sk)
  • Buffers: shared hit=60135 read=199
25. 60.280 60.280 ↑ 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.004..0.004 rows=1 loops=15,070)

  • Index Cond: (dim_product.product_sk = fa.product_sk)
  • Buffers: shared hit=60280
26. 30.140 30.140 ↑ 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.002..0.002 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