explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WQml : wat

Settings
# exclusive inclusive rows x rows loops node
1. 0.534 53,301.349 ↑ 1.0 1 1

Aggregate (cost=41,929,023.15..41,929,023.16 rows=1 width=32) (actual time=53,301.349..53,301.349 rows=1 loops=1)

2.          

CTE rr

3. 1.130 1.130 ↓ 1.0 1,180 1

Seq Scan on reservation rr (cost=0.00..300.09 rows=1,164 width=61) (actual time=0.015..1.130 rows=1,180 loops=1)

  • Filter: (user_id = 41)
  • Rows Removed by Filter: 10570
4.          

CTE rrd

5. 5.637 19,500.897 ↑ 59.9 1,181 1

Merge Join (cost=7,575.01..6,703,997.42 rows=70,684 width=100) (actual time=1,346.588..19,500.897 rows=1,181 loops=1)

  • Merge Cond: (rr_1.reservation_id = r.id)
6. 0.568 0.692 ↓ 1.0 1,180 1

Sort (cost=82.56..85.47 rows=1,164 width=4) (actual time=0.310..0.692 rows=1,180 loops=1)

  • Sort Key: rr_1.reservation_id
  • Sort Method: quicksort Memory: 104kB
7. 0.124 0.124 ↓ 1.0 1,180 1

CTE Scan on rr rr_1 (cost=0.00..23.28 rows=1,164 width=4) (actual time=0.001..0.124 rows=1,180 loops=1)

8. 6.777 19,494.568 ↑ 1.0 11,696 1

Materialize (cost=7,492.46..6,702,879.15 rows=12,145 width=100) (actual time=86.742..19,494.568 rows=11,696 loops=1)

9. 65.313 19,487.791 ↑ 1.0 11,696 1

GroupAggregate (cost=7,492.46..6,702,727.33 rows=12,145 width=253) (actual time=86.739..19,487.791 rows=11,696 loops=1)

  • Group Key: r.id, costs.price, rt2.service_zone_status
10.          

CTE reservation_status_details

11. 0.008 0.008 ↑ 1.0 7 1

Seq Scan on trip_service_zone_status (cost=0.00..1.08 rows=7 width=119) (actual time=0.007..0.008 rows=7 loops=1)

  • Filter: active
  • Rows Removed by Filter: 1
12.          

CTE mod_trips

13. 4.815 25.542 ↑ 1.0 11,847 1

Hash Left Join (cost=1,175.84..2,884.97 rows=11,873 width=945) (actual time=9.995..25.542 rows=11,847 loops=1)

  • Hash Cond: (rt.vehicle_id = rv.id)
14. 2.289 20.683 ↑ 1.0 11,847 1

Hash Left Join (cost=1,168.91..2,816.99 rows=11,873 width=912) (actual time=9.941..20.683 rows=11,847 loops=1)

  • Hash Cond: (it.trip_status_id = its.id)
15. 4.127 18.381 ↑ 1.0 11,847 1

Hash Left Join (cost=1,167.67..2,772.58 rows=11,873 width=907) (actual time=9.911..18.381 rows=11,847 loops=1)

  • Hash Cond: (rt.trip_id = it.id)
16. 4.382 4.382 ↑ 1.0 11,847 1

Seq Scan on trip rt (cost=0.00..1,573.73 rows=11,873 width=872) (actual time=0.004..4.382 rows=11,847 loops=1)

17. 4.005 9.872 ↓ 1.0 17,787 1

Hash (cost=945.63..945.63 rows=17,763 width=43) (actual time=9.872..9.872 rows=17,787 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1629kB
18. 5.867 5.867 ↓ 1.0 17,787 1

Seq Scan on trip it (cost=0.00..945.63 rows=17,763 width=43) (actual time=0.003..5.867 rows=17,787 loops=1)

19. 0.004 0.013 ↑ 1.0 11 1

Hash (cost=1.11..1.11 rows=11 width=13) (actual time=0.013..0.013 rows=11 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.009 0.009 ↑ 1.0 11 1

Seq Scan on trip_status its (cost=0.00..1.11 rows=11 width=13) (actual time=0.006..0.009 rows=11 loops=1)

21. 0.010 0.044 ↑ 1.0 41 1

Hash (cost=6.41..6.41 rows=41 width=9) (actual time=0.044..0.044 rows=41 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
22. 0.034 0.034 ↑ 1.0 41 1

Seq Scan on vehicles rv (cost=0.00..6.41 rows=41 width=9) (actual time=0.005..0.034 rows=41 loops=1)

23.          

Initplan (for GroupAggregate)

24. 0.016 0.016 ↑ 1.0 1 1

CTE Scan on reservation_status_details (cost=0.00..0.16 rows=1 width=4) (actual time=0.014..0.016 rows=1 loops=1)

  • Filter: ((description)::text = 'ServiceZoneFailure'::text)
  • Rows Removed by Filter: 6
25. 0.003 0.003 ↑ 1.0 1 1

CTE Scan on reservation_status_details reservation_status_details_1 (cost=0.00..0.16 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=1)

  • Filter: ((description)::text = 'DualFailure'::text)
  • Rows Removed by Filter: 6
26. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on reservation_status_details reservation_status_details_2 (cost=0.00..0.16 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1)

  • Filter: ((description)::text = 'VehicleServiceHoursFailure'::text)
  • Rows Removed by Filter: 6
27. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on reservation_status_details reservation_status_details_3 (cost=0.00..0.16 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1)

  • Filter: ((description)::text = 'VehicleAttributeFailure'::text)
  • Rows Removed by Filter: 6
28. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on reservation_status_details reservation_status_details_4 (cost=0.00..0.16 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1)

  • Filter: ((description)::text = 'CapacityDenial'::text)
  • Rows Removed by Filter: 6
29. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on reservation_status_details reservation_status_details_5 (cost=0.00..0.16 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

  • Filter: ((description)::text = 'Success'::text)
  • Rows Removed by Filter: 6
30. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on reservation_status_details reservation_status_details_6 (cost=0.00..0.16 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1)

  • Filter: ((description)::text = 'ServiceZoneFailure'::text)
  • Rows Removed by Filter: 6
31. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on reservation_status_details reservation_status_details_7 (cost=0.00..0.16 rows=1 width=32) (actual time=0.001..0.002 rows=1 loops=1)

  • Filter: ((description)::text = 'ServiceZoneFailure'::text)
  • Rows Removed by Filter: 6
32. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on reservation_status_details reservation_status_details_8 (cost=0.00..0.16 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=1)

  • Filter: ((description)::text = 'DualFailure'::text)
  • Rows Removed by Filter: 6
33. 0.005 0.005 ↑ 1.0 1 1

CTE Scan on reservation_status_details reservation_status_details_9 (cost=0.00..0.16 rows=1 width=32) (actual time=0.004..0.005 rows=1 loops=1)

  • Filter: ((description)::text = 'DualFailure'::text)
  • Rows Removed by Filter: 6
34. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on reservation_status_details reservation_status_details_10 (cost=0.00..0.16 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=1)

  • Filter: ((description)::text = 'VehicleServiceHoursFailure'::text)
  • Rows Removed by Filter: 6
35. 0.004 0.004 ↑ 1.0 1 1

CTE Scan on reservation_status_details reservation_status_details_11 (cost=0.00..0.16 rows=1 width=32) (actual time=0.003..0.004 rows=1 loops=1)

  • Filter: ((description)::text = 'VehicleServiceHoursFailure'::text)
  • Rows Removed by Filter: 6
36. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on reservation_status_details reservation_status_details_12 (cost=0.00..0.16 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=1)

  • Filter: ((description)::text = 'VehicleAttributeFailure'::text)
  • Rows Removed by Filter: 6
37. 0.003 0.003 ↑ 1.0 1 1

CTE Scan on reservation_status_details reservation_status_details_13 (cost=0.00..0.16 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=1)

  • Filter: ((description)::text = 'VehicleAttributeFailure'::text)
  • Rows Removed by Filter: 6
38. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on reservation_status_details reservation_status_details_14 (cost=0.00..0.16 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1)

  • Filter: ((description)::text = 'CapacityDenial'::text)
  • Rows Removed by Filter: 6
39. 0.000 0.000 ↓ 0.0 0

CTE Scan on reservation_status_details reservation_status_details_15 (cost=0.00..0.16 rows=1 width=32) (never executed)

  • Filter: ((description)::text = 'CapacityDenial'::text)
40. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on reservation_status_details reservation_status_details_16 (cost=0.00..0.16 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=1)

  • Filter: ((description)::text = 'Success'::text)
  • Rows Removed by Filter: 6
41. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on reservation_status_details reservation_status_details_17 (cost=0.00..0.16 rows=1 width=32) (actual time=0.001..0.002 rows=1 loops=1)

  • Filter: ((description)::text = 'Success'::text)
  • Rows Removed by Filter: 6
42. 0.006 0.006 ↑ 1.0 1 1

CTE Scan on reservation_status_details reservation_status_details_18 (cost=0.00..0.16 rows=1 width=32) (actual time=0.006..0.006 rows=1 loops=1)

  • Filter: ((description)::text = 'InvalidServiceZoneStatusError'::text)
  • Rows Removed by Filter: 6
43. 8.514 88.930 ↓ 1.4 16,454 1

Sort (cost=4,603.42..4,633.78 rows=12,145 width=16) (actual time=85.025..88.930 rows=16,454 loops=1)

  • Sort Key: r.id, costs.price, rt2.service_zone_status
  • Sort Method: quicksort Memory: 2040kB
44. 3.050 80.416 ↓ 1.4 16,531 1

Hash Left Join (cost=2,007.90..3,779.50 rows=12,145 width=16) (actual time=70.007..80.416 rows=16,531 loops=1)

  • Hash Cond: (r.id = costs.id)
45. 3.330 61.172 ↓ 1.4 16,531 1

Hash Right Join (cost=833.21..2,572.91 rows=12,145 width=8) (actual time=53.776..61.172 rows=16,531 loops=1)

  • Hash Cond: (rt2.reservation_id = r.id)
46. 4.091 4.091 ↑ 1.0 11,847 1

Seq Scan on trip rt2 (cost=0.00..1,573.73 rows=11,873 width=8) (actual time=0.005..4.091 rows=11,847 loops=1)

47. 1.994 53.751 ↓ 1.1 12,499 1

Hash (cost=684.80..684.80 rows=11,873 width=4) (actual time=53.751..53.751 rows=12,499 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 568kB
48. 3.960 51.757 ↓ 1.1 12,499 1

Hash Right Join (cost=416.16..684.80 rows=11,873 width=4) (actual time=12.710..51.757 rows=12,499 loops=1)

  • Hash Cond: (mt.reservation_id = r.id)
49. 45.102 45.102 ↑ 1.0 11,847 1

CTE Scan on mod_trips mt (cost=0.00..237.46 rows=11,873 width=4) (actual time=9.998..45.102 rows=11,847 loops=1)

50. 1.292 2.695 ↓ 1.0 11,750 1

Hash (cost=271.07..271.07 rows=11,607 width=8) (actual time=2.695..2.695 rows=11,750 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 587kB
51. 1.403 1.403 ↓ 1.0 11,750 1

Seq Scan on reservation r (cost=0.00..271.07 rows=11,607 width=8) (actual time=0.006..1.403 rows=11,750 loops=1)

52. 1.664 16.194 ↓ 1.0 11,750 1

Hash (cost=1,029.61..1,029.61 rows=11,607 width=12) (actual time=16.194..16.194 rows=11,750 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 633kB
53. 1.028 14.530 ↓ 1.0 11,750 1

Subquery Scan on costs (cost=797.47..1,029.61 rows=11,607 width=12) (actual time=11.984..14.530 rows=11,750 loops=1)

54. 4.340 13.502 ↓ 1.0 11,750 1

HashAggregate (cost=797.47..913.54 rows=11,607 width=12) (actual time=11.983..13.502 rows=11,750 loops=1)

  • Group Key: r_1.id
55. 2.287 9.162 ↓ 1.0 12,725 1

Hash Left Join (cost=396.27..734.47 rows=12,600 width=8) (actual time=2.828..9.162 rows=12,725 loops=1)

  • Hash Cond: ((s.fare_type)::text = (f.fare_type)::text)
56. 3.109 6.866 ↓ 1.0 12,725 1

Hash Right Join (cost=395.16..672.25 rows=12,600 width=14) (actual time=2.805..6.866 rows=12,725 loops=1)

  • Hash Cond: (s.reservation_id = r_1.id)
57. 0.973 0.973 ↓ 1.0 12,725 1

Seq Scan on seat s (cost=0.00..244.00 rows=12,600 width=14) (actual time=0.003..0.973 rows=12,725 loops=1)

58. 1.377 2.784 ↓ 1.0 11,750 1

Hash (cost=250.07..250.07 rows=11,607 width=4) (actual time=2.784..2.784 rows=11,750 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 542kB
59. 1.407 1.407 ↓ 1.0 11,750 1

Index Only Scan using reservation_pkey on reservation r_1 (cost=0.29..250.07 rows=11,607 width=4) (actual time=0.014..1.407 rows=11,750 loops=1)

  • Heap Fetches: 2585
60. 0.004 0.009 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=20) (actual time=0.009..0.009 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
61. 0.005 0.005 ↑ 1.0 5 1

Seq Scan on fares f (cost=0.00..1.05 rows=5 width=20) (actual time=0.003..0.005 rows=5 loops=1)

62.          

SubPlan (for GroupAggregate)

63. 23.392 19,333.488 ↑ 1.0 1 11,696

Subquery Scan on t_1 (cost=551.02..551.05 rows=1 width=32) (actual time=1.653..1.653 rows=1 loops=11,696)

64. 35.088 19,310.096 ↑ 1.0 1 11,696

Aggregate (cost=551.02..551.03 rows=1 width=72) (actual time=1.651..1.651 rows=1 loops=11,696)

65.          

Initplan (for Aggregate)

66. 58.480 9,777.856 ↑ 1.0 1 11,696

Aggregate (cost=275.51..275.52 rows=1 width=32) (actual time=0.836..0.836 rows=1 loops=11,696)

67. 9,719.376 9,719.376 ↑ 1.0 1 11,696

Seq Scan on seat rs (cost=0.00..275.50 rows=1 width=19) (actual time=0.414..0.831 rows=1 loops=11,696)

  • Filter: (reservation_id = r.id)
  • Rows Removed by Filter: 12724
68. 9,497.152 9,497.152 ↑ 1.0 1 11,696

Seq Scan on seat rs1 (cost=0.00..275.50 rows=1 width=4) (actual time=0.390..0.812 rows=1 loops=11,696)

  • Filter: (reservation_id = r.id)
  • Rows Removed by Filter: 12724
69.          

CTE trip_vehicles

70. 0.761 33,690.477 ↓ 1.4 1,654 1

HashAggregate (cost=35,203,708.48..35,203,753.63 rows=1,204 width=44) (actual time=33,690.268..33,690.477 rows=1,654 loops=1)

  • Group Key: iiv.id, ((jsonb_array_elements(((jsonb_array_elements(iiv.journeys)) -> 'trips'::text))) ->> 'trip_id'::text), ((((jsonb_array_elements(((jsonb_array_elements(iiv.journeys)) -> 'trips'::text))) -> 'vehicle'::text) ->> 'id'::text))::bigint
71. 0.765 33,689.716 ↑ 423,760.6 1,654 1

Result (cost=35,498.90..29,946,958.48 rows=700,900,000 width=44) (actual time=33,541.290..33,689.716 rows=1,654 loops=1)

72. 1.909 33,688.951 ↑ 423,760.6 1,654 1

ProjectSet (cost=35,498.90..3,663,208.48 rows=700,900,000 width=36) (actual time=33,541.287..33,688.951 rows=1,654 loops=1)

73. 2.087 33,687.042 ↑ 5,939.8 1,180 1

ProjectSet (cost=35,498.90..71,095.98 rows=7,009,000 width=36) (actual time=33,541.278..33,687.042 rows=1,180 loops=1)

74. 143.860 33,684.955 ↑ 59.4 1,180 1

Hash Join (cost=35,498.90..35,525.30 rows=70,090 width=36) (actual time=33,541.263..33,684.955 rows=1,180 loops=1)

  • Hash Cond: (rr_2.itinerary_id = iiv.id)
75. 0.098 0.098 ↓ 1.0 1,180 1

CTE Scan on rr rr_2 (cost=0.00..23.28 rows=1,164 width=4) (actual time=0.001..0.098 rows=1,180 loops=1)

76. 109.488 33,540.997 ↑ 1.0 11,774 1

Hash (cost=35,348.36..35,348.36 rows=12,043 width=36) (actual time=33,540.997..33,540.997 rows=11,774 loops=1)

  • Buckets: 16384 (originally 16384) Batches: 64 (originally 1) Memory Usage: 3909kB
77. 1.990 33,431.509 ↑ 1.0 11,774 1

Subquery Scan on iiv (cost=34,581.36..35,348.36 rows=12,043 width=36) (actual time=31,591.762..33,431.509 rows=11,774 loops=1)

78. 1,792.603 33,429.519 ↑ 1.0 11,774 1

GroupAggregate (cost=34,581.36..35,227.93 rows=12,043 width=60) (actual time=31,591.761..33,429.519 rows=11,774 loops=1)

  • Group Key: i.id
79. 10.777 31,636.916 ↑ 1.0 11,774 1

Merge Left Join (cost=34,581.36..35,017.18 rows=12,043 width=60) (actual time=31,591.626..31,636.916 rows=11,774 loops=1)

  • Merge Cond: (i.id = js.itinerary_id)
80. 2.833 2.833 ↑ 1.0 11,774 1

Index Only Scan using itinerary_pkey on itinerary i (cost=0.29..231.55 rows=12,043 width=4) (actual time=0.014..2.833 rows=11,774 loops=1)

  • Heap Fetches: 1256
81. 275.488 31,623.306 ↓ 1.0 11,774 1

Sort (cost=34,581.07..34,610.15 rows=11,630 width=60) (actual time=31,591.604..31,623.306 rows=11,774 loops=1)

  • Sort Key: js.itinerary_id
  • Sort Method: external sort Disk: 94752kB
82. 22.490 31,347.818 ↓ 1.0 11,774 1

Subquery Scan on js (cost=1,435.25..33,795.72 rows=11,630 width=60) (actual time=14.874..31,347.818 rows=11,774 loops=1)

83. 31,306.952 31,325.328 ↓ 1.0 11,774 1

Result (cost=1,435.25..33,679.42 rows=11,630 width=92) (actual time=14.865..31,325.328 rows=11,774 loops=1)

84. 13.648 18.376 ↓ 1.0 11,774 1

Sort (cost=1,435.25..1,464.32 rows=11,630 width=228) (actual time=10.829..18.376 rows=11,774 loops=1)

  • Sort Key: j.sequence_id
  • Sort Method: external merge Disk: 2600kB
85. 2.422 4.728 ↓ 1.0 11,774 1

Group (cost=0.29..649.90 rows=11,630 width=228) (actual time=0.012..4.728 rows=11,774 loops=1)

  • Group Key: j.id
86. 2.306 2.306 ↓ 1.0 11,774 1

Index Scan using journey_pkey on journey j (cost=0.29..620.82 rows=11,630 width=228) (actual time=0.011..2.306 rows=11,774 loops=1)

87.          

CTE drivers

88. 0.010 0.357 ↓ 1.5 25 1

Unique (cost=46.55..46.89 rows=17 width=76) (actual time=0.346..0.357 rows=25 loops=1)

89. 0.021 0.347 ↓ 1.5 25 1

Sort (cost=46.55..46.59 rows=17 width=76) (actual time=0.345..0.347 rows=25 loops=1)

  • Sort Key: trip_vehicles.itinerary_id, trip_vehicles.trip_id, u.user_id, p.first_name, p.last_name, p.cell_phone, u.vehicle_id
  • Sort Method: quicksort Memory: 27kB
90. 0.010 0.326 ↓ 1.5 25 1

Nested Loop (cost=9.58..46.20 rows=17 width=76) (actual time=0.080..0.326 rows=25 loops=1)

91. 0.003 0.291 ↓ 1.5 25 1

Nested Loop (cost=9.30..39.39 rows=17 width=48) (actual time=0.072..0.291 rows=25 loops=1)

92. 0.082 0.263 ↓ 1.5 25 1

Hash Join (cost=9.15..36.39 rows=17 width=44) (actual time=0.062..0.263 rows=25 loops=1)

  • Hash Cond: (trip_vehicles.vehicle_id = u.vehicle_id)
93. 0.123 0.123 ↓ 1.4 1,654 1

CTE Scan on trip_vehicles (cost=0.00..24.08 rows=1,204 width=44) (actual time=0.000..0.123 rows=1,654 loops=1)

94. 0.010 0.058 ↑ 96.0 2 1

Hash (cost=6.75..6.75 rows=192 width=8) (actual time=0.058..0.058 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
95. 0.048 0.048 ↑ 1.0 192 1

Seq Scan on users u (cost=0.00..6.75 rows=192 width=8) (actual time=0.004..0.048 rows=192 loops=1)

  • Filter: (NOT is_deleted)
  • Rows Removed by Filter: 83
96. 0.025 0.025 ↑ 1.0 1 25

Index Only Scan using users_pkey on users au (cost=0.15..0.18 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=25)

  • Index Cond: (id = u.user_id)
  • Heap Fetches: 0
97. 0.025 0.025 ↑ 1.0 1 25

Index Scan using profile_user_id_key on profile p (cost=0.28..0.40 rows=1 width=36) (actual time=0.001..0.001 rows=1 loops=25)

  • Index Cond: (user_id = au.id)
98.          

CTE origin

99. 0.937 14.199 ↓ 1.9 1,654 1

WindowAgg (cost=2,806.01..2,826.11 rows=893 width=116) (actual time=13.170..14.199 rows=1,654 loops=1)

100. 0.613 13.262 ↓ 1.9 1,654 1

Sort (cost=2,806.01..2,808.25 rows=893 width=96) (actual time=13.163..13.262 rows=1,654 loops=1)

  • Sort Key: rr_3.reservation_id, il.depart_time
  • Sort Method: quicksort Memory: 364kB
101. 0.650 12.649 ↓ 1.9 1,654 1

Nested Loop Left Join (cost=722.09..2,762.25 rows=893 width=96) (actual time=3.622..12.649 rows=1,654 loops=1)

102. 1.123 10.345 ↓ 1.9 1,654 1

Nested Loop (cost=721.80..2,445.93 rows=893 width=96) (actual time=3.613..10.345 rows=1,654 loops=1)

103. 2.489 7.568 ↑ 1.1 1,654 1

Hash Join (cost=721.51..1,751.34 rows=1,778 width=12) (actual time=3.605..7.568 rows=1,654 loops=1)

  • Hash Cond: (it_1.journey_id = ij.id)
104. 1.484 1.484 ↓ 1.0 17,787 1

Seq Scan on trip it_1 (cost=0.00..945.63 rows=17,763 width=12) (actual time=0.004..1.484 rows=17,787 loops=1)

105. 0.140 3.595 ↓ 1.0 1,180 1

Hash (cost=706.96..706.96 rows=1,164 width=8) (actual time=3.594..3.595 rows=1,180 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 63kB
106. 0.303 3.455 ↓ 1.0 1,180 1

Hash Join (cost=667.67..706.96 rows=1,164 width=8) (actual time=3.051..3.455 rows=1,180 loops=1)

  • Hash Cond: (rr_3.itinerary_id = ij.itinerary_id)
107. 0.119 0.119 ↓ 1.0 1,180 1

CTE Scan on rr rr_3 (cost=0.00..23.28 rows=1,164 width=8) (actual time=0.000..0.119 rows=1,180 loops=1)

108. 1.265 3.033 ↓ 1.0 11,774 1

Hash (cost=522.30..522.30 rows=11,630 width=8) (actual time=3.033..3.033 rows=11,774 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 588kB
109. 1.768 1.768 ↓ 1.0 11,774 1

Seq Scan on journey ij (cost=0.00..522.30 rows=11,630 width=8) (actual time=0.002..1.768 rows=11,774 loops=1)

110. 1.654 1.654 ↑ 1.0 1 1,654

Index Scan using idx_location_trip_id on location il (cost=0.29..0.38 rows=1 width=100) (actual time=0.001..0.001 rows=1 loops=1,654)

  • Index Cond: (trip_id = it_1.id)
  • Filter: ((depart_time IS NOT NULL) AND (location_type_id = 1))
  • Rows Removed by Filter: 1
111. 1.654 1.654 ↑ 1.0 1 1,654

Index Scan using idx_vehicle_eta_location_id on vehicle_eta ive (cost=0.29..0.34 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=1,654)

  • Index Cond: (location_id = il.id)
112.          

CTE destination

113. 0.936 15.923 ↓ 1.9 1,647 1

WindowAgg (cost=2,804.91..2,824.94 rows=890 width=116) (actual time=14.906..15.923 rows=1,647 loops=1)

114. 0.681 14.987 ↓ 1.9 1,647 1

Sort (cost=2,804.91..2,807.14 rows=890 width=96) (actual time=14.897..14.987 rows=1,647 loops=1)

  • Sort Key: rr_4.reservation_id, il_1.arrival_time DESC
  • Sort Method: quicksort Memory: 342kB
115. 0.699 14.306 ↓ 1.9 1,647 1

Nested Loop Left Join (cost=722.09..2,761.31 rows=890 width=96) (actual time=5.113..14.306 rows=1,647 loops=1)

116. 1.199 11.960 ↓ 1.9 1,647 1

Nested Loop (cost=721.80..2,445.93 rows=890 width=96) (actual time=5.106..11.960 rows=1,647 loops=1)

117. 2.535 9.107 ↑ 1.1 1,654 1

Hash Join (cost=721.51..1,751.34 rows=1,778 width=12) (actual time=5.094..9.107 rows=1,654 loops=1)

  • Hash Cond: (it_2.journey_id = ij_1.id)
118. 1.490 1.490 ↓ 1.0 17,787 1

Seq Scan on trip it_2 (cost=0.00..945.63 rows=17,763 width=12) (actual time=0.006..1.490 rows=17,787 loops=1)

119. 0.142 5.082 ↓ 1.0 1,180 1

Hash (cost=706.96..706.96 rows=1,164 width=8) (actual time=5.082..5.082 rows=1,180 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 63kB
120. 0.338 4.940 ↓ 1.0 1,180 1

Hash Join (cost=667.67..706.96 rows=1,164 width=8) (actual time=3.124..4.940 rows=1,180 loops=1)

  • Hash Cond: (rr_4.itinerary_id = ij_1.itinerary_id)
121. 1.514 1.514 ↓ 1.0 1,180 1

CTE Scan on rr rr_4 (cost=0.00..23.28 rows=1,164 width=8) (actual time=0.016..1.514 rows=1,180 loops=1)

122. 1.284 3.088 ↓ 1.0 11,774 1

Hash (cost=522.30..522.30 rows=11,630 width=8) (actual time=3.088..3.088 rows=11,774 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 588kB
123. 1.804 1.804 ↓ 1.0 11,774 1

Seq Scan on journey ij_1 (cost=0.00..522.30 rows=11,630 width=8) (actual time=0.002..1.804 rows=11,774 loops=1)

124. 1.654 1.654 ↑ 1.0 1 1,654

Index Scan using idx_location_trip_id on location il_1 (cost=0.29..0.38 rows=1 width=100) (actual time=0.001..0.001 rows=1 loops=1,654)

  • Index Cond: (trip_id = it_2.id)
  • Filter: ((arrival_time IS NOT NULL) AND (location_type_id = 2))
  • Rows Removed by Filter: 1
125. 1.647 1.647 ↑ 1.0 1 1,647

Index Scan using idx_vehicle_eta_location_id on vehicle_eta ive_1 (cost=0.29..0.34 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=1,647)

  • Index Cond: (location_id = il_1.id)
126.          

CTE path_polyline

127. 0.365 5.802 ↓ 1.0 1,180 1

Hash Join (cost=667.67..706.96 rows=1,164 width=232) (actual time=5.356..5.802 rows=1,180 loops=1)

  • Hash Cond: (rr_5.itinerary_id = j_1.itinerary_id)
128. 0.106 0.106 ↓ 1.0 1,180 1

CTE Scan on rr rr_5 (cost=0.00..23.28 rows=1,164 width=4) (actual time=0.001..0.106 rows=1,180 loops=1)

129. 3.017 5.331 ↓ 1.0 11,774 1

Hash (cost=522.30..522.30 rows=11,630 width=232) (actual time=5.331..5.331 rows=11,774 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 3071kB
130. 2.314 2.314 ↓ 1.0 11,774 1

Seq Scan on journey j_1 (cost=0.00..522.30 rows=11,630 width=232) (actual time=0.007..2.314 rows=11,774 loops=1)

131.          

CTE trip_details

132. 1.628 11.468 ↑ 1.1 1,654 1

HashAggregate (cost=2,011.09..2,028.87 rows=1,778 width=1,348) (actual time=11.134..11.468 rows=1,654 loops=1)

  • Group Key: rr_6.itinerary_id, t_2.id, t_2.path
133. 2.952 9.840 ↑ 1.1 1,654 1

Hash Join (cost=967.93..1,997.76 rows=1,778 width=1,348) (actual time=5.178..9.840 rows=1,654 loops=1)

  • Hash Cond: (t_2.journey_id = j_2.id)
134. 1.794 1.794 ↓ 1.0 17,787 1

Seq Scan on trip t_2 (cost=0.00..945.63 rows=17,763 width=1,348) (actual time=0.003..1.794 rows=17,787 loops=1)

135. 0.134 5.094 ↓ 1.0 1,180 1

Hash (cost=953.38..953.38 rows=1,164 width=8) (actual time=5.094..5.094 rows=1,180 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 63kB
136. 1.641 4.960 ↓ 1.0 1,180 1

Hash Join (cost=376.23..953.38 rows=1,164 width=8) (actual time=2.512..4.960 rows=1,180 loops=1)

  • Hash Cond: (j_2.itinerary_id = i_1.id)
137. 0.955 0.955 ↓ 1.0 11,774 1

Seq Scan on journey j_2 (cost=0.00..522.30 rows=11,630 width=8) (actual time=0.004..0.955 rows=11,774 loops=1)

138. 0.130 2.364 ↓ 1.0 1,180 1

Hash (cost=361.68..361.68 rows=1,164 width=8) (actual time=2.364..2.364 rows=1,180 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 63kB
139. 0.821 2.234 ↓ 1.0 1,180 1

Merge Join (cost=82.84..361.68 rows=1,164 width=8) (actual time=0.423..2.234 rows=1,180 loops=1)

  • Merge Cond: (i_1.id = rr_6.itinerary_id)
140. 1.047 1.047 ↑ 1.0 11,702 1

Index Only Scan using itinerary_pkey on itinerary i_1 (cost=0.29..231.55 rows=12,043 width=4) (actual time=0.010..1.047 rows=11,702 loops=1)

  • Heap Fetches: 1184
141. 0.237 0.366 ↓ 1.0 1,180 1

Sort (cost=82.56..85.47 rows=1,164 width=4) (actual time=0.313..0.366 rows=1,180 loops=1)

  • Sort Key: rr_6.itinerary_id
  • Sort Method: quicksort Memory: 104kB
142. 0.129 0.129 ↓ 1.0 1,180 1

CTE Scan on rr rr_6 (cost=0.00..23.28 rows=1,164 width=4) (actual time=0.001..0.129 rows=1,180 loops=1)

143.          

CTE payment_cards

144. 0.191 3.659 ↑ 7.0 289 1

Merge Right Join (cost=617.39..672.77 rows=2,009 width=18) (actual time=3.437..3.659 rows=289 loops=1)

  • Merge Cond: (((cc.tokenized_card -> 'payment_method_id'::text)) = ((((po.orderform -> 'customer'::text) -> 'tokenized_card'::text) -> 'payment_method_id'::text)))
145. 0.685 0.865 ↓ 1.0 345 1

Sort (cost=29.61..30.47 rows=344 width=120) (actual time=0.843..0.865 rows=345 loops=1)

  • Sort Key: ((cc.tokenized_card -> 'payment_method_id'::text))
  • Sort Method: quicksort Memory: 116kB
146. 0.137 0.180 ↓ 1.0 345 1

Hash Left Join (cost=1.14..15.11 rows=344 width=120) (actual time=0.018..0.180 rows=345 loops=1)

  • Hash Cond: (cc.card_type = ct.id)
147. 0.037 0.037 ↓ 1.0 345 1

Seq Scan on creditcards cc (cost=0.00..12.44 rows=344 width=115) (actual time=0.004..0.037 rows=345 loops=1)

148. 0.003 0.006 ↑ 1.0 6 1

Hash (cost=1.06..1.06 rows=6 width=13) (actual time=0.006..0.006 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
149. 0.003 0.003 ↑ 1.0 6 1

Seq Scan on cardtypes ct (cost=0.00..1.06 rows=6 width=13) (actual time=0.002..0.003 rows=6 loops=1)

150. 0.279 2.603 ↑ 4.0 289 1

Sort (cost=587.78..590.70 rows=1,168 width=1,449) (actual time=2.586..2.603 rows=289 loops=1)

  • Sort Key: ((((po.orderform -> 'customer'::text) -> 'tokenized_card'::text) -> 'payment_method_id'::text))
  • Sort Method: quicksort Memory: 452kB
151. 0.373 2.324 ↑ 4.0 289 1

Hash Left Join (cost=484.42..528.27 rows=1,168 width=1,449) (actual time=1.691..2.324 rows=289 loops=1)

  • Hash Cond: (pop.purchase_order = po.id)
152. 0.161 0.665 ↑ 4.0 289 1

Hash Join (cost=58.60..99.38 rows=1,168 width=8) (actual time=0.398..0.665 rows=289 loops=1)

  • Hash Cond: (rr_7.reservation_id = pop.product_id)
153. 0.112 0.112 ↓ 1.0 1,180 1

CTE Scan on rr rr_7 (cost=0.00..23.28 rows=1,164 width=4) (actual time=0.001..0.112 rows=1,180 loops=1)

154. 0.185 0.392 ↓ 1.0 1,796 1

Hash (cost=36.60..36.60 rows=1,760 width=8) (actual time=0.392..0.392 rows=1,796 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 87kB
155. 0.207 0.207 ↓ 1.0 1,796 1

Seq Scan on purchase_order_product pop (cost=0.00..36.60 rows=1,760 width=8) (actual time=0.003..0.207 rows=1,796 loops=1)

156. 0.765 1.286 ↑ 1.0 1,801 1

Hash (cost=403.14..403.14 rows=1,814 width=1,449) (actual time=1.286..1.286 rows=1,801 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 2625kB
157. 0.521 0.521 ↑ 1.0 1,801 1

Seq Scan on purchase_order po (cost=0.00..403.14 rows=1,814 width=1,449) (actual time=0.003..0.521 rows=1,801 loops=1)

158.          

CTE results

159. 0.016 53,300.731 ↑ 1.0 10 1

Limit (cost=1,785.09..11,865.25 rows=10 width=1,097) (actual time=53,255.768..53,300.731 rows=10 loops=1)

160. 0.290 53,300.715 ↑ 31.9 10 1

Result (cost=1,785.09..323,342.12 rows=319 width=1,097) (actual time=53,255.767..53,300.715 rows=10 loops=1)

161. 0.055 19,541.895 ↑ 31.9 10 1

Sort (cost=1,785.09..1,785.89 rows=319 width=401) (actual time=19,541.891..19,541.895 rows=10 loops=1)

  • Sort Key: origin."time", destination."time
  • Sort Method: quicksort Memory: 42kB
162. 0.764 19,541.840 ↑ 15.9 20 1

Hash Right Join (cost=95.93..1,778.20 rows=319 width=401) (actual time=17,512.215..19,541.840 rows=20 loops=1)

  • Hash Cond: (rrd.reservation_id = rr_8.reservation_id)
163. 19,501.822 19,501.822 ↑ 59.9 1,181 1

CTE Scan on rrd (cost=0.00..1,413.68 rows=70,684 width=100) (actual time=1,346.590..19,501.822 rows=1,181 loops=1)

164. 0.014 39.254 ↓ 20.0 20 1

Hash (cost=95.91..95.91 rows=1 width=281) (actual time=39.253..39.254 rows=20 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
165. 0.119 39.240 ↓ 20.0 20 1

Hash Right Join (cost=68.21..95.91 rows=1 width=281) (actual time=39.107..39.240 rows=20 loops=1)

  • Hash Cond: (pp.itinerary_id = rr_8.itinerary_id)
166. 6.129 6.129 ↓ 1.0 1,180 1

CTE Scan on path_polyline pp (cost=0.00..23.28 rows=1,164 width=68) (actual time=5.358..6.129 rows=1,180 loops=1)

167. 0.017 32.992 ↓ 20.0 20 1

Hash (cost=68.20..68.20 rows=1 width=217) (actual time=32.992..32.992 rows=20 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
168. 0.554 32.975 ↓ 20.0 20 1

Hash Join (cost=40.22..68.20 rows=1 width=217) (actual time=32.783..32.975 rows=20 loops=1)

  • Hash Cond: (rr_8.reservation_id = destination.reservation_id)
  • Join Filter: "overlaps"(origin."time", destination."time", ('2020-05-06 00:00:00'::timestamp without time zone)::timestamp with time zone, ('2020-05-08 00:00:00'::timestamp without time zone)::timestamp with time zone)
  • Rows Removed by Join Filter: 1154
169. 0.467 15.656 ↓ 51.3 1,181 1

Hash Join (cost=20.14..48.02 rows=23 width=141) (actual time=15.040..15.656 rows=1,181 loops=1)

  • Hash Cond: (rr_8.reservation_id = origin.reservation_id)
170. 0.156 0.156 ↓ 1.0 1,180 1

CTE Scan on rr rr_8 (cost=0.00..23.28 rows=1,164 width=57) (actual time=0.001..0.156 rows=1,180 loops=1)

171. 0.210 15.033 ↓ 295.2 1,181 1

Hash (cost=20.09..20.09 rows=4 width=84) (actual time=15.032..15.033 rows=1,181 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 186kB
172. 14.823 14.823 ↓ 295.2 1,181 1

CTE Scan on origin (cost=0.00..20.09 rows=4 width=84) (actual time=13.173..14.823 rows=1,181 loops=1)

  • Filter: (rn = 1)
  • Rows Removed by Filter: 473
173. 0.207 16.765 ↓ 293.2 1,173 1

Hash (cost=20.02..20.02 rows=4 width=84) (actual time=16.765..16.765 rows=1,173 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 186kB
174. 16.558 16.558 ↓ 293.2 1,173 1

CTE Scan on destination (cost=0.00..20.02 rows=4 width=84) (actual time=14.908..16.558 rows=1,173 loops=1)

  • Filter: (rn = 1)
  • Rows Removed by Filter: 474
175.          

SubPlan (for Result)

176. 0.020 0.020 ↑ 1.0 1 10

Seq Scan on scheduling_status ss (cost=0.00..1.05 rows=1 width=9) (actual time=0.002..0.002 rows=1 loops=10)

  • Filter: (id = rr_8.scheduling_status)
  • Rows Removed by Filter: 3
177. 0.010 37.300 ↑ 1.0 1 10

Subquery Scan on iiv_1 (cost=551.69..556.73 rows=1 width=32) (actual time=3.730..3.730 rows=1 loops=10)

178. 1.120 37.290 ↑ 1.0 1 10

GroupAggregate (cost=551.69..556.72 rows=1 width=60) (actual time=3.729..3.729 rows=1 loops=10)

  • Group Key: i_2.id
179. 0.020 36.170 ↑ 1.0 1 10

Nested Loop Left Join (cost=551.69..556.70 rows=1 width=60) (actual time=3.615..3.617 rows=1 loops=10)

  • Join Filter: (i_2.id = js_1.itinerary_id)
180. 0.060 0.060 ↑ 1.0 1 10

Index Only Scan using itinerary_pkey on itinerary i_2 (cost=0.29..2.50 rows=1 width=4) (actual time=0.005..0.006 rows=1 loops=10)

  • Index Cond: (id = rr_8.itinerary_id)
  • Heap Fetches: 10
181. 0.040 36.090 ↑ 1.0 1 10

Subquery Scan on js_1 (cost=551.40..554.19 rows=1 width=60) (actual time=3.608..3.609 rows=1 loops=10)

182. 25.570 36.050 ↑ 1.0 1 10

Result (cost=551.40..554.18 rows=1 width=92) (actual time=3.604..3.605 rows=1 loops=10)

183. 0.030 10.480 ↑ 1.0 1 10

Sort (cost=551.40..551.41 rows=1 width=228) (actual time=1.048..1.048 rows=1 loops=10)

  • Sort Key: j_3.sequence_id
  • Sort Method: quicksort Memory: 25kB
184. 0.010 10.450 ↑ 1.0 1 10

Group (cost=551.38..551.39 rows=1 width=228) (actual time=1.045..1.045 rows=1 loops=10)

  • Group Key: j_3.id
185. 0.030 10.440 ↑ 1.0 1 10

Sort (cost=551.38..551.39 rows=1 width=228) (actual time=1.044..1.044 rows=1 loops=10)

  • Sort Key: j_3.id
  • Sort Method: quicksort Memory: 25kB
186. 10.410 10.410 ↑ 1.0 1 10

Seq Scan on journey j_3 (cost=0.00..551.38 rows=1 width=228) (actual time=0.948..1.041 rows=1 loops=10)

  • Filter: (itinerary_id = rr_8.itinerary_id)
  • Rows Removed by Filter: 11773
187. 0.070 33,691.960 ↑ 1.0 1 10

Aggregate (cost=36.54..36.56 rows=1 width=32) (actual time=3,369.196..3,369.196 rows=1 loops=10)

188. 0.026 33,691.890 ↓ 0.0 0 10

Hash Join (cost=6.92..34.03 rows=1 width=84) (actual time=3,369.165..3,369.189 rows=0 loops=10)

  • Hash Cond: (tv.vehicle_id = rv_1.id)
189. 33,691.830 33,691.830 ↑ 6.0 1 10

CTE Scan on trip_vehicles tv (cost=0.00..27.09 rows=6 width=44) (actual time=3,369.099..3,369.183 rows=1 loops=10)

  • Filter: (itinerary_id = rr_8.itinerary_id)
  • Rows Removed by Filter: 1653
190. 0.007 0.034 ↑ 1.0 41 1

Hash (cost=6.41..6.41 rows=41 width=44) (actual time=0.034..0.034 rows=41 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
191. 0.027 0.027 ↑ 1.0 41 1

Seq Scan on vehicles rv_1 (cost=0.00..6.41 rows=41 width=44) (actual time=0.007..0.027 rows=41 loops=1)

192. 0.030 0.410 ↑ 1.0 1 10

Aggregate (cost=0.39..0.40 rows=1 width=32) (actual time=0.041..0.041 rows=1 loops=10)

193. 0.380 0.380 ↓ 0.0 0 10

CTE Scan on drivers (cost=0.00..0.38 rows=1 width=866) (actual time=0.038..0.038 rows=0 loops=10)

  • Filter: (itinerary_id = rr_8.itinerary_id)
  • Rows Removed by Filter: 25
194. 0.020 0.020 ↑ 1.0 1 10

Seq Scan on payment_status rps (cost=0.00..1.10 rows=1 width=11) (actual time=0.002..0.002 rows=1 loops=10)

  • Filter: (id = rr_8.payment_status)
  • Rows Removed by Filter: 7
195. 0.030 11.330 ↓ 0.0 0 10

Nested Loop (cost=0.00..276.68 rows=1 width=12) (actual time=1.047..1.133 rows=0 loops=10)

  • Join Filter: (rpa.id = rs_1.payment_action)
  • Rows Removed by Join Filter: 7
196. 11.280 11.280 ↑ 1.0 1 10

Seq Scan on seat rs_1 (cost=0.00..275.50 rows=1 width=4) (actual time=0.958..1.128 rows=1 loops=10)

  • Filter: (is_rider AND (reservation_id = rr_8.reservation_id))
  • Rows Removed by Filter: 12724
197. 0.020 0.020 ↑ 1.0 8 10

Seq Scan on payment_action rpa (cost=0.00..1.08 rows=8 width=16) (actual time=0.002..0.002 rows=8 loops=10)

198. 3.890 3.890 ↓ 0.0 0 10

CTE Scan on payment_cards pc (cost=0.00..45.20 rows=10 width=32) (actual time=0.387..0.389 rows=0 loops=10)

  • Filter: (reservation_id = rr_8.reservation_id)
  • Rows Removed by Filter: 289
199. 0.180 0.180 ↓ 0.0 0 10

CTE Scan on payment_cards pc_1 (cost=0.00..45.20 rows=10 width=90) (actual time=0.017..0.018 rows=0 loops=10)

  • Filter: (reservation_id = rr_8.reservation_id)
  • Rows Removed by Filter: 289
200. 0.140 13.420 ↑ 1.0 1 10

Aggregate (cost=40.05..40.06 rows=1 width=32) (actual time=1.341..1.342 rows=1 loops=10)

201. 13.280 13.280 ↑ 9.0 1 10

CTE Scan on trip_details td (cost=0.00..40.00 rows=9 width=44) (actual time=1.181..1.328 rows=1 loops=10)

  • Filter: (itinerary_id = rr_8.itinerary_id)
  • Rows Removed by Filter: 1653
202. 53,300.815 53,300.815 ↑ 1.0 10 1

CTE Scan on results t (cost=0.00..0.20 rows=10 width=24) (actual time=53,255.782..53,300.815 rows=10 loops=1)

Planning time : 4.490 ms
Execution time : 53,318.347 ms