explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fv3w : vari4

Settings
# exclusive inclusive rows x rows loops node
1. 0.176 1,749.482 ↑ 1.0 100 1

Limit (cost=42,242.52..42,254.02 rows=100 width=242) (actual time=1,730.940..1,749.482 rows=100 loops=1)

  • Buffers: shared hit=44553
2. 23.278 1,749.306 ↑ 279.9 100 1

Gather Merge (cost=42,242.52..45,461.48 rows=27,991 width=242) (actual time=1,730.938..1,749.306 rows=100 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
  • Buffers: shared hit=44553
3. 29.593 1,726.028 ↑ 424.1 66 2 / 2

Sort (cost=41,242.51..41,312.48 rows=27,991 width=242) (actual time=1,725.974..1,726.028 rows=66 loops=2)

  • Sort Key: mo.tracker_code
  • Sort Method: top-N heapsort Memory: 57kB
  • Worker 0: Sort Method: top-N heapsort Memory: 63kB
  • Buffers: shared hit=44553
4. 340.985 1,696.435 ↑ 1.2 23,880 2 / 2

Hash Left Join (cost=36,986.14..40,172.71 rows=27,991 width=242) (actual time=1,254.938..1,696.435 rows=23,880 loops=2)

  • Hash Cond: (mo.id = roiout.manufacturing_order_id)
  • Buffers: shared hit=44545
5. 36.752 296.068 ↑ 1.2 23,880 2 / 2

Hash Join (cost=3,165.71..5,159.16 rows=27,991 width=852) (actual time=195.316..296.068 rows=23,880 loops=2)

  • Hash Cond: (mo.bom_id = bom.id)
  • Buffers: shared hit=4604
6. 43.121 246.455 ↑ 1.2 23,880 2 / 2

Hash Join (cost=2,851.25..4,771.19 rows=27,991 width=852) (actual time=182.395..246.455 rows=23,880 loops=2)

  • Hash Cond: (mo.product_meta_id = pm.id)
  • Buffers: shared hit=4356
7. 21.008 21.008 ↑ 1.2 23,880 2 / 2

Parallel Seq Scan on manufacturing_manufacturingorder mo (cost=0.00..1,535.06 rows=27,991 width=75) (actual time=0.010..21.008 rows=23,880 loops=2)

  • Filter: is_active
  • Rows Removed by Filter: 10890
  • Buffers: shared hit=1126
8. 16.607 182.326 ↑ 1.0 11,879 2 / 2

Hash (cost=2,702.76..2,702.76 rows=11,879 width=785) (actual time=182.325..182.326 rows=11,879 loops=2)

  • Buckets: 16384 Batches: 1 Memory Usage: 10400kB
  • Buffers: shared hit=3230
9. 18.269 165.719 ↑ 1.0 11,879 2 / 2

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

  • Hash Cond: (pm.id = sp.product_meta_id)
  • Buffers: shared hit=3230
10. 18.691 82.038 ↑ 1.0 11,879 2 / 2

Hash Join (cost=1,161.09..1,999.29 rows=11,879 width=777) (actual time=31.052..82.038 rows=11,879 loops=2)

  • Hash Cond: (pm.product_id = p.id)
  • Buffers: shared hit=2894
11. 22.325 52.197 ↑ 1.0 11,879 2 / 2

Hash Join (cost=498.28..1,305.26 rows=11,879 width=39) (actual time=19.866..52.197 rows=11,879 loops=2)

  • Hash Cond: (pms.pm_id = pm.id)
  • Buffers: shared hit=1776
12. 10.085 10.085 ↑ 1.0 11,879 2 / 2

Seq Scan on report_productmetasummary pms (cost=0.00..775.79 rows=11,879 width=31) (actual time=0.012..10.085 rows=11,879 loops=2)

  • Buffers: shared hit=1314
13. 9.282 19.787 ↑ 1.0 11,879 2 / 2

Hash (cost=349.79..349.79 rows=11,879 width=8) (actual time=19.786..19.787 rows=11,879 loops=2)

  • Buckets: 16384 Batches: 1 Memory Usage: 593kB
  • Buffers: shared hit=462
14. 10.505 10.505 ↑ 1.0 11,879 2 / 2

Seq Scan on mainproduct_productmeta pm (cost=0.00..349.79 rows=11,879 width=8) (actual time=0.008..10.505 rows=11,879 loops=2)

  • Buffers: shared hit=462
15. 5.420 11.150 ↑ 1.0 4,614 2 / 2

Hash (cost=605.14..605.14 rows=4,614 width=742) (actual time=11.149..11.150 rows=4,614 loops=2)

  • Buckets: 8192 Batches: 1 Memory Usage: 3554kB
  • Buffers: shared hit=1118
16. 5.730 5.730 ↑ 1.0 4,614 2 / 2

Seq Scan on mainproduct_product p (cost=0.00..605.14 rows=4,614 width=742) (actual time=0.017..5.730 rows=4,614 loops=2)

  • Buffers: shared hit=1118
17. 3.284 65.412 ↑ 1.0 4,116 2 / 2

Hash (cost=620.83..620.83 rows=4,116 width=12) (actual time=65.411..65.412 rows=4,116 loops=2)

  • Buckets: 8192 Batches: 1 Memory Usage: 241kB
  • Buffers: shared hit=336
18. 5.587 62.128 ↑ 1.0 4,116 2 / 2

Subquery Scan on sp (cost=538.51..620.83 rows=4,116 width=12) (actual time=53.308..62.128 rows=4,116 loops=2)

  • Buffers: shared hit=336
19. 16.046 56.541 ↑ 1.0 4,116 2 / 2

HashAggregate (cost=538.51..579.67 rows=4,116 width=12) (actual time=53.305..56.541 rows=4,116 loops=2)

  • Group Key: sp_1.product_meta_id
  • Buffers: shared hit=336
20. 22.957 40.495 ↑ 1.0 15,229 2 / 2

Hash Join (cost=173.05..462.36 rows=15,229 width=8) (actual time=6.756..40.495 rows=15,229 loops=2)

  • Hash Cond: (sp_1.shelf_id = s.id)
  • Buffers: shared hit=336
21. 10.819 10.819 ↑ 1.0 15,229 2 / 2

Seq Scan on shelf_shelvedproducts sp_1 (cost=0.00..249.29 rows=15,229 width=12) (actual time=0.017..10.819 rows=15,229 loops=2)

  • Buffers: shared hit=194
22. 3.092 6.719 ↑ 1.0 4,082 2 / 2

Hash (cost=122.03..122.03 rows=4,082 width=4) (actual time=6.718..6.719 rows=4,082 loops=2)

  • Buckets: 4096 Batches: 1 Memory Usage: 176kB
  • Buffers: shared hit=142
23. 3.627 3.627 ↑ 1.0 4,082 2 / 2

Seq Scan on shelf_shelf s (cost=0.00..122.03 rows=4,082 width=4) (actual time=0.013..3.627 rows=4,082 loops=2)

  • Filter: (type = 1)
  • Buffers: shared hit=142
24. 6.371 12.861 ↑ 1.0 8,465 2 / 2

Hash (cost=208.65..208.65 rows=8,465 width=4) (actual time=12.860..12.861 rows=8,465 loops=2)

  • Buckets: 16384 Batches: 1 Memory Usage: 426kB
  • Buffers: shared hit=248
25. 6.490 6.490 ↑ 1.0 8,465 2 / 2

Seq Scan on manufacturing_bom bom (cost=0.00..208.65 rows=8,465 width=4) (actual time=0.014..6.490 rows=8,465 loops=2)

  • Buffers: shared hit=248
26. 53.364 1,059.382 ↓ 1.0 63,164 2 / 2

Hash (cost=33,051.83..33,051.83 rows=61,488 width=36) (actual time=1,059.381..1,059.382 rows=63,164 loops=2)

  • Buckets: 65536 Batches: 1 Memory Usage: 3386kB
  • Buffers: shared hit=39941
27. 85.109 1,006.018 ↓ 1.0 63,164 2 / 2

Subquery Scan on roiout (cost=30,694.29..33,051.83 rows=61,488 width=36) (actual time=746.707..1,006.018 rows=63,164 loops=2)

  • Buffers: shared hit=39941
28. 127.691 920.909 ↓ 1.0 63,164 2 / 2

GroupAggregate (cost=30,694.29..32,436.95 rows=61,488 width=36) (actual time=746.704..920.909 rows=63,164 loops=2)

  • Group Key: roi.manufacturing_order_id
  • Buffers: shared hit=39941
29. 107.142 793.218 ↑ 1.0 65,627 2 / 2

Sort (cost=30,694.29..30,858.36 rows=65,627 width=21) (actual time=746.683..793.218 rows=65,627 loops=2)

  • Sort Key: roi.manufacturing_order_id
  • Sort Method: quicksort Memory: 6391kB
  • Worker 0: Sort Method: quicksort Memory: 6391kB
  • Buffers: shared hit=39941
30. 94.862 686.076 ↑ 1.0 65,627 2 / 2

Hash Join (cost=23,895.63..25,443.48 rows=65,627 width=21) (actual time=435.871..686.076 rows=65,627 loops=2)

  • Hash Cond: (bi.order_id = o.id)
  • Buffers: shared hit=39934
31. 109.161 546.584 ↑ 1.0 65,627 2 / 2

Hash Join (cost=21,414.44..22,789.98 rows=65,627 width=8) (actual time=391.135..546.584 rows=65,627 loops=2)

  • Hash Cond: (roi.item_id = bi.id)
  • Buffers: shared hit=35834
32. 47.083 47.083 ↑ 1.0 65,627 2 / 2

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

  • Buffers: shared hit=1094
33. 154.528 390.340 ↑ 1.0 179,753 2 / 2

Hash (cost=19,167.53..19,167.53 rows=179,753 width=8) (actual time=390.339..390.340 rows=179,753 loops=2)

  • Buckets: 262144 Batches: 1 Memory Usage: 9070kB
  • Buffers: shared hit=34740
34. 235.812 235.812 ↑ 1.0 179,753 2 / 2

Seq Scan on order_basketitem bi (cost=0.00..19,167.53 rows=179,753 width=8) (actual time=0.012..235.812 rows=179,753 loops=2)

  • Buffers: shared hit=34740
35. 16.690 44.630 ↑ 1.0 19,164 2 / 2

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1100kB
  • Buffers: shared hit=4100
36. 27.940 27.940 ↑ 1.0 19,164 2 / 2

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

  • Buffers: shared hit=4100
Planning time : 4.912 ms
Execution time : 1,749.953 ms