explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8tvl : var5

Settings
# exclusive inclusive rows x rows loops node
1. 0.214 1,713.401 ↑ 1.0 100 1

Limit (cost=42,873.13..42,884.63 rows=100 width=242) (actual time=1,694.700..1,713.401 rows=100 loops=1)

  • Buffers: shared hit=45189
2. 23.334 1,713.187 ↑ 287.0 100 1

Gather Merge (cost=42,873.13..46,173.17 rows=28,696 width=242) (actual time=1,694.698..1,713.187 rows=100 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
  • Buffers: shared hit=45189
3. 28.041 1,689.853 ↑ 428.3 67 2 / 2

Sort (cost=41,873.12..41,944.86 rows=28,696 width=242) (actual time=1,689.809..1,689.853 rows=67 loops=2)

  • Sort Key: mo.tracker_code
  • Sort Method: top-N heapsort Memory: 57kB
  • Worker 0: Sort Method: top-N heapsort Memory: 66kB
  • Buffers: shared hit=45189
4. 327.679 1,661.812 ↑ 1.2 23,210 2 / 2

Hash Join (cost=37,514.89..40,776.38 rows=28,696 width=242) (actual time=1,239.937..1,661.812 rows=23,210 loops=2)

  • Hash Cond: (mo.product_meta_id = pm.id)
  • Buffers: shared hit=45181
5. 37.572 1,151.390 ↑ 1.2 23,210 2 / 2

Hash Left Join (cost=34,608.76..36,328.15 rows=28,612 width=107) (actual time=1,057.071..1,151.390 rows=23,210 loops=2)

  • Hash Cond: (mo.id = roi.manufacturing_order_id)
  • Buffers: shared hit=41863
6. 36.191 69.924 ↑ 1.2 23,210 2 / 2

Hash Join (cost=314.46..1,958.75 rows=28,612 width=75) (actual time=12.856..69.924 rows=23,210 loops=2)

  • Hash Cond: (mo.bom_id = bom.id)
  • Buffers: shared hit=1399
7. 20.994 20.994 ↑ 1.2 23,210 2 / 2

Parallel Seq Scan on manufacturing_manufacturingorder mo (cost=0.00..1,569.15 rows=28,612 width=75) (actual time=0.018..20.994 rows=23,210 loops=2)

  • Filter: is_active
  • Rows Removed by Filter: 11572
  • Buffers: shared hit=1151
8. 6.342 12.739 ↑ 1.0 8,465 2 / 2

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

  • Buckets: 16384 Batches: 1 Memory Usage: 426kB
  • Buffers: shared hit=248
9. 6.397 6.397 ↑ 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.021..6.397 rows=8,465 loops=2)

  • Buffers: shared hit=248
10. 51.014 1,043.894 ↓ 1.0 63,187 2 / 2

Hash (cost=33,510.23..33,510.23 rows=62,725 width=36) (actual time=1,043.893..1,043.894 rows=63,187 loops=2)

  • Buckets: 65536 Batches: 1 Memory Usage: 3387kB
  • Buffers: shared hit=40464
11. 84.922 992.880 ↓ 1.0 63,187 2 / 2

Subquery Scan on roi (cost=31,105.27..33,510.23 rows=62,725 width=36) (actual time=736.097..992.880 rows=63,187 loops=2)

  • Buffers: shared hit=40464
12. 125.845 907.958 ↓ 1.0 63,187 2 / 2

GroupAggregate (cost=31,105.27..32,882.98 rows=62,725 width=36) (actual time=736.094..907.958 rows=63,187 loops=2)

  • Group Key: roi_1.manufacturing_order_id
  • Buffers: shared hit=40464
13. 105.090 782.113 ↑ 1.0 65,650 2 / 2

Sort (cost=31,105.27..31,272.64 rows=66,947 width=21) (actual time=736.076..782.113 rows=65,650 loops=2)

  • Sort Key: roi_1.manufacturing_order_id
  • Sort Method: quicksort Memory: 6392kB
  • Worker 0: Sort Method: quicksort Memory: 6392kB
  • Buffers: shared hit=40464
14. 93.824 677.023 ↑ 1.0 65,650 2 / 2

Hash Join (cost=24,160.25..25,739.22 rows=66,947 width=21) (actual time=429.768..677.023 rows=65,650 loops=2)

  • Hash Cond: (bi.order_id = o.id)
  • Buffers: shared hit=40454
15. 108.077 540.282 ↑ 1.0 65,650 2 / 2

Hash Join (cost=21,669.37..23,072.58 rows=66,947 width=8) (actual time=386.690..540.282 rows=65,650 loops=2)

  • Hash Cond: (roi_1.item_id = bi.id)
  • Buffers: shared hit=36338
16. 46.746 46.746 ↑ 1.0 65,650 2 / 2

Seq Scan on manufacturing_referenceorderitem roi_1 (cost=0.00..1,227.47 rows=66,947 width=8) (actual time=0.016..46.746 rows=65,650 loops=2)

  • Buffers: shared hit=1116
17. 150.433 385.459 ↑ 1.0 179,822 2 / 2

Hash (cost=19,414.72..19,414.72 rows=180,372 width=8) (actual time=385.458..385.459 rows=179,822 loops=2)

  • Buckets: 262144 Batches: 1 Memory Usage: 9073kB
  • Buffers: shared hit=35222
18. 235.026 235.026 ↑ 1.0 179,822 2 / 2

Seq Scan on order_basketitem bi (cost=0.00..19,414.72 rows=180,372 width=8) (actual time=0.013..235.026 rows=179,822 loops=2)

  • Buffers: shared hit=35222
19. 15.413 42.917 ↑ 1.0 19,176 2 / 2

Hash (cost=2,250.39..2,250.39 rows=19,239 width=21) (actual time=42.916..42.917 rows=19,176 loops=2)

  • Buckets: 32768 Batches: 1 Memory Usage: 1101kB
  • Buffers: shared hit=4116
20. 27.504 27.504 ↑ 1.0 19,176 2 / 2

Seq Scan on order_order o (cost=0.00..2,250.39 rows=19,239 width=21) (actual time=0.021..27.504 rows=19,176 loops=2)

  • Buffers: shared hit=4116
21. 18.194 182.743 ↑ 1.0 11,879 2 / 2

Hash (cost=2,757.21..2,757.21 rows=11,914 width=785) (actual time=182.742..182.743 rows=11,879 loops=2)

  • Buckets: 16384 Batches: 1 Memory Usage: 10393kB
  • Buffers: shared hit=3318
22. 17.653 164.549 ↑ 1.0 11,879 2 / 2

Hash Left Join (cost=1,868.20..2,757.21 rows=11,914 width=785) (actual time=96.818..164.549 rows=11,879 loops=2)

  • Hash Cond: (pm.id = sp.product_meta_id)
  • Buffers: shared hit=3318
23. 18.259 82.290 ↑ 1.0 11,879 2 / 2

Hash Join (cost=1,188.37..2,046.09 rows=11,914 width=777) (actual time=32.157..82.290 rows=11,879 loops=2)

  • Hash Cond: (pm.product_id = p.id)
  • Buffers: shared hit=2978
24. 21.619 51.459 ↑ 1.0 11,879 2 / 2

Hash Join (cost=498.28..1,324.70 rows=11,914 width=39) (actual time=19.531..51.459 rows=11,879 loops=2)

  • Hash Cond: (pms.pm_id = pm.id)
  • Buffers: shared hit=1814
25. 10.416 10.416 ↑ 1.0 11,879 2 / 2

Seq Scan on report_productmetasummary pms (cost=0.00..795.14 rows=11,914 width=31) (actual time=0.015..10.416 rows=11,879 loops=2)

  • Buffers: shared hit=1352
26. 8.994 19.424 ↑ 1.0 11,879 2 / 2

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

  • Buckets: 16384 Batches: 1 Memory Usage: 593kB
  • Buffers: shared hit=462
27. 10.430 10.430 ↑ 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.430 rows=11,879 loops=2)

  • Buffers: shared hit=462
28. 6.128 12.572 ↑ 1.0 4,614 2 / 2

Hash (cost=630.04..630.04 rows=4,804 width=742) (actual time=12.571..12.572 rows=4,614 loops=2)

  • Buckets: 8192 Batches: 1 Memory Usage: 3547kB
  • Buffers: shared hit=1164
29. 6.444 6.444 ↑ 1.0 4,614 2 / 2

Seq Scan on mainproduct_product p (cost=0.00..630.04 rows=4,804 width=742) (actual time=0.018..6.444 rows=4,614 loops=2)

  • Buffers: shared hit=1164
30. 3.280 64.606 ↑ 1.0 4,116 2 / 2

Hash (cost=627.86..627.86 rows=4,158 width=12) (actual time=64.605..64.606 rows=4,116 loops=2)

  • Buckets: 8192 Batches: 1 Memory Usage: 241kB
  • Buffers: shared hit=340
31. 5.456 61.326 ↑ 1.0 4,116 2 / 2

Subquery Scan on sp (cost=544.70..627.86 rows=4,158 width=12) (actual time=52.567..61.326 rows=4,116 loops=2)

  • Buffers: shared hit=340
32. 15.985 55.870 ↑ 1.0 4,116 2 / 2

HashAggregate (cost=544.70..586.28 rows=4,158 width=12) (actual time=52.564..55.870 rows=4,116 loops=2)

  • Group Key: sp_1.product_meta_id
  • Buffers: shared hit=340
33. 22.440 39.885 ↑ 1.0 15,230 2 / 2

Hash Join (cost=175.48..467.77 rows=15,386 width=8) (actual time=6.818..39.885 rows=15,230 loops=2)

  • Hash Cond: (sp_1.shelf_id = s.id)
  • Buffers: shared hit=340
34. 10.691 10.691 ↑ 1.0 15,230 2 / 2

Seq Scan on shelf_shelvedproducts sp_1 (cost=0.00..251.86 rows=15,386 width=12) (actual time=0.013..10.691 rows=15,230 loops=2)

  • Buffers: shared hit=196
35. 3.083 6.754 ↑ 1.0 4,082 2 / 2

Hash (cost=123.74..123.74 rows=4,139 width=4) (actual time=6.753..6.754 rows=4,082 loops=2)

  • Buckets: 8192 Batches: 1 Memory Usage: 208kB
  • Buffers: shared hit=144
36. 3.671 3.671 ↑ 1.0 4,082 2 / 2

Seq Scan on shelf_shelf s (cost=0.00..123.74 rows=4,139 width=4) (actual time=0.015..3.671 rows=4,082 loops=2)

  • Filter: (type = 1)
  • Buffers: shared hit=144
Planning time : 8.709 ms
Execution time : 1,715.279 ms