explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lPVP : lrjunk

Settings
# exclusive inclusive rows x rows loops node
1. 183.033 17,349.263 ↓ 590.3 8,854 1

Unique (cost=488,858.01..488,860.23 rows=15 width=1,007) (actual time=17,077.140..17,349.263 rows=8,854 loops=1)

2.          

CTE mvsm_stacks

3. 0.258 0.258 ↑ 1.0 21 1

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

  • Filter: (vehicle_type_pk IS NOT NULL)
  • Rows Removed by Filter: 4140
4. 2,560.808 17,166.230 ↓ 5,882.3 88,235 1

Sort (cost=488,793.40..488,793.44 rows=15 width=1,007) (actual time=17,077.139..17,166.230 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. 42.330 14,605.422 ↓ 5,882.3 88,235 1

Subquery Scan on mvsm_grouping (cost=354,043.22..488,793.11 rows=15 width=1,007) (actual time=6,686.173..14,605.422 rows=88,235 loops=1)

6. 7,160.284 14,563.092 ↓ 5,882.3 88,235 1

Merge Left Join (cost=354,043.22..488,792.89 rows=15 width=1,065) (actual time=6,686.168..14,563.092 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
7. 32.021 6,095.021 ↓ 5,882.3 88,235 1

Merge Left Join (cost=274,782.36..394,451.64 rows=15 width=658) (actual time=5,720.214..6,095.021 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
8. 29.065 5,084.116 ↓ 5,882.3 88,235 1

Merge Semi Join (cost=195,521.51..300,261.17 rows=15 width=626) (actual time=4,735.972..5,084.116 rows=88,235 loops=1)

  • Merge Cond: (t.pk = t_1.pk)
9. 149.046 4,878.296 ↓ 15.6 110,754 1

Merge Left Join (cost=164,861.11..269,521.47 rows=7,097 width=626) (actual time=4,567.526..4,878.296 rows=110,754 loops=1)

  • Merge Cond: (t.pk = s.trip_pk)
10. 284.474 3,094.577 ↓ 15.6 110,754 1

Sort (cost=164,860.68..164,878.42 rows=7,097 width=594) (actual time=3,011.808..3,094.577 rows=110,754 loops=1)

  • Sort Key: t.pk
  • Sort Method: external merge Disk: 54472kB
11. 37.432 2,810.103 ↓ 15.6 110,754 1

Hash Left Join (cost=52,603.41..164,406.72 rows=7,097 width=594) (actual time=1,515.808..2,810.103 rows=110,754 loops=1)

  • Hash Cond: (tc.rate_code_pk = tc_rc.pk)
12. 37.848 2,772.662 ↓ 15.6 110,754 1

Hash Left Join (cost=52,602.23..164,307.95 rows=7,097 width=590) (actual time=1,515.788..2,772.662 rows=110,754 loops=1)

  • Hash Cond: (tc.rate_plan_pk = tc_rp.pk)
13. 494.441 2,734.656 ↓ 15.6 110,754 1

Nested Loop Left Join (cost=52,584.02..164,192.16 rows=7,097 width=559) (actual time=1,515.614..2,734.656 rows=110,754 loops=1)

14. 34.757 1,797.199 ↓ 15.6 110,754 1

Hash Left Join (cost=52,575.09..53,247.04 rows=7,097 width=535) (actual time=1,511.476..1,797.199 rows=110,754 loops=1)

  • Hash Cond: (t_j.market_pk = t_m.pk)
15. 39.137 1,762.334 ↓ 15.6 110,754 1

Hash Left Join (cost=52,561.31..53,135.68 rows=7,097 width=507) (actual time=1,511.350..1,762.334 rows=110,754 loops=1)

  • Hash Cond: (t.vehicle_pk = t_dv.pk)
16. 38.787 1,721.573 ↓ 15.6 110,754 1

Hash Left Join (cost=52,357.17..52,851.97 rows=7,097 width=484) (actual time=1,509.702..1,721.573 rows=110,754 loops=1)

  • Hash Cond: (t_us.lot_pk = t_l.pk)
17. 193.777 1,681.223 ↓ 15.6 110,754 1

Hash Right Join (cost=52,184.93..52,582.15 rows=7,097 width=408) (actual time=1,508.109..1,681.223 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
18. 3.401 8.276 ↓ 2.0 8,049 1

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

  • Hash Cond: (t_usr.vehicle_pk = t_v.pk)
19. 1.476 3.393 ↓ 2.0 8,049 1

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

20. 0.014 0.014 ↑ 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.013..0.014 rows=1 loops=1)

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

  • Index Cond: (status_pk = t_usrs.pk)
22. 0.408 1.482 ↓ 1.0 2,855 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 176kB
23. 0.579 1.074 ↓ 1.0 2,855 1

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

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

25. 0.023 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
26. 0.030 0.030 ↑ 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.030 rows=121 loops=1)

27. 11.252 1,479.170 ↓ 2.2 15,453 1

Hash (cost=51,891.79..51,891.79 rows=7,097 width=396) (actual time=1,479.170..1,479.170 rows=15,453 loops=1)

  • Buckets: 16384 (originally 8192) Batches: 1 (originally 1) Memory Usage: 5389kB
28. 4.152 1,467.918 ↓ 2.2 15,453 1

Hash Left Join (cost=51,299.40..51,891.79 rows=7,097 width=396) (actual time=1,444.595..1,467.918 rows=15,453 loops=1)

  • Hash Cond: (t.billing_stack_pk = t_us.pk)
29. 3.999 1,462.710 ↓ 2.2 15,453 1

Hash Left Join (cost=51,182.78..51,677.58 rows=7,097 width=388) (actual time=1,443.499..1,462.710 rows=15,453 loops=1)

  • Hash Cond: (r_us.lot_pk = r_l.pk)
30. 12.605 1,456.471 ↓ 2.2 15,453 1

Hash Right Join (cost=50,992.41..51,389.63 rows=7,097 width=284) (actual time=1,441.232..1,456.471 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
31. 2.097 7.087 ↓ 2.0 8,049 1

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

  • Hash Cond: (r_usr.vehicle_pk = r_v.pk)
32. 1.069 2.925 ↓ 2.0 8,049 1

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

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

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

  • Index Cond: (status_pk = r_usrs.pk)
35. 0.536 2.065 ↓ 1.0 2,855 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 209kB
36. 0.572 1.529 ↓ 1.0 2,855 1

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

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

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

38. 0.025 0.087 ↑ 1.0 121 1

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

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

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

40. 11.839 1,436.779 ↓ 1.6 11,188 1

Hash (cost=50,699.27..50,699.27 rows=7,097 width=261) (actual time=1,436.779..1,436.779 rows=11,188 loops=1)

  • Buckets: 16384 (originally 8192) Batches: 1 (originally 1) Memory Usage: 2518kB
41. 6.824 1,424.940 ↓ 1.6 11,188 1

Hash Left Join (cost=7,463.25..50,699.27 rows=7,097 width=261) (actual time=89.841..1,424.940 rows=11,188 loops=1)

  • Hash Cond: (r.stack_pk = r_us.pk)
42. 4.252 1,417.092 ↓ 1.6 11,188 1

Hash Left Join (cost=7,346.63..50,485.06 rows=7,097 width=253) (actual time=88.782..1,417.092 rows=11,188 loops=1)

  • Hash Cond: (r.status_pk = rs.pk)
43. 5.178 1,412.825 ↓ 1.6 11,188 1

Hash Join (cost=7,345.49..50,386.34 rows=7,097 width=249) (actual time=88.752..1,412.825 rows=11,188 loops=1)

  • Hash Cond: (a.rate_plan_pk = a_rp.pk)
44. 10.989 1,407.426 ↓ 1.6 11,188 1

Nested Loop Left Join (cost=7,327.28..50,270.55 rows=7,097 width=222) (actual time=88.518..1,407.426 rows=11,188 loops=1)

45. 1.050 870.601 ↓ 1.6 11,188 1

Nested Loop (cost=7,321.54..46,583.24 rows=7,097 width=189) (actual time=87.359..870.601 rows=11,188 loops=1)

46. 4.441 824.799 ↓ 1.6 11,188 1

Hash Join (cost=7,321.12..43,171.63 rows=7,097 width=193) (actual time=87.336..824.799 rows=11,188 loops=1)

  • Hash Cond: (m.member_type_pk = mt.pk)
47. 8.280 820.352 ↓ 1.6 11,188 1

Nested Loop (cost=7,320.03..43,072.96 rows=7,097 width=187) (actual time=87.314..820.352 rows=11,188 loops=1)

48. 6.268 666.628 ↓ 1.6 11,188 1

Nested Loop (cost=7,319.61..38,939.48 rows=7,097 width=161) (actual time=87.284..666.628 rows=11,188 loops=1)

49. 11.786 582.044 ↓ 1.6 11,188 1

Nested Loop (cost=7,319.19..27,262.28 rows=7,097 width=152) (actual time=87.250..582.044 rows=11,188 loops=1)

50. 4.077 413.626 ↓ 1.6 11,188 1

Hash Join (cost=7,318.77..23,893.71 rows=7,097 width=156) (actual time=86.213..413.626 rows=11,188 loops=1)

  • Hash Cond: (t.status_pk = ts.pk)
51. 22.890 409.536 ↓ 1.6 11,188 1

Hash Join (cost=7,317.70..23,795.06 rows=7,097 width=142) (actual time=86.185..409.536 rows=11,188 loops=1)

  • Hash Cond: (t.driver_pk = d.pk)
52. 301.782 307.234 ↓ 1.6 11,188 1

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

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

  • Index Cond: ((billing_end >= 1551420000) AND (billing_end <= 1553317199))
54. 33.616 79.412 ↓ 1.0 156,535 1

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

  • Buckets: 131072 Batches: 2 Memory Usage: 5405kB
55. 45.796 45.796 ↓ 1.0 156,535 1

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

56. 0.003 0.013 ↑ 1.0 3 1

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

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

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

58. 156.632 156.632 ↑ 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.014..0.014 rows=1 loops=11,188)

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

  • Index Cond: (pk = d.contact_pk)
60. 145.444 145.444 ↑ 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.013..0.013 rows=1 loops=11,188)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
62. 0.003 0.003 ↑ 1.0 4 1

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

63. 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.003..0.004 rows=1 loops=11,188)

  • Index Cond: (member_pk = d.member_pk)
64. 67.128 525.836 ↑ 1.0 1 11,188

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

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

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

66. 11.188 436.332 ↑ 1.0 1 11,188

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
67. 425.144 425.144 ↑ 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.038..0.038 rows=1 loops=11,188)

  • Index Cond: (t.reservation_pk = pk)
68. 0.080 0.221 ↑ 1.0 454 1

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

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

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

70. 0.008 0.015 ↑ 1.0 6 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
71. 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.006..0.007 rows=6 loops=1)

72. 0.600 1.024 ↑ 1.0 4,161 1

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

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

74. 0.578 2.240 ↑ 1.0 2,072 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 326kB
75. 0.619 1.662 ↑ 1.0 2,072 1

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

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

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

77. 0.075 0.346 ↑ 1.0 317 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 41kB
78. 0.092 0.271 ↑ 1.0 317 1

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

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

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

80. 0.054 0.125 ↑ 1.0 301 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
81. 0.071 0.071 ↑ 1.0 301 1

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

82. 0.588 1.056 ↑ 1.0 4,161 1

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

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

84. 0.504 1.563 ↑ 1.0 2,072 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 273kB
85. 0.746 1.059 ↑ 1.0 2,072 1

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

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

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

87. 0.067 0.120 ↑ 1.0 317 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 32kB
88. 0.053 0.053 ↑ 1.0 317 1

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

89. 0.512 1.624 ↓ 1.0 2,855 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 209kB
90. 0.546 1.112 ↓ 1.0 2,855 1

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

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

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

92. 0.018 0.050 ↑ 1.0 121 1

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

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

94. 0.060 0.108 ↑ 1.0 301 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
95. 0.048 0.048 ↑ 1.0 301 1

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

96. 0.000 443.016 ↑ 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.004..0.004 rows=1 loops=110,754)

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

SubPlan (forIndex Scan)

98. 0.000 443.016 ↑ 1.0 1 110,754

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

99. 443.016 443.016 ↑ 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.004..0.004 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
100. 0.084 0.158 ↑ 1.0 454 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 41kB
101. 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.006..0.074 rows=454 loops=1)

102. 0.003 0.009 ↑ 1.0 8 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
103. 0.006 0.006 ↑ 1.0 8 1

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

104. 113.029 1,634.673 ↓ 1.4 740,401 1

Materialize (cost=0.43..103,288.43 rows=524,187 width=36) (actual time=36.321..1,634.673 rows=740,401 loops=1)

105. 339.245 1,521.644 ↓ 1.3 661,086 1

GroupAggregate (cost=0.43..96,736.09 rows=524,187 width=36) (actual time=36.313..1,521.644 rows=661,086 loops=1)

  • Group Key: s.trip_pk
106. 610.799 1,182.399 ↓ 1.2 698,862 1

Nested Loop (cost=0.43..87,379.80 rows=560,791 width=10) (actual time=35.993..1,182.399 rows=698,862 loops=1)

  • Join Filter: (s.sale_type_pk = st.pk)
  • Rows Removed by Join Filter: 2096584
107. 571.600 571.600 ↑ 1.0 698,862 1

Index Scan using sales_trip_pk_idx on sales s (cost=0.43..45,319.39 rows=700,989 width=14) (actual time=35.971..571.600 rows=698,862 loops=1)

  • Filter: (trip_cost_pk IS NOT NULL)
  • Rows Removed by Filter: 149203
108. 0.000 0.000 ↑ 1.0 4 698,862

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

109. 0.012 0.012 ↑ 1.0 4 1

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

  • Filter: ((name)::text <> 'driving_credit'::text)
  • Rows Removed by Filter: 1
110. 1.333 176.755 ↓ 2.9 8,854 1

Materialize (cost=30,660.41..30,714.13 rows=3,070 width=4) (actual time=168.441..176.755 rows=8,854 loops=1)

111. 3.144 175.422 ↓ 2.9 8,854 1

Unique (cost=30,660.41..30,675.76 rows=3,070 width=4) (actual time=168.437..175.422 rows=8,854 loops=1)

112. 9.409 172.278 ↓ 7.4 22,772 1

Sort (cost=30,660.41..30,668.08 rows=3,070 width=4) (actual time=168.436..172.278 rows=22,772 loops=1)

  • Sort Key: t_1.pk
  • Sort Method: quicksort Memory: 1836kB
113. 4.954 162.869 ↓ 7.4 22,775 1

Hash Join (cost=7,612.66..30,482.59 rows=3,070 width=4) (actual time=65.731..162.869 rows=22,775 loops=1)

  • Hash Cond: (us.lot_pk = l_1.pk)
114. 5.098 157.446 ↓ 7.4 22,775 1

Hash Join (cost=7,523.40..30,351.12 rows=3,070 width=8) (actual time=65.247..157.446 rows=22,775 loops=1)

  • Hash Cond: ((us.status_pk = uss.pk) AND (usr.status_pk = usrs.pk))
115. 5.964 152.328 ↓ 1.3 23,070 1

Hash Join (cost=7,521.31..30,180.16 rows=18,423 width=16) (actual time=65.219..152.328 rows=23,070 loops=1)

  • Hash Cond: (usr.stack_pk = us.pk)
116. 4.861 145.393 ↓ 1.3 23,070 1

Hash Join (cost=7,404.69..29,810.22 rows=18,423 width=12) (actual time=64.228..145.393 rows=23,070 loops=1)

  • Hash Cond: (t_1.resource_pk = usr.vehicle_pk)
117. 2.587 138.465 ↓ 1.4 8,854 1

Hash Join (cost=7,151.39..29,210.42 rows=6,182 width=12) (actual time=62.139..138.465 rows=8,854 loops=1)

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

Nested Loop (cost=7,032.65..29,006.68 rows=6,182 width=8) (actual time=60.352..134.132 rows=8,854 loops=1)

119. 2.225 116.962 ↓ 1.4 8,854 1

Hash Join (cost=7,032.23..26,071.27 rows=6,182 width=16) (actual time=60.323..116.962 rows=8,854 loops=1)

  • Hash Cond: (a_1.rate_plan_pk = rp.pk)
120. 3.134 114.531 ↓ 1.6 9,704 1

Nested Loop (cost=7,012.90..25,966.87 rows=6,196 width=20) (actual time=60.098..114.531 rows=9,704 loops=1)

121. 15.380 91.989 ↓ 1.6 9,704 1

Hash Join (cost=7,012.48..22,988.39 rows=6,196 width=12) (actual time=60.082..91.989 rows=9,704 loops=1)

  • Hash Cond: (t_1.driver_pk = d_1.pk)
122. 17.939 18.908 ↓ 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=1.191..18.908 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
123. 0.969 0.969 ↓ 1.6 11,374 1

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

  • Index Cond: ((billing_end >= 1551420000) AND (billing_end <= 1553317199))
124. 29.146 57.701 ↓ 1.0 156,535 1

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

  • Buckets: 262144 Batches: 2 Memory Usage: 5107kB
125. 28.555 28.555 ↓ 1.0 156,535 1

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

126. 19.408 19.408 ↑ 1.0 1 9,704

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

  • Index Cond: (member_pk = d_1.member_pk)
127. 0.054 0.206 ↑ 1.0 453 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
128. 0.152 0.152 ↑ 1.0 453 1

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

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

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

  • Index Cond: (pk = d_1.member_pk)
  • Heap Fetches: 0
130. 0.360 1.746 ↓ 1.0 2,855 1

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

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

  • Heap Fetches: 110
132. 1.025 2.067 ↑ 1.0 8,191 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 416kB
133. 1.042 1.042 ↑ 1.0 8,191 1

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

134. 0.468 0.971 ↑ 1.0 4,161 1

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

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

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

136. 0.002 0.020 ↑ 1.0 1 1

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

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

138. 0.008 0.008 ↑ 1.0 1 1

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

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

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

  • Filter: ((name)::text = 'normal'::text)
  • Rows Removed by Filter: 1
140. 0.218 0.469 ↑ 1.0 2,072 1

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

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

  • Heap Fetches: 0
142. 13.410 978.884 ↑ 4.3 87,368 1

Materialize (cost=79,260.85..93,257.28 rows=373,238 width=44) (actual time=917.043..978.884 rows=87,368 loops=1)

143. 36.589 965.474 ↑ 4.6 82,001 1

GroupAggregate (cost=79,260.85..88,591.80 rows=373,238 width=44) (actual time=917.040..965.474 rows=82,001 loops=1)

  • Group Key: s_1.trip_pk, bp.start_stamp, bp.end_stamp
144. 571.868 928.885 ↑ 4.0 93,487 1

Sort (cost=79,260.85..80,193.95 rows=373,238 width=18) (actual time=917.030..928.885 rows=93,487 loops=1)

  • Sort Key: s_1.trip_pk, bp.start_stamp, bp.end_stamp
  • Sort Method: external merge Disk: 9560kB
145. 77.758 357.017 ↓ 1.1 406,833 1

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

  • Hash Cond: (s_1.billing_period_pk = bp.pk)
146. 114.162 279.205 ↓ 1.1 406,833 1

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

  • Hash Cond: (s_1.sale_type_pk = st_1.pk)
147. 165.033 165.033 ↑ 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.007..165.033 rows=464,550 loops=1)

  • Filter: (trip_cost_pk IS NULL)
  • Rows Removed by Filter: 702594
148. 0.001 0.010 ↑ 1.0 4 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
149. 0.009 0.009 ↑ 1.0 4 1

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

  • Filter: ((name)::text <> 'driving_credit'::text)
  • Rows Removed by Filter: 1
150. 0.020 0.054 ↑ 1.0 144 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
151. 0.034 0.034 ↑ 1.0 144 1

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

152. 13.530 954.847 ↑ 4.3 87,368 1

Materialize (cost=79,260.85..93,257.28 rows=373,238 width=40) (actual time=892.913..954.847 rows=87,368 loops=1)

153. 36.571 941.317 ↑ 4.6 82,001 1

GroupAggregate (cost=79,260.85..88,591.80 rows=373,238 width=44) (actual time=892.908..941.317 rows=82,001 loops=1)

  • Group Key: s_2.trip_pk, bp_1.start_stamp, bp_1.end_stamp
154. 556.961 904.746 ↑ 4.0 93,487 1

Sort (cost=79,260.85..80,193.95 rows=373,238 width=18) (actual time=892.899..904.746 rows=93,487 loops=1)

  • Sort Key: s_2.trip_pk, bp_1.start_stamp, bp_1.end_stamp
  • Sort Method: external merge Disk: 9560kB
155. 77.677 347.785 ↓ 1.1 406,833 1

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

  • Hash Cond: (s_2.billing_period_pk = bp_1.pk)
156. 113.940 270.062 ↓ 1.1 406,833 1

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

  • Hash Cond: (s_2.sale_type_pk = st_2.pk)
157. 156.107 156.107 ↑ 1.0 464,550 1

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

  • Filter: (trip_cost_pk IS NULL)
  • Rows Removed by Filter: 702594
158. 0.003 0.015 ↑ 1.0 4 1

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

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

  • Filter: ((name)::text <> 'driving_credit'::text)
  • Rows Removed by Filter: 1
160. 0.020 0.046 ↑ 1.0 144 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
161. 0.026 0.026 ↑ 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.026 rows=144 loops=1)

162.          

SubPlan (forMerge Left Join)

163. 0.000 352.940 ↓ 0.0 0 88,235

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

  • Group Key: s_3.trip_pk
164. 83.030 352.940 ↓ 0.0 0 88,235

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

  • Join Filter: (s_3.sale_type_pk = st_3.pk)
  • Rows Removed by Join Filter: 1
165. 176.470 176.470 ↑ 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.002 rows=1 loops=88,235)

  • Index Cond: (trip_pk = t.pk)
166. 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