explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iqjm

Settings
# exclusive inclusive rows x rows loops node
1. 0.028 84,224.184 ↓ 30.0 30 1

Subquery Scan on vw_invoices_all_picking (cost=67,613.20..67,613.21 rows=1 width=589) (actual time=84,224.156..84,224.184 rows=30 loops=1)

2. 0.409 84,224.156 ↓ 30.0 30 1

Sort (cost=67,613.20..67,613.20 rows=1 width=593) (actual time=84,224.151..84,224.156 rows=30 loops=1)

  • Sort Key: vw_invoices_all_base.has_changed DESC, ((vw_invoices_all_base."time")::date), vw_invoices_all_base.is_rushed DESC, vw_invoices_all_base."time", vw_invoices_all_base.lane, vw_invoices_all_base.customer_priority, vw_invoices_all_base.invoice_priority
  • Sort Method: quicksort Memory: 37kB
3. 0.121 84,223.747 ↓ 30.0 30 1

Subquery Scan on vw_invoices_all_base (cost=50,344.30..67,613.19 rows=1 width=593) (actual time=1,294.886..84,223.747 rows=30 loops=1)

4. 1.306 84,223.626 ↓ 30.0 30 1

Nested Loop Left Join (cost=50,344.30..67,613.18 rows=1 width=589) (actual time=1,294.883..84,223.626 rows=30 loops=1)

  • Join Filter: (pick_jobs.id = pick_jobs_shelved.pick_job)
  • Rows Removed by Join Filter: 1470
5.          

CTE multiwarehouse

6. 1.067 255.918 ↓ 17.4 660 1

Group (cost=11,495.87..11,496.16 rows=38 width=24) (actual time=254.222..255.918 rows=660 loops=1)

  • Group Key: arinvdet.doc_aid, arinvdet.warehouse
7. 10.035 254.851 ↓ 81.6 3,101 1

Sort (cost=11,495.87..11,495.97 rows=38 width=24) (actual time=254.217..254.851 rows=3,101 loops=1)

  • Sort Key: arinvdet.doc_aid, arinvdet.warehouse
  • Sort Method: quicksort Memory: 339kB
8. 3.483 244.816 ↓ 81.6 3,101 1

Nested Loop (cost=0.43..11,494.87 rows=38 width=24) (actual time=0.325..244.816 rows=3,101 loops=1)

9. 229.363 229.363 ↓ 83.1 665 1

Seq Scan on arinv arinv_1 (cost=0.00..10,944.11 rows=8 width=17) (actual time=0.290..229.363 rows=665 loops=1)

  • Filter: ((is_rma IS NOT TRUE) AND (progress < '80'::numeric) AND ((status)::text = 'U'::text))
  • Rows Removed by Filter: 106344
10. 11.970 11.970 ↑ 1.2 5 665

Index Scan using arinvdet_docaid_idx on arinvdet (cost=0.43..68.79 rows=6 width=24) (actual time=0.014..0.018 rows=5 loops=665)

  • Index Cond: ((doc_aid)::text = (arinv_1.autoid)::text)
  • Filter: ((quan > 0.0) AND ((id)::text !~~ '($)%'::text))
  • Rows Removed by Filter: 0
11. 0.142 84,216.770 ↓ 30.0 30 1

Nested Loop Left Join (cost=38,828.38..53,521.10 rows=1 width=631) (actual time=1,294.504..84,216.770 rows=30 loops=1)

12. 0.092 84,216.238 ↓ 30.0 30 1

Nested Loop (cost=38,828.11..53,517.62 rows=1 width=627) (actual time=1,294.442..84,216.238 rows=30 loops=1)

  • Join Filter: ((multiwarehouse.warehouse)::text = (inwh.warehouse)::text)
13. 0.962 84,216.086 ↓ 1.6 30 1

Merge Left Join (cost=38,828.11..53,516.29 rows=19 width=563) (actual time=1,294.400..84,216.086 rows=30 loops=1)

  • Merge Cond: (((multiwarehouse.invoice)::text = (pick_jobs_updated.original_invoice)::text) AND ((multiwarehouse.warehouse)::text = (pick_jobs_updated.original_warehouse)::text))
14. 2,268.229 84,200.327 ↓ 1.6 30 1

Nested Loop (cost=37,974.02..52,599.73 rows=19 width=559) (actual time=1,280.950..84,200.327 rows=30 loops=1)

  • Join Filter: ((multiwarehouse.invoice)::text = (scheduled_invoices.invoice_autoid)::text)
  • Rows Removed by Join Filter: 9294390
15. 4.039 328.458 ↓ 526.0 526 1

Nested Loop Left Join (cost=10,688.78..12,092.52 rows=1 width=407) (actual time=282.004..328.458 rows=526 loops=1)

16. 1.988 311.795 ↓ 526.0 526 1

Nested Loop Left Join (cost=10,688.50..12,089.32 rows=1 width=413) (actual time=281.955..311.795 rows=526 loops=1)

17. 4.562 309.281 ↓ 526.0 526 1

Nested Loop Left Join (cost=10,688.35..12,089.16 rows=1 width=400) (actual time=281.951..309.281 rows=526 loops=1)

18. 9.295 295.251 ↓ 526.0 526 1

Merge Left Join (cost=10,687.94..12,080.93 rows=1 width=361) (actual time=281.922..295.251 rows=526 loops=1)

  • Merge Cond: (((multiwarehouse.invoice)::text = (pick_jobs_deleted.original_invoice)::text) AND ((multiwarehouse.warehouse)::text = (pick_jobs_deleted.original_warehouse)::text))
19. 1.509 270.739 ↓ 526.0 526 1

Sort (cost=317.51..317.52 rows=1 width=343) (actual time=269.955..270.739 rows=526 loops=1)

  • Sort Key: multiwarehouse.invoice, multiwarehouse.warehouse
  • Sort Method: quicksort Memory: 188kB
20. 0.971 269.230 ↓ 526.0 526 1

Nested Loop (cost=0.42..317.50 rows=1 width=343) (actual time=254.348..269.230 rows=526 loops=1)

21. 256.379 256.379 ↓ 17.4 660 1

CTE Scan on multiwarehouse (cost=0.00..0.76 rows=38 width=64) (actual time=254.225..256.379 rows=660 loops=1)

22. 11.880 11.880 ↑ 1.0 1 660

Index Scan using arinv_pkey on arinv (cost=0.42..8.33 rows=1 width=279) (actual time=0.018..0.018 rows=1 loops=660)

  • Index Cond: ((autoid)::text = (multiwarehouse.invoice)::text)
  • Filter: ((is_rma IS NOT TRUE) AND (progress < '80'::numeric) AND (progress < 60.0))
  • Rows Removed by Filter: 0
23. 2.389 15.217 ↑ 47.3 1,235 1

Unique (cost=10,370.43..10,887.03 rows=58,425 width=93) (actual time=11.948..15.217 rows=1,235 loops=1)

24. 6.032 12.828 ↑ 47.3 1,456 1

Sort (cost=10,370.43..10,542.63 rows=68,880 width=93) (actual time=11.945..12.828 rows=1,456 loops=1)

  • Sort Key: pick_jobs_deleted.original_invoice, pick_jobs_deleted.original_warehouse, pick_jobs_deleted.time_deleted DESC NULLS LAST
  • Sort Method: quicksort Memory: 164kB
25. 6.796 6.796 ↑ 47.3 1,456 1

Seq Scan on pick_jobs_deleted (cost=0.00..1,303.80 rows=68,880 width=93) (actual time=0.143..6.796 rows=1,456 loops=1)

26. 9.468 9.468 ↓ 0.0 0 526

Index Scan using uc_invoice_warehouse on pick_jobs (cost=0.41..8.22 rows=1 width=106) (actual time=0.018..0.018 rows=0 loops=526)

  • Index Cond: (((multiwarehouse.invoice)::text = (invoice)::text) AND ((multiwarehouse.warehouse)::text = (warehouse)::text))
27. 0.526 0.526 ↓ 0.0 0 526

Index Scan using user_pkey on "user" (cost=0.14..0.16 rows=1 width=17) (actual time=0.001..0.001 rows=0 loops=526)

  • Index Cond: (pick_jobs."current_user" = id)
28. 12.624 12.624 ↑ 1.0 1 526

Index Scan using arcust_id_idx on arcust (cost=0.29..3.19 rows=1 width=8) (actual time=0.020..0.024 rows=1 loops=526)

  • Index Cond: ((arinv.id)::text = (id)::text)
29. 3,046.066 81,603.640 ↓ 1.9 17,670 526

Append (cost=27,285.24..40,297.61 rows=9,315 width=209) (actual time=0.900..155.140 rows=17,670 loops=526)

30.          

CTE scheduled_invoices

31. 3.313 499.976 ↑ 1.3 499 1

Nested Loop Left Join (cost=15,738.79..16,341.14 rows=660 width=124) (actual time=471.805..499.976 rows=499 loops=1)

32. 28.702 495.665 ↑ 1.3 499 1

Merge Left Join (cost=15,738.51..16,100.09 rows=660 width=147) (actual time=471.706..495.665 rows=499 loops=1)

  • Merge Cond: (((arinv_3.invoice)::text = ((invoice.invoice_id)::text)) AND (arinv_3.company = ((invoice.company)::text)))
33. 3.355 168.874 ↑ 1.3 499 1

Sort (cost=13,180.47..13,182.12 rows=660 width=144) (actual time=168.703..168.874 rows=499 loops=1)

  • Sort Key: arinv_3.invoice, arinv_3.company
  • Sort Method: quicksort Memory: 95kB
34. 1.080 165.519 ↑ 1.3 499 1

Hash Left Join (cost=1,476.16..13,149.56 rows=660 width=144) (actual time=27.310..165.519 rows=499 loops=1)

  • Hash Cond: ((arinv_3.ship_cat = customer_entry.ship_cat) AND (arinv_3.ship_date = customer_entry.date) AND ((((COALESCE((arinv_3.c_id)::text, ''::text) || COALESCE((arinv_3.c_zip)::text, ''::text)) || '_'::text) || COALESCE((arinv_3.c_address1)::text, ''::text)) = (customer_entry.sch_cust_id)::text))
  • Filter: ((arinv_3.ship_cat > 2) OR (customer_entry.load_id IS NOT NULL))
  • Rows Removed by Filter: 80
35. 0.841 137.339 ↑ 1.2 579 1

Subquery Scan on arinv_3 (cost=0.29..11,667.83 rows=710 width=232) (actual time=0.026..137.339 rows=579 loops=1)

  • Filter: (arinv_3.progress >= '40'::numeric)
  • Rows Removed by Filter: 1474
36. 0.723 136.498 ↑ 1.0 2,053 1

Append (cost=0.29..11,641.21 rows=2,130 width=1,206) (actual time=0.023..136.498 rows=2,053 loops=1)

37. 0.407 0.407 ↓ 1.1 188 1

Index Scan using idx_btree_arinv_status on arinv arinv_4 (cost=0.29..52.75 rows=169 width=395) (actual time=0.022..0.407 rows=188 loops=1)

  • Index Cond: ((status)::text = 'U'::text)
38. 0.502 3.643 ↑ 1.0 982 1

Subquery Scan on *SELECT* 2_1 (cost=0.42..523.80 rows=1,025 width=442) (actual time=0.090..3.643 rows=982 loops=1)

39. 3.141 3.141 ↑ 1.0 982 1

Index Scan using arinv_status_idx on arinv arinv_5 (cost=0.42..513.55 rows=1,025 width=450) (actual time=0.088..3.141 rows=982 loops=1)

  • Index Cond: ((status)::text = 'U'::text)
40. 128.941 128.941 ↑ 1.1 883 1

Seq Scan on arinv arinv_6 (cost=0.00..10,676.59 rows=935 width=447) (actual time=0.092..128.941 rows=883 loops=1)

  • Filter: ((status)::text = 'U'::text)
  • Rows Removed by Filter: 106126
41. 0.003 2.784 ↓ 0.0 0 1

Subquery Scan on *SELECT* 4 (cost=0.00..377.02 rows=1 width=630) (actual time=2.783..2.784 rows=0 loops=1)

42. 2.781 2.781 ↓ 0.0 0 1

Seq Scan on arinv arinv_7 (cost=0.00..377.01 rows=1 width=602) (actual time=2.781..2.781 rows=0 loops=1)

  • Filter: ((status)::text = 'U'::text)
  • Rows Removed by Filter: 3441
43. 15.691 27.100 ↑ 1.0 32,577 1

Hash (cost=905.77..905.77 rows=32,577 width=41) (actual time=27.100..27.100 rows=32,577 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 2689kB
44. 11.409 11.409 ↑ 1.0 32,577 1

Seq Scan on customer_entry (cost=0.00..905.77 rows=32,577 width=41) (actual time=0.017..11.409 rows=32,577 loops=1)

45. 267.406 298.089 ↑ 1.0 27,857 1

Sort (cost=2,558.04..2,627.79 rows=27,899 width=23) (actual time=289.703..298.089 rows=27,857 loops=1)

  • Sort Key: ((invoice.invoice_id)::text), ((invoice.company)::text)
  • Sort Method: quicksort Memory: 2955kB
46. 30.683 30.683 ↑ 1.0 27,899 1

Seq Scan on invoice (cost=0.00..497.99 rows=27,899 width=23) (actual time=0.035..30.683 rows=27,899 loops=1)

47. 0.998 0.998 ↑ 1.0 1 499

Index Scan using uc_id_date on load (cost=0.28..0.31 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=499)

  • Index Cond: (id = customer_entry.load_id)
48.          

CTE rushed_invoices

49. 226.873 226.873 ↑ 1.1 11 1

Seq Scan on arinv arinv_8 (cost=0.00..10,944.11 rows=12 width=21) (actual time=11.881..226.873 rows=11 loops=1)

  • Filter: (is_rushed AND (progress >= '30'::numeric) AND ((status)::text = 'U'::text))
  • Rows Removed by Filter: 106998
50. 628.044 628.044 ↑ 1.3 499 526

CTE Scan on scheduled_invoices (cost=0.00..13.20 rows=660 width=235) (actual time=0.900..1.194 rows=499 loops=526)

51. 1.052 237.226 ↑ 6.0 1 526

Subquery Scan on *SELECT* 2 (cost=21.45..21.98 rows=6 width=209) (actual time=0.094..0.451 rows=1 loops=526)

52. 6.338 236.174 ↑ 6.0 1 526

Hash Anti Join (cost=21.45..21.91 rows=6 width=205) (actual time=0.092..0.449 rows=1 loops=526)

  • Hash Cond: ((rushed_invoices.autoid)::text = (scheduled_invoices_1.invoice_autoid)::text)
53. 229.336 229.336 ↑ 1.1 11 526

CTE Scan on rushed_invoices (cost=0.00..0.24 rows=12 width=36) (actual time=0.024..0.436 rows=11 loops=526)

54. 0.251 0.500 ↑ 1.3 499 1

Hash (cost=13.20..13.20 rows=660 width=32) (actual time=0.500..0.500 rows=499 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 32kB
55. 0.249 0.249 ↑ 1.3 499 1

CTE Scan on scheduled_invoices scheduled_invoices_1 (cost=0.00..13.20 rows=660 width=32) (actual time=0.003..0.249 rows=499 loops=1)

56. 4,851.287 77,692.304 ↓ 2.0 17,170 526

Hash Anti Join (cost=21.84..12,884.10 rows=8,649 width=194) (actual time=107.799..147.704 rows=17,170 loops=526)

  • Hash Cond: ((pick_jobs_1.invoice)::text = (rushed_invoices_1.autoid)::text)
57. 4,423.883 72,841.006 ↓ 2.0 17,171 526

Hash Anti Join (cost=21.45..12,774.49 rows=8,655 width=17) (actual time=107.795..138.481 rows=17,171 loops=526)

  • Hash Cond: ((pick_jobs_1.invoice)::text = (scheduled_invoices_2.invoice_autoid)::text)
58. 68,416.820 68,416.820 ↑ 1.0 17,310 526

Seq Scan on pick_jobs pick_jobs_1 (cost=0.00..11,908.10 rows=17,310 width=17) (actual time=107.791..130.070 rows=17,310 loops=526)

59. 0.156 0.303 ↑ 1.3 499 1

Hash (cost=13.20..13.20 rows=660 width=32) (actual time=0.303..0.303 rows=499 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 32kB
60. 0.147 0.147 ↑ 1.3 499 1

CTE Scan on scheduled_invoices scheduled_invoices_2 (cost=0.00..13.20 rows=660 width=32) (actual time=0.003..0.147 rows=499 loops=1)

61. 0.005 0.011 ↑ 1.1 11 1

Hash (cost=0.24..0.24 rows=12 width=32) (actual time=0.011..0.011 rows=11 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
62. 0.006 0.006 ↑ 1.1 11 1

CTE Scan on rushed_invoices rushed_invoices_1 (cost=0.00..0.24 rows=12 width=32) (actual time=0.002..0.006 rows=11 loops=1)

63. 0.869 14.797 ↑ 2.2 1,212 1

Unique (cost=854.09..877.28 rows=2,612 width=79) (actual time=13.419..14.797 rows=1,212 loops=1)

64. 9.953 13.928 ↑ 2.2 1,436 1

Sort (cost=854.09..861.82 rows=3,091 width=79) (actual time=13.415..13.928 rows=1,436 loops=1)

  • Sort Key: pick_jobs_updated.original_invoice, pick_jobs_updated.original_warehouse, pick_jobs_updated.time_updated DESC NULLS LAST
  • Sort Method: quicksort Memory: 170kB
65. 3.975 3.975 ↑ 2.0 1,549 1

Seq Scan on pick_jobs_updated (cost=0.00..674.91 rows=3,091 width=79) (actual time=0.036..3.975 rows=1,549 loops=1)

66. 0.032 0.060 ↑ 1.0 1 30

Materialize (cost=0.00..1.05 rows=1 width=64) (actual time=0.002..0.002 rows=1 loops=30)

67. 0.028 0.028 ↑ 1.0 1 1

Seq Scan on inwh (cost=0.00..1.05 rows=1 width=64) (actual time=0.028..0.028 rows=1 loops=1)

  • Filter: ((autoid)::text = '2GA89EVTVE3SGAD1'::text)
  • Rows Removed by Filter: 2
68. 0.390 0.390 ↓ 0.0 0 30

Index Scan using uc_invoice_warehosue on invoice_pictures (cost=0.27..3.45 rows=1 width=71) (actual time=0.013..0.013 rows=0 loops=30)

  • Index Cond: (((multiwarehouse.invoice)::text = (invoice)::text) AND ((multiwarehouse.warehouse)::text = (warehouse)::text))
69. 0.690 1.140 ↓ 9.8 49 30

Unique (cost=19.76..19.78 rows=5 width=44) (actual time=0.005..0.038 rows=49 loops=30)

70. 0.405 0.450 ↓ 9.8 49 30

Sort (cost=19.76..19.77 rows=5 width=44) (actual time=0.004..0.015 rows=49 loops=30)

  • Sort Key: pick_jobs_shelved.pick_job, pick_jobs_shelved.shelved_time DESC NULLS LAST
  • Sort Method: quicksort Memory: 27kB
71. 0.045 0.045 ↓ 9.8 49 1

Seq Scan on pick_jobs_shelved (cost=0.00..19.70 rows=5 width=44) (actual time=0.024..0.045 rows=49 loops=1)

  • Filter: (unshelved_time IS NULL)
72.          

SubPlan (forNested Loop Left Join)

73. 0.090 1.770 ↑ 1.0 1 30

Aggregate (cost=95.42..95.43 rows=1 width=8) (actual time=0.059..0.059 rows=1 loops=30)

74. 0.300 1.680 ↓ 0.0 0 30

Unique (cost=95.38..95.40 rows=1 width=154) (actual time=0.056..0.056 rows=0 loops=30)

75. 0.750 1.380 ↓ 0.0 0 30

Sort (cost=95.38..95.39 rows=1 width=154) (actual time=0.046..0.046 rows=0 loops=30)

  • Sort Key: arinvdet_1.autoid, arinvdet_1.descr, arinvdet_1.unit_meas, ((gbl_functions.fn_mainunit_to_uom_qty(inventry.id, arinvdet_1.unit_meas, arinvdet_1.quan))::integer), ((NULLIF((arinvdet_1.gpar_time)::text, ''::text))::character varying), (COALESCE(pick_transactions.qty_shipped, 0))
  • Sort Method: quicksort Memory: 25kB
76. 0.060 0.630 ↓ 0.0 0 30

Nested Loop Left Join (cost=1.55..95.37 rows=1 width=154) (actual time=0.021..0.021 rows=0 loops=30)

77. 0.030 0.570 ↓ 0.0 0 30

Nested Loop (cost=1.13..86.68 rows=1 width=132) (actual time=0.019..0.019 rows=0 loops=30)

78. 0.030 0.540 ↓ 0.0 0 30

Nested Loop (cost=0.71..78.23 rows=1 width=132) (actual time=0.018..0.018 rows=0 loops=30)

79. 0.360 0.510 ↓ 0.0 0 30

Nested Loop (cost=0.43..69.91 rows=1 width=130) (actual time=0.017..0.017 rows=0 loops=30)

  • Join Filter: ((arinvdet_1.warehouse)::text = (inwh_1.warehouse)::text)
80. 0.150 0.150 ↓ 0.0 0 30

Seq Scan on inwh inwh_1 (cost=0.00..1.05 rows=1 width=64) (actual time=0.005..0.005 rows=0 loops=30)

  • Filter: ((autoid)::text = (multiwarehouse.warehouse)::text)
  • Rows Removed by Filter: 4
81. 0.000 0.000 ↓ 0.0 0

Index Scan using arinvdet_docaid_idx on arinvdet arinvdet_1 (cost=0.43..68.79 rows=6 width=105) (never executed)

  • Index Cond: ((doc_aid)::text = (multiwarehouse.invoice)::text)
  • Filter: ((quan > 0.0) AND ((id)::text !~~ '($)%'::text))
82. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_btree_inventry_id on inventry (cost=0.29..8.31 rows=1 width=12) (never executed)

  • Index Cond: ((id)::text = (arinvdet_1.inven)::text)
  • Filter: (((c_type <> 1.0) OR (auto_cost IS NOT TRUE)) AND (discou_typ <= 0))
83. 0.000 0.000 ↓ 0.0 0

Index Scan using arinv_pkey on arinv arinv_2 (cost=0.42..8.44 rows=1 width=17) (never executed)

  • Index Cond: ((autoid)::text = (multiwarehouse.invoice)::text)
  • Filter: ((is_rma IS NOT TRUE) AND (progress < '80'::numeric) AND ((status)::text = 'U'::text))
84. 0.000 0.000 ↓ 0.0 0

Index Scan using uc_item on pick_transactions (cost=0.42..8.44 rows=1 width=21) (never executed)

  • Index Cond: ((arinvdet_1.autoid)::text = (item)::text)
85. 0.540 0.660 ↑ 1.0 1 30

Aggregate (cost=2,467.44..2,467.45 rows=1 width=8) (actual time=0.021..0.022 rows=1 loops=30)

86. 0.090 0.120 ↓ 0.0 0 30

Bitmap Heap Scan on pick_transactions pick_transactions_1 (cost=13.76..2,465.72 rows=689 width=17) (actual time=0.004..0.004 rows=0 loops=30)

  • Recheck Cond: (pick_job = pick_jobs.id)
87. 0.030 0.030 ↓ 0.0 0 30

Bitmap Index Scan on idx_btree_picktransactions_pickjob (cost=0.00..13.59 rows=689 width=0) (actual time=0.001..0.001 rows=0 loops=30)

  • Index Cond: (pick_job = pick_jobs.id)
88. 1.140 1.140 ↓ 0.0 0 30

Seq Scan on "user" user_1 (cost=0.00..6.56 rows=1 width=13) (actual time=0.038..0.038 rows=0 loops=30)

  • Filter: (id = pick_jobs_deleted.original_picker)
  • Rows Removed by Filter: 205
89. 0.840 0.840 ↓ 0.0 0 30

Seq Scan on "user" user_1_1 (cost=0.00..6.56 rows=1 width=13) (actual time=0.028..0.028 rows=0 loops=30)

  • Filter: (id = pick_jobs_updated.original_picker)
  • Rows Removed by Filter: 205
Planning time : 15.310 ms
Execution time : 84,226.966 ms