explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hV13

Settings
# exclusive inclusive rows x rows loops node
1. 0.010 6,330.477 ↑ 6.6 142 1

Append (cost=151,535.17..152,506.94 rows=935 width=354) (actual time=5,212.026..6,330.477 rows=142 loops=1)

2.          

CTE temp_sales

3. 0.048 4,846.274 ↓ 10.1 151 1

Append (cost=49,237.20..97,920.30 rows=15 width=157) (actual time=4,738.268..4,846.274 rows=151 loops=1)

4. 1.184 4,738.425 ↓ 20.2 121 1

HashAggregate (cost=49,237.20..49,237.29 rows=6 width=35) (actual time=4,738.258..4,738.425 rows=121 loops=1)

  • Group Key: soitem.sales_order_id_
5. 0.463 4,737.241 ↓ 25.7 154 1

Nested Loop Left Join (cost=20.59..49,237.13 rows=6 width=35) (actual time=33.922..4,737.241 rows=154 loops=1)

6. 0.597 4,667.632 ↓ 25.7 154 1

Nested Loop (cost=20.04..49,222.27 rows=6 width=68) (actual time=33.886..4,667.632 rows=154 loops=1)

  • Join Filter: ((itemprogress.order_item_id_)::text = (item.order_item_id_)::text)
7. 11.999 4,547.069 ↓ 25.7 154 1

Nested Loop (cost=19.48..49,206.61 rows=6 width=100) (actual time=33.836..4,547.069 rows=154 loops=1)

8. 8.749 723.496 ↓ 1.1 9,458 1

Nested Loop (cost=18.91..24,129.93 rows=8,818 width=58) (actual time=30.059..723.496 rows=9,458 loops=1)

9. 67.757 67.757 ↓ 156.3 2,813 1

Index Scan using order_info_idx02 on order_info orderinfo_2 (cost=0.56..7,012.45 rows=18 width=37) (actual time=2.190..67.757 rows=2,813 loops=1)

  • Index Cond: (((site_id_)::text = 'JA0004'::text) AND ((non_serial_item_flag_)::text = '1'::text) AND ((order_date_)::text >= '20190601'::text) AND ((order_date_)::text <= '20190701'::text))
  • Filter: ((delivery_facility_)::text = 'af2ff7d0-ece9-463f-9c62-52fe4a627520'::text)
10. 64.699 646.990 ↑ 163.3 3 2,813

Bitmap Heap Scan on sales_order_item soitem (cost=18.36..946.07 rows=490 width=58) (actual time=0.228..0.230 rows=3 loops=2,813)

  • Recheck Cond: ((sales_order_id_)::text = (orderinfo_2.order_id_)::text)
  • Heap Blocks: exact=2713
11. 582.291 582.291 ↑ 163.3 3 2,813

Bitmap Index Scan on sales_order_item_idx02 (cost=0.00..18.23 rows=490 width=0) (actual time=0.207..0.207 rows=3 loops=2,813)

  • Index Cond: ((sales_order_id_)::text = (orderinfo_2.order_id_)::text)
12. 3,811.574 3,811.574 ↓ 0.0 0 9,458

Index Scan using order_item_progress_idx01 on order_item_progress itemprogress (cost=0.56..2.83 rows=1 width=42) (actual time=0.403..0.403 rows=0 loops=9,458)

  • Index Cond: (((order_item_id_)::text = (soitem.order_item_id_)::text) AND ((order_item_progress_type_id_)::text = 'C041BACKORDERQTY'::text))
13. 119.966 119.966 ↑ 1.0 1 154

Index Scan using pk_retail_order_item on order_item item (cost=0.56..2.60 rows=1 width=79) (actual time=0.778..0.779 rows=1 loops=154)

  • Index Cond: ((order_item_id_)::text = (soitem.order_item_id_)::text)
14. 69.146 69.146 ↓ 0.0 0 154

Index Scan using product_stock_status_idx02 on product_stock_status stock (cost=0.55..2.47 rows=1 width=40) (actual time=0.449..0.449 rows=0 loops=154)

  • Index Cond: (((site_id_)::text = 'JA0004'::text) AND ((facility_id_)::text = 'af2ff7d0-ece9-463f-9c62-52fe4a627520'::text) AND ((product_id_)::text = (item.product_id_)::text) AND ((product_stock_status_type_id_)::text = 'C071ONHANDQTY'::text))
  • Filter: (quantity_ > 0::numeric)
  • Rows Removed by Filter: 1
15. 0.069 107.801 ↓ 3.3 30 1

HashAggregate (cost=48,682.77..48,682.86 rows=9 width=21) (actual time=107.785..107.801 rows=30 loops=1)

  • Group Key: soitem_1.sales_order_id_
16. 0.120 107.732 ↓ 8.7 78 1

Nested Loop (cost=20.04..48,682.75 rows=9 width=21) (actual time=27.066..107.732 rows=78 loops=1)

  • Join Filter: ((itemprogress_1.order_item_id_)::text = (item_1.order_item_id_)::text)
17. 6.257 106.988 ↓ 8.7 78 1

Nested Loop (cost=19.48..48,659.97 rows=9 width=95) (actual time=27.039..106.988 rows=78 loops=1)

18. 2.222 43.983 ↓ 1.1 9,458 1

Nested Loop (cost=18.91..24,129.93 rows=8,818 width=58) (actual time=16.133..43.983 rows=9,458 loops=1)

19. 19.257 19.257 ↓ 156.3 2,813 1

Index Scan using order_info_idx02 on order_info orderinfo_3 (cost=0.56..7,012.45 rows=18 width=37) (actual time=0.991..19.257 rows=2,813 loops=1)

  • Index Cond: (((site_id_)::text = 'JA0004'::text) AND ((non_serial_item_flag_)::text = '1'::text) AND ((order_date_)::text >= '20190601'::text) AND ((order_date_)::text <= '20190701'::text))
  • Filter: ((delivery_facility_)::text = 'af2ff7d0-ece9-463f-9c62-52fe4a627520'::text)
20. 5.626 22.504 ↑ 163.3 3 2,813

Bitmap Heap Scan on sales_order_item soitem_1 (cost=18.36..946.07 rows=490 width=58) (actual time=0.007..0.008 rows=3 loops=2,813)

  • Recheck Cond: ((sales_order_id_)::text = (orderinfo_3.order_id_)::text)
  • Heap Blocks: exact=2713
21. 16.878 16.878 ↑ 163.3 3 2,813

Bitmap Index Scan on sales_order_item_idx02 (cost=0.00..18.23 rows=490 width=0) (actual time=0.006..0.006 rows=3 loops=2,813)

  • Index Cond: ((sales_order_id_)::text = (orderinfo_3.order_id_)::text)
22. 56.748 56.748 ↓ 0.0 0 9,458

Index Only Scan using order_item_progress_idx01 on order_item_progress itemprogress_1 (cost=0.56..2.77 rows=1 width=37) (actual time=0.006..0.006 rows=0 loops=9,458)

  • Index Cond: ((order_item_id_ = (soitem_1.order_item_id_)::text) AND (order_item_progress_type_id_ = 'C041ALLOCATEDQTY'::text))
  • Heap Fetches: 78
23. 0.624 0.624 ↑ 1.0 1 78

Index Only Scan using pk_retail_order_item on order_item item_1 (cost=0.56..2.52 rows=1 width=37) (actual time=0.008..0.008 rows=1 loops=78)

  • Index Cond: (order_item_id_ = (soitem_1.order_item_id_)::text)
  • Heap Fetches: 78
24.          

CTE temp_service

25. 0.002 1,118.375 ↓ 0.0 0 1

Append (cost=7,371.77..39,681.54 rows=6 width=173) (actual time=1,118.375..1,118.375 rows=0 loops=1)

26. 0.001 31.293 ↓ 0.0 0 1

HashAggregate (cost=7,371.77..7,371.78 rows=1 width=51) (actual time=31.293..31.293 rows=0 loops=1)

  • Group Key: soitem_2.service_order_id_
27. 0.001 31.292 ↓ 0.0 0 1

Nested Loop Left Join (cost=7.27..7,371.75 rows=1 width=51) (actual time=31.292..31.292 rows=0 loops=1)

28. 0.001 31.291 ↓ 0.0 0 1

Nested Loop (cost=6.72..7,369.28 rows=1 width=84) (actual time=31.291..31.291 rows=0 loops=1)

  • Join Filter: ((itemprogress_2.order_item_id_)::text = (item_2.order_item_id_)::text)
29. 0.000 31.290 ↓ 0.0 0 1

Nested Loop (cost=6.16..7,366.67 rows=1 width=116) (actual time=31.290..31.290 rows=0 loops=1)

30. 0.001 31.290 ↓ 0.0 0 1

Nested Loop (cost=5.60..7,190.35 rows=62 width=74) (actual time=31.290..31.290 rows=0 loops=1)

31. 31.289 31.289 ↓ 0.0 0 1

Index Scan using order_info_idx03 on order_info orderinfo_4 (cost=0.56..7,061.26 rows=1 width=37) (actual time=31.289..31.289 rows=0 loops=1)

  • Index Cond: (((order_date_)::text >= '20190601'::text) AND ((order_date_)::text <= '20190701'::text) AND ((site_id_)::text = 'JA0004'::text) AND ((delivery_facility_)::text = 'af2ff7d0-ece9-463f-9c62-52fe4a627520'::text))
  • Filter: (((order_status_)::text = ANY ('{C116WAITFORSTART,C116WAITFORFINISH}'::text[])) AND ((service_flag_)::text = '1'::text))
  • Rows Removed by Filter: 3234
32. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on sales_order_item soitem_2 (cost=5.04..128.48 rows=62 width=74) (never executed)

  • Recheck Cond: ((service_order_id_)::text = (orderinfo_4.order_id_)::text)
33. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on sales_order_item_idx03 (cost=0.00..5.02 rows=62 width=0) (never executed)

  • Index Cond: ((service_order_id_)::text = (orderinfo_4.order_id_)::text)
34. 0.000 0.000 ↓ 0.0 0

Index Scan using order_item_progress_idx01 on order_item_progress itemprogress_2 (cost=0.56..2.83 rows=1 width=42) (never executed)

  • Index Cond: (((order_item_id_)::text = (soitem_2.order_item_id_)::text) AND ((order_item_progress_type_id_)::text = 'C041BACKORDERQTY'::text))
35. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_retail_order_item on order_item item_2 (cost=0.56..2.60 rows=1 width=79) (never executed)

  • Index Cond: ((order_item_id_)::text = (soitem_2.order_item_id_)::text)
36. 0.000 0.000 ↓ 0.0 0

Index Scan using product_stock_status_idx02 on product_stock_status stock_1 (cost=0.55..2.47 rows=1 width=40) (never executed)

  • Index Cond: (((site_id_)::text = 'JA0004'::text) AND ((facility_id_)::text = 'af2ff7d0-ece9-463f-9c62-52fe4a627520'::text) AND ((product_id_)::text = (item_2.product_id_)::text) AND ((product_stock_status_type_id_)::text = 'C071ONHANDQTY'::text))
  • Filter: (quantity_ > 0::numeric)
37. 0.002 1,087.080 ↓ 0.0 0 1

HashAggregate (cost=32,309.65..32,309.70 rows=5 width=37) (actual time=1,087.080..1,087.080 rows=0 loops=1)

  • Group Key: soitem_3.service_order_id_
38. 0.001 1,087.078 ↓ 0.0 0 1

Nested Loop (cost=6.72..32,309.64 rows=5 width=37) (actual time=1,087.078..1,087.078 rows=0 loops=1)

  • Join Filter: ((itemprogress_3.order_item_id_)::text = (item_3.order_item_id_)::text)
39. 1.571 1,087.077 ↓ 0.0 0 1

Nested Loop (cost=6.16..32,296.98 rows=5 width=111) (actual time=1,087.077..1,087.077 rows=0 loops=1)

40. 1.578 472.428 ↑ 5.4 967 1

Nested Loop (cost=5.60..17,739.75 rows=5,233 width=74) (actual time=5.449..472.428 rows=967 loops=1)

41. 33.310 33.310 ↓ 4.0 334 1

Index Scan using order_info_idx03 on order_info orderinfo_5 (cost=0.56..7,061.00 rows=84 width=37) (actual time=4.573..33.310 rows=334 loops=1)

  • Index Cond: (((order_date_)::text >= '20190601'::text) AND ((order_date_)::text <= '20190701'::text) AND ((site_id_)::text = 'JA0004'::text) AND ((delivery_facility_)::text = 'af2ff7d0-ece9-463f-9c62-52fe4a627520'::text))
  • Filter: ((service_flag_)::text = '1'::text)
  • Rows Removed by Filter: 2900
42. 19.372 437.540 ↑ 20.7 3 334

Bitmap Heap Scan on sales_order_item soitem_3 (cost=5.04..126.51 rows=62 width=74) (actual time=1.297..1.310 rows=3 loops=334)

  • Recheck Cond: ((service_order_id_)::text = (orderinfo_5.order_id_)::text)
  • Heap Blocks: exact=439
43. 418.168 418.168 ↑ 20.7 3 334

Bitmap Index Scan on sales_order_item_idx03 (cost=0.00..5.02 rows=62 width=0) (actual time=1.252..1.252 rows=3 loops=334)

  • Index Cond: ((service_order_id_)::text = (orderinfo_5.order_id_)::text)
44. 613.078 613.078 ↓ 0.0 0 967

Index Only Scan using order_item_progress_idx01 on order_item_progress itemprogress_3 (cost=0.56..2.77 rows=1 width=37) (actual time=0.634..0.634 rows=0 loops=967)

  • Index Cond: ((order_item_id_ = (soitem_3.order_item_id_)::text) AND (order_item_progress_type_id_ = 'C041ALLOCATEDQTY'::text))
  • Heap Fetches: 0
45. 0.000 0.000 ↓ 0.0 0

Index Only Scan using pk_retail_order_item on order_item item_3 (cost=0.56..2.52 rows=1 width=37) (never executed)

  • Index Cond: (order_item_id_ = (soitem_3.order_item_id_)::text)
  • Heap Fetches: 0
46. 0.265 5,212.081 ↑ 5.2 142 1

HashAggregate (cost=13,933.32..13,942.51 rows=735 width=337) (actual time=5,212.025..5,212.081 rows=142 loops=1)

  • Group Key: tempsales.sales_order_id_, orderinfo.order_category_id_, orderinfo.order_no_, orderinfo.order_date_, salesorder.feature_3_, salesorder.feature_4_, tempsales.salestype, salesorder.order_phone_no_, salesorder.feature_15_, orderinfo.comment_, count(1), sum((orderitem.quantity_ * orderitem.price_))
47. 3.091 5,211.816 ↑ 48.7 151 1

HashAggregate (cost=13,474.01..13,565.87 rows=7,349 width=308) (actual time=5,211.731..5,211.816 rows=151 loops=1)

  • Group Key: tempsales.sales_order_id_, orderinfo.order_category_id_, orderinfo.order_no_, orderinfo.order_date_, salesorder.feature_3_, salesorder.feature_4_, tempsales.backorderlines, tempsales.backorderamount, tempsales.boflag, tempsales.pickflag, tempsales.salestype, salesorder.order_phone_no_, salesorder.feature_15_, orderinfo.comment_
48. 1.046 5,208.725 ↑ 9.8 749 1

Nested Loop (cost=2.10..13,161.67 rows=7,349 width=308) (actual time=4,740.567..5,208.725 rows=749 loops=1)

49. 0.485 4,981.481 ↑ 9.8 749 1

Nested Loop (cost=1.54..2,922.40 rows=7,349 width=334) (actual time=4,740.242..4,981.481 rows=749 loops=1)

  • Join Filter: ((tempsales.sales_order_id_)::text = (salesitem.sales_order_id_)::text)
50. 0.385 4,978.580 ↓ 10.1 151 1

Nested Loop Left Join (cost=0.98..90.22 rows=15 width=334) (actual time=4,740.196..4,978.580 rows=151 loops=1)

51. 0.305 4,922.174 ↓ 10.1 151 1

Nested Loop (cost=0.42..67.13 rows=15 width=309) (actual time=4,739.548..4,922.174 rows=151 loops=1)

52. 4,846.520 4,846.520 ↓ 10.1 151 1

CTE Scan on temp_sales tempsales (cost=0.00..0.30 rows=15 width=234) (actual time=4,738.271..4,846.520 rows=151 loops=1)

53. 75.349 75.349 ↑ 1.0 1 151

Index Scan using pk_sales_order on sales_order salesorder (cost=0.42..4.45 rows=1 width=75) (actual time=0.499..0.499 rows=1 loops=151)

  • Index Cond: ((order_id_)::text = (tempsales.sales_order_id_)::text)
54. 56.021 56.021 ↑ 1.0 1 151

Index Scan using pk_retail_order on order_info orderinfo (cost=0.56..1.53 rows=1 width=76) (actual time=0.367..0.371 rows=1 loops=151)

  • Index Cond: ((salesorder.order_id_)::text = (order_id_)::text)
  • Filter: ((salesorder.site_id_)::text = (site_id_)::text)
55. 2.416 2.416 ↑ 98.0 5 151

Index Only Scan using sales_order_item_idx02 on sales_order_item salesitem (cost=0.56..182.69 rows=490 width=58) (actual time=0.012..0.016 rows=5 loops=151)

  • Index Cond: (sales_order_id_ = (salesorder.order_id_)::text)
  • Heap Fetches: 749
56. 226.198 226.198 ↑ 1.0 1 749

Index Scan using pk_retail_order_item on order_item orderitem (cost=0.56..1.38 rows=1 width=48) (actual time=0.299..0.302 rows=1 loops=749)

  • Index Cond: ((order_item_id_)::text = (salesitem.order_item_id_)::text)
57. 0.002 1,118.386 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=950.73..955.23 rows=200 width=415) (actual time=1,118.386..1,118.386 rows=0 loops=1)

58. 0.001 1,118.384 ↓ 0.0 0 1

HashAggregate (cost=950.73..953.23 rows=200 width=415) (actual time=1,118.384..1,118.384 rows=0 loops=1)

  • Group Key: tempservice.service_order_id_, orderinfo_1.order_category_id_, orderinfo_1.order_no_, orderinfo_1.order_date_, (''::text), serviceorder.consumer_name_, tempservice.salestype, (''::text), (''::text), orderinfo_1.comment_, count(1), sum((orderitem_1.quantity_ * orderitem_1.price_))
59. 0.004 1,118.383 ↓ 0.0 0 1

HashAggregate (cost=927.36..932.03 rows=374 width=290) (actual time=1,118.383..1,118.383 rows=0 loops=1)

  • Group Key: tempservice.service_order_id_, orderinfo_1.order_category_id_, orderinfo_1.order_no_, orderinfo_1.order_date_, ''::text, serviceorder.consumer_name_, tempservice.backorderlines, tempservice.backorderamount, tempservice.boflag, tempservice.pickflag, tempservice.salestype, ''::text, ''::text, orderinfo_1.comment_
60. 0.000 1,118.379 ↓ 0.0 0 1

Nested Loop (cost=2.23..911.46 rows=374 width=290) (actual time=1,118.379..1,118.379 rows=0 loops=1)

61. 0.000 1,118.379 ↓ 0.0 0 1

Nested Loop (cost=1.67..280.22 rows=374 width=316) (actual time=1,118.379..1,118.379 rows=0 loops=1)

  • Join Filter: ((tempservice.service_order_id_)::text = (salesitem_1.service_order_id_)::text)
62. 0.002 1,118.379 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.11..36.41 rows=6 width=316) (actual time=1,118.379..1,118.379 rows=0 loops=1)

63. 0.000 1,118.377 ↓ 0.0 0 1

Nested Loop (cost=0.56..27.63 rows=6 width=291) (actual time=1,118.377..1,118.377 rows=0 loops=1)

64. 1,118.377 1,118.377 ↓ 0.0 0 1

CTE Scan on temp_service tempservice (cost=0.00..0.12 rows=6 width=234) (actual time=1,118.377..1,118.377 rows=0 loops=1)

65. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_service_order on service_order serviceorder (cost=0.56..4.58 rows=1 width=57) (never executed)

  • Index Cond: ((order_id_)::text = (tempservice.service_order_id_)::text)
66. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_retail_order on order_info orderinfo_1 (cost=0.56..1.45 rows=1 width=76) (never executed)

  • Index Cond: ((serviceorder.order_id_)::text = (order_id_)::text)
  • Filter: ((serviceorder.site_id_)::text = (site_id_)::text)
67. 0.000 0.000 ↓ 0.0 0

Index Only Scan using sales_order_item_idx03 on sales_order_item salesitem_1 (cost=0.56..39.86 rows=62 width=74) (never executed)

  • Index Cond: (service_order_id_ = (serviceorder.order_id_)::text)
  • Heap Fetches: 0
68. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_retail_order_item on order_item orderitem_1 (cost=0.56..1.68 rows=1 width=48) (never executed)

  • Index Cond: ((order_item_id_)::text = (salesitem_1.order_item_id_)::text)
Planning time : 13.483 ms
Execution time : 6,331.212 ms