explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aIyu : Optimization for: Optimization for: Optimization for: plan #ykf1; plan #Kkls; plan #hRGP

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 38.515 507.080 ↓ 21,380.5 42,761 1

Hash Join (cost=3,596.79..6,356.29 rows=2 width=198) (actual time=41.826..507.08 rows=42,761 loops=1)

  • Buffers: shared hit=407049, local hit=179058 read=2787
2. 38.685 432.005 ↓ 21,380.5 42,761 1

Nested Loop (cost=382.34..3,141.8 rows=2 width=152) (actual time=5.07..432.005 rows=42,761 loops=1)

  • Buffers: shared hit=407049, local hit=179058 read=1215
3. 36.704 272.384 ↓ 6,046.8 60,468 1

Nested Loop (cost=381.93..3,115.12 rows=10 width=156) (actual time=5.054..272.384 rows=60,468 loops=1)

  • Buffers: shared hit=181776, local hit=179058 read=1215
4. 26.459 175.092 ↓ 5,508.0 60,588 1

Nested Loop (cost=381.65..3,111.3 rows=11 width=172) (actual time=5.042..175.092 rows=60,588 loops=1)

  • Buffers: shared hit=132, local hit=179058 read=1215
5. 57.602 78.689 ↓ 1,345.1 69,944 1

Hash Join (cost=381.37..3,090.24 rows=52 width=188) (actual time=5.024..78.689 rows=69,944 loops=1)

  • Buffers: shared hit=132, local hit=18 read=1215
6. 16.108 16.108 ↑ 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..16.108 rows=41,069 loops=1)

  • Filter: (s.redistributor_company_id IS NOT NULL)
  • Buffers: local read=1185
7. 0.807 4.979 ↓ 1.7 2,725 1

Hash (cost=361.5..361.5 rows=1,589 width=102) (actual time=4.979..4.979 rows=2,725 loops=1)

  • Buffers: shared hit=132, local hit=18 read=30
8. 1.416 4.172 ↓ 1.7 2,725 1

Hash Join (cost=226..361.5 rows=1,589 width=102) (actual time=2.145..4.172 rows=2,725 loops=1)

  • Buffers: shared hit=132, local hit=18 read=30
9. 0.625 0.625 ↑ 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.625 rows=5,645 loops=1)

  • Buffers: local hit=18 read=24
10. 0.176 2.131 ↓ 3.6 502 1

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

  • Buffers: shared hit=132, local read=6
11. 0.173 1.955 ↓ 3.6 502 1

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

  • Buffers: shared hit=132, local read=6
12. 0.387 1.630 ↓ 1.4 534 1

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

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

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

  • Buffers: shared hit=16
14. 0.130 1.092 ↓ 1.4 483 1

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

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

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

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

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

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

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

  • Buffers: shared hit=115, local read=3
18. 0.326 0.610 ↑ 1.0 501 1

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

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

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

  • Buffers: shared hit=28
20. 0.080 0.147 ↑ 1.0 501 1

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

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

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

  • Buffers: local read=3
22. 0.004 0.085 ↑ 1.3 15 1

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

  • Buffers: shared hit=87
23. 0.081 0.081 ↑ 1.3 15 1

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

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

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

  • Buffers: shared hit=1
25. 0.005 0.005 ↑ 1.0 11 1

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

  • Buffers: shared hit=1
26. 0.083 0.152 ↑ 1.0 520 1

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

  • Buffers: local read=3
27. 0.069 0.069 ↑ 1.0 520 1

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

  • Buffers: local read=3
28. 69.944 69.944 ↑ 4.0 1 69,944

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=1 loops=69,944)

  • Index Cond: (xx.company_id = s.redistributor_company_id)
  • Buffers: local hit=179040
29. 60.588 60.588 ↑ 1.0 1 60,588

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=60,588)

  • Index Cond: (cav.contract_renew_version_id = crv.contract_renew_version_id)
  • Buffers: shared hit=181644
30. 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
31. 17.079 36.560 ↑ 1.0 65,698 1

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

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

  • Buffers: local read=1572
Planning time : 28.861 ms
Execution time : 511.152 ms