explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WHSv

Settings
# exclusive inclusive rows x rows loops node
1. 4.578 814.767 ↑ 1.0 1 1

Aggregate (cost=7,005.8..7,005.81 rows=1 width=8) (actual time=814.766..814.767 rows=1 loops=1)

  • Buffers: shared hit=202081, local hit=96870 read=2786, temp read=4972 written=4958
2. 9.850 810.189 ↓ 153.2 42,750 1

Nested Loop (cost=3,894.32..7,005.1 rows=279 width=0) (actual time=536.846..810.189 rows=42,750 loops=1)

  • Buffers: shared hit=202081, local hit=96870 read=2786, temp read=4972 written=4958
3. 20.726 757.589 ↓ 105.6 42,750 1

Hash Join (cost=3,894.04..6,841.11 rows=405 width=32) (actual time=536.828..757.589 rows=42,750 loops=1)

  • Buffers: shared hit=202081, local hit=19 read=2786, temp read=4972 written=4958
4. 174.810 673.198 ↓ 104.7 43,865 1

Hash Join (cost=679.59..3,620.21 rows=419 width=64) (actual time=472.363..673.198 rows=43,865 loops=1)

  • Buffers: shared hit=202081, local hit=19 read=1214, temp read=4972 written=4958
5. 26.331 26.331 ↑ 1.0 41,069 1

Seq Scan on _sales_raw s (cost=0..1,704.41 rows=41,069 width=68) (actual time=0.027..26.331 rows=41,069 loops=1)

  • Filter: (s.redistributor_company_id IS NOT NULL)
  • Buffers: local read=1185
6. 150.865 472.057 ↓ 118.7 267,505 1

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

  • Buffers: shared hit=202081, local hit=19 read=29, temp written=2791
7. 131.190 321.192 ↓ 118.7 267,505 1

Nested Loop (cost=209.13..645.78 rows=2,254 width=78) (actual time=3.918..321.192 rows=267,505 loops=1)

  • Buffers: shared hit=202081, local hit=19 read=29
8. 2.472 47.061 ↓ 52.9 2,697 1

Nested Loop (cost=208.71..309.66 rows=51 width=66) (actual time=3.903..47.061 rows=2,697 loops=1)

  • Buffers: shared hit=44999, local hit=19 read=29
9. 0.000 39.139 ↓ 48.7 2,725 1

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

  • Buffers: shared hit=36852, local hit=19 read=29
10. 4.940 27.498 ↓ 76.2 6,171 1

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

  • Buffers: shared hit=18661, local hit=19 read=29
11. 4.337 10.216 ↓ 76.2 6,171 1

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

  • Buffers: shared hit=16, local hit=19 read=29
12. 3.970 4.599 ↓ 4.0 5,645 1

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

  • Group Key: cagg.contract_family_id, cagg.company_id
  • Buffers: local hit=19 read=23
13. 0.629 0.629 ↑ 1.0 5,645 1

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

  • Buffers: local hit=19 read=23
14. 0.194 1.280 ↓ 2.5 520 1

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

  • Buffers: shared hit=16, local read=6
15. 0.157 1.086 ↓ 2.5 520 1

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

  • Buffers: shared hit=16, local read=6
16. 0.296 0.569 ↑ 1.0 520 1

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

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

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

  • Buffers: shared hit=16
18. 0.103 0.161 ↑ 1.0 520 1

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

  • Buffers: local read=3
19. 0.058 0.058 ↑ 1.0 520 1

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

  • Buffers: local read=3
20. 0.087 0.360 ↑ 1.0 501 1

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

  • Buffers: local read=3
21. 0.208 0.273 ↑ 1.0 501 1

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

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

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

  • Buffers: local read=3
23. 12.342 12.342 ↑ 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.002..0.002 rows=1 loops=6,171)

  • Index Cond: (cf.contract_family_id = crv.contract_family_id)
  • Buffers: shared hit=18645
24. 12.342 12.342 ↑ 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.002..0.002 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
25. 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=8147
26. 142.941 142.941 ↓ 1.8 105 2,697

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.005..0.053 rows=105 loops=2,697)

  • Index Cond: (contract_product_materialized.contract_id = cav.contract_amend_version_id)
  • Buffers: shared hit=157082
27. 30.510 63.665 ↑ 1.0 65,698 1

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

  • Buffers: local read=1572
28. 33.155 33.155 ↑ 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.016..33.155 rows=65,698 loops=1)

  • Buffers: local read=1572
29. 42.750 42.750 ↓ 0.0 0 42,750

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,750)

  • Index Cond: (xx.company_id = s.redistributor_company_id)
  • Buffers: local hit=96851
Planning time : 51.569 ms
Execution time : 815.199 ms