explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cyrq

Settings
# exclusive inclusive rows x rows loops node
1. 3,214.166 3,917.781 ↑ 1.7 3 1

Gather (cost=1,004.12..97,234.28 rows=5 width=113) (actual time=528.326..3,917.781 rows=3 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
2. 0.005 660.325 ↑ 1.0 1 5 / 5

Nested Loop Left Join (cost=4.12..94,056.14 rows=1 width=81) (actual time=105.432..660.325 rows=1 loops=5)

3. 282.623 660.318 ↑ 1.0 1 5 / 5

Hash Join (cost=4.09..94,056.10 rows=1 width=49) (actual time=105.428..660.318 rows=1 loops=5)

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

Parallel Seq Scan on arinvdet (cost=0.00..93,854.16 rows=376,850 width=40) (actual time=0.002..377.690 rows=304,551 loops=5)

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

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

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

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

  • Index Cond: ((autoid)::text = '2J2ELP9VUS4E2EB1'::text)
7. 0.002 0.002 ↓ 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.004..0.004 rows=0 loops=3)

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

SubPlan (for Gather)

9. 0.012 43.290 ↑ 1.0 1 3

Aggregate (cost=435.52..435.53 rows=1 width=32) (actual time=14.429..14.430 rows=1 loops=3)

10. 0.012 43.278 ↓ 0.0 0 3

Append (cost=2.42..435.17 rows=101 width=130) (actual time=14.426..14.426 rows=0 loops=3)

11. 0.044 43.233 ↓ 0.0 0 3

Subquery Scan on vw_upstreams_only (cost=2.42..335.06 rows=100 width=130) (actual time=14.411..14.411 rows=0 loops=3)

  • Filter: ((alternatives: SubPlan 3 or hashed SubPlan 4) OR vw_upstreams_only.is_auto_linked)
  • Rows Removed by Filter: 6
12. 0.027 43.179 ↑ 22.3 6 3

Append (cost=2.42..97.85 rows=134 width=129) (actual time=2.240..14.393 rows=6 loops=3)

13. 1.654 42.837 ↓ 2.5 5 3

Nested Loop (cost=2.42..81.64 rows=2 width=86) (actual time=2.174..14.279 rows=5 loops=3)

14. 9.699 16.203 ↓ 7.2 416 3

Nested Loop (cost=2.33..74.71 rows=58 width=74) (actual time=0.186..5.401 rows=416 loops=3)

15. 0.015 0.153 ↑ 1.0 1 3

Nested Loop Left Join (cost=0.20..8.22 rows=1 width=63) (actual time=0.045..0.051 rows=1 loops=3)

16. 0.024 0.120 ↑ 1.0 1 3

Nested Loop Left Join (cost=0.17..8.18 rows=1 width=34) (actual time=0.036..0.040 rows=1 loops=3)

17. 0.060 0.060 ↑ 1.0 1 3

Index Scan using arinvdet_pkey on arinvdet arinvdet_1 (cost=0.09..4.09 rows=1 width=27) (actual time=0.019..0.020 rows=1 loops=3)

  • Index Cond: ((autoid)::text = (arinvdet.autoid)::text)
18. 0.036 0.036 ↑ 1.0 1 3

Index Scan using idx_btree_inventry_idpattern on inventry (cost=0.08..4.09 rows=1 width=19) (actual time=0.011..0.012 rows=1 loops=3)

  • Index Cond: ((arinvdet_1.inven)::text = (id)::text)
19. 0.018 0.018 ↑ 1.0 1 3

Index Scan using idx_btree_inmanufacturers_mfgid on inmanufacturers (cost=0.03..0.03 rows=1 width=43) (actual time=0.005..0.006 rows=1 loops=3)

  • Index Cond: ((inventry.mfg)::text = (mfg_id)::text)
20. 6.099 6.351 ↓ 12.6 416 3

Bitmap Heap Scan on apinvdet apinvdet_1 (cost=2.14..66.39 rows=33 width=32) (actual time=0.131..2.117 rows=416 loops=3)

  • Recheck Cond: ((inven)::text = (arinvdet_1.inven)::text)
  • Heap Blocks: exact=986
21. 0.252 0.252 ↓ 12.6 417 3

Bitmap Index Scan on idx_btree_apinvdet_inven (cost=0.00..2.13 rows=33 width=0) (actual time=0.084..0.084 rows=417 loops=3)

  • Index Cond: ((inven)::text = (arinvdet_1.inven)::text)
22. 24.980 24.980 ↓ 0.0 0 1,249

Index Scan using inmfg_pkey on inmfg (cost=0.08..0.12 rows=1 width=33) (actual time=0.020..0.020 rows=0 loops=1,249)

  • Index Cond: ((autoid)::text = (apinvdet_1.doc_aid)::text)
  • Filter: (status = '0'::numeric)
  • Rows Removed by Filter: 1
23. 0.018 0.315 ↑ 132.0 1 3

Subquery Scan on "*SELECT* 2_1" (cost=15.35..16.21 rows=132 width=114) (actual time=0.103..0.105 rows=1 loops=3)

24. 0.069 0.297 ↑ 132.0 1 3

HashAggregate (cost=15.35..15.81 rows=132 width=126) (actual time=0.097..0.099 rows=1 loops=3)

  • Group Key: arinvdet_2.autoid, invenwh.id
25. 0.027 0.228 ↑ 66.0 2 3

Nested Loop Left Join (cost=0.17..14.62 rows=132 width=90) (actual time=0.063..0.076 rows=2 loops=3)

26. 0.045 0.045 ↑ 1.0 1 3

Index Scan using arinvdet_pkey on arinvdet arinvdet_2 (cost=0.09..4.09 rows=1 width=27) (actual time=0.014..0.015 rows=1 loops=3)

  • Index Cond: ((autoid)::text = (arinvdet.autoid)::text)
27. 0.018 0.156 ↑ 100.0 1 3

Nested Loop Left Join (cost=0.08..10.23 rows=100 width=73) (actual time=0.042..0.052 rows=1 loops=3)

28. 0.105 0.105 ↑ 1.0 1 3

Index Scan using idx_btree_invenwh_id on invenwh (cost=0.08..9.63 rows=1 width=42) (actual time=0.028..0.035 rows=1 loops=3)

  • Index Cond: ((arinvdet_2.inven)::text = (id)::text)
  • Filter: (((((count + pur_s) + m_in_s) + wt_in_s) - ((sales_s + m_out_s) + wt_out_s)) > '0'::numeric)
  • Rows Removed by Filter: 3
29. 0.033 0.033 ↑ 100.0 1 3

Function Scan on unnest warehouse_location (cost=0.00..0.30 rows=100 width=32) (actual time=0.010..0.011 rows=1 loops=3)

30.          

SubPlan (for Subquery Scan)

31. 0.000 0.000 ↓ 0.0 0

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

  • Filter: (((component_autoid)::text = (vw_upstreams_only.component_id)::text) AND ((component_link)::text = vw_upstreams_only.upstream_id))
32. 0.010 0.010 ↓ 2.3 7 1

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

33. 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_upstreams_only.component_id)::text) AND ((component_link)::text = vw_upstreams_only.upstream_id))
34. 0.000 0.000 ↓ 0.0 0

Seq Scan on component_links component_links_1 (cost=0.00..1.01 rows=3 width=64) (never executed)

35. 0.009 0.033 ↓ 0.0 0 3

Subquery Scan on "*SELECT* 2" (cost=2.42..99.81 rows=1 width=115) (actual time=0.011..0.011 rows=0 loops=3)

36. 0.009 0.024 ↓ 0.0 0 3

Nested Loop Anti Join (cost=2.42..99.80 rows=1 width=89) (actual time=0.008..0.008 rows=0 loops=3)

  • Join Filter: (((arinvdet_3.autoid)::text = (component_links_4.component_autoid)::text) AND ((btrim((inmfg_1.batch)::text)) = (component_links_4.component_link)::text))
37. 0.015 0.015 ↓ 0.0 0 3

Seq Scan on component_links component_links_4 (cost=0.00..1.01 rows=1 width=23) (actual time=0.005..0.005 rows=0 loops=3)

  • Filter: ((component_autoid)::text = (arinvdet.autoid)::text)
  • Rows Removed by Filter: 7
38. 0.000 0.000 ↓ 0.0 0

Append (cost=2.42..97.85 rows=134 width=129) (never executed)

39. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=2.42..81.64 rows=2 width=86) (never executed)

40. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=2.33..74.71 rows=58 width=74) (never executed)

41. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=0.20..8.22 rows=1 width=63) (never executed)

42. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=0.17..8.18 rows=1 width=34) (never executed)

43. 0.000 0.000 ↓ 0.0 0

Index Scan using arinvdet_pkey on arinvdet arinvdet_3 (cost=0.09..4.09 rows=1 width=27) (never executed)

  • Index Cond: ((autoid)::text = (arinvdet.autoid)::text)
44. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_btree_inventry_idpattern on inventry inventry_1 (cost=0.08..4.09 rows=1 width=19) (never executed)

  • Index Cond: ((arinvdet_3.inven)::text = (id)::text)
45. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_btree_inmanufacturers_mfgid on inmanufacturers inmanufacturers_1 (cost=0.03..0.03 rows=1 width=43) (never executed)

  • Index Cond: ((inventry_1.mfg)::text = (mfg_id)::text)
46. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on apinvdet apinvdet_2 (cost=2.14..66.39 rows=33 width=32) (never executed)

  • Recheck Cond: ((inven)::text = (arinvdet_3.inven)::text)
47. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_btree_apinvdet_inven (cost=0.00..2.13 rows=33 width=0) (never executed)

  • Index Cond: ((inven)::text = (arinvdet_3.inven)::text)
48. 0.000 0.000 ↓ 0.0 0

Index Scan using inmfg_pkey on inmfg inmfg_1 (cost=0.08..0.12 rows=1 width=33) (never executed)

  • Index Cond: ((autoid)::text = (apinvdet_2.doc_aid)::text)
  • Filter: (status = '0'::numeric)
49. 0.000 0.000 ↓ 0.0 0

Subquery Scan on "*SELECT* 2_2" (cost=15.35..16.21 rows=132 width=114) (never executed)

50. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=15.35..15.81 rows=132 width=126) (never executed)

  • Group Key: arinvdet_4.autoid, invenwh_1.id
51. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=0.17..14.62 rows=132 width=90) (never executed)

52. 0.000 0.000 ↓ 0.0 0

Index Scan using arinvdet_pkey on arinvdet arinvdet_4 (cost=0.09..4.09 rows=1 width=27) (never executed)

  • Index Cond: ((autoid)::text = (arinvdet.autoid)::text)
53. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=0.08..10.23 rows=100 width=73) (never executed)

54. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_btree_invenwh_id on invenwh invenwh_1 (cost=0.08..9.63 rows=1 width=42) (never executed)

  • Index Cond: ((arinvdet_4.inven)::text = (id)::text)
  • Filter: (((((count + pur_s) + m_in_s) + wt_in_s) - ((sales_s + m_out_s) + wt_out_s)) > '0'::numeric)
55. 0.000 0.000 ↓ 0.0 0

Function Scan on unnest warehouse_location_1 (cost=0.00..0.30 rows=100 width=32) (never executed)

Planning time : 8.582 ms
Execution time : 3,918.228 ms