explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YrCw

Settings
# exclusive inclusive rows x rows loops node
1. 179.911 8,232.059 ↓ 553.4 8,854 1

Unique (cost=486,120.06..486,122.42 rows=16 width=979) (actual time=7,968.088..8,232.059 rows=8,854 loops=1)

2.          

CTE mvsm_stacks

3. 0.159 0.159 ↑ 1.0 21 1

Seq Scan on unified_stacks (cost=0.00..64.61 rows=21 width=8) (actual time=0.016..0.159 rows=21 loops=1)

  • Filter: (vehicle_type_pk IS NOT NULL)
  • Rows Removed by Filter: 4140
4. 2,425.378 8,052.148 ↓ 5,514.7 88,235 1

Sort (cost=486,055.45..486,055.49 rows=16 width=979) (actual time=7,968.086..8,052.148 rows=88,235 loops=1)

  • Sort Key: t.billing_end, t.id, m.id, d.id, (((dc.first_name || ' '::text) || dc.last_name)), m.main_name, mt.descr, a_rp.descr, r.job_code, r.job_category, (CASE WHEN (mvsm_stacks.pk > 0) THEN NULL::integer ELSE r_v.id END), (CASE WHEN (mvsm_stacks.pk > 0) THEN NULL::character varying ELSE CASE WHEN (mvsm_stacks.pk IS NOT NULL) THEN COALESCE(r_v.plate) ELSE r_v.plate END END), r_vt.descr, r_l.id, r_l.descr, r_j.descr, r_m.descr, r.start_stamp, r.end_stamp, rs.descr, ts.descr, ts.name, t.start_stamp, t.end_stamp, ((COALESCE(t.end_stamp, 0) - COALESCE(t.start_stamp, 0))), t.billing_start, ((COALESCE(t.billing_end, 0) - COALESCE(t.billing_start, 0))), t.start_odo, t.end_odo, ((COALESCE(t.end_odo, 0) - COALESCE(t.start_odo, 0))), t.did_not_drive, t.member_notes, t.billing_notes, (CASE WHEN (mvsm_stacks.pk IS NOT NULL) THEN COALESCE(t_dv.id) ELSE NULL::integer END), (CASE WHEN (mvsm_stacks.pk IS NOT NULL) THEN COALESCE(t_dv.plate) ELSE NULL::character varying END), t_dvt.descr, (CASE WHEN (mvsm_stacks.pk IS NOT NULL) THEN COALESCE(t_dv.id) ELSE NULL::integer END), (CASE WHEN (mvsm_stacks.pk IS NOT NULL) THEN COALESCE(t_dv.plate) ELSE NULL::character varying END), t_vt.descr, t_l.id, t_l.descr, t_j.descr, t_m.descr, tc_rp.id, tc_rp.descr, tc_rc.descr, (trip_cost_amount_for_rate_unit_types(t.pk, 'time|flat_rate|limit_time'::text)), (trip_cost_amount_for_rate_unit_types(t.pk, 'distance|extra_distance|limit_distance'::text)), (COALESCE(tc.total_amount, 0.0)), (COALESCE(tc.fee_amount, 0.0)), (COALESCE((tc.grand_total_amount - tc.subtotal_amount), 0.0)), (COALESCE(tc.grand_total_amount, 0.0)), (COALESCE((sum(s.total_amount)), 0.0)), (COALESCE((sum(s_1.total_amount)), 0.0)), (COALESCE((sum(s_2.total_amount)), 0.0)), (COALESCE((sum(s_3.total_amount)), 0.0)), r_us.id, t_us.id
  • Sort Method: external merge Disk: 46032kB
5. 23.426 5,626.770 ↓ 5,514.7 88,235 1

Nested Loop Left Join (cost=344,561.85..486,055.13 rows=16 width=979) (actual time=4,027.933..5,626.770 rows=88,235 loops=1)

6. 46.872 5,515.109 ↓ 5,514.7 88,235 1

Nested Loop Left Join (cost=344,561.71..486,052.11 rows=16 width=946) (actual time=4,027.923..5,515.109 rows=88,235 loops=1)

7. 19.614 5,380.002 ↓ 5,514.7 88,235 1

Nested Loop Left Join (cost=344,561.43..486,046.41 rows=16 width=935) (actual time=4,027.914..5,380.002 rows=88,235 loops=1)

8. 933.732 5,125.738 ↓ 771.9 12,350 1

Hash Left Join (cost=344,561.02..485,913.33 rows=16 width=1,003) (actual time=4,027.890..5,125.738 rows=12,350 loops=1)

  • Hash Cond: (tc.rate_code_pk = tc_rc.pk)
9. 5.417 4,191.995 ↓ 771.9 12,350 1

Hash Left Join (cost=344,559.84..485,903.69 rows=16 width=667) (actual time=4,026.134..4,191.995 rows=12,350 loops=1)

  • Hash Cond: (tc.rate_plan_pk = tc_rp.pk)
10. 53.677 4,186.430 ↓ 771.9 12,350 1

Merge Right Join (cost=344,541.62..485,885.25 rows=16 width=636) (actual time=4,025.970..4,186.430 rows=12,350 loops=1)

  • Merge Cond: (s.trip_pk = t.pk)
11. 290.045 1,233.098 ↓ 1.2 661,035 1

GroupAggregate (cost=0.43..96,758.98 rows=532,381 width=36) (actual time=29.918..1,233.098 rows=661,035 loops=1)

  • Group Key: s.trip_pk
12. 537.572 943.053 ↓ 1.2 698,794 1

Nested Loop (cost=0.43..87,304.39 rows=559,966 width=10) (actual time=29.651..943.053 rows=698,794 loops=1)

  • Join Filter: (s.sale_type_pk = st.pk)
  • Rows Removed by Join Filter: 2096380
13. 405.481 405.481 ↑ 1.0 698,794 1

Index Scan using sales_trip_pk_idx on sales s (cost=0.43..45,305.90 rows=699,957 width=14) (actual time=29.625..405.481 rows=698,794 loops=1)

  • Filter: (trip_cost_pk IS NOT NULL)
  • Rows Removed by Filter: 148021
14. 0.000 0.000 ↑ 1.0 4 698,794

Materialize (cost=0.00..1.08 rows=4 width=4) (actual time=0.000..0.000 rows=4 loops=698,794)

15. 0.018 0.018 ↑ 1.0 4 1

Seq Scan on sale_types st (cost=0.00..1.06 rows=4 width=4) (actual time=0.014..0.018 rows=4 loops=1)

  • Filter: ((name)::text <> 'driving_credit'::text)
  • Rows Removed by Filter: 1
16. 12.257 2,899.655 ↓ 771.9 12,350 1

Materialize (cost=344,541.20..382,471.41 rows=16 width=604) (actual time=2,861.737..2,899.655 rows=12,350 loops=1)

17. 7.798 2,887.398 ↓ 771.9 12,350 1

Merge Left Join (cost=344,541.20..382,471.37 rows=16 width=604) (actual time=2,861.734..2,887.398 rows=12,350 loops=1)

  • Merge Cond: (t.pk = s_3.trip_pk)
18. 9.775 2,614.685 ↓ 771.9 12,350 1

Merge Left Join (cost=286,646.49..316,420.40 rows=16 width=572) (actual time=2,596.456..2,614.685 rows=12,350 loops=1)

  • Merge Cond: (t.pk = s_2.trip_pk)
  • Join Filter: (bp_1.start_stamp > t.billing_end)
  • Rows Removed by Join Filter: 5
19. 15.295 1,685.047 ↓ 771.9 12,350 1

Merge Left Join (cost=207,551.06..222,438.04 rows=16 width=540) (actual time=1,670.864..1,685.047 rows=12,350 loops=1)

  • Merge Cond: (t.pk = s_1.trip_pk)
  • Join Filter: ((bp.start_stamp <= t.billing_end) AND (bp.end_stamp >= t.billing_end))
  • Rows Removed by Join Filter: 261
20. 14.298 687.426 ↓ 771.9 12,350 1

Sort (cost=128,455.64..128,455.68 rows=16 width=508) (actual time=682.808..687.426 rows=12,350 loops=1)

  • Sort Key: t.pk
  • Sort Method: quicksort Memory: 7479kB
21. 5.653 673.128 ↓ 771.9 12,350 1

Hash Semi Join (cost=67,226.24..128,455.32 rows=16 width=508) (actual time=585.055..673.128 rows=12,350 loops=1)

  • Hash Cond: (t.pk = t_1.pk)
22. 1.015 514.076 ↓ 2.2 15,553 1

Nested Loop Left Join (cost=51,738.65..112,948.93 rows=7,094 width=508) (actual time=431.644..514.076 rows=15,553 loops=1)

23. 4.038 466.402 ↓ 2.2 15,553 1

Hash Left Join (cost=51,738.22..52,508.05 rows=7,094 width=484) (actual time=431.615..466.402 rows=15,553 loops=1)

  • Hash Cond: (t_v.vehicle_type_pk = t_vt.pk)
24. 4.522 462.318 ↓ 2.2 15,553 1

Hash Left Join (cost=51,727.50..52,399.78 rows=7,094 width=472) (actual time=431.562..462.318 rows=15,553 loops=1)

  • Hash Cond: (t.vehicle_pk = t_dv.pk)
25. 4.374 456.071 ↓ 2.2 15,553 1

Hash Left Join (cost=51,523.29..52,115.54 rows=7,094 width=449) (actual time=429.825..456.071 rows=15,553 loops=1)

  • Hash Cond: (t_us.lot_pk = t_l.pk)
26. 4.232 447.779 ↓ 2.2 15,553 1

Hash Left Join (cost=51,332.91..51,827.63 rows=7,094 width=345) (actual time=425.894..447.779 rows=15,553 loops=1)

  • Hash Cond: (r_l.jurisdiction_pk = r_j.pk)
27. 17.548 442.828 ↓ 2.2 15,553 1

Hash Right Join (cost=51,301.65..51,698.82 rows=7,094 width=284) (actual time=425.159..442.828 rows=15,553 loops=1)

  • Hash Cond: (t_usr.stack_pk = t_us.pk)
  • Join Filter: ((t.billing_end > t_usr.start_stamp) AND (t.billing_end <= t_usr.end_stamp))
  • Rows Removed by Join Filter: 20673
28. 2.713 8.919 ↓ 2.0 8,049 1

Hash Left Join (cost=154.52..426.47 rows=4,096 width=16) (actual time=2.421..8.919 rows=8,049 loops=1)

  • Hash Cond: (t_usr.vehicle_pk = t_v.pk)
29. 1.627 3.836 ↓ 2.0 8,049 1

Nested Loop (cost=0.28..215.91 rows=4,096 width=16) (actual time=0.033..3.836 rows=8,049 loops=1)

30. 0.011 0.011 ↑ 1.0 1 1

Seq Scan on unified_stack_resource_status t_usrs (cost=0.00..1.02 rows=1 width=4) (actual time=0.010..0.011 rows=1 loops=1)

  • Filter: ((name)::text = 'normal'::text)
  • Rows Removed by Filter: 1
31. 2.198 2.198 ↓ 2.0 8,049 1

Index Scan using idx_unified_stack_resources_status_pk on unified_stack_resources t_usr (cost=0.28..173.93 rows=4,096 width=20) (actual time=0.019..2.198 rows=8,049 loops=1)

  • Index Cond: (status_pk = t_usrs.pk)
32. 0.849 2.370 ↑ 1.0 2,855 1

Hash (cost=118.55..118.55 rows=2,855 width=8) (actual time=2.370..2.370 rows=2,855 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 144kB
33. 1.521 1.521 ↑ 1.0 2,855 1

Seq Scan on vehicles t_v (cost=0.00..118.55 rows=2,855 width=8) (actual time=0.008..1.521 rows=2,855 loops=1)

34. 7.968 416.361 ↓ 1.6 11,188 1

Hash (cost=51,058.45..51,058.45 rows=7,094 width=284) (actual time=416.361..416.361 rows=11,188 loops=1)

  • Buckets: 16384 (originally 8192) Batches: 1 (originally 1) Memory Usage: 2999kB
35. 5.161 408.393 ↓ 1.6 11,188 1

Hash Left Join (cost=7,757.56..51,058.45 rows=7,094 width=284) (actual time=71.589..408.393 rows=11,188 loops=1)

  • Hash Cond: (t.billing_stack_pk = t_us.pk)
36. 2.845 402.279 ↓ 1.6 11,188 1

Hash Left Join (cost=7,640.94..50,844.29 rows=7,094 width=276) (actual time=70.622..402.279 rows=11,188 loops=1)

  • Hash Cond: (r.status_pk = rs.pk)
37. 3.316 399.425 ↓ 1.6 11,188 1

Hash Join (cost=7,639.81..50,745.61 rows=7,094 width=272) (actual time=70.604..399.425 rows=11,188 loops=1)

  • Hash Cond: (a.rate_plan_pk = a_rp.pk)
38. 4.855 395.957 ↓ 1.6 11,188 1

Hash Left Join (cost=7,621.59..50,629.85 rows=7,094 width=245) (actual time=70.446..395.957 rows=11,188 loops=1)

  • Hash Cond: (r.stack_pk = r_us.pk)
39. 5.369 387.895 ↓ 1.6 11,188 1

Nested Loop Left Join (cost=7,317.14..50,227.85 rows=7,094 width=194) (actual time=67.223..387.895 rows=11,188 loops=1)

40. 7.597 259.458 ↓ 1.6 11,188 1

Nested Loop (cost=7,311.40..46,542.10 rows=7,094 width=161) (actual time=67.024..259.458 rows=11,188 loops=1)

41. 3.220 229.485 ↓ 1.6 11,188 1

Hash Join (cost=7,310.98..43,131.35 rows=7,094 width=165) (actual time=67.015..229.485 rows=11,188 loops=1)

  • Hash Cond: (m.member_type_pk = mt.pk)
42. 1.219 226.259 ↓ 1.6 11,188 1

Nested Loop (cost=7,309.89..43,032.72 rows=7,094 width=159) (actual time=67.005..226.259 rows=11,188 loops=1)

43. 2.054 191.476 ↓ 1.6 11,188 1

Nested Loop (cost=7,309.47..38,898.73 rows=7,094 width=133) (actual time=66.992..191.476 rows=11,188 loops=1)

44. 9.653 133.482 ↓ 1.6 11,188 1

Nested Loop (cost=7,309.05..27,206.68 rows=7,094 width=124) (actual time=66.968..133.482 rows=11,188 loops=1)

45. 2.918 101.453 ↓ 1.6 11,188 1

Hash Join (cost=7,308.63..23,839.09 rows=7,094 width=128) (actual time=66.931..101.453 rows=11,188 loops=1)

  • Hash Cond: (t.status_pk = ts.pk)
46. 16.932 98.530 ↓ 1.6 11,188 1

Hash Join (cost=7,307.56..23,740.48 rows=7,094 width=114) (actual time=66.921..98.530 rows=11,188 loops=1)

  • Hash Cond: (t.driver_pk = d.pk)
47. 15.082 15.976 ↓ 1.6 11,188 1

Bitmap Heap Scan on trips t (cost=153.95..15,352.32 rows=7,094 width=99) (actual time=1.110..15.976 rows=11,188 loops=1)

  • Recheck Cond: ((billing_end >= 1551420000) AND (billing_end <= 1553317199))
  • Filter: ((void = 'N'::bpchar) AND (unconfirmed = 'N'::bpchar))
  • Rows Removed by Filter: 186
  • Heap Blocks: exact=1916
48. 0.894 0.894 ↓ 1.6 11,374 1

Bitmap Index Scan on trips_inx7 (cost=0.00..152.18 rows=7,175 width=0) (actual time=0.894..0.894 rows=11,374 loops=1)

  • Index Cond: ((billing_end >= 1551420000) AND (billing_end <= 1553317199))
49. 30.509 65.622 ↓ 1.0 156,119 1

Hash (cost=4,287.16..4,287.16 rows=156,116 width=23) (actual time=65.622..65.622 rows=156,119 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 5394kB
50. 35.113 35.113 ↓ 1.0 156,119 1

Seq Scan on drivers d (cost=0.00..4,287.16 rows=156,116 width=23) (actual time=0.006..35.113 rows=156,119 loops=1)

51. 0.002 0.005 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=22) (actual time=0.005..0.005 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
52. 0.003 0.003 ↑ 1.0 3 1

Seq Scan on trip_status ts (cost=0.00..1.03 rows=3 width=22) (actual time=0.003..0.003 rows=3 loops=1)

53. 22.376 22.376 ↑ 1.0 1 11,188

Index Only Scan using logins_pk_role_pk on logins l (cost=0.42..0.46 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=11,188)

  • Index Cond: (pk = d.login_pk)
  • Heap Fetches: 924
54. 55.940 55.940 ↑ 1.0 1 11,188

Index Scan using contacts_pkey on contacts dc (cost=0.42..1.64 rows=1 width=17) (actual time=0.005..0.005 rows=1 loops=11,188)

  • Index Cond: (pk = d.contact_pk)
55. 33.564 33.564 ↑ 1.0 1 11,188

Index Scan using member_pk_contact_pk on members m (cost=0.42..0.57 rows=1 width=26) (actual time=0.003..0.003 rows=1 loops=11,188)

  • Index Cond: (pk = d.member_pk)
56. 0.001 0.006 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=14) (actual time=0.006..0.006 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
57. 0.005 0.005 ↑ 1.0 4 1

Seq Scan on member_types mt (cost=0.00..1.04 rows=4 width=14) (actual time=0.004..0.005 rows=4 loops=1)

58. 22.376 22.376 ↑ 1.0 1 11,188

Index Scan using accounts_member_payment_info_pk on accounts a (cost=0.42..0.47 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=11,188)

  • Index Cond: (member_pk = d.member_pk)
59. 44.752 123.068 ↑ 1.0 1 11,188

Hash Right Join (cost=5.74..6.25 rows=1 width=41) (actual time=0.010..0.011 rows=1 loops=11,188)

  • Hash Cond: (mvsm_stacks.pk = r.stack_pk)
60. 22.376 22.376 ↑ 1.0 21 11,188

CTE Scan on mvsm_stacks (cost=0.00..0.42 rows=21 width=4) (actual time=0.000..0.002 rows=21 loops=11,188)

61. 11.188 55.940 ↑ 1.0 1 11,188

Hash (cost=5.73..5.73 rows=1 width=37) (actual time=0.005..0.005 rows=1 loops=11,188)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
62. 44.752 44.752 ↑ 1.0 1 11,188

Index Scan using reservations_pkey on reservations r (cost=0.43..5.73 rows=1 width=37) (actual time=0.004..0.004 rows=1 loops=11,188)

  • Index Cond: (t.reservation_pk = pk)
63. 0.747 3.207 ↑ 1.0 4,161 1

Hash (cost=252.44..252.44 rows=4,161 width=55) (actual time=3.207..3.207 rows=4,161 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 411kB
64. 1.101 2.460 ↑ 1.0 4,161 1

Hash Left Join (cost=130.62..252.44 rows=4,161 width=55) (actual time=1.094..2.460 rows=4,161 loops=1)

  • Hash Cond: (r_us.lot_pk = r_l.pk)
65. 0.281 0.281 ↑ 1.0 4,161 1

Seq Scan on unified_stacks r_us (cost=0.00..64.61 rows=4,161 width=16) (actual time=0.002..0.281 rows=4,161 loops=1)

66. 0.338 1.078 ↑ 1.0 2,072 1

Hash (cost=104.72..104.72 rows=2,072 width=51) (actual time=1.078..1.078 rows=2,072 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 205kB
67. 0.740 0.740 ↑ 1.0 2,072 1

Seq Scan on lots r_l (cost=0.00..104.72 rows=2,072 width=51) (actual time=0.016..0.740 rows=2,072 loops=1)

68. 0.061 0.152 ↑ 1.0 454 1

Hash (cost=12.54..12.54 rows=454 width=35) (actual time=0.152..0.152 rows=454 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 39kB
69. 0.091 0.091 ↑ 1.0 454 1

Seq Scan on rate_plans a_rp (cost=0.00..12.54 rows=454 width=35) (actual time=0.009..0.091 rows=454 loops=1)

70. 0.003 0.009 ↑ 1.0 6 1

Hash (cost=1.06..1.06 rows=6 width=12) (actual time=0.009..0.009 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
71. 0.006 0.006 ↑ 1.0 6 1

Seq Scan on reservation_status rs (cost=0.00..1.06 rows=6 width=12) (actual time=0.006..0.006 rows=6 loops=1)

72. 0.491 0.953 ↑ 1.0 4,161 1

Hash (cost=64.61..64.61 rows=4,161 width=16) (actual time=0.953..0.953 rows=4,161 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 260kB
73. 0.462 0.462 ↑ 1.0 4,161 1

Seq Scan on unified_stacks t_us (cost=0.00..64.61 rows=4,161 width=16) (actual time=0.003..0.462 rows=4,161 loops=1)

74. 0.155 0.719 ↑ 1.0 317 1

Hash (cost=27.30..27.30 rows=317 width=69) (actual time=0.719..0.719 rows=317 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 41kB
75. 0.228 0.564 ↑ 1.0 317 1

Hash Left Join (cost=13.77..27.30 rows=317 width=69) (actual time=0.287..0.564 rows=317 loops=1)

  • Hash Cond: (r_j.market_pk = r_m.pk)
76. 0.069 0.069 ↑ 1.0 317 1

Seq Scan on jurisdictions r_j (cost=0.00..9.17 rows=317 width=41) (actual time=0.008..0.069 rows=317 loops=1)

77. 0.123 0.267 ↑ 1.0 301 1

Hash (cost=10.01..10.01 rows=301 width=36) (actual time=0.267..0.267 rows=301 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
78. 0.144 0.144 ↑ 1.0 301 1

Seq Scan on markets r_m (cost=0.00..10.01 rows=301 width=36) (actual time=0.007..0.144 rows=301 loops=1)

79. 1.037 3.918 ↑ 1.0 2,072 1

Hash (cost=164.47..164.47 rows=2,072 width=112) (actual time=3.918..3.918 rows=2,072 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 326kB
80. 1.360 2.881 ↑ 1.0 2,072 1

Hash Left Join (cost=31.26..164.47 rows=2,072 width=112) (actual time=0.673..2.881 rows=2,072 loops=1)

  • Hash Cond: (t_l.jurisdiction_pk = t_j.pk)
81. 0.868 0.868 ↑ 1.0 2,072 1

Seq Scan on lots t_l (cost=0.00..104.72 rows=2,072 width=51) (actual time=0.013..0.868 rows=2,072 loops=1)

82. 0.143 0.653 ↑ 1.0 317 1

Hash (cost=27.30..27.30 rows=317 width=69) (actual time=0.653..0.653 rows=317 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 41kB
83. 0.217 0.510 ↑ 1.0 317 1

Hash Left Join (cost=13.77..27.30 rows=317 width=69) (actual time=0.250..0.510 rows=317 loops=1)

  • Hash Cond: (t_j.market_pk = t_m.pk)
84. 0.056 0.056 ↑ 1.0 317 1

Seq Scan on jurisdictions t_j (cost=0.00..9.17 rows=317 width=41) (actual time=0.004..0.056 rows=317 loops=1)

85. 0.116 0.237 ↑ 1.0 301 1

Hash (cost=10.01..10.01 rows=301 width=36) (actual time=0.237..0.237 rows=301 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
86. 0.121 0.121 ↑ 1.0 301 1

Seq Scan on markets t_m (cost=0.00..10.01 rows=301 width=36) (actual time=0.004..0.121 rows=301 loops=1)

87. 0.526 1.725 ↑ 1.0 2,855 1

Hash (cost=168.53..168.53 rows=2,855 width=31) (actual time=1.725..1.725 rows=2,855 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 209kB
88. 0.584 1.199 ↑ 1.0 2,855 1

Hash Left Join (cost=10.72..168.53 rows=2,855 width=31) (actual time=0.084..1.199 rows=2,855 loops=1)

  • Hash Cond: (t_dv.vehicle_type_pk = t_dvt.pk)
89. 0.541 0.541 ↑ 1.0 2,855 1

Seq Scan on vehicles t_dv (cost=0.00..118.55 rows=2,855 width=19) (actual time=0.004..0.541 rows=2,855 loops=1)

90. 0.028 0.074 ↑ 1.0 121 1

Hash (cost=9.21..9.21 rows=121 width=20) (actual time=0.074..0.074 rows=121 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
91. 0.046 0.046 ↑ 1.0 121 1

Seq Scan on vehicle_types t_dvt (cost=0.00..9.21 rows=121 width=20) (actual time=0.008..0.046 rows=121 loops=1)

92. 0.017 0.046 ↑ 1.0 121 1

Hash (cost=9.21..9.21 rows=121 width=20) (actual time=0.046..0.046 rows=121 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
93. 0.029 0.029 ↑ 1.0 121 1

Seq Scan on vehicle_types t_vt (cost=0.00..9.21 rows=121 width=20) (actual time=0.004..0.029 rows=121 loops=1)

94. 15.553 46.659 ↑ 1.0 1 15,553

Limit (cost=0.43..8.50 rows=1 width=64) (actual time=0.003..0.003 rows=1 loops=15,553)

95. 31.106 31.106 ↑ 1.0 1 15,553

Index Scan Backward using newlr_tc01 on trip_costs tc (cost=0.43..8.50 rows=1 width=64) (actual time=0.002..0.002 rows=1 loops=15,553)

  • Index Cond: ((t.pk = trip_pk) AND (is_estimate = 'N'::bpchar) AND (is_current = 'Y'::bpchar) AND (is_selected = 'Y'::bpchar))
96. 1.074 153.399 ↓ 2.9 8,854 1

Hash (cost=15,449.15..15,449.15 rows=3,075 width=4) (actual time=153.399..153.399 rows=8,854 loops=1)

  • Buckets: 16384 (originally 4096) Batches: 1 (originally 1) Memory Usage: 440kB
97. 6.148 152.325 ↓ 2.9 8,854 1

HashAggregate (cost=15,387.65..15,418.40 rows=3,075 width=4) (actual time=150.983..152.325 rows=8,854 loops=1)

  • Group Key: t_1.pk
98. 4.423 146.177 ↓ 7.4 22,775 1

Hash Join (cost=7,674.52..15,379.97 rows=3,075 width=4) (actual time=70.112..146.177 rows=22,775 loops=1)

  • Hash Cond: (us.lot_pk = l_1.pk)
99. 5.103 141.108 ↓ 7.4 22,775 1

Hash Join (cost=7,585.26..15,248.42 rows=3,075 width=8) (actual time=69.457..141.108 rows=22,775 loops=1)

  • Hash Cond: ((us.status_pk = uss.pk) AND (usr.status_pk = usrs.pk))
100. 4.478 135.989 ↓ 1.3 23,070 1

Hash Join (cost=7,583.17..15,077.20 rows=18,451 width=16) (actual time=69.433..135.989 rows=23,070 loops=1)

  • Hash Cond: (t_1.resource_pk = v.pk)
101. 0.000 121.113 ↓ 1.4 8,854 1

Nested Loop (cost=6,869.21..14,147.13 rows=6,185 width=8) (actual time=59.020..121.113 rows=8,854 loops=1)

102. 2.121 103.903 ↓ 1.4 8,854 1

Hash Join (cost=6,868.79..11,209.87 rows=6,185 width=16) (actual time=58.998..103.903 rows=8,854 loops=1)

  • Hash Cond: (a_1.rate_plan_pk = rp.pk)
103. 3.189 101.507 ↓ 1.6 9,704 1

Nested Loop (cost=6,849.45..11,105.44 rows=6,199 width=20) (actual time=58.714..101.507 rows=9,704 loops=1)

104. 12.509 78.910 ↓ 1.6 9,704 1

Hash Join (cost=6,849.04..8,125.00 rows=6,199 width=12) (actual time=58.684..78.910 rows=9,704 loops=1)

  • Hash Cond: (t_1.driver_pk = d_1.pk)
105. 8.169 8.169 ↓ 1.6 9,704 1

Index Only Scan using newjg_trips_01 on trips t_1 (cost=0.43..519.15 rows=6,199 width=12) (actual time=0.098..8.169 rows=9,704 loops=1)

  • Index Cond: ((unconfirmed = 'N'::bpchar) AND (cancelled = 'N'::bpchar) AND (void = 'N'::bpchar) AND (billing_end >= 1551420000) AND (billing_end <= 1553317199))
  • Heap Fetches: 2805
106. 29.079 58.232 ↓ 1.0 156,119 1

Hash (cost=4,287.16..4,287.16 rows=156,116 width=8) (actual time=58.232..58.232 rows=156,119 loops=1)

  • Buckets: 262144 Batches: 2 Memory Usage: 5098kB
107. 29.153 29.153 ↓ 1.0 156,119 1

Seq Scan on drivers d_1 (cost=0.00..4,287.16 rows=156,116 width=8) (actual time=0.018..29.153 rows=156,119 loops=1)

108. 19.408 19.408 ↑ 1.0 1 9,704

Index Scan using accounts_member_payment_info_pk on accounts a_1 (cost=0.42..0.47 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=9,704)

  • Index Cond: (member_pk = d_1.member_pk)
109. 0.076 0.275 ↑ 1.0 453 1

Hash (cost=13.68..13.68 rows=453 width=4) (actual time=0.275..0.275 rows=453 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
110. 0.199 0.199 ↑ 1.0 453 1

Seq Scan on rate_plans rp (cost=0.00..13.68 rows=453 width=4) (actual time=0.022..0.199 rows=453 loops=1)

  • Filter: (is_billable_exempt = 'N'::bpchar)
  • Rows Removed by Filter: 1
111. 17.708 17.708 ↑ 1.0 1 8,854

Index Only Scan using member_pk_contact_pk on members m_1 (cost=0.42..0.46 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=8,854)

  • Index Cond: (pk = d_1.member_pk)
  • Heap Fetches: 0
112. 1.729 10.398 ↑ 1.0 8,191 1

Hash (cost=611.58..611.58 rows=8,191 width=20) (actual time=10.398..10.398 rows=8,191 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 480kB
113. 2.606 8.669 ↑ 1.0 8,191 1

Hash Join (cost=235.42..611.58 rows=8,191 width=20) (actual time=2.398..8.669 rows=8,191 loops=1)

  • Hash Cond: (usr.stack_pk = us.pk)
114. 2.556 4.588 ↑ 1.0 8,191 1

Hash Join (cost=118.79..382.33 rows=8,191 width=16) (actual time=0.909..4.588 rows=8,191 loops=1)

  • Hash Cond: (usr.vehicle_pk = v.pk)
115. 1.142 1.142 ↑ 1.0 8,191 1

Seq Scan on unified_stack_resources usr (cost=0.00..150.91 rows=8,191 width=12) (actual time=0.004..1.142 rows=8,191 loops=1)

116. 0.470 0.890 ↑ 1.0 2,855 1

Hash (cost=83.11..83.11 rows=2,855 width=4) (actual time=0.890..0.890 rows=2,855 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 133kB
117. 0.420 0.420 ↑ 1.0 2,855 1

Index Only Scan using vehicles_pkey on vehicles v (cost=0.28..83.11 rows=2,855 width=4) (actual time=0.015..0.420 rows=2,855 loops=1)

  • Heap Fetches: 0
118. 0.732 1.475 ↑ 1.0 4,161 1

Hash (cost=64.61..64.61 rows=4,161 width=12) (actual time=1.475..1.475 rows=4,161 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 243kB
119. 0.743 0.743 ↑ 1.0 4,161 1

Seq Scan on unified_stacks us (cost=0.00..64.61 rows=4,161 width=12) (actual time=0.011..0.743 rows=4,161 loops=1)

120. 0.000 0.016 ↑ 1.0 1 1

Hash (cost=2.07..2.07 rows=1 width=8) (actual time=0.016..0.016 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
121. 0.004 0.016 ↑ 1.0 1 1

Nested Loop (cost=0.00..2.07 rows=1 width=8) (actual time=0.014..0.016 rows=1 loops=1)

122. 0.007 0.007 ↑ 1.0 1 1

Seq Scan on unified_stack_status uss (cost=0.00..1.04 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=1)

  • Filter: ((name)::text = 'normal'::text)
  • Rows Removed by Filter: 2
123. 0.005 0.005 ↑ 1.0 1 1

Seq Scan on unified_stack_resource_status usrs (cost=0.00..1.02 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=1)

  • Filter: ((name)::text = 'normal'::text)
  • Rows Removed by Filter: 1
124. 0.327 0.646 ↑ 1.0 2,072 1

Hash (cost=63.36..63.36 rows=2,072 width=4) (actual time=0.646..0.646 rows=2,072 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 105kB
125. 0.319 0.319 ↑ 1.0 2,072 1

Index Only Scan using lots_pkey on lots l_1 (cost=0.28..63.36 rows=2,072 width=4) (actual time=0.018..0.319 rows=2,072 loops=1)

  • Heap Fetches: 0
126. 12.895 982.326 ↑ 4.6 81,301 1

Materialize (cost=79,095.43..93,051.84 rows=372,171 width=44) (actual time=922.022..982.326 rows=81,301 loops=1)

127. 35.785 969.431 ↑ 4.6 81,054 1

GroupAggregate (cost=79,095.43..88,399.70 rows=372,171 width=44) (actual time=922.018..969.431 rows=81,054 loops=1)

  • Group Key: s_1.trip_pk, bp.start_stamp, bp.end_stamp
128. 560.507 933.646 ↑ 4.0 92,315 1

Sort (cost=79,095.43..80,025.85 rows=372,171 width=18) (actual time=922.009..933.646 rows=92,315 loops=1)

  • Sort Key: s_1.trip_pk, bp.start_stamp, bp.end_stamp
  • Sort Method: external merge Disk: 9504kB
129. 79.631 373.139 ↓ 1.1 404,819 1

Hash Join (cost=6.35..37,025.68 rows=372,171 width=18) (actual time=0.075..373.139 rows=404,819 loops=1)

  • Hash Cond: (s_1.billing_period_pk = bp.pk)
130. 116.330 293.464 ↓ 1.1 404,819 1

Hash Join (cost=1.11..31,903.08 rows=372,171 width=14) (actual time=0.025..293.464 rows=404,819 loops=1)

  • Hash Cond: (s_1.sale_type_pk = st_1.pk)
131. 177.125 177.125 ↑ 1.0 462,531 1

Seq Scan on sales s_1 (cost=0.00..26,435.71 rows=465,214 width=18) (actual time=0.008..177.125 rows=462,531 loops=1)

  • Filter: (trip_cost_pk IS NULL)
  • Rows Removed by Filter: 702790
132. 0.001 0.009 ↑ 1.0 4 1

Hash (cost=1.06..1.06 rows=4 width=4) (actual time=0.009..0.009 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
133. 0.008 0.008 ↑ 1.0 4 1

Seq Scan on sale_types st_1 (cost=0.00..1.06 rows=4 width=4) (actual time=0.006..0.008 rows=4 loops=1)

  • Filter: ((name)::text <> 'driving_credit'::text)
  • Rows Removed by Filter: 1
134. 0.013 0.044 ↑ 1.0 144 1

Hash (cost=3.44..3.44 rows=144 width=12) (actual time=0.044..0.044 rows=144 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
135. 0.031 0.031 ↑ 1.0 144 1

Seq Scan on billing_periods bp (cost=0.00..3.44 rows=144 width=12) (actual time=0.005..0.031 rows=144 loops=1)

136. 12.706 919.863 ↑ 4.6 81,301 1

Materialize (cost=79,095.43..93,051.84 rows=372,171 width=40) (actual time=860.970..919.863 rows=81,301 loops=1)

137. 34.856 907.157 ↑ 4.6 81,054 1

GroupAggregate (cost=79,095.43..88,399.70 rows=372,171 width=44) (actual time=860.966..907.157 rows=81,054 loops=1)

  • Group Key: s_2.trip_pk, bp_1.start_stamp, bp_1.end_stamp
138. 530.909 872.301 ↑ 4.0 92,315 1

Sort (cost=79,095.43..80,025.85 rows=372,171 width=18) (actual time=860.957..872.301 rows=92,315 loops=1)

  • Sort Key: s_2.trip_pk, bp_1.start_stamp, bp_1.end_stamp
  • Sort Method: external merge Disk: 9504kB
139. 77.209 341.392 ↓ 1.1 404,819 1

Hash Join (cost=6.35..37,025.68 rows=372,171 width=18) (actual time=0.083..341.392 rows=404,819 loops=1)

  • Hash Cond: (s_2.billing_period_pk = bp_1.pk)
140. 112.963 264.140 ↓ 1.1 404,819 1

Hash Join (cost=1.11..31,903.08 rows=372,171 width=14) (actual time=0.035..264.140 rows=404,819 loops=1)

  • Hash Cond: (s_2.sale_type_pk = st_2.pk)
141. 151.166 151.166 ↑ 1.0 462,531 1

Seq Scan on sales s_2 (cost=0.00..26,435.71 rows=465,214 width=18) (actual time=0.017..151.166 rows=462,531 loops=1)

  • Filter: (trip_cost_pk IS NULL)
  • Rows Removed by Filter: 702790
142. 0.003 0.011 ↑ 1.0 4 1

Hash (cost=1.06..1.06 rows=4 width=4) (actual time=0.011..0.011 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
143. 0.008 0.008 ↑ 1.0 4 1

Seq Scan on sale_types st_2 (cost=0.00..1.06 rows=4 width=4) (actual time=0.007..0.008 rows=4 loops=1)

  • Filter: ((name)::text <> 'driving_credit'::text)
  • Rows Removed by Filter: 1
144. 0.016 0.043 ↑ 1.0 144 1

Hash (cost=3.44..3.44 rows=144 width=12) (actual time=0.043..0.043 rows=144 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
145. 0.027 0.027 ↑ 1.0 144 1

Seq Scan on billing_periods bp_1 (cost=0.00..3.44 rows=144 width=12) (actual time=0.005..0.027 rows=144 loops=1)

146. 8.099 264.915 ↑ 4.2 55,499 1

Materialize (cost=57,894.71..65,468.31 rows=233,034 width=36) (actual time=230.901..264.915 rows=55,499 loops=1)

147. 20.819 256.816 ↑ 4.2 55,499 1

GroupAggregate (cost=57,894.71..62,555.39 rows=233,034 width=36) (actual time=230.897..256.816 rows=55,499 loops=1)

  • Group Key: s_3.trip_pk
148. 16.732 235.997 ↑ 4.2 55,710 1

Sort (cost=57,894.71..58,477.29 rows=233,034 width=10) (actual time=230.887..235.997 rows=55,710 loops=1)

  • Sort Key: s_3.trip_pk
  • Sort Method: quicksort Memory: 4242kB
149. 135.901 219.265 ↑ 4.0 57,712 1

Hash Join (cost=1.07..33,136.52 rows=233,034 width=10) (actual time=0.040..219.265 rows=57,712 loops=1)

  • Hash Cond: (s_3.sale_type_pk = st_3.pk)
150. 83.353 83.353 ↓ 1.0 1,165,321 1

Seq Scan on sales s_3 (cost=0.00..26,435.71 rows=1,165,171 width=14) (actual time=0.014..83.353 rows=1,165,321 loops=1)

151. 0.001 0.011 ↑ 1.0 1 1

Hash (cost=1.06..1.06 rows=1 width=4) (actual time=0.011..0.011 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
152. 0.010 0.010 ↑ 1.0 1 1

Seq Scan on sale_types st_3 (cost=0.00..1.06 rows=1 width=4) (actual time=0.009..0.010 rows=1 loops=1)

  • Filter: ((name)::text = 'driving_credit'::text)
  • Rows Removed by Filter: 4
153. 0.072 0.148 ↑ 1.0 454 1

Hash (cost=12.54..12.54 rows=454 width=39) (actual time=0.148..0.148 rows=454 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 41kB
154. 0.076 0.076 ↑ 1.0 454 1

Seq Scan on rate_plans tc_rp (cost=0.00..12.54 rows=454 width=39) (actual time=0.011..0.076 rows=454 loops=1)

155. 0.001 0.011 ↑ 1.0 8 1

Hash (cost=1.08..1.08 rows=8 width=12) (actual time=0.011..0.011 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
156. 0.010 0.010 ↑ 1.0 8 1

Seq Scan on rate_codes tc_rc (cost=0.00..1.08 rows=8 width=12) (actual time=0.008..0.010 rows=8 loops=1)

157. 10.538 234.650 ↓ 7.0 7 12,350

Nested Loop (cost=0.41..8.30 rows=1 width=16) (actual time=0.009..0.019 rows=7 loops=12,350)

158. 135.850 135.850 ↓ 7.0 7 12,350

Index Scan using idx_unified_stack_resources_stack_pk on unified_stack_resources r_usr (cost=0.28..8.14 rows=1 width=20) (actual time=0.008..0.011 rows=7 loops=12,350)

  • Index Cond: (r_us.pk = stack_pk)
  • Filter: ((r.end_stamp > start_stamp) AND (r.end_stamp <= end_stamp))
  • Rows Removed by Filter: 28
159. 88.262 88.262 ↑ 1.0 1 88,262

Index Scan using unified_stack_resource_status_pkey on unified_stack_resource_status r_usrs (cost=0.13..0.15 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=88,262)

  • Index Cond: (pk = r_usr.status_pk)
  • Filter: ((name)::text = 'normal'::text)
  • Rows Removed by Filter: 0
160. 88.235 88.235 ↑ 1.0 1 88,235

Index Scan using vehicles_pkey on vehicles r_v (cost=0.28..0.35 rows=1 width=19) (actual time=0.001..0.001 rows=1 loops=88,235)

  • Index Cond: (r_usr.vehicle_pk = pk)
161. 88.235 88.235 ↑ 1.0 1 88,235

Index Scan using vehicle_types_pkey on vehicle_types r_vt (cost=0.14..0.17 rows=1 width=20) (actual time=0.001..0.001 rows=1 loops=88,235)

  • Index Cond: (r_v.vehicle_type_pk = pk)