explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YRV1

Settings
# exclusive inclusive rows x rows loops node
1. 8.648 12,515.404 ↑ 28.8 25,847 1

Hash Join (cost=81,732.42..199,873.93 rows=745,189 width=205) (actual time=2,044.081..12,515.404 rows=25,847 loops=1)

  • Buffers: shared hit=7004622 read=130, local hit=336 read=37913 written=1018, temp read=150114 written=150020
2. 167.872 12,506.336 ↑ 15.8 25,847 1

Hash Join (cost=81,696.53..190,481.58 rows=408,178 width=205) (actual time=2,043.644..12,506.336 rows=25,847 loops=1)

  • Buffers: shared hit=7004622 read=130, local hit=336 read=37906 written=1011, temp read=150114 written=150020
3. 5,970.575 11,718.684 ↑ 26.2 25,847 1

Hash Join (cost=24,930.76..49,668.57 rows=677,999 width=205) (actual time=1,422.154..11,718.684 rows=25,847 loops=1)

  • Buffers: shared hit=7004622 read=130, local hit=336 read=13925 written=1011, temp read=143245 written=143181
4. 1,796.354 4,701.723 ↓ 74.7 8,117,324 1

Nested Loop (cost=1,533.77..22,605.9 rows=108,612 width=98) (actual time=92.197..4,701.723 rows=8,117,324 loops=1)

  • Buffers: shared hit=7004520 read=130, local hit=2 read=347 written=347, temp read=567 written=565
5. 32.316 126.775 ↓ 49.4 71,246 1

Hash Join (cost=1,533.35..1,925.43 rows=1,442 width=78) (actual time=92.172..126.775 rows=71,246 loops=1)

  • Buffers: shared hit=391, local hit=2 read=347 written=347, temp read=567 written=565
6. 2.666 2.666 ↑ 1.1 5,338 1

Seq Scan on contract_amend_version cav (cost=0..361.72 rows=5,823 width=32) (actual time=0.01..2.666 rows=5,338 loops=1)

  • Filter: (cav.contract_state = ANY ('{APPROVED,ACTIVE,EXPIRED}'::enum.contract_state[]))
  • Buffers: shared hit=227
7. 25.362 91.793 ↓ 49.7 71,246 1

Hash (cost=1,515.44..1,515.44 rows=1,433 width=94) (actual time=91.793..91.793 rows=71,246 loops=1)

  • Buffers: shared hit=164, local hit=2 read=347 written=347, temp written=527
8. 16.439 66.431 ↓ 49.7 71,246 1

Hash Join (cost=1,192.11..1,515.44 rows=1,433 width=94) (actual time=47.564..66.431 rows=71,246 loops=1)

  • Buffers: shared hit=164, local hit=2 read=347 written=347
9. 1.872 3.225 ↑ 1.0 2,656 1

Hash Join (cost=74.76..344.9 rows=2,656 width=62) (actual time=0.749..3.225 rows=2,656 loops=1)

  • Buffers: shared hit=164, local read=15 written=15
10. 0.629 0.629 ↓ 1.0 5,845 1

Seq Scan on contract_renew_version crv (cost=0..221.88 rows=5,788 width=46) (actual time=0.003..0.629 rows=5,845 loops=1)

  • Buffers: shared hit=164
11. 0.389 0.724 ↑ 1.0 2,656 1

Hash (cost=41.56..41.56 rows=2,656 width=16) (actual time=0.724..0.724 rows=2,656 loops=1)

  • Buffers: local read=15 written=15
12. 0.335 0.335 ↑ 1.0 2,656 1

Seq Scan on _contract_renew_version crx (cost=0..41.56 rows=2,656 width=16) (actual time=0.015..0.335 rows=2,656 loops=1)

  • Buffers: local read=15 written=15
13. 9.908 46.767 ↓ 10.0 45,412 1

Hash (cost=1,060.59..1,060.59 rows=4,541 width=32) (actual time=46.767..46.767 rows=45,412 loops=1)

  • Buffers: local hit=2 read=332 written=332
14. 29.759 36.859 ↓ 10.0 45,412 1

HashAggregate (cost=1,015.18..1,060.59 rows=4,541 width=32) (actual time=25.477..36.859 rows=45,412 loops=1)

  • Group Key: cagg.contract_family_id, cagg.company_id
  • Buffers: local hit=2 read=332 written=332
15. 7.100 7.100 ↑ 1.0 45,412 1

Seq Scan on _contractee_agg cagg (cost=0..788.12 rows=45,412 width=32) (actual time=0.017..7.1 rows=45,412 loops=1)

  • Buffers: local hit=2 read=332 written=332
16. 2,778.594 2,778.594 ↑ 1.5 114 71,246

Index Scan using contract_product_materialized_pkey on contract_product_materialized contract_product_materialized (cost=0.42..12.64 rows=170 width=52) (actual time=0.002..0.039 rows=114 loops=71,246)

  • Index Cond: (contract_product_materialized.contract_id = cav.contract_amend_version_id)
  • Buffers: shared hit=7004129 read=130
17. 555.268 1,046.386 ↓ 57.3 892,779 1

Hash (cost=23,124.3..23,124.3 rows=15,582 width=161) (actual time=1,046.386..1,046.386 rows=892,779 loops=1)

  • Buffers: shared hit=102, local hit=334 read=13578 written=664, temp written=20026
18. 250.858 491.118 ↓ 57.3 892,779 1

Hash Join (cost=1,287.73..23,124.3 rows=15,582 width=161) (actual time=60.686..491.118 rows=892,779 loops=1)

  • Buffers: shared hit=102, local hit=334 read=13578 written=664
19. 179.618 179.618 ↑ 1.0 205,040 1

Seq Scan on _sales_raw s (cost=0..20,649.4 rows=206,273 width=121) (actual time=0.031..179.618 rows=205,040 loops=1)

  • Filter: (s.redistributor_company_id IS NOT NULL)
  • Buffers: local read=13578 written=664
20. 9.543 60.642 ↓ 32.0 38,322 1

Hash (cost=1,272.77..1,272.77 rows=1,197 width=56) (actual time=60.642..60.642 rows=38,322 loops=1)

  • Buffers: shared hit=102, local hit=334
21. 7.993 51.099 ↓ 32.0 38,322 1

Hash Join (cost=1,171.67..1,272.77 rows=1,197 width=56) (actual time=21.278..51.099 rows=38,322 loops=1)

  • Buffers: shared hit=102, local hit=334
22. 11.642 43.019 ↓ 35.4 45,412 1

Hash Join (cost=1,117.9..1,202.92 rows=1,284 width=72) (actual time=21.17..43.019 rows=45,412 loops=1)

  • Buffers: shared hit=50, local hit=334
23. 26.024 30.506 ↓ 10.0 45,412 1

HashAggregate (cost=1,015.18..1,060.59 rows=4,541 width=32) (actual time=20.285..30.506 rows=45,412 loops=1)

  • Group Key: xx.contract_family_id, xx.company_id
  • Buffers: local hit=334
24. 4.482 4.482 ↑ 1.0 45,412 1

Seq Scan on _contractee_agg xx (cost=0..788.12 rows=45,412 width=32) (actual time=0.008..4.482 rows=45,412 loops=1)

  • Buffers: local hit=334
25. 0.391 0.871 ↓ 1.0 2,411 1

Hash (cost=73.43..73.43 rows=2,343 width=40) (actual time=0.871..0.871 rows=2,411 loops=1)

  • Buffers: shared hit=50
26. 0.480 0.480 ↓ 1.0 2,411 1

Seq Scan on contract_family cf (cost=0..73.43 rows=2,343 width=40) (actual time=0.012..0.48 rows=2,411 loops=1)

  • Buffers: shared hit=50
27. 0.005 0.087 ↑ 1.0 9 1

Hash (cost=53.66..53.66 rows=9 width=16) (actual time=0.087..0.087 rows=9 loops=1)

  • Buffers: shared hit=52
28. 0.082 0.082 ↑ 1.0 9 1

Seq Scan on grouping_company gc (cost=0..53.66 rows=9 width=16) (actual time=0.018..0.082 rows=9 loops=1)

  • Filter: (gc.grouping_company_type = 'BUYING_GROUP'::enum.grouping_company_type)
  • Buffers: shared hit=52
29. 284.735 619.780 ↑ 1.0 1,029,791 1

Hash (cost=34,278.91..34,278.91 rows=1,029,791 width=32) (actual time=619.78..619.78 rows=1,029,791 loops=1)

  • Buffers: local read=23981, temp written=6121
30. 335.045 335.045 ↑ 1.0 1,029,791 1

Seq Scan on _sales_potential_earnings x (cost=0..34,278.91 rows=1,029,791 width=32) (actual time=0.026..335.045 rows=1,029,791 loops=1)

  • Buffers: local read=23981
31. 0.221 0.420 ↑ 1.0 1,284 1

Hash (cost=19.84..19.84 rows=1,284 width=16) (actual time=0.42..0.42 rows=1,284 loops=1)

  • Buffers: local read=7 written=7
32. 0.199 0.199 ↑ 1.0 1,284 1

Seq Scan on _contract_family xcf (cost=0..19.84 rows=1,284 width=16) (actual time=0.045..0.199 rows=1,284 loops=1)

  • Buffers: local read=7 written=7
Planning time : 86.448 ms
Execution time : 12,517.915 ms