explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ykf1

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 32.672 297.331 ↓ 198.9 42,761 1

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

  • Buffers: shared hit=233554, local hit=47 read=2800
2. 17.735 228.654 ↓ 191.8 42,761 1

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

  • Buffers: shared hit=233554, local hit=47 read=1228
3. 17.575 89.983 ↓ 54.5 60,468 1

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

  • Buffers: shared hit=8281, local hit=47 read=1228
4. 44.502 70.462 ↓ 13.2 69,824 1

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

  • Buffers: shared hit=8281, local hit=5 read=1228
5. 14.499 14.499 ↑ 1.0 41,069 1

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

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

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

  • Buffers: shared hit=8281, local hit=5 read=43
7. 1.044 10.663 ↓ 20.9 2,699 1

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

  • Buffers: shared hit=8281, local hit=5 read=43
8. 1.220 6.894 ↓ 19.3 2,725 1

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

  • Buffers: shared hit=132, local hit=5 read=43
9. 2.827 3.588 ↓ 4.0 5,645 1

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

  • Group Key: cagg.contract_family_id, cagg.company_id
  • Buffers: local hit=2 read=40
10. 0.761 0.761 ↑ 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.761 rows=5,645 loops=1)

  • Buffers: local hit=2 read=40
11. 0.161 2.086 ↓ 3.6 502 1

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

  • Buffers: shared hit=132, local hit=3 read=3
12. 0.165 1.925 ↓ 3.6 502 1

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

  • Buffers: shared hit=132, local hit=3 read=3
13. 0.361 1.613 ↓ 1.4 534 1

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

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

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

  • Buffers: shared hit=16
15. 0.117 1.114 ↓ 1.4 483 1

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

  • Buffers: shared hit=116, local hit=3
16. 0.077 0.997 ↓ 1.4 483 1

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

  • Buffers: shared hit=116, local hit=3
17. 0.096 0.920 ↓ 1.4 483 1

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

  • Buffers: shared hit=116, local hit=3
18. 0.097 0.815 ↓ 1.4 483 1

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

  • Buffers: shared hit=115, local hit=3
19. 0.345 0.629 ↑ 1.0 501 1

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

  • Buffers: shared hit=28, local hit=3
20. 0.148 0.148 ↓ 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.148 rows=1,273 loops=1)

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

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

  • Buffers: local hit=3
22. 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.008..0.057 rows=501 loops=1)

  • Buffers: local hit=3
23. 0.004 0.089 ↑ 1.3 15 1

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

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

Seq Scan on grouping_company gc (cost=0..87.76 rows=19 width=16) (actual time=0.006..0.085 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.081 0.147 ↑ 1.0 520 1

Hash (cost=8.2..8.2 rows=520 width=16) (actual time=0.147..0.147 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.012..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. 1.235 1.946 ↑ 1.0 5,645 1

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

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

Seq Scan on _contractee_agg xx (cost=0..98.45 rows=5,645 width=32) (actual time=0.016..0.711 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.830 36.005 ↑ 1.0 65,698 1

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

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

  • Buffers: local read=1572
Planning time : 28.492 ms
Execution time : 300.808 ms