explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GYQe

Settings
# exclusive inclusive rows x rows loops node
1. 4,803.296 9,940.821 ↓ 29,050.0 29,050 1

Hash Join (cost=36.2..643,764.58 rows=1 width=5,870) (actual time=6,737.997..9,940.821 rows=29,050 loops=1)

  • Buffers: shared hit=173962 read=479958
2. 4,272.660 4,272.660 ↑ 1.0 11,876,456 1

Seq Scan on fact_contract_view fact_contract_view (cost=0..599,191.36 rows=11,876,536 width=498) (actual time=0.015..4,272.66 rows=11,876,456 loops=1)

  • Buffers: shared hit=468 read=479958
3. 201.088 864.865 ↓ 29,050.0 29,050 1

Hash (cost=36.19..36.19 rows=1 width=5,372) (actual time=864.865..864.865 rows=29,050 loops=1)

  • Buffers: shared hit=173494
4. 206.601 663.777 ↓ 29,050.0 29,050 1

Nested Loop (cost=22.35..36.19 rows=1 width=5,372) (actual time=40.945..663.777 rows=29,050 loops=1)

  • Buffers: shared hit=173494
5. 148.480 282.876 ↓ 29,050.0 29,050 1

Nested Loop (cost=21.93..35.04 rows=1 width=3,852) (actual time=40.91..282.876 rows=29,050 loops=1)

  • Buffers: shared hit=57294
6. 6.380 92.396 ↓ 1,000.0 1,000 1

Nested Loop (cost=21.64..33.08 rows=1 width=3,836) (actual time=40.883..92.396 rows=1,000 loops=1)

  • Buffers: shared hit=33026
7. 6.140 81.016 ↓ 1,000.0 1,000 1

Nested Loop (cost=21.22..29.64 rows=1 width=3,630) (actual time=40.853..81.016 rows=1,000 loops=1)

  • Buffers: shared hit=29026
8. 5.394 71.876 ↓ 1,000.0 1,000 1

Nested Loop (cost=21.08..29.47 rows=1 width=3,472) (actual time=40.829..71.876 rows=1,000 loops=1)

  • Buffers: shared hit=27026
9. 4.577 60.482 ↓ 1,000.0 1,000 1

Nested Loop (cost=20.66..26.03 rows=1 width=1,952) (actual time=40.799..60.482 rows=1,000 loops=1)

  • Buffers: shared hit=23026
10. 4.196 52.905 ↓ 1,000.0 1,000 1

Nested Loop (cost=20.52..25.86 rows=1 width=1,847) (actual time=40.779..52.905 rows=1,000 loops=1)

  • Buffers: shared hit=21026
11. 4.958 45.709 ↓ 1,000.0 1,000 1

Hash Join (cost=20.37..22.67 rows=1 width=1,777) (actual time=40.76..45.709 rows=1,000 loops=1)

  • Buffers: shared hit=19026
12. 0.030 0.030 ↓ 1.0 96 1

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

  • Buffers: shared hit=1
13. 2.724 40.721 ↓ 1,000.0 1,000 1

Hash (cost=20.36..20.36 rows=1 width=1,760) (actual time=40.721..40.721 rows=1,000 loops=1)

  • Buffers: shared hit=19025
14. 4.054 37.997 ↓ 1,000.0 1,000 1

Nested Loop (cost=2.4..20.36 rows=1 width=1,760) (actual time=0.149..37.997 rows=1,000 loops=1)

  • Buffers: shared hit=19025
15. 3.128 29.943 ↓ 1,000.0 1,000 1

Nested Loop (cost=1.98..16.92 rows=1 width=1,607) (actual time=0.128..29.943 rows=1,000 loops=1)

  • Buffers: shared hit=15025
16. 2.418 22.815 ↓ 1,000.0 1,000 1

Nested Loop (cost=1.56..13.48 rows=1 width=1,546) (actual time=0.109..22.815 rows=1,000 loops=1)

  • Buffers: shared hit=11025
17. 1.704 14.397 ↓ 1,000.0 1,000 1

Nested Loop (cost=1.14..10.05 rows=1 width=619) (actual time=0.086..14.397 rows=1,000 loops=1)

  • Buffers: shared hit=7025
18. 0.325 8.693 ↓ 1,000.0 1,000 1

Limit (cost=0.85..6.73 rows=1 width=405) (actual time=0.068..8.693 rows=1,000 loops=1)

  • Buffers: shared hit=4025
19. 3.123 8.368 ↓ 1,000.0 1,000 1

Nested Loop (cost=0.85..6.73 rows=1 width=405) (actual time=0.067..8.368 rows=1,000 loops=1)

  • Buffers: shared hit=4025
20. 1.245 1.245 ↓ 1,000.0 1,000 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.042..1.245 rows=1,000 loops=1)

  • Index Cond: (fa.client_sk = 27)
  • Buffers: shared hit=27
21. 4.000 4.000 ↑ 1.0 1 1,000

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.004..0.004 rows=1 loops=1,000)

  • Index Cond: ((fa.product_sk = fpuc.product_sk) AND (fa.uom_sk = fpuc.uom_sk))
  • Buffers: shared hit=3998
22. 4.000 4.000 ↑ 1.0 1 1,000

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.004..0.004 rows=1 loops=1,000)

  • Index Cond: (dim_contract_family.contract_family_sk = fa.contract_family_sk)
  • Buffers: shared hit=3000
23. 6.000 6.000 ↑ 1.0 1 1,000

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

  • Index Cond: (dim_product.product_sk = fa.product_sk)
  • Buffers: shared hit=4000
24. 4.000 4.000 ↑ 1.0 1 1,000

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=1,000)

  • Index Cond: (dim_contract_renew_version.contract_renew_version_sk = fa.contract_renew_version_sk)
  • Buffers: shared hit=4000
25. 4.000 4.000 ↑ 1.0 1 1,000

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=1,000)

  • Index Cond: ((dim_date.date = fa.accrual_date) AND (dim_date.client_sk = fa.client_sk))
  • Buffers: shared hit=4000
26. 3.000 3.000 ↑ 1.0 1 1,000

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=1,000)

  • Index Cond: (dim_lumpsum.lumpsum_sk = fa.lumpsum_sk)
  • Buffers: shared hit=2000
27. 3.000 3.000 ↑ 1.0 1 1,000

Index Scan using dim_client_pkey on dim_client dim_client (cost=0.14..0.16 rows=1 width=105) (actual time=0.003..0.003 rows=1 loops=1,000)

  • Index Cond: (dim_client.client_sk = dim_date.client_sk)
  • Buffers: shared hit=2000
28. 6.000 6.000 ↑ 1.0 1 1,000

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=1,000)

  • Index Cond: (dim_company.company_sk = fa.accrual_company_sk)
  • Buffers: shared hit=4000
29. 3.000 3.000 ↑ 1.0 1 1,000

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=1,000)

  • 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=2000
30. 5.000 5.000 ↑ 1.0 1 1,000

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=1,000)

  • Index Cond: (dim_contract_amend_version.contract_amend_version_sk = fa.contract_amend_version_sk)
  • Buffers: shared hit=4000
31. 42.000 42.000 ↑ 1.8 29 1,000

Index Scan using dim_contract_family_contractee_contract_family_sk_idx on dim_contract_family_contractee dim_contract_family_contractee (cost=0.29..1.32 rows=51 width=16) (actual time=0.005..0.042 rows=29 loops=1,000)

  • Index Cond: (dim_contract_family_contractee.contract_family_sk = dim_contract_family.contract_family_sk)
  • Filter: (dim_contract_family.client_sk = dim_contract_family_contractee.client_sk)
  • Buffers: shared hit=24268
32. 174.300 174.300 ↑ 1.0 1 29,050

Index Scan using dim_company_pkey on dim_company dim_company_contractee (cost=0.42..1.14 rows=1 width=1,520) (actual time=0.006..0.006 rows=1 loops=29,050)

  • Index Cond: (dim_company_contractee.company_sk = dim_contract_family_contractee.company_sk)
  • Filter: (dim_contract_family_contractee.client_sk = dim_company_contractee.client_sk)
  • Buffers: shared hit=116200