explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EC3g

Settings
# exclusive inclusive rows x rows loops node
1. 13,521.431 1,062,991.976 ↓ 50.0 100 1

Limit (cost=1,621,660.81..1,621,661.22 rows=2 width=1,082) (actual time=1,062,989.414..1,062,991.976 rows=100 loops=1)

  • Functions: 376
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 64.014 ms, Inlining 94.084 ms, Optimization 8155.215 ms, Emission 5239.796 ms, Total 13553.108 ms
2.          

CTE pickups_and_deliveries_view2

3. 67.250 1,048,392.727 ↓ 36,661.5 73,323 1

Append (cost=1,621,658.49..1,621,660.76 rows=2 width=2,197) (actual time=477.847..1,048,392.727 rows=73,323 loops=1)

4.          

CTE delivery_list_view2

5. 1,665.894 1,046,861.274 ↓ 36,519.0 73,038 1

Nested Loop Left Join (cost=893,577.75..900,169.08 rows=2 width=2,625) (actual time=4,379.858..1,046,861.274 rows=73,038 loops=1)

6.          

CTE delivery_all_view2

7. 171.828 4,267.239 ↓ 6,086.6 73,039 1

Nested Loop (cost=1.12..893,163.64 rows=12 width=274) (actual time=0.990..4,267.239 rows=73,039 loops=1)

8. 3,292.466 3,876.294 ↓ 1,432.1 73,039 1

Nested Loop (cost=0.56..892,924.43 rows=51 width=190) (actual time=0.818..3,876.294 rows=73,039 loops=1)

  • Join Filter: ((tt_event_teck.gscode(h.geo_status, h.reason_code1, h.reason_code2, h.reason_code3))::text = (gs_status.gs_code)::text)
  • Rows Removed by Join Filter: 803465
9. 15.941 510.786 ↓ 45.8 73,042 1

Nested Loop (cost=0.56..887,835.72 rows=1,594 width=162) (actual time=0.620..510.786 rows=73,042 loops=1)

10. 62.445 62.445 ↓ 1.0 108,100 1

Seq Scan on scandata (cost=0.00..3,892.87 rows=108,087 width=98) (actual time=0.016..62.445 rows=108,100 loops=1)

11. 432.400 432.400 ↑ 1.0 1 108,100

Index Scan using pk_ttevents_header on tt_event_header h (cost=0.56..8.18 rows=1 width=64) (actual time=0.004..0.004 rows=1 loops=108,100)

  • Index Cond: (id = scandata.tt_event_header)
  • Filter: (((('2019-06-12'::cstring)::date IS NULL) OR (status_date_time >= ('2019-06-12'::cstring)::date)) AND ((('2019-06-13'::cstring)::date IS NULL) OR (status_date_time < ('2019-06-13'::cstring)::date)))
  • Rows Removed by Filter: 0
12. 72.930 73.042 ↑ 1.0 12 73,042

Materialize (cost=0.00..19.82 rows=12 width=32) (actual time=0.000..0.001 rows=12 loops=73,042)

13. 0.112 0.112 ↑ 1.0 12 1

Seq Scan on gs_status (cost=0.00..19.76 rows=12 width=32) (actual time=0.012..0.112 rows=12 loops=1)

  • Filter: (delivery_scan IS TRUE)
  • Rows Removed by Filter: 364
14. 219.117 219.117 ↑ 1.0 1 73,039

Index Scan using tt_event_additional_tt_event_header_idx on tt_event_additional additional (cost=0.56..4.68 rows=1 width=82) (actual time=0.003..0.003 rows=1 loops=73,039)

  • Index Cond: (tt_event_header = h.id)
15.          

Initplan (forNested Loop Left Join)

16. 0.007 0.007 ↑ 1.0 1 1

Seq Scan on warning_times_setting (cost=0.00..1.01 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=1)

17. 360.006 558,981.407 ↓ 36,519.0 73,038 1

Nested Loop Left Join (cost=412.82..6,986.71 rows=2 width=2,777) (actual time=4,379.802..558,981.407 rows=73,038 loops=1)

18. 475.182 557,744.945 ↓ 36,519.0 73,038 1

Nested Loop Left Join (cost=412.38..6,985.66 rows=2 width=2,730) (actual time=4,379.784..557,744.945 rows=73,038 loops=1)

19. 200.036 556,028.117 ↓ 36,519.0 73,038 1

Nested Loop Left Join (cost=345.14..6,835.35 rows=2 width=2,747) (actual time=4,379.768..556,028.117 rows=73,038 loops=1)

  • Join Filter: (((detour_address.parcelno)::text = (dv.parcel_number)::text) AND (detour_address.insert_date = (SubPlan 5)))
20. 727.257 555,828.081 ↓ 36,519.0 73,038 1

Nested Loop Left Join (cost=344.86..6,808.69 rows=2 width=2,732) (actual time=4,379.649..555,828.081 rows=73,038 loops=1)

  • Join Filter: (dv.customerno IS NULL)
21. 291.674 551,156.772 ↓ 73,038.0 73,038 1

Nested Loop Left Join (cost=1.68..45.45 rows=1 width=2,723) (actual time=4,379.521..551,156.772 rows=73,038 loops=1)

22. 392.965 549,842.566 ↓ 73,038.0 73,038 1

Nested Loop Left Join (cost=1.11..44.44 rows=1 width=2,717) (actual time=4,379.469..549,842.566 rows=73,038 loops=1)

23. 497.647 547,842.765 ↓ 73,038.0 73,038 1

Nested Loop Left Join (cost=0.55..35.86 rows=1 width=2,709) (actual time=4,379.410..547,842.765 rows=73,038 loops=1)

24. 533.281 546,687.776 ↓ 73,038.0 73,038 1

Nested Loop Left Join (cost=0.28..21.26 rows=1 width=2,642) (actual time=4,379.368..546,687.776 rows=73,038 loops=1)

25. 918.375 545,058.925 ↓ 73,038.0 73,038 1

CTE Scan on delivery_all_view2 dv (cost=0.00..4.64 rows=1 width=2,634) (actual time=4,379.346..545,058.925 rows=73,038 loops=1)

  • Filter: (((('2019-06-12'::cstring)::date IS NULL) OR (status_date >= ('2019-06-12'::cstring)::date)) AND ((('2019-06-13'::cstring)::date IS NULL) OR (status_date < ('2019-06-13'::cstring)::date)) AND (SubPlan 8))
  • Rows Removed by Filter: 1
26.          

SubPlan (forCTE Scan)

27. 511.273 544,140.550 ↑ 1.0 1 73,039

HashAggregate (cost=0.32..0.33 rows=1 width=170) (actual time=7.450..7.450 rows=1 loops=73,039)

  • Group Key: dv_sub.status_date, dv_sub.depot, dv_sub.tour, dv_sub.delivery_sheet_no, dv_sub.stop, dv_sub.parcel_number
28. 543,629.277 543,629.277 ↑ 1.0 1 73,039

CTE Scan on delivery_all_view2 dv_sub (cost=0.00..0.30 rows=1 width=170) (actual time=3.714..7.443 rows=1 loops=73,039)

  • Filter: (((parcel_number)::text = (dv.parcel_number)::text) AND (status_date = dv.status_date))
  • Rows Removed by Filter: 73038
29. 1,095.549 1,095.570 ↓ 0.0 0 73,038

Index Scan using detour_avis_parcelno_idx on detour_avis detour_date (cost=0.28..16.61 rows=1 width=31) (actual time=0.015..0.015 rows=0 loops=73,038)

  • Index Cond: ((parcelno)::text = (dv.parcel_number)::text)
  • Filter: (((detour_type)::text = '3'::text) AND (insert_date = (SubPlan 6)))
30.          

SubPlan (forIndex Scan)

31. 0.009 0.021 ↑ 1.0 1 1

Aggregate (cost=8.30..8.31 rows=1 width=8) (actual time=0.021..0.021 rows=1 loops=1)

32. 0.012 0.012 ↑ 1.0 1 1

Index Scan using detour_avis_parcelno_idx on detour_avis detour_1 (cost=0.28..8.30 rows=1 width=8) (actual time=0.011..0.012 rows=1 loops=1)

  • Index Cond: ((parcelno)::text = (dv.parcel_number)::text)
  • Filter: ((detour_type)::text = '3'::text)
33. 657.294 657.342 ↓ 0.0 0 73,038

Index Scan using mdu_pod_depot_tour_stop_idx on mdu_pod pod (cost=0.27..14.59 rows=1 width=109) (actual time=0.009..0.009 rows=0 loops=73,038)

  • Index Cond: (((depot)::text = (dv.depot)::text) AND ((tour)::text = (dv.tour)::text) AND ((stop)::text = (dv.stop)::text))
  • Filter: ((insert_date >= dv.status_date) AND (insert_date < (dv.status_date + '1 day'::interval)) AND ((hashtag)::text ~~ ((dv.delivery_sheet_no)::text || '%'::text)) AND (SubPlan 7))
  • Rows Removed by Filter: 0
34.          

SubPlan (forIndex Scan)

35. 0.048 0.048 ↑ 2.0 1 1

Index Scan using mdu_pod_parcels_parcel_idx on mdu_pod_parcels pp (cost=0.27..12.28 rows=2 width=8) (actual time=0.048..0.048 rows=1 loops=1)

  • Index Cond: ((parcel)::text = (dv.parcel_number)::text)
36. 1,606.836 1,606.836 ↑ 1.0 1 73,038

Index Scan using unique_parcelnumber on parcel (cost=0.56..8.58 rows=1 width=23) (actual time=0.022..0.022 rows=1 loops=73,038)

  • Index Cond: ((parcelnumber)::text = (dv.parcel_number)::text)
37. 1,022.532 1,022.532 ↑ 1.0 1 73,038

Index Scan using pk_shipment on shipment (cost=0.56..1.02 rows=1 width=14) (actual time=0.014..0.014 rows=1 loops=73,038)

  • Index Cond: (id = parcel.shipment_id)
38. 511.266 3,944.052 ↑ 1,906.0 1 73,038

Bitmap Heap Scan on pl_intervals pli (cost=343.18..6,744.18 rows=1,906 width=31) (actual time=0.053..0.054 rows=1 loops=73,038)

  • Recheck Cond: ((dv.parcel_number)::bigint <@ pl_range)
  • Heap Blocks: exact=73750
39. 3,432.786 3,432.786 ↑ 1,906.0 1 73,038

Bitmap Index Scan on idx_pl_range (cost=0.00..342.71 rows=1,906 width=0) (actual time=0.047..0.047 rows=1 loops=73,038)

  • Index Cond: ((dv.parcel_number)::bigint <@ pl_range)
40. 0.000 0.000 ↓ 0.0 0 73,038

Materialize (cost=0.28..10.00 rows=1 width=23) (actual time=0.000..0.000 rows=0 loops=73,038)

41. 0.113 0.113 ↓ 0.0 0 1

Index Scan using detour_avis_detour_type_idx on detour_avis detour_address (cost=0.28..10.00 rows=1 width=23) (actual time=0.113..0.113 rows=0 loops=1)

  • Index Cond: ((detour_type)::text = '0'::text)
  • Filter: ((parcelshop_id)::text = ANY ('{NULL,""}'::text[]))
  • Rows Removed by Filter: 55
42.          

SubPlan (forNested Loop Left Join)

43. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=8.30..8.31 rows=1 width=8) (never executed)

44. 0.000 0.000 ↓ 0.0 0

Index Scan using detour_avis_parcelno_idx on detour_avis detour (cost=0.28..8.30 rows=1 width=8) (never executed)

  • Index Cond: ((parcelno)::text = (dv.parcel_number)::text)
  • Filter: ((detour_type)::text = '0'::text)
45. 365.190 1,241.646 ↑ 1.0 1 73,038

Bitmap Heap Scan on customers customer (cost=67.24..75.14 rows=1 width=24) (actual time=0.017..0.017 rows=1 loops=73,038)

  • Recheck Cond: (((customerno)::text = (dv.customerno)::text) OR ((customerno)::text = (pli.customerno)::text))
  • Filter: (((dv.customerno IS NOT NULL) AND ((customerno)::text = (dv.customerno)::text)) OR ((dv.customerno IS NULL) AND ((customerno)::text = (pli.customerno)::text)))
  • Heap Blocks: exact=52470
46. 219.114 876.456 ↓ 0.0 0 73,038

BitmapOr (cost=67.24..67.24 rows=2 width=0) (actual time=0.012..0.012 rows=0 loops=73,038)

47. 0.000 0.000 ↓ 0.0 0 73,038

Bitmap Index Scan on pk_customers (cost=0.00..4.43 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=73,038)

  • Index Cond: ((customerno)::text = (dv.customerno)::text)
48. 657.342 657.342 ↑ 1.0 1 73,038

Bitmap Index Scan on pk_customers (cost=0.00..0.44 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=73,038)

  • Index Cond: ((customerno)::text = (pli.customerno)::text)
49. 876.456 876.456 ↑ 1.0 1 73,038

Index Scan using sender_shipment_id_idx on sender (cost=0.43..0.52 rows=1 width=63) (actual time=0.009..0.012 rows=1 loops=73,038)

  • Index Cond: (shipment_id = shipment.id)
50. 584.304 584.304 ↓ 0.0 0 73,038

Index Scan using tour_geopost_depot_number_tour_key on tour t (cost=0.28..8.30 rows=1 width=20) (actual time=0.008..0.008 rows=0 loops=73,038)

  • Index Cond: (((geopost_depot_number)::text = (dv.depot)::text) AND ((tour)::text = (dv.tour)::text))
51.          

SubPlan (forNested Loop Left Join)

52. 219.114 485,629.662 ↑ 1.0 1 73,038

Result (cost=0.45..0.46 rows=1 width=1) (actual time=6.649..6.649 rows=1 loops=73,038)

53.          

Initplan (forResult)

54. 485,410.548 485,410.548 ↑ 1.0 1 73,038

CTE Scan on delivery_all_view2 dav (cost=0.00..0.45 rows=1 width=0) (actual time=6.646..6.646 rows=1 loops=73,038)

  • Filter: (((parcel_number)::text = (dv.parcel_number)::text) AND ((depot)::text = (dv.depot)::text) AND ((tour)::text = (dv.tour)::text) AND ((delivery_sheet_no)::text = (dv.delivery_sheet_no)::text) AND (status_date = dv.status_date) AND ((stop)::text = (dv.stop)::text) AND ((order_status)::text = 'OUT_FOR_DELIVERY'::text))
  • Rows Removed by Filter: 36519
55.          

CTE pickups_view2

56. 0.451 477.443 ↓ 35.6 285 1

Subquery Scan on t_1 (cost=68,722.43..721,489.41 rows=8 width=1,203) (actual time=472.817..477.443 rows=285 loops=1)

  • Filter: (((t_1.parcel_number IS NULL) OR (t_1.scan_rank_in_pl_number IS NULL) OR (t_1.scan_rank_in_pl_number = 1)) AND ((('2019-06-12'::cstring)::date IS NULL) OR (t_1.order_date >= ('2019-06-12'::cstring)::date)) AND ((('2019-06-13'::cstring)::date IS NULL) OR (t_1.order_date < ('2019-06-13'::cstring)::date)))
57.          

CTE pickup_scan_view2

58. 0.001 6.308 ↓ 0.0 0 1

Nested Loop (cost=31.24..13,334.55 rows=1 width=107) (actual time=6.307..6.308 rows=0 loops=1)

59. 0.004 6.307 ↓ 0.0 0 1

Nested Loop (cost=30.68..13,329.86 rows=1 width=99) (actual time=6.307..6.307 rows=0 loops=1)

  • Join Filter: ((tt_event_teck.gscode(h_1.geo_status, h_1.reason_code1, h_1.reason_code2, h_1.reason_code3))::text = (gs_status_1.gs_code)::text)
60. 0.115 0.115 ↑ 1.5 13 1

Seq Scan on gs_status gs_status_1 (cost=0.00..19.76 rows=20 width=27) (actual time=0.011..0.115 rows=13 loops=1)

  • Filter: ((pickup_scan IS TRUE) OR (collection_request_scan IS TRUE))
  • Rows Removed by Filter: 363
61. 0.003 6.188 ↓ 0.0 0 13

Materialize (cost=30.68..13,209.45 rows=19 width=88) (actual time=0.476..0.476 rows=0 loops=13)

62. 0.419 6.185 ↓ 0.0 0 1

Nested Loop (cost=30.68..13,209.36 rows=19 width=88) (actual time=6.185..6.185 rows=0 loops=1)

63. 0.931 1.002 ↑ 1.1 1,191 1

Bitmap Heap Scan on scandata scandata_1 (cost=30.12..2,299.05 rows=1,268 width=32) (actual time=0.095..1.002 rows=1,191 loops=1)

  • Recheck Cond: (disp_ref IS NOT NULL)
  • Heap Blocks: exact=165
64. 0.071 0.071 ↑ 1.1 1,191 1

Bitmap Index Scan on scandata_dispref_idx (cost=0.00..29.80 rows=1,268 width=0) (actual time=0.071..0.071 rows=1,191 loops=1)

  • Index Cond: (disp_ref IS NOT NULL)
65. 4.764 4.764 ↓ 0.0 0 1,191

Index Scan using pk_ttevents_header on tt_event_header h_1 (cost=0.56..8.60 rows=1 width=56) (actual time=0.004..0.004 rows=0 loops=1,191)

  • Index Cond: (id = scandata_1.tt_event_header)
  • Filter: (((('2019-06-12'::cstring)::date IS NULL) OR (status_date_time >= ('2019-06-12'::cstring)::date)) AND ((('2019-06-13'::cstring)::date IS NULL) OR (status_date_time < ('2019-06-13'::cstring)::date)))
  • Rows Removed by Filter: 1
66. 0.000 0.000 ↓ 0.0 0

Index Scan using tt_event_additional_tt_event_header_idx on tt_event_additional additional_1 (cost=0.56..4.68 rows=1 width=20) (never executed)

  • Index Cond: (tt_event_header = h_1.id)
67. 1.722 476.992 ↑ 54.3 285 1

WindowAgg (cost=55,387.88..707,575.04 rows=15,462 width=1,261) (actual time=472.808..476.992 rows=285 loops=1)

68.          

Initplan (forWindowAgg)

69. 0.008 0.008 ↑ 1.0 1 1

Seq Scan on warning_times_setting warning_times_setting_1 (cost=0.00..1.01 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=1)

70. 0.252 472.697 ↑ 54.3 285 1

Sort (cost=55,386.87..55,425.52 rows=15,462 width=918) (actual time=472.654..472.697 rows=285 loops=1)

  • Sort Key: p.id, pv.parcel_number, p.pickup_date, pv.status_date_time DESC
  • Sort Method: quicksort Memory: 100kB
71. 0.152 472.445 ↑ 54.3 285 1

Hash Left Join (cost=42,166.53..54,310.99 rows=15,462 width=918) (actual time=405.177..472.445 rows=285 loops=1)

  • Hash Cond: (((p.tour)::text = (tour.tour)::text) AND ((p.depo)::text = (tour.geopost_depot_number)::text))
72. 0.097 471.038 ↑ 54.3 285 1

Hash Left Join (cost=41,976.15..54,039.42 rows=15,462 width=910) (actual time=403.893..471.038 rows=285 loops=1)

  • Hash Cond: (p.id = pv.dispref)
  • Join Filter: ((p.pickup_type <> 3) OR ((pp_1.parcelnumber)::text = (pv.parcel_number)::text))
73. 0.072 464.630 ↑ 54.3 285 1

Hash Left Join (cost=41,976.12..53,981.39 rows=15,462 width=188) (actual time=397.551..464.630 rows=285 loops=1)

  • Hash Cond: (p.id = dc.reference)
74. 0.081 464.547 ↑ 54.3 285 1

Hash Left Join (cost=41,975.08..53,922.36 rows=15,462 width=180) (actual time=397.531..464.547 rows=285 loops=1)

  • Hash Cond: (p.pickup_type = pt.id)
75. 35.030 464.447 ↑ 54.3 285 1

Hash Right Join (cost=41,973.92..53,856.03 rows=15,462 width=158) (actual time=397.492..464.447 rows=285 loops=1)

  • Hash Cond: (pp_1.pickup_id = p.id)
76. 53.727 53.727 ↓ 1.0 407,648 1

Seq Scan on pickup_parcels pp_1 (cost=0.00..10,812.08 rows=407,630 width=23) (actual time=0.032..53.727 rows=407,648 loops=1)

  • Filter: ((parcelnumber IS NOT NULL) AND ((parcelnumber)::text <> ''::text))
  • Rows Removed by Filter: 1318
77. 0.188 375.690 ↑ 57.1 271 1

Hash (cost=41,780.65..41,780.65 rows=15,462 width=143) (actual time=375.690..375.690 rows=271 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 174kB
78. 375.502 375.502 ↑ 57.1 271 1

Seq Scan on pickups p (cost=0.00..41,780.65 rows=15,462 width=143) (actual time=0.407..375.502 rows=271 loops=1)

  • Filter: (((('2019-06-12'::cstring)::date IS NULL) OR (pickup_date >= ('2019-06-12'::cstring)::date)) AND ((('2019-06-13'::cstring)::date IS NULL) OR (pickup_date < ('2019-06-13'::cstring)::date)))
  • Rows Removed by Filter: 454776
79. 0.006 0.019 ↑ 1.0 7 1

Hash (cost=1.07..1.07 rows=7 width=22) (actual time=0.019..0.019 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
80. 0.013 0.013 ↑ 1.0 7 1

Seq Scan on pickup_types pt (cost=0.00..1.07 rows=7 width=22) (actual time=0.012..0.013 rows=7 loops=1)

81. 0.004 0.011 ↑ 1.0 2 1

Hash (cost=1.02..1.02 rows=2 width=16) (actual time=0.011..0.011 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
82. 0.007 0.007 ↑ 1.0 2 1

Seq Scan on dispatcher_comment dc (cost=0.00..1.02 rows=2 width=16) (actual time=0.006..0.007 rows=2 loops=1)

83. 0.001 6.311 ↓ 0.0 0 1

Hash (cost=0.02..0.02 rows=1 width=730) (actual time=6.311..6.311 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
84. 6.310 6.310 ↓ 0.0 0 1

CTE Scan on pickup_scan_view2 pv (cost=0.00..0.02 rows=1 width=730) (actual time=6.310..6.310 rows=0 loops=1)

85. 0.668 1.255 ↑ 1.0 3,735 1

Hash (cost=134.35..134.35 rows=3,735 width=20) (actual time=1.254..1.255 rows=3,735 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 196kB
86. 0.587 0.587 ↑ 1.0 3,735 1

Seq Scan on tour (cost=0.00..134.35 rows=3,735 width=20) (actual time=0.009..0.587 rows=3,735 loops=1)

87.          

SubPlan (forWindowAgg)

88. 1.710 1.710 ↑ 1.0 1 285

Index Scan using pk_customers on customers cus (cost=0.42..8.44 rows=1 width=13) (actual time=0.006..0.006 rows=1 loops=285)

  • Index Cond: ((customerno)::text = (p.customerno)::text)
89. 0.285 0.285 ↓ 0.0 0 285

Index Scan using pk_pickups on pickups p2 (cost=0.42..8.44 rows=1 width=5) (actual time=0.001..0.001 rows=0 loops=285)

  • Index Cond: (id = p.parent_pickup)
90. 0.000 0.000 ↓ 0.0 0 285

Index Scan using pk_customers on customers cust (cost=8.86..16.88 rows=1 width=13) (actual time=0.000..0.000 rows=0 loops=285)

  • Index Cond: ((customerno)::text = ($47)::text)
91.          

Initplan (forIndex Scan)

92. 0.285 0.285 ↓ 0.0 0 285

Index Scan using pk_pickups on pickups p2_1 (cost=0.42..8.44 rows=1 width=5) (actual time=0.001..0.001 rows=0 loops=285)

  • Index Cond: (id = p.parent_pickup)
93. 0.570 0.570 ↑ 1.0 1 285

Aggregate (cost=8.31..8.32 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=285)

94. 0.000 0.000 ↓ 0.0 0 285

Index Scan using mdu_pod_depot_tour_stop_idx on mdu_pod pod_1 (cost=0.27..8.30 rows=1 width=67) (actual time=0.000..0.000 rows=0 loops=285)

  • Index Cond: (((depot)::text = (p.depo)::text) AND ((tour)::text = (p.tour)::text) AND ((stop)::text = (pv.stop)::text))
  • Filter: ((((hashtag)::text ~~ ((pv.order_sheet_number)::text || '%'::text)) OR ((hashtag IS NULL) AND (pv.order_sheet_number IS NULL))) AND (tt_event_teck.timestamp_to_date(insert_date) = p.pickup_date))
95. 0.037 478.309 ↓ 285.0 285 1

Subquery Scan on *SELECT* 1 (cost=0.00..2.15 rows=1 width=2,963) (actual time=477.844..478.309 rows=285 loops=1)

96. 473.302 478.272 ↓ 285.0 285 1

CTE Scan on pickups_view2 pickups_view (cost=0.00..2.14 rows=1 width=2,959) (actual time=477.840..478.272 rows=285 loops=1)

  • Filter: (((('2019-06-12'::cstring)::date IS NULL) OR (order_date >= ('2019-06-12'::cstring)::date)) AND ((('2019-06-12'::cstring)::date IS NULL) OR (order_date <= ('2019-06-12'::cstring)::date)) AND (((parcel_number)::text <> '00000000000000'::text) OR (pickup_id IS NULL) OR (NOT (alternatives: SubPlan 18 or hashed SubPlan 19))))
97.          

SubPlan (forCTE Scan)

98. 0.000 0.000 ↓ 0.0 0

CTE Scan on pickups_view2 p_1 (cost=0.00..0.22 rows=1 width=0) (never executed)

  • Filter: (((parcel_number)::text <> '00000000000000'::text) AND (pickup_id = pickups_view.pickup_id) AND (order_date = pickups_view.order_date))
99. 4.970 4.970 ↓ 6.0 42 1

CTE Scan on pickups_view2 p_2 (cost=0.00..0.18 rows=7 width=12) (actual time=3.597..4.970 rows=42 loops=1)

  • Filter: ((parcel_number)::text <> '00000000000000'::text)
  • Rows Removed by Filter: 243
100. 134.729 1,047,847.168 ↓ 73,038.0 73,038 1

Subquery Scan on *SELECT* 2 (cost=0.00..0.11 rows=1 width=2,603) (actual time=4,379.880..1,047,847.168 rows=73,038 loops=1)

101. 1,047,712.439 1,047,712.439 ↓ 73,038.0 73,038 1

CTE Scan on delivery_list_view2 d (cost=0.00..0.09 rows=1 width=2,595) (actual time=4,379.876..1,047,712.439 rows=73,038 loops=1)

  • Filter: (((('2019-06-12'::cstring)::date IS NULL) OR (order_date >= ('2019-06-12'::cstring)::date)) AND ((('2019-06-12'::cstring)::date IS NULL) OR (order_date <= ('2019-06-12'::cstring)::date)))
102. 4.875 1,049,470.545 ↓ 50.0 100 1

GroupAggregate (cost=0.05..0.46 rows=2 width=1,082) (actual time=1,049,467.996..1,049,470.545 rows=100 loops=1)

  • Group Key: pdv.order_date, pdv.depot, pdv.tour, pdv.order_sheet_no, pdv.stop_order, pdv.pickup_status, pdv.delivery_status, pdv.order_type, pdv.order_id
103. 625.468 1,049,465.670 ↓ 57.5 115 1

Sort (cost=0.05..0.06 rows=2 width=2,189) (actual time=1,049,465.646..1,049,465.670 rows=115 loops=1)

  • Sort Key: pdv.order_date, pdv.depot, pdv.tour, pdv.order_sheet_no, pdv.stop_order, pdv.pickup_status, pdv.delivery_status, pdv.order_type, pdv.order_id
  • Sort Method: quicksort Memory: 33571kB
104. 1,048,840.202 1,048,840.202 ↓ 36,661.5 73,323 1

CTE Scan on pickups_and_deliveries_view2 pdv (cost=0.00..0.04 rows=2 width=2,189) (actual time=477.855..1,048,840.202 rows=73,323 loops=1)

Planning time : 8.937 ms
Execution time : 1,063,084.278 ms