explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xWVZ

Settings
# exclusive inclusive rows x rows loops node
1. 3.969 565.448 ↓ 153.3 42,761 1

Nested Loop (cost=3,894.32..7,005.1 rows=279 width=180) (actual time=384.775..565.448 rows=42,761 loops=1)

  • Buffers: shared hit=200346 read=1755, local hit=97631 read=2047, temp read=4975 written=4961
2. 17.860 518.718 ↓ 105.6 42,761 1

Hash Join (cost=3,894.04..6,841.11 rows=405 width=196) (actual time=384.745..518.718 rows=42,761 loops=1)

  • Buffers: shared hit=200346 read=1755, local hit=781 read=2024, temp read=4975 written=4961
3. 103.684 467.072 ↓ 104.7 43,876 1

Hash Join (cost=679.59..3,620.21 rows=419 width=212) (actual time=350.425..467.072 rows=43,876 loops=1)

  • Buffers: shared hit=200346 read=1755, local hit=2 read=1231, temp read=4975 written=4961
4. 13.411 13.411 ↑ 1.0 41,069 1

Seq Scan on _sales_raw s (cost=0..1,704.41 rows=41,069 width=180) (actual time=0.024..13.411 rows=41,069 loops=1)

  • Filter: (s.redistributor_company_id IS NOT NULL)
  • Buffers: local read=1185
5. 107.650 349.977 ↓ 118.9 267,893 1

Hash (cost=645.78..645.78 rows=2,254 width=78) (actual time=349.977..349.977 rows=267,893 loops=1)

  • Buffers: shared hit=200346 read=1755, local hit=2 read=46, temp written=2796
6. 99.854 242.327 ↓ 118.9 267,893 1

Nested Loop (cost=209.13..645.78 rows=2,254 width=78) (actual time=3.748..242.327 rows=267,893 loops=1)

  • Buffers: shared hit=200346 read=1755, local hit=2 read=46
7. 0.165 34.513 ↓ 52.9 2,699 1

Nested Loop (cost=208.71..309.66 rows=51 width=66) (actual time=3.735..34.513 rows=2,699 loops=1)

  • Buffers: shared hit=45001, local hit=2 read=46
8. 3.183 28.898 ↓ 48.7 2,725 1

Nested Loop (cost=208.44..290.19 rows=56 width=82) (actual time=3.725..28.898 rows=2,725 loops=1)

  • Buffers: shared hit=36852, local hit=2 read=46
9. 5.280 19.544 ↓ 76.2 6,171 1

Nested Loop (cost=208.17..256.76 rows=81 width=98) (actual time=3.716..19.544 rows=6,171 loops=1)

  • Buffers: shared hit=18661, local hit=2 read=46
10. 2.673 8.093 ↓ 76.2 6,171 1

Hash Join (cost=207.89..229.64 rows=81 width=110) (actual time=3.695..8.093 rows=6,171 loops=1)

  • Buffers: shared hit=16, local hit=2 read=46
11. 3.663 4.328 ↓ 4.0 5,645 1

HashAggregate (cost=126.68..140.84 rows=1,416 width=32) (actual time=2.592..4.328 rows=5,645 loops=1)

  • Group Key: cagg.contract_family_id, cagg.company_id
  • Buffers: local hit=2 read=40
12. 0.665 0.665 ↑ 1.0 5,645 1

Seq Scan on _contractee_agg cagg (cost=0..98.45 rows=5,645 width=32) (actual time=0.01..0.665 rows=5,645 loops=1)

  • Buffers: local hit=2 read=40
13. 0.136 1.092 ↓ 2.5 520 1

Hash (cost=78.66..78.66 rows=205 width=78) (actual time=1.092..1.092 rows=520 loops=1)

  • Buffers: shared hit=16, local read=6
14. 0.128 0.956 ↓ 2.5 520 1

Hash Join (cost=35.23..78.66 rows=205 width=78) (actual time=0.481..0.956 rows=520 loops=1)

  • Buffers: shared hit=16, local read=6
15. 0.262 0.501 ↑ 1.0 520 1

Hash Join (cost=14.7..54.12 rows=520 width=62) (actual time=0.146..0.501 rows=520 loops=1)

  • Buffers: shared hit=16, local read=3
16. 0.107 0.107 ↑ 1.0 1,397 1

Seq Scan on contract_renew_version crv (cost=0..29.97 rows=1,397 width=46) (actual time=0.004..0.107 rows=1,397 loops=1)

  • Buffers: shared hit=16
17. 0.076 0.132 ↑ 1.0 520 1

Hash (cost=8.2..8.2 rows=520 width=16) (actual time=0.132..0.132 rows=520 loops=1)

  • Buffers: local read=3
18. 0.056 0.056 ↑ 1.0 520 1

Seq Scan on _contract_renew_version xcrv (cost=0..8.2 rows=520 width=16) (actual time=0.009..0.056 rows=520 loops=1)

  • Buffers: local read=3
19. 0.087 0.327 ↑ 1.0 501 1

Hash (cost=14.27..14.27 rows=501 width=16) (actual time=0.327..0.327 rows=501 loops=1)

  • Buffers: local read=3
20. 0.183 0.240 ↑ 1.0 501 1

HashAggregate (cost=9.26..14.27 rows=501 width=16) (actual time=0.173..0.24 rows=501 loops=1)

  • Group Key: xcf.contract_family_id
  • Buffers: local read=3
21. 0.057 0.057 ↑ 1.0 501 1

Seq Scan on _contract_family xcf (cost=0..8.01 rows=501 width=16) (actual time=0.01..0.057 rows=501 loops=1)

  • Buffers: local read=3
22. 6.171 6.171 ↑ 1.0 1 6,171

Index Scan using contract_family_pkey on contract_family cf (cost=0.28..0.33 rows=1 width=36) (actual time=0.001..0.001 rows=1 loops=6,171)

  • Index Cond: (cf.contract_family_id = crv.contract_family_id)
  • Buffers: shared hit=18645
23. 6.171 6.171 ↑ 1.0 1 6,171

Index Scan using grouping_company_pkey on grouping_company gc (cost=0.27..0.41 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=6,171)

  • Index Cond: (gc.grouping_id = cf.contractee_grouping_id)
  • Filter: (gc.grouping_company_type = 'BUYING_GROUP'::enum.grouping_company_type)
  • Buffers: shared hit=18191
24. 5.450 5.450 ↑ 1.0 1 2,725

Index Scan using contract_amend_version_contract_renew_version_id_idx on contract_amend_version cav (cost=0.28..0.35 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=2,725)

  • Index Cond: (cav.contract_renew_version_id = crv.contract_renew_version_id)
  • Buffers: shared hit=8149
25. 107.960 107.960 ↓ 1.8 105 2,699

Index Scan using contract_product_materialized_pkey on contract_product_materialized contract_product_materialized (cost=0.42..5.59 rows=57 width=52) (actual time=0.004..0.04 rows=105 loops=2,699)

  • Index Cond: (contract_product_materialized.contract_id = cav.contract_amend_version_id)
  • Buffers: shared hit=155345 read=1755
26. 15.952 33.786 ↑ 1.0 65,698 1

Hash (cost=2,228.98..2,228.98 rows=65,698 width=32) (actual time=33.786..33.786 rows=65,698 loops=1)

  • Buffers: local hit=779 read=793
27. 17.834 17.834 ↑ 1.0 65,698 1

Seq Scan on _sales_potential_earnings x (cost=0..2,228.98 rows=65,698 width=32) (actual time=0.014..17.834 rows=65,698 loops=1)

  • Buffers: local hit=779 read=793
28. 42.761 42.761 ↓ 0.0 0 42,761

Index Scan using ix_tmp_contractee_agg_company on _contractee_agg xx (cost=0.28..0.35 rows=4 width=32) (actual time=0.001..0.001 rows=0 loops=42,761)

  • Index Cond: (xx.company_id = s.redistributor_company_id)
  • Buffers: local hit=96850 read=23
Planning time : 46.626 ms
Execution time : 568.089 ms