explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DPa2

Settings
# exclusive inclusive rows x rows loops node
1. 41.809 426.691 ↓ 198.9 42,761 1

Hash Join (cost=3,824.75..8,938.2 rows=215 width=198) (actual time=49.794..426.691 rows=42,761 loops=1)

  • Buffers: shared hit=233237 read=317, local hit=60 read=2787
2. 47.809 349.616 ↓ 191.8 42,761 1

Nested Loop (cost=610.3..5,718.7 rows=223 width=152) (actual time=14.337..349.616 rows=42,761 loops=1)

  • Buffers: shared hit=233237 read=317, local hit=60 read=1215
3. 23.653 120.403 ↓ 54.5 60,468 1

Hash Join (cost=609.88..2,757.84 rows=1,110 width=156) (actual time=14.314..120.403 rows=60,468 loops=1)

  • Buffers: shared hit=8278 read=3, local hit=60 read=1215
4. 64.022 95.253 ↓ 13.2 69,824 1

Hash Join (cost=426.76..2,337.55 rows=5,305 width=172) (actual time=12.789..95.253 rows=69,824 loops=1)

  • Buffers: shared hit=8278 read=3, local hit=18 read=1215
5. 18.492 18.492 ↑ 1.0 41,069 1

Seq Scan on _sales_raw s (cost=0..1,704.41 rows=41,069 width=116) (actual time=0.032..18.492 rows=41,069 loops=1)

  • Filter: (s.redistributor_company_id IS NOT NULL)
  • Buffers: local read=1185
6. 0.839 12.739 ↓ 20.9 2,699 1

Hash (cost=425.14..425.14 rows=129 width=86) (actual time=12.739..12.739 rows=2,699 loops=1)

  • Buffers: shared hit=8278 read=3, local hit=18 read=30
7. 1.977 11.900 ↓ 20.9 2,699 1

Nested Loop (cost=352.95..425.14 rows=129 width=86) (actual time=5.062..11.9 rows=2,699 loops=1)

  • Buffers: shared hit=8278 read=3, local hit=18 read=30
8. 1.272 7.198 ↓ 19.3 2,725 1

Hash Join (cost=352.67..376.13 rows=141 width=102) (actual time=5.041..7.198 rows=2,725 loops=1)

  • Buffers: shared hit=132, local hit=18 read=30
9. 2.884 3.623 ↓ 4.0 5,645 1

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

  • Group Key: cagg.contract_family_id, cagg.company_id
  • Buffers: local hit=18 read=24
10. 0.739 0.739 ↑ 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.739 rows=5,645 loops=1)

  • Buffers: local hit=18 read=24
11. 0.194 2.303 ↓ 3.6 502 1

Hash (cost=224.23..224.23 rows=141 width=102) (actual time=2.303..2.303 rows=502 loops=1)

  • Buffers: shared hit=132, local read=6
12. 0.268 2.109 ↓ 3.6 502 1

Hash Join (cost=182.67..224.23 rows=141 width=102) (actual time=1.295..2.109 rows=502 loops=1)

  • Buffers: shared hit=132, local read=6
13. 0.409 1.680 ↓ 1.4 534 1

Hash Join (cost=167.97..206.97 rows=379 width=86) (actual time=1.13..1.68 rows=534 loops=1)

  • Buffers: shared hit=132, local read=3
14. 0.163 0.163 ↓ 1.0 1,399 1

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

  • Buffers: shared hit=16
15. 0.104 1.108 ↓ 1.4 483 1

Hash (cost=163.66..163.66 rows=345 width=40) (actual time=1.108..1.108 rows=483 loops=1)

  • Buffers: shared hit=116, local read=3
16. 0.072 1.004 ↓ 1.4 483 1

Subquery Scan on cf (cost=103.52..163.66 rows=345 width=40) (actual time=0.293..1.004 rows=483 loops=1)

  • Buffers: shared hit=116, local read=3
17. 0.095 0.932 ↓ 1.4 483 1

Hash Join (cost=103.52..160.21 rows=345 width=40) (actual time=0.292..0.932 rows=483 loops=1)

  • Buffers: shared hit=116, local read=3
18. 0.094 0.827 ↓ 1.4 483 1

Hash Join (cost=102.27..158.04 rows=345 width=40) (actual time=0.277..0.827 rows=483 loops=1)

  • Buffers: shared hit=115, local read=3
19. 0.329 0.639 ↑ 1.0 501 1

Hash Join (cost=14.27..63.91 rows=501 width=40) (actual time=0.177..0.639 rows=501 loops=1)

  • Buffers: shared hit=28, local read=3
20. 0.146 0.146 ↓ 1.0 1,274 1

Seq Scan on contract_family cf2 (cost=0..40.72 rows=1,272 width=40) (actual time=0.005..0.146 rows=1,274 loops=1)

  • Buffers: shared hit=28
21. 0.079 0.164 ↑ 1.0 501 1

Hash (cost=8.01..8.01 rows=501 width=16) (actual time=0.164..0.164 rows=501 loops=1)

  • Buffers: local read=3
22. 0.085 0.085 ↑ 1.0 501 1

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

  • Buffers: local read=3
23. 0.005 0.094 ↑ 1.3 15 1

Hash (cost=87.76..87.76 rows=19 width=16) (actual time=0.094..0.094 rows=15 loops=1)

  • Buffers: shared hit=87
24. 0.089 0.089 ↑ 1.3 15 1

Seq Scan on grouping_company gc (cost=0..87.76 rows=19 width=16) (actual time=0.008..0.089 rows=15 loops=1)

  • Filter: (gc.grouping_company_type = 'BUYING_GROUP'::enum.grouping_company_type)
  • Buffers: shared hit=87
25. 0.004 0.010 ↑ 1.0 11 1

Hash (cost=1.11..1.11 rows=11 width=32) (actual time=0.009..0.01 rows=11 loops=1)

  • Buffers: shared hit=1
26. 0.006 0.006 ↑ 1.0 11 1

Seq Scan on grouping_company_parent gcp (cost=0..1.11 rows=11 width=32) (actual time=0.004..0.006 rows=11 loops=1)

  • Buffers: shared hit=1
27. 0.094 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
28. 0.067 0.067 ↑ 1.0 520 1

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

  • Buffers: local read=3
29. 2.725 2.725 ↑ 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.001..0.001 rows=1 loops=2,725)

  • Index Cond: (cav.contract_renew_version_id = crv.contract_renew_version_id)
  • Buffers: shared hit=8146 read=3
30. 0.909 1.497 ↑ 1.0 5,645 1

Hash (cost=98.45..98.45 rows=5,645 width=32) (actual time=1.497..1.497 rows=5,645 loops=1)

  • Buffers: local hit=42
31. 0.588 0.588 ↑ 1.0 5,645 1

Seq Scan on _contractee_agg xx (cost=0..98.45 rows=5,645 width=32) (actual time=0.015..0.588 rows=5,645 loops=1)

  • Buffers: local hit=42
32. 181.404 181.404 ↑ 1.0 1 60,468

Index Scan using contract_product_materialized_pkey on contract_product_materialized contract_product_materialized (cost=0.42..2.65 rows=1 width=52) (actual time=0.003..0.003 rows=1 loops=60,468)

  • Index Cond: ((contract_product_materialized.contract_id = cav.contract_amend_version_id) AND (contract_product_materialized.product_id = s.product_id))
  • Buffers: shared hit=224959 read=314
33. 16.199 35.266 ↑ 1.0 65,698 1

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

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

  • Buffers: local read=1572
Planning time : 33.165 ms
Execution time : 430.97 ms