explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Kkls : Optimization for: plan #ykf1

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 34.424 318.740 ↓ 198.9 42,761 1

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

  • Buffers: shared hit=233554, local hit=46 read=2801
2. 31.019 248.748 ↓ 191.8 42,761 1

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

  • Buffers: shared hit=233554, local hit=46 read=1229
3. 18.479 96.793 ↓ 54.5 60,468 1

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

  • Buffers: shared hit=8281, local hit=46 read=1229
4. 49.718 76.899 ↓ 13.2 69,824 1

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

  • Buffers: shared hit=8281, local hit=4 read=1229
5. 15.397 15.397 ↑ 1.0 41,069 1

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

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

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

  • Buffers: shared hit=8281, local hit=4 read=44
7. 1.183 10.962 ↓ 20.9 2,699 1

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

  • Buffers: shared hit=8281, local hit=4 read=44
8. 1.264 7.054 ↓ 19.3 2,725 1

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

  • Buffers: shared hit=132, local hit=4 read=44
9. 2.934 3.711 ↓ 4.0 5,645 1

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

  • Group Key: cagg.contract_family_id, cagg.company_id
  • Buffers: local hit=4 read=38
10. 0.777 0.777 ↑ 1.0 5,645 1

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

  • Buffers: local hit=4 read=38
11. 0.151 2.079 ↓ 3.6 502 1

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

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

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

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

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

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

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

  • Buffers: shared hit=16
15. 0.114 1.172 ↓ 1.4 483 1

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

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

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

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

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

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

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

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

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

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

Seq Scan on contract_family cf2 (cost=0..40.72 rows=1,272 width=40) (actual time=0.004..0.153 rows=1,273 loops=1)

  • Buffers: shared hit=28
21. 0.078 0.143 ↑ 1.0 501 1

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

  • 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.011..0.065 rows=501 loops=1)

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

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

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

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

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

Hash (cost=1.11..1.11 rows=11 width=32) (actual time=0.009..0.009 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.082 0.148 ↑ 1.0 520 1

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

  • Buffers: local read=3
28. 0.066 0.066 ↑ 1.0 520 1

Seq Scan on _contract_renew_version xcrv (cost=0..8.2 rows=520 width=16) (actual time=0.013..0.066 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=8149
30. 0.862 1.415 ↑ 1.0 5,645 1

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

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

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

  • Buffers: local hit=42
32. 120.936 120.936 ↑ 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.002..0.002 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=225273
33. 16.420 35.568 ↑ 1.0 65,698 1

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

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

  • Buffers: local read=1572
Planning time : 27.763 ms
Execution time : 322.043 ms