explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0e5 : Vari3

Settings
# exclusive inclusive rows x rows loops node
1. 0.181 1,728.495 ↑ 1.0 100 1

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

  • Buffers: shared hit=44553
2. 23.801 1,728.314 ↑ 279.9 100 1

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

  • Workers Planned: 1
  • Workers Launched: 1
  • Buffers: shared hit=44553
3. 29.440 1,704.513 ↑ 388.8 72 2 / 2

Sort (cost=41,242.51..41,312.48 rows=27,991 width=242) (actual time=1,704.463..1,704.513 rows=72 loops=2)

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

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

  • Hash Cond: (mo.id = roi.manufacturing_order_id)
  • Buffers: shared hit=44545
5. 36.493 293.503 ↑ 1.2 23,880 2 / 2

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

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

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

  • Hash Cond: (mo.product_meta_id = pm.id)
  • Buffers: shared hit=4356
7. 21.122 21.122 ↑ 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.012..21.122 rows=23,880 loops=2)

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

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

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

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

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

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

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

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

  • Hash Cond: (pms.pm_id = pm.id)
  • Buffers: shared hit=1776
12. 9.927 9.927 ↑ 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.018..9.927 rows=11,879 loops=2)

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

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

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

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

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

  • Buckets: 8192 Batches: 1 Memory Usage: 3554kB
  • Buffers: shared hit=1118
16. 5.767 5.767 ↑ 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.020..5.767 rows=4,614 loops=2)

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

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

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

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

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

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

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

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

  • Hash Cond: (sp_1.shelf_id = s.id)
  • Buffers: shared hit=336
21. 10.818 10.818 ↑ 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.015..10.818 rows=15,229 loops=2)

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

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

  • Buckets: 4096 Batches: 1 Memory Usage: 176kB
  • Buffers: shared hit=142
23. 3.600 3.600 ↑ 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.600 rows=4,082 loops=2)

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

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

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

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

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

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

Subquery Scan on roi (cost=30,694.29..33,051.83 rows=61,488 width=36) (actual time=734.822..994.809 rows=63,164 loops=2)

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

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

  • Group Key: roi_1.manufacturing_order_id
  • Buffers: shared hit=39941
29. 107.393 781.360 ↑ 1.0 65,627 2 / 2

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

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

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

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

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

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

Seq Scan on manufacturing_referenceorderitem roi_1 (cost=0.00..1,203.27 rows=65,627 width=8) (actual time=0.015..46.956 rows=65,627 loops=2)

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

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

  • Buckets: 262144 Batches: 1 Memory Usage: 9070kB
  • Buffers: shared hit=34740
34. 229.579 229.579 ↑ 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.013..229.579 rows=179,753 loops=2)

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

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1100kB
  • Buffers: shared hit=4100
36. 26.976 26.976 ↑ 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..26.976 rows=19,164 loops=2)

  • Buffers: shared hit=4100
Planning time : 4.762 ms
Execution time : 1,729.030 ms