explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uENt : correlated, 512mb

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

Unique (cost=344,610.05..344,612.26 rows=15 width=1,007) (actual time=18,960.911..19,164.450 rows=8,854 loops=1)

2.          

CTE mvsm_stacks

3. 0.191 0.191 ↑ 1.0 21 1

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

  • Filter: (vehicle_type_pk IS NOT NULL)
  • Rows Removed by Filter: 4140
4. 1,558.129 18,983.013 ↓ 5,882.3 88,235 1

Sort (cost=344,545.44..344,545.47 rows=15 width=1,007) (actual time=18,960.911..18,983.013 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. 47.552 17,424.884 ↓ 5,882.3 88,235 1

Subquery Scan on mvsm_grouping (cost=334,593.27..344,545.14 rows=15 width=1,007) (actual time=8,508.053..17,424.884 rows=88,235 loops=1)

6. 8,336.633 17,377.332 ↓ 5,882.3 88,235 1

Hash Right Join (cost=334,593.27..344,544.92 rows=15 width=1,065) (actual time=8,508.046..17,377.332 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. 169.361 512.246 ↑ 4.6 81,661 1

HashAggregate (cost=40,809.84..45,476.89 rows=373,364 width=44) (actual time=475.795..512.246 rows=81,661 loops=1)

  • Group Key: s.trip_pk, bp.start_stamp, bp.end_stamp
8. 81.746 342.885 ↓ 1.1 405,920 1

Hash Join (cost=6.35..37,076.20 rows=373,364 width=18) (actual time=0.161..342.885 rows=405,920 loops=1)

  • Hash Cond: (s.billing_period_pk = bp.pk)
9. 114.549 261.081 ↓ 1.1 405,920 1

Hash Join (cost=1.11..31,937.21 rows=373,364 width=14) (actual time=0.086..261.081 rows=405,920 loops=1)

  • Hash Cond: (s.sale_type_pk = st.pk)
10. 146.509 146.509 ↑ 1.0 463,643 1

Seq Scan on sales s (cost=0.00..26,452.31 rows=466,705 width=18) (actual time=0.020..146.509 rows=463,643 loops=1)

  • Filter: (trip_cost_pk IS NULL)
  • Rows Removed by Filter: 702658
11. 0.006 0.023 ↑ 1.0 4 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
12. 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
13. 0.028 0.058 ↑ 1.0 144 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
14. 0.030 0.030 ↑ 1.0 144 1

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

15. 83.505 7,999.043 ↓ 5,882.3 88,235 1

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

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

Hash Right Join (cost=283,982.38..293,783.24 rows=15 width=658) (actual time=7,817.777..7,915.538 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. 169.322 510.715 ↑ 4.6 81,661 1

HashAggregate (cost=40,809.84..45,476.89 rows=373,364 width=44) (actual time=472.362..510.715 rows=81,661 loops=1)

  • Group Key: s_1.trip_pk, bp_1.start_stamp, bp_1.end_stamp
18. 76.316 341.393 ↓ 1.1 405,920 1

Hash Join (cost=6.35..37,076.20 rows=373,364 width=18) (actual time=0.153..341.393 rows=405,920 loops=1)

  • Hash Cond: (s_1.billing_period_pk = bp_1.pk)
19. 112.643 265.008 ↓ 1.1 405,920 1

Hash Join (cost=1.11..31,937.21 rows=373,364 width=14) (actual time=0.062..265.008 rows=405,920 loops=1)

  • Hash Cond: (s_1.sale_type_pk = st_1.pk)
20. 152.341 152.341 ↑ 1.0 463,643 1

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

  • Filter: (trip_cost_pk IS NULL)
  • Rows Removed by Filter: 702658
21. 0.004 0.024 ↑ 1.0 4 1

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

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

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

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

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

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

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

25. 96.998 7,340.946 ↓ 5,882.3 88,235 1

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

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

Hash Semi Join (cost=118,050.08..243,172.35 rows=15 width=626) (actual time=5,701.622..7,243.948 rows=88,235 loops=1)

  • Hash Cond: (t.pk = t_1.pk)
27. 34.305 5,858.505 ↓ 15.6 110,754 1

Hash Left Join (cost=88,594.97..213,698.45 rows=7,097 width=626) (actual time=4,351.519..5,858.505 rows=110,754 loops=1)

  • Hash Cond: (tc.rate_code_pk = tc_rc.pk)
28. 36.556 5,824.188 ↓ 15.6 110,754 1

Hash Left Join (cost=88,593.79..213,599.69 rows=7,097 width=622) (actual time=4,351.483..5,824.188 rows=110,754 loops=1)

  • Hash Cond: (tc.rate_plan_pk = tc_rp.pk)
29. 476.481 5,787.463 ↓ 15.6 110,754 1

Nested Loop Left Join (cost=88,575.58..213,483.89 rows=7,097 width=591) (actual time=4,351.287..5,787.463 rows=110,754 loops=1)

30. 34.146 4,978.720 ↓ 15.6 110,754 1

Hash Left Join (cost=88,566.65..102,534.77 rows=7,097 width=567) (actual time=4,347.625..4,978.720 rows=110,754 loops=1)

  • Hash Cond: (t_j.market_pk = t_m.pk)
31. 39.100 4,944.448 ↓ 15.6 110,754 1

Hash Left Join (cost=88,552.87..102,423.41 rows=7,097 width=539) (actual time=4,347.477..4,944.448 rows=110,754 loops=1)

  • Hash Cond: (t.vehicle_pk = t_dv.pk)
32. 201.684 4,903.408 ↓ 15.6 110,754 1

Hash Right Join (cost=88,348.73..102,139.70 rows=7,097 width=516) (actual time=4,345.503..4,903.408 rows=110,754 loops=1)

  • Hash Cond: (s_2.trip_pk = t.pk)
33. 790.483 1,245.715 ↓ 1.3 664,759 1

HashAggregate (cost=37,497.74..44,052.29 rows=524,364 width=36) (actual time=889.435..1,245.715 rows=664,759 loops=1)

  • Group Key: s_2.trip_pk
34. 132.577 455.232 ↓ 1.3 702,658 1

Hash Join (cost=1.11..34,692.83 rows=560,981 width=10) (actual time=0.067..455.232 rows=702,658 loops=1)

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

Seq Scan on sales s_2 (cost=0.00..26,452.31 rows=701,226 width=14) (actual time=0.037..322.638 rows=702,658 loops=1)

  • Filter: (trip_cost_pk IS NOT NULL)
  • Rows Removed by Filter: 463643
36. 0.005 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
37. 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.011..0.012 rows=4 loops=1)

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

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

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

Hash Left Join (cost=50,267.48..50,762.28 rows=7,097 width=484) (actual time=3,203.259..3,365.287 rows=110,754 loops=1)

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

Hash Right Join (cost=50,095.24..50,492.46 rows=7,097 width=408) (actual time=3,201.631..3,334.943 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.711 6.986 ↓ 2.0 8,049 1

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

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

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

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

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

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

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

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

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

  • Hash Cond: (t_v.vehicle_type_pk = t_vt.pk)
47. 0.500 0.500 ↓ 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.500 rows=2,855 loops=1)

48. 0.025 0.053 ↑ 1.0 121 1

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

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

50. 11.935 3,172.244 ↓ 2.2 15,453 1

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

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

Hash Left Join (cost=49,209.71..49,802.10 rows=7,097 width=396) (actual time=3,136.660..3,160.309 rows=15,453 loops=1)

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

Hash Left Join (cost=49,093.09..49,587.89 rows=7,097 width=388) (actual time=3,135.506..3,154.884 rows=15,453 loops=1)

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

Hash Right Join (cost=48,902.72..49,299.94 rows=7,097 width=284) (actual time=3,127.951..3,143.416 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. 2.099 13.195 ↓ 2.0 8,049 1

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

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

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

56. 0.013 0.013 ↑ 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.012..0.013 rows=1 loops=1)

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

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

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

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

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

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

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

61. 0.027 1.690 ↑ 1.0 121 1

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

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

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

63. 18.569 3,117.326 ↓ 1.6 11,188 1

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

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

Hash Left Join (cost=6,541.80..48,609.58 rows=7,097 width=261) (actual time=87.675..3,098.757 rows=11,188 loops=1)

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

Hash Left Join (cost=6,425.17..48,395.37 rows=7,097 width=253) (actual time=86.539..3,086.440 rows=11,188 loops=1)

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

Hash Join (cost=6,424.04..48,296.65 rows=7,097 width=249) (actual time=86.514..3,080.087 rows=11,188 loops=1)

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

Nested Loop Left Join (cost=6,405.82..48,180.85 rows=7,097 width=222) (actual time=86.346..3,071.401 rows=11,188 loops=1)

68. 3.983 2,369.401 ↓ 1.6 11,188 1

Nested Loop (cost=6,400.08..44,493.55 rows=7,097 width=189) (actual time=84.729..2,369.401 rows=11,188 loops=1)

69. 8.145 2,309.478 ↓ 1.6 11,188 1

Hash Join (cost=6,399.67..41,081.62 rows=7,097 width=193) (actual time=84.718..2,309.478 rows=11,188 loops=1)

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

Nested Loop (cost=6,398.58..40,982.94 rows=7,097 width=187) (actual time=84.686..2,301.318 rows=11,188 loops=1)

71. 11.633 2,223.785 ↓ 1.6 11,188 1

Nested Loop (cost=6,398.16..36,848.23 rows=7,097 width=161) (actual time=84.663..2,223.785 rows=11,188 loops=1)

72. 4.662 410.884 ↓ 1.6 11,188 1

Nested Loop (cost=6,397.74..25,160.29 rows=7,097 width=152) (actual time=82.297..410.884 rows=11,188 loops=1)

73. 13.305 115.334 ↓ 1.6 11,188 1

Hash Join (cost=6,397.32..21,791.47 rows=7,097 width=156) (actual time=80.627..115.334 rows=11,188 loops=1)

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

Hash Join (cost=155.04..15,451.61 rows=7,097 width=141) (actual time=1.115..23.519 rows=11,188 loops=1)

  • Hash Cond: (t.status_pk = ts.pk)
75. 16.924 17.796 ↓ 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.095..17.796 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.872 0.872 ↓ 1.6 11,374 1

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

  • Index Cond: ((billing_end >= 1551420000) AND (billing_end <= 1553317199))
77. 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
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. 39.212 78.510 ↑ 1.0 156,251 1

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

  • Buckets: 262144 Batches: 1 Memory Usage: 10766kB
80. 39.298 39.298 ↑ 1.0 156,251 1

Seq Scan on drivers d (cost=0.00..4,288.79 rows=156,279 width=23) (actual time=0.013..39.298 rows=156,251 loops=1)

81. 290.888 290.888 ↑ 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.025..0.026 rows=1 loops=11,188)

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

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

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

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

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

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

86. 55.940 55.940 ↑ 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.005 rows=1 loops=11,188)

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

Hash Right Join (cost=5.74..6.25 rows=1 width=41) (actual time=0.060..0.061 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. 22.376 548.212 ↑ 1.0 1 11,188

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
90. 525.836 525.836 ↑ 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.047..0.047 rows=1 loops=11,188)

  • Index Cond: (t.reservation_pk = pk)
91. 0.081 0.156 ↑ 1.0 454 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 39kB
92. 0.075 0.075 ↑ 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.075 rows=454 loops=1)

93. 0.002 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
94. 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)

95. 0.624 1.091 ↑ 1.0 4,161 1

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

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

97. 0.647 7.525 ↑ 1.0 2,072 1

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

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

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

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

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

100. 0.084 0.768 ↑ 1.0 317 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 41kB
101. 0.086 0.684 ↑ 1.0 317 1

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

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

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

103. 0.066 0.136 ↑ 1.0 301 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
104. 0.070 0.070 ↑ 1.0 301 1

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

105. 0.598 1.106 ↑ 1.0 4,161 1

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

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

107. 0.498 1.584 ↑ 1.0 2,072 1

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

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

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

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

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

110. 0.067 0.119 ↑ 1.0 317 1

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

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

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

112. 0.559 1.940 ↓ 1.0 2,855 1

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

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

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

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

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

115. 0.028 0.077 ↑ 1.0 121 1

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

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

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

117. 0.059 0.126 ↑ 1.0 301 1

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

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

Seq Scan on markets t_m (cost=0.00..10.01 rows=301 width=36) (actual time=0.009..0.067 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: 20252
123. 0.084 0.169 ↑ 1.0 454 1

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

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

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

125. 0.004 0.012 ↑ 1.0 8 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
126. 0.008 0.008 ↑ 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.008 rows=8 loops=1)

127. 1.588 1,350.080 ↓ 2.9 8,854 1

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

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

Unique (cost=29,370.68..29,386.03 rows=3,070 width=4) (actual time=1,342.492..1,348.492 rows=8,854 loops=1)

129. 15.187 1,345.266 ↓ 7.4 22,775 1

Sort (cost=29,370.68..29,378.36 rows=3,070 width=4) (actual time=1,342.491..1,345.266 rows=22,775 loops=1)

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

Hash Join (cost=6,996.20..29,192.87 rows=3,070 width=4) (actual time=101.711..1,330.079 rows=22,775 loops=1)

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

Hash Join (cost=6,906.94..29,061.39 rows=3,070 width=8) (actual time=100.449..1,315.658 rows=22,775 loops=1)

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

Hash Join (cost=6,904.85..28,890.43 rows=18,423 width=16) (actual time=100.406..1,303.007 rows=23,070 loops=1)

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

Hash Join (cost=6,788.23..28,520.50 rows=18,423 width=12) (actual time=98.961..1,285.206 rows=23,070 loops=1)

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

Hash Join (cost=6,534.93..27,920.69 rows=6,182 width=12) (actual time=91.124..1,263.969 rows=8,854 loops=1)

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

Nested Loop (cost=6,416.20..27,716.95 rows=6,182 width=8) (actual time=85.907..1,251.312 rows=8,854 loops=1)

136. 6.410 1,039.006 ↓ 1.4 8,854 1

Hash Join (cost=6,415.78..24,781.31 rows=6,182 width=16) (actual time=80.784..1,039.006 rows=8,854 loops=1)

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

Nested Loop (cost=6,396.44..24,676.91 rows=6,196 width=20) (actual time=78.677..1,030.527 rows=9,704 loops=1)

138. 13.824 715.212 ↓ 1.6 9,704 1

Hash Join (cost=6,396.02..21,698.15 rows=6,196 width=12) (actual time=75.300..715.212 rows=9,704 loops=1)

  • Hash Cond: (t_1.driver_pk = d_1.pk)
139. 629.093 635.515 ↓ 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=7.230..635.515 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. 6.422 6.422 ↓ 1.6 11,374 1

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

  • Index Cond: ((billing_end >= 1551420000) AND (billing_end <= 1553317199))
141. 30.793 65.873 ↑ 1.0 156,251 1

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

  • Buckets: 262144 Batches: 1 Memory Usage: 8152kB
142. 35.080 35.080 ↑ 1.0 156,251 1

Seq Scan on drivers d_1 (cost=0.00..4,288.79 rows=156,279 width=8) (actual time=0.032..35.080 rows=156,251 loops=1)

143. 300.824 300.824 ↑ 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.031..0.031 rows=1 loops=9,704)

  • Index Cond: (member_pk = d_1.member_pk)
144. 0.178 2.069 ↑ 1.0 453 1

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

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

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

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

  • Index Cond: (pk = d_1.member_pk)
  • Heap Fetches: 507
147. 0.954 5.173 ↓ 1.0 2,855 1

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

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

  • Heap Fetches: 110
149. 1.216 7.792 ↑ 1.0 8,191 1

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

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

151. 0.579 1.405 ↑ 1.0 4,161 1

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

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

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

153. 0.001 0.025 ↑ 1.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
154. 0.004 0.024 ↑ 1.0 1 1

Nested Loop (cost=0.00..2.07 rows=1 width=8) (actual time=0.022..0.024 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.012..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.273 1.235 ↑ 1.0 2,072 1

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

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

  • Heap Fetches: 0
159.          

SubPlan (forHash Right Join)

160. 88.235 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. 83.030 441.175 ↓ 0.0 0 88,235

Nested Loop (cost=0.43..9.52 rows=1 width=10) (actual time=0.005..0.005 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.002..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