explain.depesz.com

PostgreSQL's explain analyze made readable

Result: goJM : original

Settings
# exclusive inclusive rows x rows loops node
1. 189.369 36,899.534 ↓ 421.6 8,854 1

Unique (cost=575,672.44..575,675.54 rows=21 width=1,007) (actual time=36,617.133..36,899.534 rows=8,854 loops=1)

2.          

CTE mvsm_stacks

3. 0.164 0.164 ↑ 1.0 21 1

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

  • Filter: (vehicle_type_pk IS NOT NULL)
  • Rows Removed by Filter: 4140
4. 2,525.669 36,710.165 ↓ 4,201.7 88,235 1

Sort (cost=575,607.83..575,607.88 rows=21 width=1,007) (actual time=36,617.131..36,710.165 rows=88,235 loops=1)

  • Sort Key: mvsm_grouping.trip_billing_end_stamp, mvsm_grouping.trip_id, mvsm_grouping.member_id, mvsm_grouping.driver_id, mvsm_grouping.driver_name, mvsm_grouping.member_name, mvsm_grouping.member_type, mvsm_grouping.account_rate_plan, mvsm_grouping.job_code, mvsm_grouping.job_category, (CASE WHEN (mvsm_grouping.mvsm_pk > 0) THEN NULL::integer ELSE mvsm_grouping.reserved_vehicle_id END), (CASE WHEN (mvsm_grouping.mvsm_pk > 0) THEN NULL::character varying ELSE mvsm_grouping.reserved_vehicle_plate END), mvsm_grouping.reserved_vehicle_type, mvsm_grouping.reserved_lot_id, mvsm_grouping.reserved_lot, mvsm_grouping.reserved_jurisdiction, mvsm_grouping.reserved_market, mvsm_grouping.reserved_start_stamp, mvsm_grouping.reserved_end_stamp, mvsm_grouping.reserved_status, mvsm_grouping.trip_status, mvsm_grouping.trip_status_name, mvsm_grouping.trip_start_stamp, mvsm_grouping.trip_end_stamp, mvsm_grouping.trip_time_used, mvsm_grouping.trip_billing_start_stamp, mvsm_grouping.trip_time_billed, mvsm_grouping.trip_start_odo, mvsm_grouping.trip_end_odo, mvsm_grouping.trip_distance_used, mvsm_grouping.trip_did_not_drive, mvsm_grouping.member_notes, mvsm_grouping.admin_notes, mvsm_grouping.trip_driven_vehicle_id, mvsm_grouping.trip_driven_vehicle_plate, mvsm_grouping.trip_driven_vehicle_descr, mvsm_grouping.trip_vehicle_id, mvsm_grouping.trip_vehicle_plate, mvsm_grouping.trip_vehicle_descr, mvsm_grouping.trip_lot_id, mvsm_grouping.trip_lot, mvsm_grouping.trip_jurisdiction, mvsm_grouping.trip_market, mvsm_grouping.rate_plan_id, mvsm_grouping.rate_plan, mvsm_grouping.rate_code, mvsm_grouping.time_amount, mvsm_grouping.distance_amount, mvsm_grouping.total_amount, mvsm_grouping.fee_amount, mvsm_grouping.tax_amount, mvsm_grouping.grand_total_amount, mvsm_grouping.billed_amount, mvsm_grouping.pc_sales_total_amount, mvsm_grouping.pc_sales_other_bp_total_amount, mvsm_grouping.dc_sales_total_amount, mvsm_grouping.reserved_stack_id, mvsm_grouping.trip_stack_id
  • Sort Method: external merge Disk: 46032kB
5. 39.547 34,184.496 ↓ 4,201.7 88,235 1

Subquery Scan on mvsm_grouping (cost=434,232.42..575,607.37 rows=21 width=1,007) (actual time=26,737.478..34,184.496 rows=88,235 loops=1)

6. 7,106.912 34,144.949 ↓ 4,201.7 88,235 1

Merge Right Join (cost=434,232.42..575,607.05 rows=21 width=1,065) (actual time=26,737.469..34,144.949 rows=88,235 loops=1)

  • Merge Cond: (s.trip_pk = t.pk)
7. 293.614 1,285.165 ↓ 1.3 661,079 1

GroupAggregate (cost=0.43..96,764.47 rows=524,364 width=36) (actual time=31.393..1,285.165 rows=661,079 loops=1)

  • Group Key: s.trip_pk
8. 548.214 991.551 ↓ 1.2 698,853 1

Nested Loop (cost=0.43..87,405.02 rows=560,981 width=10) (actual time=31.087..991.551 rows=698,853 loops=1)

  • Join Filter: (s.sale_type_pk = st.pk)
  • Rows Removed by Join Filter: 2096557
9. 443.337 443.337 ↑ 1.0 698,853 1

Index Scan using sales_trip_pk_idx on sales s (cost=0.43..45,330.39 rows=701,226 width=14) (actual time=31.052..443.337 rows=698,853 loops=1)

  • Filter: (trip_cost_pk IS NOT NULL)
  • Rows Removed by Filter: 148528
10. 0.000 0.000 ↑ 1.0 4 698,853

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

11. 0.017 0.017 ↑ 1.0 4 1

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

  • Filter: ((name)::text <> 'driving_credit'::text)
  • Rows Removed by Filter: 1
12. 64.579 25,752.872 ↓ 4,201.7 88,235 1

Materialize (cost=434,231.99..472,277.08 rows=21 width=690) (actual time=25,510.178..25,752.872 rows=88,235 loops=1)

13. 28.455 25,688.293 ↓ 4,201.7 88,235 1

Merge Left Join (cost=434,231.99..472,277.03 rows=21 width=690) (actual time=25,510.173..25,688.293 rows=88,235 loops=1)

  • Merge Cond: (t.pk = s_3.trip_pk)
14. 29.519 25,398.285 ↓ 4,201.7 88,235 1

Merge Left Join (cost=376,241.12..406,110.56 rows=21 width=658) (actual time=25,251.472..25,398.285 rows=88,235 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
15. 65.189 24,431.506 ↓ 4,201.7 88,235 1

Merge Left Join (cost=296,951.66..311,886.41 rows=21 width=626) (actual time=24,309.637..24,431.506 rows=88,235 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: 5381
16. 190.691 23,374.254 ↓ 4,201.7 88,235 1

Sort (cost=217,662.20..217,662.26 rows=21 width=594) (actual time=23,313.197..23,374.254 rows=88,235 loops=1)

  • Sort Key: t.pk
  • Sort Method: external merge Disk: 43952kB
17. 30.865 23,183.563 ↓ 4,201.7 88,235 1

Hash Semi Join (cost=208,444.26..217,661.74 rows=21 width=594) (actual time=7,634.191..23,183.563 rows=88,235 loops=1)

  • Hash Cond: (t.pk = t_1.pk)
18. 32.325 21,785.792 ↓ 11.5 110,754 1

Hash Left Join (cost=177,705.16..186,897.21 rows=9,596 width=594) (actual time=6,267.256..21,785.792 rows=110,754 loops=1)

  • Hash Cond: (tc.rate_code_pk = tc_rc.pk)
19. 32.658 21,753.444 ↓ 11.5 110,754 1

Hash Left Join (cost=177,703.98..186,764.09 rows=9,596 width=590) (actual time=6,267.222..21,753.444 rows=110,754 loops=1)

  • Hash Cond: (tc.rate_plan_pk = tc_rp.pk)
20. 720.154 21,720.405 ↓ 11.5 110,754 1

Hash Right Join (cost=177,685.76..186,613.93 rows=9,596 width=559) (actual time=6,266.821..21,720.405 rows=110,754 loops=1)

  • Hash Cond: (tc.trip_pk = t.pk)
21. 586.418 17,491.737 ↑ 1.4 1,405,309 1

Nested Loop (cost=124,343.70..126,032.77 rows=1,904,836 width=28) (actual time=2,169.340..17,491.737 rows=1,405,309 loops=1)

22. 1,501.506 2,852.229 ↓ 7,026.5 1,405,309 1

HashAggregate (cost=124,343.27..124,345.27 rows=200 width=4) (actual time=2,166.396..2,852.229 rows=1,405,309 loops=1)

  • Group Key: max(tc_1.pk)
23. 441.530 1,350.723 ↓ 3.2 1,405,309 1

GroupAggregate (cost=0.43..118,859.46 rows=438,705 width=8) (actual time=0.119..1,350.723 rows=1,405,309 loops=1)

  • Group Key: tc_1.trip_pk
24. 909.193 909.193 ↓ 2.6 1,405,380 1

Index Only Scan using newlr_tc01 on trip_costs tc_1 (cost=0.43..111,773.28 rows=539,827 width=8) (actual time=0.116..909.193 rows=1,405,380 loops=1)

  • Index Cond: ((is_estimate = 'N'::bpchar) AND (is_current = 'Y'::bpchar) AND (is_selected = 'Y'::bpchar))
  • Heap Fetches: 59678
25. 14,053.090 14,053.090 ↑ 1.0 1 1,405,309

Index Scan using trip_costs_pkey on trip_costs tc (cost=0.43..8.43 rows=1 width=32) (actual time=0.010..0.010 rows=1 loops=1,405,309)

  • Index Cond: (pk = (max(tc_1.pk)))
26. 113.560 3,508.514 ↓ 15.6 110,754 1

Hash (cost=53,253.35..53,253.35 rows=7,097 width=535) (actual time=3,508.514..3,508.514 rows=110,754 loops=1)

  • Buckets: 32768 (originally 8192) Batches: 16 (originally 1) Memory Usage: 7937kB
27. 30.175 3,394.954 ↓ 15.6 110,754 1

Hash Left Join (cost=52,581.40..53,253.35 rows=7,097 width=535) (actual time=3,159.107..3,394.954 rows=110,754 loops=1)

  • Hash Cond: (t_j.market_pk = t_m.pk)
28. 33.432 3,364.664 ↓ 15.6 110,754 1

Hash Left Join (cost=52,567.62..53,141.99 rows=7,097 width=507) (actual time=3,158.970..3,364.664 rows=110,754 loops=1)

  • Hash Cond: (t.vehicle_pk = t_dv.pk)
29. 33.216 3,329.578 ↓ 15.6 110,754 1

Hash Left Join (cost=52,363.48..52,858.28 rows=7,097 width=484) (actual time=3,157.283..3,329.578 rows=110,754 loops=1)

  • Hash Cond: (t_us.lot_pk = t_l.pk)
30. 160.900 3,294.718 ↓ 15.6 110,754 1

Hash Right Join (cost=52,191.24..52,588.46 rows=7,097 width=408) (actual time=3,155.591..3,294.718 rows=110,754 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: 441677
31. 2.906 7.389 ↓ 2.0 8,049 1

Hash Left Join (cost=204.43..476.37 rows=4,096 width=28) (actual time=1.569..7.389 rows=8,049 loops=1)

  • Hash Cond: (t_usr.vehicle_pk = t_v.pk)
32. 1.213 2.959 ↓ 2.0 8,049 1

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

33. 0.015 0.015 ↑ 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.014..0.015 rows=1 loops=1)

  • Filter: ((name)::text = 'normal'::text)
  • Rows Removed by Filter: 1
34. 1.731 1.731 ↓ 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.011..1.731 rows=8,049 loops=1)

  • Index Cond: (status_pk = t_usrs.pk)
35. 0.459 1.524 ↓ 1.0 2,855 1

Hash (cost=168.48..168.48 rows=2,853 width=20) (actual time=1.524..1.524 rows=2,855 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 176kB
36. 0.540 1.065 ↓ 1.0 2,855 1

Hash Left Join (cost=10.72..168.48 rows=2,853 width=20) (actual time=0.070..1.065 rows=2,855 loops=1)

  • Hash Cond: (t_v.vehicle_type_pk = t_vt.pk)
37. 0.474 0.474 ↓ 1.0 2,855 1

Seq Scan on vehicles t_v (cost=0.00..118.53 rows=2,853 width=8) (actual time=0.003..0.474 rows=2,855 loops=1)

38. 0.020 0.051 ↑ 1.0 121 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
39. 0.031 0.031 ↑ 1.0 121 1

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

40. 15.768 3,126.429 ↓ 2.2 15,453 1

Hash (cost=51,898.10..51,898.10 rows=7,097 width=396) (actual time=3,126.429..3,126.429 rows=15,453 loops=1)

  • Buckets: 16384 (originally 8192) Batches: 1 (originally 1) Memory Usage: 5389kB
41. 6.160 3,110.661 ↓ 2.2 15,453 1

Hash Left Join (cost=51,305.71..51,898.10 rows=7,097 width=396) (actual time=3,080.508..3,110.661 rows=15,453 loops=1)

  • Hash Cond: (t.billing_stack_pk = t_us.pk)
42. 5.741 3,103.405 ↓ 2.2 15,453 1

Hash Left Join (cost=51,189.09..51,683.89 rows=7,097 width=388) (actual time=3,079.362..3,103.405 rows=15,453 loops=1)

  • Hash Cond: (r_us.lot_pk = r_l.pk)
43. 15.383 3,090.168 ↓ 2.2 15,453 1

Hash Right Join (cost=50,998.72..51,395.94 rows=7,097 width=284) (actual time=3,071.830..3,090.168 rows=15,453 loops=1)

  • Hash Cond: (r_usr.stack_pk = r_us.pk)
  • Join Filter: ((r.end_stamp > r_usr.start_stamp) AND (r.end_stamp <= r_usr.end_stamp))
  • Rows Removed by Join Filter: 20155
44. 2.242 15.564 ↓ 2.0 8,049 1

Hash Left Join (cost=204.43..476.37 rows=4,096 width=39) (actual time=10.171..15.564 rows=8,049 loops=1)

  • Hash Cond: (r_usr.vehicle_pk = r_v.pk)
45. 1.217 3.243 ↓ 2.0 8,049 1

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

46. 0.016 0.016 ↑ 1.0 1 1

Seq Scan on unified_stack_resource_status r_usrs (cost=0.00..1.02 rows=1 width=4) (actual time=0.015..0.016 rows=1 loops=1)

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

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

  • Index Cond: (status_pk = r_usrs.pk)
48. 0.630 10.079 ↓ 1.0 2,855 1

Hash (cost=168.48..168.48 rows=2,853 width=31) (actual time=10.079..10.079 rows=2,855 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 209kB
49. 0.658 9.449 ↓ 1.0 2,855 1

Hash Left Join (cost=10.72..168.48 rows=2,853 width=31) (actual time=2.892..9.449 rows=2,855 loops=1)

  • Hash Cond: (r_v.vehicle_type_pk = r_vt.pk)
50. 7.045 7.045 ↓ 1.0 2,855 1

Seq Scan on vehicles r_v (cost=0.00..118.53 rows=2,853 width=19) (actual time=1.134..7.045 rows=2,855 loops=1)

51. 0.026 1.746 ↑ 1.0 121 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
52. 1.720 1.720 ↑ 1.0 121 1

Seq Scan on vehicle_types r_vt (cost=0.00..9.21 rows=121 width=20) (actual time=0.879..1.720 rows=121 loops=1)

53. 17.084 3,059.221 ↓ 1.6 11,188 1

Hash (cost=50,705.58..50,705.58 rows=7,097 width=261) (actual time=3,059.221..3,059.221 rows=11,188 loops=1)

  • Buckets: 16384 (originally 8192) Batches: 1 (originally 1) Memory Usage: 2518kB
54. 10.134 3,042.137 ↓ 1.6 11,188 1

Hash Left Join (cost=7,457.80..50,705.58 rows=7,097 width=261) (actual time=81.713..3,042.137 rows=11,188 loops=1)

  • Hash Cond: (r.stack_pk = r_us.pk)
55. 5.845 3,030.934 ↓ 1.6 11,188 1

Hash Left Join (cost=7,341.17..50,491.37 rows=7,097 width=253) (actual time=80.596..3,030.934 rows=11,188 loops=1)

  • Hash Cond: (r.status_pk = rs.pk)
56. 7.693 3,025.073 ↓ 1.6 11,188 1

Hash Join (cost=7,340.04..50,392.65 rows=7,097 width=249) (actual time=80.558..3,025.073 rows=11,188 loops=1)

  • Hash Cond: (a.rate_plan_pk = a_rp.pk)
57. 8.547 3,017.213 ↓ 1.6 11,188 1

Nested Loop Left Join (cost=7,321.82..50,276.85 rows=7,097 width=222) (actual time=80.382..3,017.213 rows=11,188 loops=1)

58. 10.477 2,359.762 ↓ 1.6 11,188 1

Nested Loop (cost=7,316.08..46,589.55 rows=7,097 width=189) (actual time=78.424..2,359.762 rows=11,188 loops=1)

59. 7.530 2,304.533 ↓ 1.6 11,188 1

Hash Join (cost=7,315.67..43,177.62 rows=7,097 width=193) (actual time=78.414..2,304.533 rows=11,188 loops=1)

  • Hash Cond: (m.member_type_pk = mt.pk)
60. 5.807 2,296.992 ↓ 1.6 11,188 1

Nested Loop (cost=7,314.58..43,078.94 rows=7,097 width=187) (actual time=78.390..2,296.992 rows=11,188 loops=1)

61. 11.553 2,224.057 ↓ 1.6 11,188 1

Nested Loop (cost=7,314.16..38,944.23 rows=7,097 width=161) (actual time=78.370..2,224.057 rows=11,188 loops=1)

62. 7.328 433.612 ↓ 1.6 11,188 1

Nested Loop (cost=7,313.74..27,256.29 rows=7,097 width=152) (actual time=76.127..433.612 rows=11,188 loops=1)

63. 5.795 124.208 ↓ 1.6 11,188 1

Hash Join (cost=7,313.32..23,887.47 rows=7,097 width=156) (actual time=73.721..124.208 rows=11,188 loops=1)

  • Hash Cond: (t.status_pk = ts.pk)
64. 27.540 118.405 ↓ 1.6 11,188 1

Hash Join (cost=7,312.25..23,788.82 rows=7,097 width=142) (actual time=73.703..118.405 rows=11,188 loops=1)

  • Hash Cond: (t.driver_pk = d.pk)
65. 17.975 18.849 ↓ 1.6 11,188 1

Bitmap Heap Scan on trips t (cost=153.97..15,352.96 rows=7,097 width=127) (actual time=1.098..18.849 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
66. 0.874 0.874 ↓ 1.6 11,374 1

Bitmap Index Scan on trips_inx7 (cost=0.00..152.20 rows=7,177 width=0) (actual time=0.874..0.874 rows=11,374 loops=1)

  • Index Cond: ((billing_end >= 1551420000) AND (billing_end <= 1553317199))
67. 36.160 72.016 ↑ 1.0 156,229 1

Hash (cost=4,288.79..4,288.79 rows=156,279 width=23) (actual time=72.016..72.016 rows=156,229 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 5397kB
68. 35.856 35.856 ↑ 1.0 156,229 1

Seq Scan on drivers d (cost=0.00..4,288.79 rows=156,279 width=23) (actual time=0.009..35.856 rows=156,229 loops=1)

69. 0.004 0.008 ↑ 1.0 3 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
70. 0.004 0.004 ↑ 1.0 3 1

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

71. 302.076 302.076 ↑ 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.027..0.027 rows=1 loops=11,188)

  • Index Cond: (pk = d.login_pk)
  • Heap Fetches: 1422
72. 1,778.892 1,778.892 ↑ 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.157..0.159 rows=1 loops=11,188)

  • Index Cond: (pk = d.contact_pk)
73. 67.128 67.128 ↑ 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.005..0.006 rows=1 loops=11,188)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
75. 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.003..0.005 rows=4 loops=1)

76. 44.752 44.752 ↑ 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.004..0.004 rows=1 loops=11,188)

  • Index Cond: (member_pk = d.member_pk)
77. 100.692 648.904 ↑ 1.0 1 11,188

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

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

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

79. 11.188 514.648 ↑ 1.0 1 11,188

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
80. 503.460 503.460 ↑ 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.045..0.045 rows=1 loops=11,188)

  • Index Cond: (t.reservation_pk = pk)
81. 0.085 0.167 ↑ 1.0 454 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 39kB
82. 0.082 0.082 ↑ 1.0 454 1

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

83. 0.009 0.016 ↑ 1.0 6 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
84. 0.007 0.007 ↑ 1.0 6 1

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

85. 0.609 1.069 ↑ 1.0 4,161 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 260kB
86. 0.460 0.460 ↑ 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.003..0.460 rows=4,161 loops=1)

87. 0.653 7.496 ↑ 1.0 2,072 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 326kB
88. 0.656 6.843 ↑ 1.0 2,072 1

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

  • Hash Cond: (r_l.jurisdiction_pk = r_j.pk)
89. 5.521 5.521 ↑ 1.0 2,072 1

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

90. 0.080 0.666 ↑ 1.0 317 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 41kB
91. 0.088 0.586 ↑ 1.0 317 1

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

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

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

93. 0.060 0.138 ↑ 1.0 301 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
94. 0.078 0.078 ↑ 1.0 301 1

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

95. 0.615 1.096 ↑ 1.0 4,161 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 260kB
96. 0.481 0.481 ↑ 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.006..0.481 rows=4,161 loops=1)

97. 0.549 1.644 ↑ 1.0 2,072 1

Hash (cost=146.34..146.34 rows=2,072 width=84) (actual time=1.643..1.644 rows=2,072 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 273kB
98. 0.787 1.095 ↑ 1.0 2,072 1

Hash Left Join (cost=13.13..146.34 rows=2,072 width=84) (actual time=0.157..1.095 rows=2,072 loops=1)

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

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

100. 0.070 0.121 ↑ 1.0 317 1

Hash (cost=9.17..9.17 rows=317 width=41) (actual time=0.121..0.121 rows=317 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 32kB
101. 0.051 0.051 ↑ 1.0 317 1

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

102. 0.544 1.654 ↓ 1.0 2,855 1

Hash (cost=168.48..168.48 rows=2,853 width=31) (actual time=1.654..1.654 rows=2,855 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 209kB
103. 0.542 1.110 ↓ 1.0 2,855 1

Hash Left Join (cost=10.72..168.48 rows=2,853 width=31) (actual time=0.070..1.110 rows=2,855 loops=1)

  • Hash Cond: (t_dv.vehicle_type_pk = t_dvt.pk)
104. 0.515 0.515 ↓ 1.0 2,855 1

Seq Scan on vehicles t_dv (cost=0.00..118.53 rows=2,853 width=19) (actual time=0.005..0.515 rows=2,855 loops=1)

105. 0.028 0.053 ↑ 1.0 121 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
106. 0.025 0.025 ↑ 1.0 121 1

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

107. 0.053 0.115 ↑ 1.0 301 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
108. 0.062 0.062 ↑ 1.0 301 1

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

109. 0.186 0.381 ↑ 1.0 454 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 41kB
110. 0.195 0.195 ↑ 1.0 454 1

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

111. 0.008 0.023 ↑ 1.0 8 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
112. 0.015 0.015 ↑ 1.0 8 1

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

113. 1.228 1,366.906 ↓ 2.9 8,854 1

Hash (cost=30,700.73..30,700.73 rows=3,070 width=4) (actual time=1,366.906..1,366.906 rows=8,854 loops=1)

  • Buckets: 16384 (originally 4096) Batches: 1 (originally 1) Memory Usage: 440kB
114. 2.245 1,365.678 ↓ 2.9 8,854 1

Unique (cost=30,654.68..30,670.03 rows=3,070 width=4) (actual time=1,361.370..1,365.678 rows=8,854 loops=1)

115. 11.607 1,363.433 ↓ 7.4 22,775 1

Sort (cost=30,654.68..30,662.36 rows=3,070 width=4) (actual time=1,361.369..1,363.433 rows=22,775 loops=1)

  • Sort Key: t_1.pk
  • Sort Method: quicksort Memory: 1836kB
116. 10.935 1,351.826 ↓ 7.4 22,775 1

Hash Join (cost=7,607.20..30,476.87 rows=3,070 width=4) (actual time=91.514..1,351.826 rows=22,775 loops=1)

  • Hash Cond: (us.lot_pk = l_1.pk)
117. 10.780 1,339.746 ↓ 7.4 22,775 1

Hash Join (cost=7,517.94..30,345.39 rows=3,070 width=8) (actual time=90.343..1,339.746 rows=22,775 loops=1)

  • Hash Cond: ((us.status_pk = uss.pk) AND (usr.status_pk = usrs.pk))
118. 13.513 1,328.944 ↓ 1.3 23,070 1

Hash Join (cost=7,515.85..30,174.43 rows=18,423 width=16) (actual time=90.301..1,328.944 rows=23,070 loops=1)

  • Hash Cond: (usr.stack_pk = us.pk)
119. 11.080 1,313.976 ↓ 1.3 23,070 1

Hash Join (cost=7,399.23..29,804.50 rows=18,423 width=12) (actual time=88.806..1,313.976 rows=23,070 loops=1)

  • Hash Cond: (t_1.resource_pk = usr.vehicle_pk)
120. 6.052 1,296.532 ↓ 1.4 8,854 1

Hash Join (cost=7,145.93..29,204.69 rows=6,182 width=12) (actual time=82.398..1,296.532 rows=8,854 loops=1)

  • Hash Cond: (t_1.resource_pk = v.pk)
121. 4.584 1,286.786 ↓ 1.4 8,854 1

Nested Loop (cost=7,027.20..29,000.95 rows=6,182 width=8) (actual time=78.670..1,286.786 rows=8,854 loops=1)

122. 5.419 1,043.144 ↓ 1.4 8,854 1

Hash Join (cost=7,026.78..26,065.31 rows=6,182 width=16) (actual time=77.004..1,043.144 rows=8,854 loops=1)

  • Hash Cond: (a_1.rate_plan_pk = rp.pk)
123. 10.383 1,036.053 ↓ 1.6 9,704 1

Nested Loop (cost=7,007.44..25,960.91 rows=6,196 width=20) (actual time=75.299..1,036.053 rows=9,704 loops=1)

124. 28.801 744.254 ↓ 1.6 9,704 1

Hash Join (cost=7,007.02..22,982.15 rows=6,196 width=12) (actual time=73.486..744.254 rows=9,704 loops=1)

  • Hash Cond: (t_1.driver_pk = d_1.pk)
125. 643.686 648.957 ↓ 1.6 9,704 1

Bitmap Heap Scan on trips t_1 (cost=153.75..15,370.67 rows=6,196 width=12) (actual time=5.788..648.957 rows=9,704 loops=1)

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

Bitmap Index Scan on trips_inx7 (cost=0.00..152.20 rows=7,177 width=0) (actual time=5.271..5.271 rows=11,374 loops=1)

  • Index Cond: ((billing_end >= 1551420000) AND (billing_end <= 1553317199))
127. 32.481 66.496 ↑ 1.0 156,229 1

Hash (cost=4,288.79..4,288.79 rows=156,279 width=8) (actual time=66.496..66.496 rows=156,229 loops=1)

  • Buckets: 262144 Batches: 2 Memory Usage: 5101kB
128. 34.015 34.015 ↑ 1.0 156,229 1

Seq Scan on drivers d_1 (cost=0.00..4,288.79 rows=156,279 width=8) (actual time=0.020..34.015 rows=156,229 loops=1)

129. 281.416 281.416 ↑ 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.029..0.029 rows=1 loops=9,704)

  • Index Cond: (member_pk = d_1.member_pk)
130. 0.069 1.672 ↑ 1.0 453 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
131. 1.603 1.603 ↑ 1.0 453 1

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

  • Filter: (is_billable_exempt = 'N'::bpchar)
  • Rows Removed by Filter: 1
132. 239.058 239.058 ↑ 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.026..0.027 rows=1 loops=8,854)

  • Index Cond: (pk = d_1.member_pk)
  • Heap Fetches: 507
133. 0.381 3.694 ↓ 1.0 2,855 1

Hash (cost=83.08..83.08 rows=2,853 width=4) (actual time=3.694..3.694 rows=2,855 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 133kB
134. 3.313 3.313 ↓ 1.0 2,855 1

Index Only Scan using vehicles_pkey on vehicles v (cost=0.28..83.08 rows=2,853 width=4) (actual time=0.721..3.313 rows=2,855 loops=1)

  • Heap Fetches: 110
135. 1.188 6.364 ↑ 1.0 8,191 1

Hash (cost=150.91..150.91 rows=8,191 width=12) (actual time=6.364..6.364 rows=8,191 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 416kB
136. 5.176 5.176 ↑ 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..5.176 rows=8,191 loops=1)

137. 0.581 1.455 ↑ 1.0 4,161 1

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

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

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

139. 0.004 0.022 ↑ 1.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
140. 0.001 0.018 ↑ 1.0 1 1

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

141. 0.009 0.009 ↑ 1.0 1 1

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

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

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

  • Filter: ((name)::text = 'normal'::text)
  • Rows Removed by Filter: 1
143. 0.260 1.145 ↑ 1.0 2,072 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 105kB
144. 0.885 0.885 ↑ 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.684..0.885 rows=2,072 loops=1)

  • Heap Fetches: 0
145. 13.269 992.063 ↑ 4.3 86,793 1

Materialize (cost=79,289.46..93,290.61 rows=373,364 width=44) (actual time=931.677..992.063 rows=86,793 loops=1)

146. 35.583 978.794 ↑ 4.6 81,426 1

GroupAggregate (cost=79,289.46..88,623.56 rows=373,364 width=44) (actual time=931.673..978.794 rows=81,426 loops=1)

  • Group Key: s_1.trip_pk, bp.start_stamp, bp.end_stamp
147. 568.469 943.211 ↑ 4.0 92,811 1

Sort (cost=79,289.46..80,222.87 rows=373,364 width=18) (actual time=931.661..943.211 rows=92,811 loops=1)

  • Sort Key: s_1.trip_pk, bp.start_stamp, bp.end_stamp
  • Sort Method: external merge Disk: 9528kB
148. 77.641 374.742 ↓ 1.1 405,686 1

Hash Join (cost=6.35..37,076.20 rows=373,364 width=18) (actual time=0.114..374.742 rows=405,686 loops=1)

  • Hash Cond: (s_1.billing_period_pk = bp.pk)
149. 113.403 297.053 ↓ 1.1 405,686 1

Hash Join (cost=1.11..31,937.21 rows=373,364 width=14) (actual time=0.056..297.053 rows=405,686 loops=1)

  • Hash Cond: (s_1.sale_type_pk = st_1.pk)
150. 183.630 183.630 ↑ 1.0 463,409 1

Seq Scan on sales s_1 (cost=0.00..26,452.31 rows=466,705 width=18) (actual time=0.025..183.630 rows=463,409 loops=1)

  • Filter: (trip_cost_pk IS NULL)
  • Rows Removed by Filter: 702649
151. 0.003 0.020 ↑ 1.0 4 1

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

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

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

  • Filter: ((name)::text <> 'driving_credit'::text)
  • Rows Removed by Filter: 1
153. 0.019 0.048 ↑ 1.0 144 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
154. 0.029 0.029 ↑ 1.0 144 1

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

155. 13.161 937.260 ↑ 4.3 86,793 1

Materialize (cost=79,289.46..93,290.61 rows=373,364 width=40) (actual time=877.078..937.260 rows=86,793 loops=1)

156. 35.685 924.099 ↑ 4.6 81,426 1

GroupAggregate (cost=79,289.46..88,623.56 rows=373,364 width=44) (actual time=877.073..924.099 rows=81,426 loops=1)

  • Group Key: s_2.trip_pk, bp_1.start_stamp, bp_1.end_stamp
157. 549.753 888.414 ↑ 4.0 92,811 1

Sort (cost=79,289.46..80,222.87 rows=373,364 width=18) (actual time=877.063..888.414 rows=92,811 loops=1)

  • Sort Key: s_2.trip_pk, bp_1.start_stamp, bp_1.end_stamp
  • Sort Method: external merge Disk: 9528kB
158. 75.351 338.661 ↓ 1.1 405,686 1

Hash Join (cost=6.35..37,076.20 rows=373,364 width=18) (actual time=0.099..338.661 rows=405,686 loops=1)

  • Hash Cond: (s_2.billing_period_pk = bp_1.pk)
159. 110.888 263.267 ↓ 1.1 405,686 1

Hash Join (cost=1.11..31,937.21 rows=373,364 width=14) (actual time=0.048..263.267 rows=405,686 loops=1)

  • Hash Cond: (s_2.sale_type_pk = st_2.pk)
160. 152.366 152.366 ↑ 1.0 463,409 1

Seq Scan on sales s_2 (cost=0.00..26,452.31 rows=466,705 width=18) (actual time=0.017..152.366 rows=463,409 loops=1)

  • Filter: (trip_cost_pk IS NULL)
  • Rows Removed by Filter: 702649
161. 0.001 0.013 ↑ 1.0 4 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
162. 0.012 0.012 ↑ 1.0 4 1

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

  • Filter: ((name)::text <> 'driving_credit'::text)
  • Rows Removed by Filter: 1
163. 0.019 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
164. 0.024 0.024 ↑ 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.024 rows=144 loops=1)

165. 9.165 261.553 ↑ 4.2 55,510 1

Materialize (cost=57,990.88..65,582.42 rows=233,586 width=36) (actual time=223.596..261.553 rows=55,510 loops=1)

166. 22.644 252.388 ↑ 4.2 55,510 1

GroupAggregate (cost=57,990.88..62,662.60 rows=233,586 width=36) (actual time=223.592..252.388 rows=55,510 loops=1)

  • Group Key: s_3.trip_pk
167. 17.891 229.744 ↑ 4.2 55,721 1

Sort (cost=57,990.88..58,574.84 rows=233,586 width=10) (actual time=223.578..229.744 rows=55,721 loops=1)

  • Sort Key: s_3.trip_pk
  • Sort Method: quicksort Memory: 4242kB
168. 127.862 211.853 ↑ 4.0 57,723 1

Hash Join (cost=1.07..33,168.99 rows=233,586 width=10) (actual time=0.042..211.853 rows=57,723 loops=1)

  • Hash Cond: (s_3.sale_type_pk = st_3.pk)
169. 83.980 83.980 ↑ 1.0 1,166,058 1

Seq Scan on sales s_3 (cost=0.00..26,452.31 rows=1,167,931 width=14) (actual time=0.013..83.980 rows=1,166,058 loops=1)

170. 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
171. 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