explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JvA6 : work mem

Settings
# exclusive inclusive rows x rows loops node
1. 182.233 19,809.124 ↓ 590.3 8,854 1

Unique (cost=344,549.91..344,552.13 rows=15 width=1,007) (actual time=19,604.093..19,809.124 rows=8,854 loops=1)

2.          

CTE mvsm_stacks

3. 0.172 0.172 ↑ 1.0 21 1

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

  • Filter: (vehicle_type_pk IS NOT NULL)
  • Rows Removed by Filter: 4140
4. 1,615.018 19,626.891 ↓ 5,882.3 88,235 1

Sort (cost=344,485.30..344,485.34 rows=15 width=1,007) (actual time=19,604.093..19,626.891 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: quicksort Memory: 90398kB
5. 49.826 18,011.873 ↓ 5,882.3 88,235 1

Subquery Scan on mvsm_grouping (cost=334,536.44..344,485.01 rows=15 width=1,007) (actual time=8,406.732..18,011.873 rows=88,235 loops=1)

6. 9,026.233 17,962.047 ↓ 5,882.3 88,235 1

Hash Right Join (cost=334,536.44..344,484.79 rows=15 width=1,065) (actual time=8,406.721..17,962.047 rows=88,235 loops=1)

  • Hash Cond: (s.trip_pk = t.pk)
  • Join Filter: ((bp.start_stamp <= t.billing_end) AND (bp.end_stamp >= t.billing_end))
  • Rows Removed by Join Filter: 5381
7. 167.466 499.415 ↑ 4.5 82,138 1

HashAggregate (cost=40,796.05..45,461.52 rows=373,238 width=44) (actual time=462.321..499.415 rows=82,138 loops=1)

  • Group Key: s.trip_pk, bp.start_stamp, bp.end_stamp
8. 77.907 331.949 ↓ 1.1 406,833 1

Hash Join (cost=6.35..37,063.67 rows=373,238 width=18) (actual time=0.135..331.949 rows=406,833 loops=1)

  • Hash Cond: (s.billing_period_pk = bp.pk)
9. 110.656 253.994 ↓ 1.1 406,833 1

Hash Join (cost=1.11..31,926.40 rows=373,238 width=14) (actual time=0.068..253.994 rows=406,833 loops=1)

  • Hash Cond: (s.sale_type_pk = st.pk)
10. 143.321 143.321 ↑ 1.0 464,550 1

Seq Scan on sales s (cost=0.00..26,443.36 rows=466,547 width=18) (actual time=0.014..143.321 rows=464,550 loops=1)

  • Filter: (trip_cost_pk IS NULL)
  • Rows Removed by Filter: 702594
11. 0.004 0.017 ↑ 1.0 4 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
12. 0.013 0.013 ↑ 1.0 4 1

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

  • Filter: ((name)::text <> 'driving_credit'::text)
  • Rows Removed by Filter: 1
13. 0.022 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
14. 0.026 0.026 ↑ 1.0 144 1

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

15. 85.415 7,906.989 ↓ 5,882.3 88,235 1

Hash (cost=293,740.21..293,740.21 rows=15 width=658) (actual time=7,906.989..7,906.989 rows=88,235 loops=1)

  • Buckets: 131072 (originally 1024) Batches: 1 (originally 1) Memory Usage: 47493kB
16. 62.234 7,821.574 ↓ 5,882.3 88,235 1

Hash Right Join (cost=283,942.66..293,740.21 rows=15 width=658) (actual time=7,728.428..7,821.574 rows=88,235 loops=1)

  • Hash Cond: (s_1.trip_pk = t.pk)
  • Join Filter: (bp_1.start_stamp > t.billing_end)
  • Rows Removed by Join Filter: 5
17. 168.310 511.423 ↑ 4.5 82,138 1

HashAggregate (cost=40,796.05..45,461.52 rows=373,238 width=44) (actual time=476.438..511.423 rows=82,138 loops=1)

  • Group Key: s_1.trip_pk, bp_1.start_stamp, bp_1.end_stamp
18. 78.669 343.113 ↓ 1.1 406,833 1

Hash Join (cost=6.35..37,063.67 rows=373,238 width=18) (actual time=1.623..343.113 rows=406,833 loops=1)

  • Hash Cond: (s_1.billing_period_pk = bp_1.pk)
19. 115.793 262.893 ↓ 1.1 406,833 1

Hash Join (cost=1.11..31,926.40 rows=373,238 width=14) (actual time=0.055..262.893 rows=406,833 loops=1)

  • Hash Cond: (s_1.sale_type_pk = st_1.pk)
20. 147.081 147.081 ↑ 1.0 464,550 1

Seq Scan on sales s_1 (cost=0.00..26,443.36 rows=466,547 width=18) (actual time=0.010..147.081 rows=464,550 loops=1)

  • Filter: (trip_cost_pk IS NULL)
  • Rows Removed by Filter: 702594
21. 0.008 0.019 ↑ 1.0 4 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
22. 0.011 0.011 ↑ 1.0 4 1

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

  • Filter: ((name)::text <> 'driving_credit'::text)
  • Rows Removed by Filter: 1
23. 0.034 1.551 ↑ 1.0 144 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
24. 1.517 1.517 ↑ 1.0 144 1

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

25. 98.954 7,247.917 ↓ 5,882.3 88,235 1

Hash (cost=243,146.43..243,146.43 rows=15 width=626) (actual time=7,247.917..7,247.917 rows=88,235 loops=1)

  • Buckets: 131072 (originally 1024) Batches: 1 (originally 1) Memory Usage: 47458kB
26. 36.056 7,148.963 ↓ 5,882.3 88,235 1

Hash Semi Join (cost=118,032.81..243,146.43 rows=15 width=626) (actual time=5,671.280..7,148.963 rows=88,235 loops=1)

  • Hash Cond: (t.pk = t_1.pk)
27. 34.026 6,087.304 ↓ 15.6 110,754 1

Hash Left Join (cost=88,573.97..213,668.80 rows=7,097 width=626) (actual time=4,645.643..6,087.304 rows=110,754 loops=1)

  • Hash Cond: (tc.rate_code_pk = tc_rc.pk)
28. 35.807 6,053.262 ↓ 15.6 110,754 1

Hash Left Join (cost=88,572.79..213,570.03 rows=7,097 width=622) (actual time=4,645.609..6,053.262 rows=110,754 loops=1)

  • Hash Cond: (tc.rate_plan_pk = tc_rp.pk)
29. 473.943 6,017.292 ↓ 15.6 110,754 1

Nested Loop Left Join (cost=88,554.58..213,454.23 rows=7,097 width=591) (actual time=4,645.424..6,017.292 rows=110,754 loops=1)

30. 33.663 5,211.087 ↓ 15.6 110,754 1

Hash Left Join (cost=88,545.65..102,509.11 rows=7,097 width=567) (actual time=4,640.718..5,211.087 rows=110,754 loops=1)

  • Hash Cond: (t_j.market_pk = t_m.pk)
31. 38.226 5,177.311 ↓ 15.6 110,754 1

Hash Left Join (cost=88,531.87..102,397.76 rows=7,097 width=539) (actual time=4,640.582..5,177.311 rows=110,754 loops=1)

  • Hash Cond: (t.vehicle_pk = t_dv.pk)
32. 180.428 5,137.390 ↓ 15.6 110,754 1

Hash Right Join (cost=88,327.73..102,114.05 rows=7,097 width=516) (actual time=4,638.837..5,137.390 rows=110,754 loops=1)

  • Hash Cond: (s_2.trip_pk = t.pk)
33. 813.448 1,335.505 ↓ 1.3 664,695 1

HashAggregate (cost=37,485.05..44,037.38 rows=524,187 width=36) (actual time=1,017.318..1,335.505 rows=664,695 loops=1)

  • Group Key: s_2.trip_pk
34. 146.595 522.057 ↓ 1.3 702,594 1

Hash Join (cost=1.11..34,681.09 rows=560,791 width=10) (actual time=1.262..522.057 rows=702,594 loops=1)

  • Hash Cond: (s_2.sale_type_pk = st_2.pk)
35. 375.440 375.440 ↓ 1.0 702,594 1

Seq Scan on sales s_2 (cost=0.00..26,443.36 rows=700,989 width=14) (actual time=1.221..375.440 rows=702,594 loops=1)

  • Filter: (trip_cost_pk IS NOT NULL)
  • Rows Removed by Filter: 464550
36. 0.009 0.022 ↑ 1.0 4 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
37. 0.013 0.013 ↑ 1.0 4 1

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

  • Filter: ((name)::text <> 'driving_credit'::text)
  • Rows Removed by Filter: 1
38. 122.750 3,621.457 ↓ 15.6 110,754 1

Hash (cost=50,753.97..50,753.97 rows=7,097 width=484) (actual time=3,621.457..3,621.457 rows=110,754 loops=1)

  • Buckets: 131072 (originally 8192) Batches: 1 (originally 1) Memory Usage: 46958kB
39. 40.032 3,498.707 ↓ 15.6 110,754 1

Hash Left Join (cost=50,259.17..50,753.97 rows=7,097 width=484) (actual time=3,309.758..3,498.707 rows=110,754 loops=1)

  • Hash Cond: (t_us.lot_pk = t_l.pk)
40. 171.201 3,456.940 ↓ 15.6 110,754 1

Hash Right Join (cost=50,086.93..50,484.15 rows=7,097 width=408) (actual time=3,307.974..3,456.940 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
41. 2.375 6.784 ↓ 2.0 8,049 1

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

  • Hash Cond: (t_usr.vehicle_pk = t_v.pk)
42. 1.158 2.880 ↓ 2.0 8,049 1

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

43. 0.022 0.022 ↑ 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.021..0.022 rows=1 loops=1)

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

  • Index Cond: (status_pk = t_usrs.pk)
45. 0.445 1.529 ↓ 1.0 2,855 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 176kB
46. 0.544 1.084 ↓ 1.0 2,855 1

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

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

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

48. 0.021 0.055 ↑ 1.0 121 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
49. 0.034 0.034 ↑ 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.034 rows=121 loops=1)

50. 17.850 3,278.955 ↓ 2.2 15,453 1

Hash (cost=49,793.79..49,793.79 rows=7,097 width=396) (actual time=3,278.955..3,278.955 rows=15,453 loops=1)

  • Buckets: 16384 (originally 8192) Batches: 1 (originally 1) Memory Usage: 5389kB
51. 6.640 3,261.105 ↓ 2.2 15,453 1

Hash Left Join (cost=49,201.40..49,793.79 rows=7,097 width=396) (actual time=3,218.150..3,261.105 rows=15,453 loops=1)

  • Hash Cond: (t.billing_stack_pk = t_us.pk)
52. 6.329 3,252.350 ↓ 2.2 15,453 1

Hash Left Join (cost=49,084.78..49,579.58 rows=7,097 width=388) (actual time=3,215.975..3,252.350 rows=15,453 loops=1)

  • Hash Cond: (r_us.lot_pk = r_l.pk)
53. 24.570 3,238.682 ↓ 2.2 15,453 1

Hash Right Join (cost=48,894.41..49,291.63 rows=7,097 width=284) (actual time=3,208.595..3,238.682 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
54. 4.973 24.336 ↓ 2.0 8,049 1

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

  • Hash Cond: (r_usr.vehicle_pk = r_v.pk)
55. 2.670 8.296 ↓ 2.0 8,049 1

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

56. 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
57. 5.610 5.610 ↓ 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.023..5.610 rows=8,049 loops=1)

  • Index Cond: (status_pk = r_usrs.pk)
58. 1.489 11.067 ↓ 1.0 2,855 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 209kB
59. 1.702 9.578 ↓ 1.0 2,855 1

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

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

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

61. 0.078 2.288 ↑ 1.0 121 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
62. 2.210 2.210 ↑ 1.0 121 1

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

63. 17.747 3,189.776 ↓ 1.6 11,188 1

Hash (cost=48,601.27..48,601.27 rows=7,097 width=261) (actual time=3,189.776..3,189.776 rows=11,188 loops=1)

  • Buckets: 16384 (originally 8192) Batches: 1 (originally 1) Memory Usage: 2518kB
64. 10.367 3,172.029 ↓ 1.6 11,188 1

Hash Left Join (cost=6,546.25..48,601.27 rows=7,097 width=261) (actual time=81.295..3,172.029 rows=11,188 loops=1)

  • Hash Cond: (r.stack_pk = r_us.pk)
65. 5.989 3,160.623 ↓ 1.6 11,188 1

Hash Left Join (cost=6,429.63..48,387.06 rows=7,097 width=253) (actual time=80.206..3,160.623 rows=11,188 loops=1)

  • Hash Cond: (r.status_pk = rs.pk)
66. 7.915 3,154.622 ↓ 1.6 11,188 1

Hash Join (cost=6,428.49..48,288.34 rows=7,097 width=249) (actual time=80.173..3,154.622 rows=11,188 loops=1)

  • Hash Cond: (a.rate_plan_pk = a_rp.pk)
67. 8.075 3,146.551 ↓ 1.6 11,188 1

Nested Loop Left Join (cost=6,410.28..48,172.55 rows=7,097 width=222) (actual time=80.009..3,146.551 rows=11,188 loops=1)

68. 10.956 2,478.384 ↓ 1.6 11,188 1

Nested Loop (cost=6,404.54..44,485.24 rows=7,097 width=189) (actual time=78.999..2,478.384 rows=11,188 loops=1)

69. 7.865 2,422.676 ↓ 1.6 11,188 1

Hash Join (cost=6,404.12..41,073.63 rows=7,097 width=193) (actual time=78.987..2,422.676 rows=11,188 loops=1)

  • Hash Cond: (m.member_type_pk = mt.pk)
70. 14.690 2,414.801 ↓ 1.6 11,188 1

Nested Loop (cost=6,403.03..40,974.96 rows=7,097 width=187) (actual time=78.958..2,414.801 rows=11,188 loops=1)

71. 12.862 2,131.599 ↓ 1.6 11,188 1

Nested Loop (cost=6,402.61..36,841.48 rows=7,097 width=161) (actual time=78.176..2,131.599 rows=11,188 loops=1)

72. 10.713 328.657 ↓ 1.6 11,188 1

Nested Loop (cost=6,402.19..25,164.28 rows=7,097 width=152) (actual time=75.851..328.657 rows=11,188 loops=1)

73. 12.473 105.372 ↓ 1.6 11,188 1

Hash Join (cost=6,401.77..21,795.71 rows=7,097 width=156) (actual time=72.427..105.372 rows=11,188 loops=1)

  • Hash Cond: (t.driver_pk = d.pk)
74. 5.500 22.455 ↓ 1.6 11,188 1

Hash Join (cost=155.04..15,451.39 rows=7,097 width=141) (actual time=1.082..22.455 rows=11,188 loops=1)

  • Hash Cond: (t.status_pk = ts.pk)
75. 16.098 16.948 ↓ 1.6 11,188 1

Bitmap Heap Scan on trips t (cost=153.97..15,352.74 rows=7,097 width=127) (actual time=1.064..16.948 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
76. 0.850 0.850 ↓ 1.6 11,374 1

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

  • Index Cond: ((billing_end >= 1551420000) AND (billing_end <= 1553317199))
77. 0.003 0.007 ↑ 1.0 3 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
78. 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)

79. 34.973 70.444 ↓ 1.0 156,535 1

Hash (cost=4,290.77..4,290.77 rows=156,477 width=23) (actual time=70.444..70.444 rows=156,535 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 10782kB
80. 35.471 35.471 ↓ 1.0 156,535 1

Seq Scan on drivers d (cost=0.00..4,290.77 rows=156,477 width=23) (actual time=0.008..35.471 rows=156,535 loops=1)

81. 212.572 212.572 ↑ 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.019..0.019 rows=1 loops=11,188)

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

  • Index Cond: (pk = d.contact_pk)
83. 268.512 268.512 ↑ 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.024..0.024 rows=1 loops=11,188)

  • Index Cond: (pk = d.member_pk)
84. 0.003 0.010 ↑ 1.0 4 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
85. 0.007 0.007 ↑ 1.0 4 1

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

86. 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)
87. 111.880 660.092 ↑ 1.0 1 11,188

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

  • Hash Cond: (mvsm_stacks.pk = r.stack_pk)
88. 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)

89. 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
90. 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)
91. 0.083 0.156 ↑ 1.0 454 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 39kB
92. 0.073 0.073 ↑ 1.0 454 1

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

93. 0.007 0.012 ↑ 1.0 6 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
94. 0.005 0.005 ↑ 1.0 6 1

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

95. 0.602 1.039 ↑ 1.0 4,161 1

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

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

97. 0.875 7.339 ↑ 1.0 2,072 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 326kB
98. 0.899 6.464 ↑ 1.0 2,072 1

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

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

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

100. 0.116 0.480 ↑ 1.0 317 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 41kB
101. 0.114 0.364 ↑ 1.0 317 1

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

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

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

103. 0.095 0.173 ↑ 1.0 301 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
104. 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.005..0.078 rows=301 loops=1)

105. 1.235 2.115 ↑ 1.0 4,161 1

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

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

107. 0.645 1.735 ↑ 1.0 2,072 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 273kB
108. 0.752 1.090 ↑ 1.0 2,072 1

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

  • Hash Cond: (t_l.jurisdiction_pk = t_j.pk)
109. 0.214 0.214 ↑ 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.214 rows=2,072 loops=1)

110. 0.075 0.124 ↑ 1.0 317 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 32kB
111. 0.049 0.049 ↑ 1.0 317 1

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

112. 0.571 1.695 ↓ 1.0 2,855 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 209kB
113. 0.564 1.124 ↓ 1.0 2,855 1

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

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

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

115. 0.025 0.057 ↑ 1.0 121 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
116. 0.032 0.032 ↑ 1.0 121 1

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

117. 0.055 0.113 ↑ 1.0 301 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
118. 0.058 0.058 ↑ 1.0 301 1

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

119. 0.000 332.262 ↑ 1.0 1 110,754

Index Scan using trip_costs_pkey on trip_costs tc (cost=8.93..15.62 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=110,754)

  • Index Cond: (pk = (SubPlan 3))
  • Filter: (t.pk = trip_pk)
120.          

SubPlan (forIndex Scan)

121. 0.000 332.262 ↑ 1.0 1 110,754

Limit (cost=0.43..8.50 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=110,754)

122. 332.262 332.262 ↑ 1.0 1 110,754

Index Only Scan Backward using newlr_tc01 on trip_costs tc_1 (cost=0.43..8.50 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=110,754)

  • Index Cond: ((trip_pk = t.pk) AND (is_estimate = 'N'::bpchar) AND (is_current = 'Y'::bpchar) AND (is_selected = 'Y'::bpchar))
  • Heap Fetches: 19514
123. 0.089 0.163 ↑ 1.0 454 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 41kB
124. 0.074 0.074 ↑ 1.0 454 1

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

125. 0.004 0.016 ↑ 1.0 8 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
126. 0.012 0.012 ↑ 1.0 8 1

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

127. 1.138 1,025.603 ↓ 2.9 8,854 1

Hash (cost=29,420.46..29,420.46 rows=3,070 width=4) (actual time=1,025.603..1,025.603 rows=8,854 loops=1)

  • Buckets: 16384 (originally 4096) Batches: 1 (originally 1) Memory Usage: 440kB
128. 2.227 1,024.465 ↓ 2.9 8,854 1

Unique (cost=29,374.41..29,389.76 rows=3,070 width=4) (actual time=1,020.144..1,024.465 rows=8,854 loops=1)

129. 11.469 1,022.238 ↓ 7.4 22,775 1

Sort (cost=29,374.41..29,382.08 rows=3,070 width=4) (actual time=1,020.144..1,022.238 rows=22,775 loops=1)

  • Sort Key: t_1.pk
  • Sort Method: quicksort Memory: 1836kB
130. 11.049 1,010.769 ↓ 7.4 22,775 1

Hash Join (cost=7,000.66..29,196.59 rows=3,070 width=4) (actual time=151.790..1,010.769 rows=22,775 loops=1)

  • Hash Cond: (us.lot_pk = l_1.pk)
131. 10.862 994.522 ↓ 7.4 22,775 1

Hash Join (cost=6,911.40..29,065.12 rows=3,070 width=8) (actual time=146.569..994.522 rows=22,775 loops=1)

  • Hash Cond: ((us.status_pk = uss.pk) AND (usr.status_pk = usrs.pk))
132. 13.215 983.637 ↓ 1.3 23,070 1

Hash Join (cost=6,909.31..28,894.16 rows=18,423 width=16) (actual time=146.531..983.637 rows=23,070 loops=1)

  • Hash Cond: (usr.stack_pk = us.pk)
133. 10.565 969.093 ↓ 1.3 23,070 1

Hash Join (cost=6,792.69..28,524.22 rows=18,423 width=12) (actual time=145.161..969.093 rows=23,070 loops=1)

  • Hash Cond: (t_1.resource_pk = usr.vehicle_pk)
134. 5.887 952.641 ↓ 1.4 8,854 1

Hash Join (cost=6,539.39..27,924.42 rows=6,182 width=12) (actual time=139.233..952.641 rows=8,854 loops=1)

  • Hash Cond: (t_1.resource_pk = v.pk)
135. 3.253 940.537 ↓ 1.4 8,854 1

Nested Loop (cost=6,420.65..27,720.68 rows=6,182 width=8) (actual time=132.979..940.537 rows=8,854 loops=1)

136. 5.148 751.350 ↓ 1.4 8,854 1

Hash Join (cost=6,420.23..24,785.27 rows=6,182 width=16) (actual time=130.992..751.350 rows=8,854 loops=1)

  • Hash Cond: (a_1.rate_plan_pk = rp.pk)
137. 6.859 744.209 ↓ 1.6 9,704 1

Nested Loop (cost=6,400.90..24,680.87 rows=6,196 width=20) (actual time=128.979..744.209 rows=9,704 loops=1)

138. 10.139 494.750 ↓ 1.6 9,704 1

Hash Join (cost=6,400.48..21,702.39 rows=6,196 width=12) (actual time=126.976..494.750 rows=9,704 loops=1)

  • Hash Cond: (t_1.driver_pk = d_1.pk)
139. 359.199 364.292 ↓ 1.6 9,704 1

Bitmap Heap Scan on trips t_1 (cost=153.75..15,370.46 rows=6,196 width=12) (actual time=5.596..364.292 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
140. 5.093 5.093 ↓ 1.6 11,374 1

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

  • Index Cond: ((billing_end >= 1551420000) AND (billing_end <= 1553317199))
141. 36.353 120.319 ↓ 1.0 156,535 1

Hash (cost=4,290.77..4,290.77 rows=156,477 width=8) (actual time=120.319..120.319 rows=156,535 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 8163kB
142. 83.966 83.966 ↓ 1.0 156,535 1

Seq Scan on drivers d_1 (cost=0.00..4,290.77 rows=156,477 width=8) (actual time=1.291..83.966 rows=156,535 loops=1)

143. 242.600 242.600 ↑ 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.025..0.025 rows=1 loops=9,704)

  • Index Cond: (member_pk = d_1.member_pk)
144. 0.076 1.993 ↑ 1.0 453 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
145. 1.917 1.917 ↑ 1.0 453 1

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

  • Filter: (is_billable_exempt = 'N'::bpchar)
  • Rows Removed by Filter: 1
146. 185.934 185.934 ↑ 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.020..0.021 rows=1 loops=8,854)

  • Index Cond: (pk = d_1.member_pk)
  • Heap Fetches: 0
147. 0.368 6.217 ↓ 1.0 2,855 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 133kB
148. 5.849 5.849 ↓ 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.768..5.849 rows=2,855 loops=1)

  • Heap Fetches: 110
149. 1.148 5.887 ↑ 1.0 8,191 1

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

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

151. 0.569 1.329 ↑ 1.0 4,161 1

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

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

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

153. 0.002 0.023 ↑ 1.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
154. 0.001 0.021 ↑ 1.0 1 1

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

155. 0.012 0.012 ↑ 1.0 1 1

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

  • Filter: ((name)::text = 'normal'::text)
  • Rows Removed by Filter: 2
156. 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.007..0.008 rows=1 loops=1)

  • Filter: ((name)::text = 'normal'::text)
  • Rows Removed by Filter: 1
157. 0.299 5.198 ↑ 1.0 2,072 1

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

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

  • Heap Fetches: 0
159.          

SubPlan (forHash Right Join)

160. 0.000 529.410 ↓ 0.0 0 88,235

GroupAggregate (cost=0.43..9.54 rows=1 width=36) (actual time=0.006..0.006 rows=0 loops=88,235)

  • Group Key: s_3.trip_pk
161. 171.265 529.410 ↓ 0.0 0 88,235

Nested Loop (cost=0.43..9.52 rows=1 width=10) (actual time=0.006..0.006 rows=0 loops=88,235)

  • Join Filter: (s_3.sale_type_pk = st_3.pk)
  • Rows Removed by Join Filter: 1
162. 264.705 264.705 ↑ 1.0 1 88,235

Index Scan using sales_trip_pk_idx on sales s_3 (cost=0.43..8.45 rows=1 width=14) (actual time=0.003..0.003 rows=1 loops=88,235)

  • Index Cond: (trip_pk = t.pk)
163. 93.440 93.440 ↑ 1.0 1 93,440

Seq Scan on sale_types st_3 (cost=0.00..1.06 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=93,440)

  • Filter: ((name)::text = 'driving_credit'::text)
  • Rows Removed by Filter: 4