explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9wuW

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 262,159.834 ↑ 1.7 3 1

Gather (cost=1,004.12..1,333,265,427.58 rows=5 width=113) (actual time=128,117.934..262,159.834 rows=3 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
2. 0.004 2,409.599 ↑ 1.0 1 5 / 5

Nested Loop Left Join (cost=4.12..94,035.78 rows=1 width=81) (actual time=1,904.004..2,409.599 rows=1 loops=5)

3. 1,063.051 2,409.591 ↑ 1.0 1 5 / 5

Hash Join (cost=4.09..94,035.74 rows=1 width=49) (actual time=1,903.998..2,409.591 rows=1 loops=5)

  • Hash Cond: ((arinvdet.ap_partime)::text = (apinvdet.""timestamp"")::text)
4. 1,344.515 1,344.515 ↑ 1.2 304,540 5 / 5

Parallel Seq Scan on arinvdet (cost=0.00..93,833.84 rows=376,769 width=40) (actual time=0.005..1,344.515 rows=304,540 loops=5)

  • Filter: ((inven IS NOT NULL) AND ((inven)::text <> ALL ('{LABOR,OVERHEAD}'::text[])))
  • Rows Removed by Filter: 33221
5. 0.003 2.025 ↑ 1.0 1 5 / 5

Hash (cost=4.09..4.09 rows=1 width=27) (actual time=2.025..2.025 rows=1 loops=5)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
6. 2.022 2.022 ↑ 1.0 1 5 / 5

Index Scan using apinvdet_pkey on apinvdet (cost=0.08..4.09 rows=1 width=27) (actual time=2.021..2.022 rows=1 loops=5)

  • Index Cond: ((autoid)::text = '2J2ELP9VUS4E2EB1'::text)
7. 0.004 0.004 ↓ 0.0 0 3 / 5

Index Scan using uc_componentautoid on item_components (cost=0.03..0.03 rows=1 width=64) (actual time=0.006..0.006 rows=0 loops=3)

  • Index Cond: ((arinvdet.autoid)::text = (component_autoid)::text)
8.          

SubPlan (for Gather)

9. 101.100 260,767.068 ↑ 1.0 1 3

Aggregate (cost=266,634,078.26..266,634,078.26 rows=1 width=32) (actual time=86,922.356..86,922.356 rows=1 loops=3)

10. 3.321 260,665.968 ↓ 0.0 0 3

Subquery Scan on vw_upstreams_including_links (cost=266,408,052.19..266,634,078.26 rows=1 width=32) (actual time=86,888.656..86,888.656 rows=0 loops=3)

  • Filter: (vw_upstreams_including_links.is_linked AND ((vw_upstreams_including_links.component_id)::text = (arinvdet.autoid)::text))
  • Rows Removed by Filter: 3599
11. 3,223.698 260,662.647 ↓ 18.0 3,599 3

Unique (cost=266,408,052.19..266,634,077.56 rows=200 width=131) (actual time=83,807.294..86,887.549 rows=3,599 loops=3)

12. 31,455.267 257,438.949 ↑ 66.2 3,414,260 3

Sort (cost=266,408,052.19..266,521,064.87 rows=226,025,363 width=131) (actual time=83,807.292..85,812.983 rows=3,414,260 loops=3)

  • Sort Key: linked_info.upstream_id, ((linked_info.upstream_locations IS NULL))
  • Sort Method: external merge Disk: 118648kB
13. 10,310.706 225,983.682 ↑ 66.2 3,414,260 3

Subquery Scan on linked_info (cost=7,763.64..249,099,021.79 rows=226,025,363 width=131) (actual time=161.193..75,327.894 rows=3,414,260 loops=3)

14. 8,661.765 215,672.976 ↑ 66.2 3,414,260 3

Append (cost=7,763.64..248,420,945.70 rows=226,025,363 width=130) (actual time=161.192..71,890.992 rows=3,414,260 loops=3)

15. 13,274.734 207,011.175 ↑ 66.2 3,414,257 3

Subquery Scan on vw_component_upstreams (cost=7,763.64..247,742,868.60 rows=226,025,360 width=130) (actual time=161.190..69,003.725 rows=3,414,257 loops=3)

16. 8,518.530 193,736.433 ↑ 66.2 3,414,257 3

Append (cost=7,763.64..18,327,128.20 rows=226,025,360 width=129) (actual time=161.181..64,578.811 rows=3,414,257 loops=3)

17. 14,716.695 41,831.091 ↑ 1.5 1,725,450 3

Hash Left Join (cost=7,763.64..55,967.36 rows=2,564,635 width=86) (actual time=161.180..13,943.697 rows=1,725,450 loops=3)

  • Hash Cond: ((arinvdet_1.inven)::text = (inventry.id)::text)
18. 12,376.905 26,654.112 ↑ 1.5 1,725,450 3

Nested Loop (cost=1,469.38..38,800.58 rows=2,564,635 width=47) (actual time=7.716..8,884.704 rows=1,725,450 loops=3)

19. 1,388.060 1,972.590 ↑ 2.1 3,601 3

Hash Join (cost=1,469.29..12,887.33 rows=7,452 width=31) (actual time=7.686..657.530 rows=3,601 loops=3)

  • Hash Cond: ((apinvdet_1.doc_aid)::text = (inmfg.autoid)::text)
20. 561.726 561.726 ↑ 1.0 284,617 3

Seq Scan on apinvdet apinvdet_1 (cost=0.00..11,268.49 rows=284,831 width=32) (actual time=0.006..187.242 rows=284,617 loops=3)

21. 10.368 22.804 ↑ 1.0 3,601 1

Hash (cost=1,456.21..1,456.21 rows=3,737 width=33) (actual time=22.803..22.804 rows=3,601 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 261kB
22. 12.436 12.436 ↑ 1.0 3,601 1

Index Scan using idx_btree_inmfg_status on inmfg (cost=0.08..1,456.21 rows=3,737 width=33) (actual time=0.052..12.436 rows=3,601 loops=1)

  • Index Cond: (status = '0'::numeric)
23. 12,304.617 12,304.617 ↓ 1.3 479 10,803

Index Scan using idx_btree_arinvdet_inven on arinvdet arinvdet_1 (cost=0.09..2.40 rows=360 width=27) (actual time=0.039..1.139 rows=479 loops=10,803)

  • Index Cond: ((inven)::text = (apinvdet_1.inven)::text)
24. 91.449 460.284 ↑ 1.0 55,760 1

Hash (cost=6,096.72..6,096.72 rows=56,440 width=48) (actual time=460.284..460.284 rows=55,760 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3581kB
25. 176.687 368.835 ↑ 1.0 55,760 1

Hash Left Join (cost=1.16..6,096.72 rows=56,440 width=48) (actual time=0.054..368.835 rows=55,760 loops=1)

  • Hash Cond: ((inventry.mfg)::text = (inmanufacturers.mfg_id)::text)
26. 192.111 192.111 ↑ 1.0 55,760 1

Seq Scan on inventry (cost=0.00..5,894.32 rows=56,440 width=19) (actual time=0.008..192.111 rows=55,760 loops=1)

27. 0.020 0.037 ↑ 1.0 25 1

Hash (cost=1.07..1.07 rows=25 width=43) (actual time=0.037..0.037 rows=25 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
28. 0.017 0.017 ↑ 1.0 25 1

Seq Scan on inmanufacturers (cost=0.00..1.07 rows=25 width=43) (actual time=0.006..0.017 rows=25 loops=1)

29. 3,762.867 143,386.812 ↑ 132.3 1,688,807 3

Subquery Scan on "*SELECT* 2_1" (cost=15,470,207.88..18,263,466.94 rows=223,460,725 width=114) (actual time=36,059.651..47,795.604 rows=1,688,807 loops=3)

30. 19,831.284 139,623.945 ↑ 132.3 1,688,807 3

GroupAggregate (cost=15,470,207.88..17,593,084.77 rows=223,460,725 width=126) (actual time=36,059.648..46,541.315 rows=1,688,807 loops=3)

  • Group Key: arinvdet_2.autoid, invenwh.id
31. 88,277.883 119,792.661 ↑ 87.4 2,556,040 3

Sort (cost=15,470,207.88..15,581,938.24 rows=223,460,725 width=90) (actual time=36,059.630..39,930.887 rows=2,556,040 loops=3)

  • Sort Key: arinvdet_2.autoid, invenwh.id
  • Sort Method: external merge Disk: 163128kB
32. 8,753.694 31,514.778 ↑ 87.4 2,556,040 3

Merge Right Join (cost=132,391.44..1,089,047.50 rows=223,460,725 width=90) (actual time=5,441.099..10,504.926 rows=2,556,040 loops=3)

  • Merge Cond: ((invenwh.id)::text = (arinvdet_2.inven)::text)
33. 56.073 1,567.416 ↑ 1,691.8 4,501 3

Nested Loop Left Join (cost=0.08..59,442.50 rows=7,614,600 width=73) (actual time=17.412..522.472 rows=4,501 loops=3)

34. 1,468.335 1,468.335 ↑ 21.2 3,584 3

Index Scan using idx_btree_invenwh_id on invenwh (cost=0.08..13,754.90 rows=76,146 width=42) (actual time=17.394..489.445 rows=3,584 loops=3)

  • Filter: (((((count + pur_s) + m_in_s) + wt_in_s) - ((sales_s + m_out_s) + wt_out_s)) > '0'::numeric)
  • Rows Removed by Filter: 219493
35. 43.008 43.008 ↑ 100.0 1 10,752

Function Scan on unnest warehouse_location (cost=0.00..0.30 rows=100 width=32) (actual time=0.004..0.004 rows=1 loops=10,752)

36. 17,894.900 21,193.668 ↓ 1.5 2,556,040 3

Sort (cost=132,391.36..133,236.41 rows=1,690,105 width=27) (actual time=5,423.682..7,064.556 rows=2,556,040 loops=3)

  • Sort Key: arinvdet_2.inven
  • Sort Method: external sort Disk: 68352kB
37. 3,298.768 3,298.768 ↑ 1.0 1,688,807 1

Seq Scan on arinvdet arinvdet_2 (cost=0.00..97,425.32 rows=1,690,105 width=27) (actual time=0.009..3,298.768 rows=1,688,807 loops=1)

38.          

SubPlan (for Subquery Scan)

39. 0.000 0.000 ↓ 0.0 0

Seq Scan on component_links (cost=0.00..1.01 rows=1 width=0) (never executed)

  • Filter: (((component_autoid)::text = (vw_component_upstreams.component_id)::text) AND ((component_link)::text = vw_component_upstreams.upstream_id))
40. 0.008 0.008 ↑ 1.0 3 1

Seq Scan on component_links component_links_1 (cost=0.00..1.01 rows=3 width=64) (actual time=0.006..0.008 rows=3 loops=1)

41. 0.009 0.036 ↑ 1.0 3 3

Subquery Scan on "*SELECT* 2" (cost=0.00..1.02 rows=3 width=115) (actual time=0.008..0.012 rows=3 loops=3)

42. 0.027 0.027 ↑ 1.0 3 3

Seq Scan on component_links component_links_2 (cost=0.00..1.01 rows=3 width=89) (actual time=0.007..0.009 rows=3 loops=3)

Planning time : 27.341 ms
Execution time : 262,211.561 ms