explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PHWc : vari4

Settings
# exclusive inclusive rows x rows loops node
1. 650.249 1,925.982 ↓ 1.0 47,761 1

Hash Join (cost=36,276.23..41,477.85 rows=47,585 width=242) (actual time=936.029..1,925.982 rows=47,761 loops=1)

  • Hash Cond: (mo.bom_id = bom.id)
  • Buffers: shared hit=22832
2. 78.301 1,263.808 ↓ 1.0 47,761 1

Hash Join (cost=35,961.77..39,135.01 rows=47,585 width=884) (actual time=924.048..1,263.808 rows=47,761 loops=1)

  • Hash Cond: (mo.product_meta_id = pm.id)
  • Buffers: shared hit=22708
3. 93.820 1,023.123 ↓ 1.0 47,761 1

Hash Right Join (cost=33,110.52..35,629.47 rows=47,585 width=107) (actual time=761.638..1,023.123 rows=47,761 loops=1)

  • Hash Cond: (roi.manufacturing_order_id = mo.id)
  • Buffers: shared hit=21093
4. 124.010 846.325 ↓ 1.0 63,164 1

GroupAggregate (cost=30,694.29..32,436.95 rows=61,488 width=36) (actual time=678.566..846.325 rows=63,164 loops=1)

  • Group Key: roi.manufacturing_order_id
  • Buffers: shared hit=19967
5. 101.305 722.315 ↑ 1.0 65,627 1

Sort (cost=30,694.29..30,858.36 rows=65,627 width=21) (actual time=678.546..722.315 rows=65,627 loops=1)

  • Sort Key: roi.manufacturing_order_id
  • Sort Method: quicksort Memory: 6391kB
  • Buffers: shared hit=19967
6. 87.324 621.010 ↑ 1.0 65,627 1

Hash Join (cost=23,895.63..25,443.48 rows=65,627 width=21) (actual time=390.478..621.010 rows=65,627 loops=1)

  • Hash Cond: (bi.order_id = o.id)
  • Buffers: shared hit=19967
7. 99.962 495.593 ↑ 1.0 65,627 1

Hash Join (cost=21,414.44..22,789.98 rows=65,627 width=8) (actual time=352.337..495.593 rows=65,627 loops=1)

  • Hash Cond: (roi.item_id = bi.id)
  • Buffers: shared hit=17917
8. 43.623 43.623 ↑ 1.0 65,627 1

Seq Scan on manufacturing_referenceorderitem roi (cost=0.00..1,203.27 rows=65,627 width=8) (actual time=0.011..43.623 rows=65,627 loops=1)

  • Buffers: shared hit=547
9. 137.667 352.008 ↑ 1.0 179,753 1

Hash (cost=19,167.53..19,167.53 rows=179,753 width=8) (actual time=352.007..352.008 rows=179,753 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 9070kB
  • Buffers: shared hit=17370
10. 214.341 214.341 ↑ 1.0 179,753 1

Seq Scan on order_basketitem bi (cost=0.00..19,167.53 rows=179,753 width=8) (actual time=0.007..214.341 rows=179,753 loops=1)

  • Buffers: shared hit=17370
11. 14.484 38.093 ↑ 1.0 19,164 1

Hash (cost=2,241.64..2,241.64 rows=19,164 width=21) (actual time=38.093..38.093 rows=19,164 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1100kB
  • Buffers: shared hit=2050
12. 23.609 23.609 ↑ 1.0 19,164 1

Seq Scan on order_order o (cost=0.00..2,241.64 rows=19,164 width=21) (actual time=0.011..23.609 rows=19,164 loops=1)

  • Buffers: shared hit=2050
13. 39.431 82.978 ↓ 1.0 47,761 1

Hash (cost=1,821.41..1,821.41 rows=47,585 width=75) (actual time=82.977..82.978 rows=47,761 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 4977kB
  • Buffers: shared hit=1126
14. 43.547 43.547 ↓ 1.0 47,761 1

Seq Scan on manufacturing_manufacturingorder mo (cost=0.00..1,821.41 rows=47,585 width=75) (actual time=0.013..43.547 rows=47,761 loops=1)

  • Filter: is_active
  • Rows Removed by Filter: 21780
  • Buffers: shared hit=1126
15. 13.255 162.384 ↑ 1.0 11,879 1

Hash (cost=2,702.76..2,702.76 rows=11,879 width=785) (actual time=162.383..162.384 rows=11,879 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 10400kB
  • Buffers: shared hit=1615
16. 16.189 149.129 ↑ 1.0 11,879 1

Hash Left Join (cost=1,833.37..2,702.76 rows=11,879 width=785) (actual time=87.229..149.129 rows=11,879 loops=1)

  • Hash Cond: (pm.id = sp.product_meta_id)
  • Buffers: shared hit=1615
17. 16.768 72.977 ↑ 1.0 11,879 1

Hash Join (cost=1,161.09..1,999.29 rows=11,879 width=777) (actual time=27.250..72.977 rows=11,879 loops=1)

  • Hash Cond: (pm.product_id = p.id)
  • Buffers: shared hit=1447
18. 20.231 47.134 ↑ 1.0 11,879 1

Hash Join (cost=498.28..1,305.26 rows=11,879 width=39) (actual time=18.159..47.134 rows=11,879 loops=1)

  • Hash Cond: (pms.pm_id = pm.id)
  • Buffers: shared hit=888
19. 8.777 8.777 ↑ 1.0 11,879 1

Seq Scan on report_productmetasummary pms (cost=0.00..775.79 rows=11,879 width=31) (actual time=0.006..8.777 rows=11,879 loops=1)

  • Buffers: shared hit=657
20. 8.427 18.126 ↑ 1.0 11,879 1

Hash (cost=349.79..349.79 rows=11,879 width=8) (actual time=18.125..18.126 rows=11,879 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 593kB
  • Buffers: shared hit=231
21. 9.699 9.699 ↑ 1.0 11,879 1

Seq Scan on mainproduct_productmeta pm (cost=0.00..349.79 rows=11,879 width=8) (actual time=0.007..9.699 rows=11,879 loops=1)

  • Buffers: shared hit=231
22. 4.318 9.075 ↑ 1.0 4,614 1

Hash (cost=605.14..605.14 rows=4,614 width=742) (actual time=9.075..9.075 rows=4,614 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 3554kB
  • Buffers: shared hit=559
23. 4.757 4.757 ↑ 1.0 4,614 1

Seq Scan on mainproduct_product p (cost=0.00..605.14 rows=4,614 width=742) (actual time=0.008..4.757 rows=4,614 loops=1)

  • Buffers: shared hit=559
24. 2.982 59.963 ↑ 1.0 4,116 1

Hash (cost=620.83..620.83 rows=4,116 width=12) (actual time=59.963..59.963 rows=4,116 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 241kB
  • Buffers: shared hit=168
25. 5.072 56.981 ↑ 1.0 4,116 1

Subquery Scan on sp (cost=538.51..620.83 rows=4,116 width=12) (actual time=48.920..56.981 rows=4,116 loops=1)

  • Buffers: shared hit=168
26. 14.840 51.909 ↑ 1.0 4,116 1

HashAggregate (cost=538.51..579.67 rows=4,116 width=12) (actual time=48.917..51.909 rows=4,116 loops=1)

  • Group Key: sp_1.product_meta_id
  • Buffers: shared hit=168
27. 20.888 37.069 ↑ 1.0 15,229 1

Hash Join (cost=173.05..462.36 rows=15,229 width=8) (actual time=6.170..37.069 rows=15,229 loops=1)

  • Hash Cond: (sp_1.shelf_id = s.id)
  • Buffers: shared hit=168
28. 10.030 10.030 ↑ 1.0 15,229 1

Seq Scan on shelf_shelvedproducts sp_1 (cost=0.00..249.29 rows=15,229 width=12) (actual time=0.008..10.030 rows=15,229 loops=1)

  • Buffers: shared hit=97
29. 2.823 6.151 ↑ 1.0 4,082 1

Hash (cost=122.03..122.03 rows=4,082 width=4) (actual time=6.151..6.151 rows=4,082 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 176kB
  • Buffers: shared hit=71
30. 3.328 3.328 ↑ 1.0 4,082 1

Seq Scan on shelf_shelf s (cost=0.00..122.03 rows=4,082 width=4) (actual time=0.007..3.328 rows=4,082 loops=1)

  • Filter: (type = 1)
  • Buffers: shared hit=71
31. 5.935 11.925 ↑ 1.0 8,465 1

Hash (cost=208.65..208.65 rows=8,465 width=4) (actual time=11.925..11.925 rows=8,465 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 426kB
  • Buffers: shared hit=124
32. 5.990 5.990 ↑ 1.0 8,465 1

Seq Scan on manufacturing_bom bom (cost=0.00..208.65 rows=8,465 width=4) (actual time=0.009..5.990 rows=8,465 loops=1)

  • Buffers: shared hit=124
Planning time : 2.885 ms
Execution time : 1,957.276 ms