explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 39.223 504.482 ↓ 21,380.5 42,761 1

Hash Join (cost=3,557.69..6,317.19 rows=2 width=198) (actual time=40.855..504.482 rows=42,761 loops=1)

  • Buffers: shared hit=407049, local hit=182697 read=2791
2. 37.982 429.838 ↓ 21,380.5 42,761 1

Nested Loop (cost=343.24..3,102.7 rows=2 width=152) (actual time=5.244..429.838 rows=42,761 loops=1)

  • Buffers: shared hit=407049, local hit=182697 read=1219
3. 35.479 270.920 ↓ 6,046.8 60,468 1

Nested Loop (cost=342.83..3,076.02 rows=10 width=156) (actual time=5.225..270.92 rows=60,468 loops=1)

  • Buffers: shared hit=181776, local hit=182697 read=1219
4. 26.026 174.853 ↓ 5,508.0 60,588 1

Nested Loop (cost=342.55..3,072.2 rows=11 width=172) (actual time=5.214..174.853 rows=60,588 loops=1)

  • Buffers: shared hit=132, local hit=182697 read=1219
5. 57.219 78.883 ↓ 1,345.1 69,944 1

Hash Join (cost=342.27..3,051.14 rows=52 width=188) (actual time=5.188..78.883 rows=69,944 loops=1)

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

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

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

  • Buffers: shared hit=132, local hit=3664 read=27
8. 0.930 4.253 ↓ 1.7 2,725 1

Nested Loop (cost=182.95..322.4 rows=1,589 width=102) (actual time=1.112..4.253 rows=2,725 loops=1)

  • Buffers: shared hit=132, local hit=3664 read=27
9. 0.162 1.817 ↓ 3.6 502 1

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

  • Buffers: shared hit=132, local read=6
10. 0.472 1.522 ↓ 1.4 534 1

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

  • Buffers: shared hit=132, local read=3
11. 0.128 0.128 ↓ 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.128 rows=1,398 loops=1)

  • Buffers: shared hit=16
12. 0.086 0.922 ↓ 1.4 483 1

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

  • Buffers: shared hit=116, local read=3
13. 0.073 0.836 ↓ 1.4 483 1

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

  • Buffers: shared hit=116, local read=3
14. 0.076 0.763 ↓ 1.4 483 1

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

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

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

  • Buffers: shared hit=115, local read=3
16. 0.283 0.523 ↑ 1.0 501 1

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

  • Buffers: shared hit=28, local read=3
17. 0.115 0.115 ↓ 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.115 rows=1,273 loops=1)

  • Buffers: shared hit=28
18. 0.066 0.125 ↑ 1.0 501 1

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

  • Buffers: local read=3
19. 0.059 0.059 ↑ 1.0 501 1

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

  • Buffers: local read=3
20. 0.004 0.078 ↑ 1.3 15 1

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

  • Buffers: shared hit=87
21. 0.074 0.074 ↑ 1.3 15 1

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

  • Filter: (gc.grouping_company_type = 'BUYING_GROUP'::enum.grouping_company_type)
  • Buffers: shared hit=87
22. 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
23. 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
24. 0.070 0.133 ↑ 1.0 520 1

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

  • Buffers: local read=3
25. 0.063 0.063 ↑ 1.0 520 1

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

  • Buffers: local read=3
26. 1.506 1.506 ↑ 2.2 5 502

Index Only Scan using idx_contractee_agg_test on _contractee_agg cagg (cost=0.28..0.56 rows=11 width=32) (actual time=0.002..0.003 rows=5 loops=502)

  • Index Cond: (cagg.contract_family_id = crv.contract_family_id)
  • Heap Fetches: 2725
  • Buffers: local hit=3664 read=21
27. 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=179033 read=7
28. 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
29. 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
30. 16.298 35.421 ↑ 1.0 65,698 1

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

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

  • Buffers: local read=1572
Planning time : 27.033 ms
Execution time : 508.342 ms