explain.depesz.com

PostgreSQL's explain analyze made readable

Result: X5so

Settings
# exclusive inclusive rows x rows loops node
1. 50.646 755.528 ↓ 21,375.0 42,750 1

Hash Join (cost=3,648.1..5,717.61 rows=2 width=198) (actual time=41.12..755.528 rows=42,750 loops=1)

  • Buffers: shared hit=223550 read=1843, local hit=179417 read=2027
2. 55.912 669.353 ↓ 21,375.0 42,750 1

Nested Loop (cost=433.65..2,503.12 rows=2 width=152) (actual time=5.406..669.353 rows=42,750 loops=1)

  • Buffers: shared hit=223550 read=1843, local hit=178618 read=1254
3. 22.967 250.711 ↓ 6,045.5 60,455 1

Hash Join (cost=433.23..2,476.52 rows=10 width=156) (actual time=5.081..250.711 rows=60,455 loops=1)

  • Buffers: shared hit=170, local hit=178618 read=1254
4. 55.794 227.732 ↓ 6,045.5 60,455 1

Nested Loop (cost=431.99..2,475.24 rows=10 width=156) (actual time=5.058..227.732 rows=60,455 loops=1)

  • Buffers: shared hit=169, local hit=178618 read=1254
5. 76.365 102.127 ↓ 4,986.5 69,811 1

Hash Join (cost=431.7..2,469.57 rows=14 width=172) (actual time=5.04..102.127 rows=69,811 loops=1)

  • Buffers: shared hit=169, local hit=2 read=1231
6. 20.770 20.770 ↑ 1.0 41,069 1

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

  • Filter: (s.redistributor_company_id IS NOT NULL)
  • Buffers: local read=1185
7. 0.686 4.992 ↓ 6.1 2,697 1

Hash (cost=426.18..426.18 rows=442 width=86) (actual time=4.992..4.992 rows=2,697 loops=1)

  • Buffers: shared hit=169, local hit=2 read=46
8. 1.213 4.306 ↓ 6.1 2,697 1

Hash Join (cost=302.14..426.18 rows=442 width=86) (actual time=2.507..4.306 rows=2,697 loops=1)

  • Buffers: shared hit=169, local hit=2 read=46
9. 0.604 0.604 ↑ 1.0 5,645 1

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

  • Buffers: local hit=2 read=40
10. 0.128 2.489 ↓ 4.9 485 1

Hash (cost=300.9..300.9 rows=99 width=102) (actual time=2.489..2.489 rows=485 loops=1)

  • Buffers: shared hit=169, local read=6
11. 0.148 2.361 ↓ 4.9 485 1

Hash Join (cost=227.05..300.9 rows=99 width=102) (actual time=1.707..2.361 rows=485 loops=1)

  • Buffers: shared hit=169, local read=6
12. 0.201 0.857 ↓ 1.4 502 1

Hash Join (cost=21.2..92.93 rows=366 width=48) (actual time=0.347..0.857 rows=502 loops=1)

  • Buffers: shared hit=38, local read=3
13. 0.322 0.322 ↑ 1.0 1,274 1

Seq Scan on contract_amend_version cav (cost=0..63.23 rows=1,291 width=32) (actual time=0.008..0.322 rows=1,274 loops=1)

  • Filter: (cav.contract_state = ANY ('{APPROVED,ACTIVE,EXPIRED}'::enum.contract_state[]))
  • Buffers: shared hit=38
14. 0.078 0.334 ↑ 1.0 520 1

Hash (cost=14.7..14.7 rows=520 width=16) (actual time=0.334..0.334 rows=520 loops=1)

  • Buffers: local read=3
15. 0.183 0.256 ↑ 1.0 520 1

HashAggregate (cost=9.5..14.7 rows=520 width=16) (actual time=0.183..0.256 rows=520 loops=1)

  • Group Key: xcrv.contract_renew_version_id
  • Buffers: local read=3
16. 0.073 0.073 ↑ 1.0 520 1

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

  • Buffers: local read=3
17. 0.126 1.356 ↓ 1.4 534 1

Hash (cost=201.14..201.14 rows=377 width=102) (actual time=1.356..1.356 rows=534 loops=1)

  • Buffers: shared hit=131, local read=3
18. 0.296 1.230 ↓ 1.4 534 1

Hash Join (cost=162.22..201.14 rows=377 width=102) (actual time=0.849..1.23 rows=534 loops=1)

  • Buffers: shared hit=131, local read=3
19. 0.100 0.100 ↑ 1.0 1,394 1

Seq Scan on contract_renew_version crv (cost=0..29.94 rows=1,394 width=46) (actual time=0.004..0.1 rows=1,394 loops=1)

  • Buffers: shared hit=16
20. 0.095 0.834 ↓ 1.4 483 1

Hash (cost=157.94..157.94 rows=342 width=56) (actual time=0.834..0.834 rows=483 loops=1)

  • Buffers: shared hit=115, local read=3
21. 0.100 0.739 ↓ 1.4 483 1

Hash Join (cost=102.26..157.94 rows=342 width=56) (actual time=0.252..0.739 rows=483 loops=1)

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

Hash Join (cost=14.27..63.82 rows=501 width=56) (actual time=0.166..0.557 rows=501 loops=1)

  • Buffers: shared hit=28, local read=3
23. 0.117 0.117 ↓ 1.0 1,269 1

Seq Scan on contract_family cf (cost=0..40.65 rows=1,265 width=40) (actual time=0.003..0.117 rows=1,269 loops=1)

  • Buffers: shared hit=28
24. 0.080 0.157 ↑ 1.0 501 1

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

  • Buffers: local read=3
25. 0.077 0.077 ↑ 1.0 501 1

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

  • Buffers: local read=3
26. 0.004 0.082 ↑ 1.3 15 1

Hash (cost=87.75..87.75 rows=19 width=16) (actual time=0.082..0.082 rows=15 loops=1)

  • Buffers: shared hit=87
27. 0.078 0.078 ↑ 1.3 15 1

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

  • Filter: (gc.grouping_company_type = 'BUYING_GROUP'::enum.grouping_company_type)
  • Buffers: shared hit=87
28. 69.811 69.811 ↑ 4.0 1 69,811

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,811)

  • Index Cond: (xx.company_id = s.redistributor_company_id)
  • Buffers: local hit=178616 read=23
29. 0.004 0.012 ↑ 1.0 11 1

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

  • Buffers: shared hit=1
30. 0.008 0.008 ↑ 1.0 11 1

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

  • Buffers: shared hit=1
31. 362.730 362.730 ↑ 1.0 1 60,455

Index Scan using contract_product_materialized_pkey on contract_product_materialized contract_product_materialized (cost=0.42..2.64 rows=1 width=52) (actual time=0.006..0.006 rows=1 loops=60,455)

  • Index Cond: ((contract_product_materialized.contract_id = cav.contract_amend_version_id) AND (contract_product_materialized.product_id = s.product_id))
  • Buffers: shared hit=223380 read=1843
32. 17.281 35.529 ↑ 1.0 65,698 1

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

  • Buffers: local hit=799 read=773
33. 18.248 18.248 ↑ 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.009..18.248 rows=65,698 loops=1)

  • Buffers: local hit=799 read=773
Planning time : 81.114 ms
Execution time : 760.618 ms