explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gJ7d

Settings
# exclusive inclusive rows x rows loops node
1. 1,738.303 4,583,431.523 ↓ 1.3 47,719 1

GroupAggregate (cost=199,749.99..1,886,520,757.97 rows=37,119 width=195) (actual time=6,577.969..4,583,431.523 rows=47,719 loops=1)

  • Group Key: pp.ad_org_id, p.m_product_id, p.sku, p.user1w_id, p.user1x_id, p.user1y_id, p.user1z_id, p.user1_id, pp.pricelist, (COALESCE(sum(CASE WHEN (o_1.c_doctypetarget_id = 1000300::numeric) THEN ol_1.qtyordered WHEN (o_1.c_doctypetarget_id = 1000301::numeric) THEN (ol_1.qtyordered * (-1)::numeric) ELSE 0::numeric END), 0::numeric)), (COALESCE(sum(CASE WHEN (o.c_doctypetarget_id = 1000300::numeric) THEN ol.qtyordered WHEN (o.c_doctypetarget_id = 1000301::numeric) THEN (ol.qtyordered * (-1)::numeric) ELSE 0::numeric END), 0::numeric)), (COALESCE(sum(r.qtywait), 0::numeric)), p.isactive
2. 3,017.723 7,446.980 ↓ 6.2 229,938 1

Sort (cost=199,749.99..199,842.79 rows=37,119 width=195) (actual time=6,374.217..7,446.980 rows=229,938 loops=1)

  • Sort Key: pp.ad_org_id, p.m_product_id, p.sku, p.user1w_id, p.user1x_id, p.user1y_id, p.user1z_id, p.user1_id, pp.pricelist, (COALESCE(sum(CASE WHEN (o_1.c_doctypetarget_id = 1000300::numeric) THEN ol_1.qtyordered WHEN (o_1.c_doctypetarget_id = 1000301::numeric) THEN (ol_1.qtyordered * (-1)::numeric) ELSE 0::numeric END), 0::numeric)), (COALESCE(sum(CASE WHEN (o.c_doctypetarget_id = 1000300::numeric) THEN ol.qtyordered WHEN (o.c_doctypetarget_id = 1000301::numeric) THEN (ol.qtyordered * (-1)::numeric) ELSE 0::numeric END), 0::numeric)), (COALESCE(sum(r.qtywait), 0::numeric)), p.isactive
  • Sort Method: external merge Disk: 21712kB
3. 215.828 4,429.257 ↓ 6.2 229,938 1

Hash Right Join (cost=191,610.95..193,380.18 rows=37,119 width=195) (actual time=4,214.933..4,429.257 rows=229,938 loops=1)

  • Hash Cond: ((ol.m_product_id = p.m_product_id) AND (o.ad_org_id = pp.ad_org_id))
4. 13.257 621.091 ↑ 2.7 4,932 1

HashAggregate (cost=79,085.48..79,251.52 rows=13,283 width=25) (actual time=619.357..621.091 rows=4,932 loops=1)

  • Group Key: o.ad_org_id, ol.m_product_id
5. 4.237 607.834 ↓ 1.2 16,466 1

Nested Loop (cost=11,338.77..78,886.24 rows=13,283 width=25) (actual time=577.026..607.834 rows=16,466 loops=1)

6. 192.493 597.675 ↑ 3.2 126 1

Hash Join (cost=11,338.33..46,025.55 rows=397 width=20) (actual time=576.993..597.675 rows=126 loops=1)

  • Hash Cond: (o.bsca_route_id = br.bsca_route_id)
7. 206.153 206.153 ↓ 1.0 402,199 1

Seq Scan on c_order o (cost=0.00..33,181.28 rows=400,527 width=27) (actual time=0.040..206.153 rows=402,199 loops=1)

  • Filter: (c_doctypetarget_id = ANY ('{1000300,1000301}'::numeric[]))
  • Rows Removed by Filter: 95103
8. 0.034 199.029 ↓ 1.8 160 1

Hash (cost=11,337.25..11,337.25 rows=87 width=6) (actual time=199.029..199.029 rows=160 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 7kB
9. 11.395 198.995 ↓ 1.8 160 1

Index Scan using docstatus_bsca_route_idx on bsca_route br (cost=10,836.55..11,337.25 rows=87 width=6) (actual time=198.813..198.995 rows=160 loops=1)

  • Index Cond: ((docstatus)::text = 'DR'::text)
  • Filter: (((docstatus)::text ~~ 'DR'::text) AND (NOT (hashed SubPlan 5)))
  • Rows Removed by Filter: 1
10.          

SubPlan (for Index Scan)

11. 117.479 187.600 ↓ 1.3 45,232 1

HashAggregate (cost=10,404.51..10,749.91 rows=34,540 width=6) (actual time=177.199..187.600 rows=45,232 loops=1)

  • Group Key: ro_1.bsca_route_id
12. 70.121 70.121 ↑ 1.0 327,881 1

Seq Scan on bsca_routeout ro_1 (cost=0.00..9,584.81 rows=327,881 width=6) (actual time=0.011..70.121 rows=327,881 loops=1)

13. 5.922 5.922 ↑ 3.5 131 126

Index Scan using c_orderline_order on c_orderline ol (cost=0.43..78.16 rows=461 width=17) (actual time=0.009..0.047 rows=131 loops=126)

  • Index Cond: (c_order_id = o.c_order_id)
14. 284.079 3,592.338 ↓ 6.2 229,938 1

Hash (cost=111,098.68..111,098.68 rows=37,119 width=163) (actual time=3,592.338..3,592.338 rows=229,938 loops=1)

  • Buckets: 4096 Batches: 8 (originally 2) Memory Usage: 4097kB
15. 98.103 3,308.259 ↓ 6.2 229,938 1

Hash Right Join (cost=109,474.45..111,098.68 rows=37,119 width=163) (actual time=3,212.082..3,308.259 rows=229,938 loops=1)

  • Hash Cond: ((ol_1.m_product_id = p.m_product_id) AND (o_1.ad_org_id = pp.ad_org_id))
16. 14.976 1,027.146 ↑ 2.7 4,932 1

HashAggregate (cost=79,085.48..79,251.52 rows=13,283 width=25) (actual time=1,025.443..1,027.146 rows=4,932 loops=1)

  • Group Key: o_1.ad_org_id, ol_1.m_product_id
17. 4.580 1,012.170 ↓ 1.2 16,466 1

Nested Loop (cost=11,338.77..78,886.24 rows=13,283 width=25) (actual time=722.569..1,012.170 rows=16,466 loops=1)

18. 192.254 739.462 ↑ 3.2 126 1

Hash Join (cost=11,338.33..46,025.55 rows=397 width=20) (actual time=702.467..739.462 rows=126 loops=1)

  • Hash Cond: (o_1.bsca_route_id = br_1.bsca_route_id)
19. 229.151 229.151 ↓ 1.0 402,199 1

Seq Scan on c_order o_1 (cost=0.00..33,181.28 rows=400,527 width=27) (actual time=0.029..229.151 rows=402,199 loops=1)

  • Filter: (c_doctypetarget_id = ANY ('{1000300,1000301}'::numeric[]))
  • Rows Removed by Filter: 95103
20. 0.123 318.057 ↓ 1.8 160 1

Hash (cost=11,337.25..11,337.25 rows=87 width=6) (actual time=318.057..318.057 rows=160 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 7kB
21. 118.089 317.934 ↓ 1.8 160 1

Index Scan using docstatus_bsca_route_idx on bsca_route br_1 (cost=10,836.55..11,337.25 rows=87 width=6) (actual time=254.238..317.934 rows=160 loops=1)

  • Index Cond: ((docstatus)::text = 'DR'::text)
  • Filter: (((docstatus)::text ~~ 'DR'::text) AND (NOT (hashed SubPlan 4)))
  • Rows Removed by Filter: 1
22.          

SubPlan (for Index Scan)

23. 115.351 199.845 ↓ 1.3 45,232 1

HashAggregate (cost=10,404.51..10,749.91 rows=34,540 width=6) (actual time=189.854..199.845 rows=45,232 loops=1)

  • Group Key: ro.bsca_route_id
24. 84.494 84.494 ↑ 1.0 327,881 1

Seq Scan on bsca_routeout ro (cost=0.00..9,584.81 rows=327,881 width=6) (actual time=0.013..84.494 rows=327,881 loops=1)

25. 268.128 268.128 ↑ 3.5 131 126

Index Scan using c_orderline_order on c_orderline ol_1 (cost=0.43..78.16 rows=461 width=17) (actual time=1.016..2.128 rows=131 loops=126)

  • Index Cond: (c_order_id = o_1.c_order_id)
26. 262.028 2,183.010 ↓ 6.2 229,938 1

Hash (cost=29,107.18..29,107.18 rows=37,119 width=131) (actual time=2,183.010..2,183.010 rows=229,938 loops=1)

  • Buckets: 4096 Batches: 8 (originally 2) Memory Usage: 4097kB
27. 114.869 1,920.982 ↓ 6.2 229,938 1

Hash Right Join (cost=28,368.34..29,107.18 rows=37,119 width=131) (actual time=1,805.732..1,920.982 rows=229,938 loops=1)

  • Hash Cond: ((r.m_product_id = p.m_product_id) AND (r.ad_org_id = pp.ad_org_id))
28. 15.370 71.046 ↑ 1.4 1,657 1

HashAggregate (cost=885.19..914.35 rows=2,333 width=19) (actual time=70.375..71.046 rows=1,657 loops=1)

  • Group Key: r.ad_org_id, r.m_product_id
29. 55.676 55.676 ↑ 1.0 23,325 1

Seq Scan on bsca_routeoutwait r (cost=0.00..710.25 rows=23,325 width=19) (actual time=17.074..55.676 rows=23,325 loops=1)

30. 346.327 1,735.067 ↓ 6.2 229,938 1

Hash (cost=26,346.37..26,346.37 rows=37,119 width=99) (actual time=1,735.067..1,735.067 rows=229,938 loops=1)

  • Buckets: 4096 Batches: 8 (originally 2) Memory Usage: 4097kB
31. 603.456 1,388.740 ↓ 6.2 229,938 1

Hash Right Join (cost=5,938.97..26,346.37 rows=37,119 width=99) (actual time=428.342..1,388.740 rows=229,938 loops=1)

  • Hash Cond: (("*SELECT* 1".m_product_id = p.m_product_id) AND ("*SELECT* 1".ad_org_id = pp.ad_org_id))
32. 18.448 366.296 ↑ 1.0 233,504 1

Append (cost=0.00..11,630.92 rows=233,532 width=112) (actual time=9.126..366.296 rows=233,504 loops=1)

33. 56.281 281.796 ↑ 1.0 207,557 1

Result (cost=0.00..9,435.85 rows=207,558 width=113) (actual time=9.125..281.796 rows=207,557 loops=1)

34. 15.465 225.515 ↑ 1.0 207,557 1

Append (cost=0.00..9,435.85 rows=207,558 width=113) (actual time=9.122..225.515 rows=207,557 loops=1)

35. 57.909 154.203 ↑ 1.0 207,557 1

Subquery Scan on *SELECT* 1 (cost=0.00..8,143.14 rows=207,557 width=113) (actual time=9.122..154.203 rows=207,557 loops=1)

36. 96.294 96.294 ↑ 1.0 207,557 1

Seq Scan on m_storageonhand s (cost=0.00..6,067.57 rows=207,557 width=113) (actual time=9.110..96.294 rows=207,557 loops=1)

37. 0.000 55.847 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=0.41..1,292.71 rows=1 width=108) (actual time=55.847..55.847 rows=0 loops=1)

38. 0.002 55.847 ↓ 0.0 0 1

Nested Loop (cost=0.41..1,292.70 rows=1 width=108) (actual time=55.847..55.847 rows=0 loops=1)

  • Join Filter: (sr.m_warehouse_id = w.m_warehouse_id)
39. 55.845 55.845 ↓ 0.0 0 1

Index Scan using m_storagereservation_pkey on m_storagereservation sr (cost=0.41..1,289.30 rows=1 width=108) (actual time=55.845..55.845 rows=0 loops=1)

  • Index Cond: (issotrx = 'Y'::bpchar)
  • Filter: (qty <> 0::numeric)
40. 0.000 0.000 ↓ 0.0 0

Seq Scan on m_warehouse w (cost=0.00..2.62 rows=62 width=12) (never executed)

41. 9.197 66.052 ↑ 1.0 25,947 1

Subquery Scan on *SELECT* 3 (cost=3.40..2,195.08 rows=25,974 width=108) (actual time=25.563..66.052 rows=25,947 loops=1)

42. 12.455 56.855 ↑ 1.0 25,947 1

Hash Join (cost=3.40..1,935.34 rows=25,974 width=108) (actual time=25.559..56.855 rows=25,947 loops=1)

  • Hash Cond: (so.m_warehouse_id = w_1.m_warehouse_id)
43. 27.363 27.363 ↑ 1.0 25,947 1

Seq Scan on m_storagereservation so (cost=0.00..1,574.80 rows=25,974 width=108) (actual time=8.504..27.363 rows=25,947 loops=1)

  • Filter: ((qty <> 0::numeric) AND (issotrx = 'N'::bpchar))
  • Rows Removed by Filter: 20573
44. 0.021 17.037 ↑ 1.0 62 1

Hash (cost=2.62..2.62 rows=62 width=12) (actual time=17.037..17.037 rows=62 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 3kB
45. 17.016 17.016 ↑ 1.0 62 1

Seq Scan on m_warehouse w_1 (cost=0.00..2.62 rows=62 width=12) (actual time=16.995..17.016 rows=62 loops=1)

46. 53.558 418.988 ↓ 1.3 47,719 1

Hash (cost=5,382.18..5,382.18 rows=37,119 width=67) (actual time=418.988..418.988 rows=47,719 loops=1)

  • Buckets: 4096 Batches: 2 (originally 1) Memory Usage: 4097kB
47. 46.470 365.430 ↓ 1.3 47,719 1

Hash Right Join (cost=1,718.39..5,382.18 rows=37,119 width=67) (actual time=154.140..365.430 rows=47,719 loops=1)

  • Hash Cond: (pp.m_product_id = p.m_product_id)
48. 182.063 182.063 ↓ 1.0 37,338 1

Seq Scan on m_productprice pp (cost=0.00..2,921.41 rows=37,119 width=17) (actual time=17.212..182.063 rows=37,338 loops=1)

  • Filter: (bsca_pricechange_id IS NOT NULL)
  • Rows Removed by Filter: 48203
49. 8.718 136.897 ↑ 1.0 22,773 1

Hash (cost=1,433.73..1,433.73 rows=22,773 width=56) (actual time=136.897..136.897 rows=22,773 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 1951kB
50. 128.179 128.179 ↑ 1.0 22,773 1

Seq Scan on m_product p (cost=0.00..1,433.73 rows=22,773 width=56) (actual time=4.843..128.179 rows=22,773 loops=1)

51.          

SubPlan (for GroupAggregate)

52. 143.157 1,877,074.584 ↑ 1.0 1 47,719

Limit (cost=16,938.69..16,938.70 rows=1 width=14) (actual time=39.335..39.336 rows=1 loops=47,719)

53. 763.504 1,876,931.427 ↑ 119.0 1 47,719

Sort (cost=16,938.69..16,938.99 rows=119 width=14) (actual time=39.333..39.333 rows=1 loops=47,719)

  • Sort Key: inl.created
  • Sort Method: quicksort Memory: 25kB
54. 37,400.151 1,876,167.923 ↑ 29.8 4 47,719

Hash Semi Join (cost=10,259.31..16,938.10 rows=119 width=14) (actual time=28.307..39.317 rows=4 loops=47,719)

  • Hash Cond: (inl.c_invoice_id = c_invoice.c_invoice_id)
55. 623,320.572 633,327.156 ↑ 1.5 1,181 37,338

Bitmap Heap Scan on c_invoiceline inl (cost=34.08..6,706.57 rows=1,761 width=20) (actual time=0.437..16.962 rows=1,181 loops=37,338)

  • Recheck Cond: (m_product_id = p.m_product_id)
  • Rows Removed by Index Recheck: 517
  • Heap Blocks: exact=39421953 lossy=677045
56. 10,006.584 10,006.584 ↑ 1.5 1,181 37,338

Bitmap Index Scan on c_invoiceline_product (cost=0.00..33.64 rows=1,761 width=0) (actual time=0.268..0.268 rows=1,181 loops=37,338)

  • Index Cond: (m_product_id = p.m_product_id)
57. 51,278.124 1,205,440.616 ↓ 2.2 5,428 47,612

Hash (cost=10,194.64..10,194.64 rows=2,447 width=6) (actual time=25.318..25.318 rows=5,428 loops=47,612)

  • Buckets: 1024 Batches: 1 Memory Usage: 0kB
58. 1,154,162.492 1,154,162.492 ↓ 2.2 5,428 47,612

Index Scan using c_doctypetarget_id_c_invoice_idx on c_invoice (cost=0.42..10,194.64 rows=2,447 width=6) (actual time=5.019..24.241 rows=5,428 loops=47,612)

  • Index Cond: (c_doctypetarget_id = ANY ('{1000143,1000204,1000327,1000342}'::numeric[]))
  • Filter: ((docstatus = ANY ('{CO,CL}'::bpchar[])) AND (ad_org_id = pp.ad_org_id))
  • Rows Removed by Filter: 27857
59. 143.157 1,372,398.440 ↑ 1.0 1 47,719

Limit (cost=16,940.74..16,940.74 rows=1 width=16) (actual time=28.760..28.760 rows=1 loops=47,719)

60. 668.066 1,372,255.283 ↑ 9.0 1 47,719

Sort (cost=16,940.74..16,940.76 rows=9 width=16) (actual time=28.757..28.757 rows=1 loops=47,719)

  • Sort Key: inl_1.created
  • Sort Method: quicksort Memory: 25kB
61. 28,701.309 1,371,587.217 ↑ 2.2 4 47,719

Hash Join (cost=10,259.31..16,940.69 rows=9 width=16) (actual time=26.980..28.743 rows=4 loops=47,719)

  • Hash Cond: (inl_1.c_invoice_id = iv.c_invoice_id)
62. 97,041.462 105,069.132 ↑ 1.5 1,181 37,338

Bitmap Heap Scan on c_invoiceline inl_1 (cost=34.08..6,706.57 rows=1,761 width=14) (actual time=0.370..2.814 rows=1,181 loops=37,338)

  • Recheck Cond: (m_product_id = p.m_product_id)
  • Rows Removed by Index Recheck: 517
  • Heap Blocks: exact=39421953 lossy=677045
63. 8,027.670 8,027.670 ↑ 1.5 1,181 37,338

Bitmap Index Scan on c_invoiceline_product (cost=0.00..33.64 rows=1,761 width=0) (actual time=0.215..0.215 rows=1,181 loops=37,338)

  • Index Cond: (m_product_id = p.m_product_id)
64. 53,658.724 1,237,816.776 ↓ 2.2 5,428 47,612

Hash (cost=10,194.64..10,194.64 rows=2,447 width=14) (actual time=25.998..25.998 rows=5,428 loops=47,612)

  • Buckets: 1024 Batches: 1 Memory Usage: 0kB
65. 1,184,158.052 1,184,158.052 ↓ 2.2 5,428 47,612

Index Scan using c_doctypetarget_id_c_invoice_idx on c_invoice iv (cost=0.42..10,194.64 rows=2,447 width=14) (actual time=5.073..24.871 rows=5,428 loops=47,612)

  • Index Cond: (c_doctypetarget_id = ANY ('{1000143,1000204,1000327,1000342}'::numeric[]))
  • Filter: ((docstatus = ANY ('{CO,CL}'::bpchar[])) AND (ad_org_id = pp.ad_org_id))
  • Rows Removed by Filter: 27857
66. 142.992 1,324,773.216 ↑ 1.0 1 47,664

Limit (cost=16,938.69..16,938.70 rows=1 width=14) (actual time=27.794..27.794 rows=1 loops=47,664)

67. 667.296 1,324,630.224 ↑ 119.0 1 47,664

Sort (cost=16,938.69..16,938.99 rows=119 width=14) (actual time=27.791..27.791 rows=1 loops=47,664)

  • Sort Key: inl_2.created
  • Sort Method: quicksort Memory: 25kB
68. 27,359.165 1,323,962.928 ↑ 29.8 4 47,664

Hash Semi Join (cost=10,259.31..16,938.10 rows=119 width=14) (actual time=26.172..27.777 rows=4 loops=47,664)

  • Hash Cond: (inl_2.c_invoice_id = c_invoice_1.c_invoice_id)
69. 85,750.900 93,692.179 ↑ 1.5 1,183 37,283

Bitmap Heap Scan on c_invoiceline inl_2 (cost=34.08..6,706.57 rows=1,761 width=20) (actual time=0.368..2.513 rows=1,183 loops=37,283)

  • Recheck Cond: (m_product_id = p.m_product_id)
  • Rows Removed by Index Recheck: 518
  • Heap Blocks: exact=39417384 lossy=677045
70. 7,941.279 7,941.279 ↑ 1.5 1,183 37,283

Bitmap Index Scan on c_invoiceline_product (cost=0.00..33.64 rows=1,761 width=0) (actual time=0.213..0.213 rows=1,183 loops=37,283)

  • Index Cond: (m_product_id = p.m_product_id)
71. 50,858.744 1,202,911.584 ↓ 2.2 5,426 47,576

Hash (cost=10,194.64..10,194.64 rows=2,447 width=6) (actual time=25.284..25.284 rows=5,426 loops=47,576)

  • Buckets: 1024 Batches: 1 Memory Usage: 0kB
72. 1,152,052.840 1,152,052.840 ↓ 2.2 5,426 47,576

Index Scan using c_doctypetarget_id_c_invoice_idx on c_invoice c_invoice_1 (cost=0.42..10,194.64 rows=2,447 width=6) (actual time=5.011..24.215 rows=5,426 loops=47,576)

  • Index Cond: (c_doctypetarget_id = ANY ('{1000143,1000204,1000327,1000342}'::numeric[]))
  • Filter: ((docstatus = ANY ('{CO,CL}'::bpchar[])) AND (ad_org_id = pp.ad_org_id))
  • Rows Removed by Filter: 27859
Planning time : 577.788 ms
Execution time : 4,583,529.705 ms