explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ePOq

Settings
# exclusive inclusive rows x rows loops node
1. 0.269 744.553 ↓ 1.5 2,000 1

Limit (cost=584,141.12..584,144.48 rows=1,343 width=447) (actual time=744.138..744.553 rows=2,000 loops=1)

2. 4.453 744.284 ↓ 1.5 2,000 1

Sort (cost=584,141.12..584,144.48 rows=1,343 width=447) (actual time=744.137..744.284 rows=2,000 loops=1)

  • Sort Key: (CASE WHEN ((r.update_time < '2020-06-30 10:10:49.160373'::timestamp without time zone) AND (r.update_time >= '2020-06-30 09:10:48.413966'::timestamp without time zone) AND (bi.update_time < '2020-06-30 10:10:49.160373'::timestamp without time zone) AND (bi.update_time >= '2020-06-30 09:10:48.413966'::timestamp without time zone)) THEN CASE WHEN (r.update_time > bi.update_time) THEN r.update_time ELSE bi.update_time END WHEN ((r.update_time < '2020-06-30 10:10:49.160373'::timestamp without time zone) AND (r.update_time >= '2020-06-30 09:10:48.413966'::timestamp without time zone)) THEN r.update_time ELSE bi.update_time END), bi.update_time DESC, r.id DESC NULLS LAST
  • Sort Method: quicksort Memory: 1,802kB
3. 0.302 739.831 ↓ 2.9 3,872 1

Append (cost=1,006.13..584,071.35 rows=1,343 width=447) (actual time=292.386..739.831 rows=3,872 loops=1)

4. 0.000 440.974 ↓ 1.9 2,508 1

Gather (cost=1,006.13..460,088.53 rows=1,342 width=447) (actual time=292.386..440.974 rows=2,508 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 0.293 324.468 ↓ 1.5 836 3 / 3

Hash Left Join (cost=6.13..173,795.25 rows=559 width=263) (actual time=284.334..324.468 rows=836 loops=3)

  • Hash Cond: (bi.price_plan_id = bp.id)
6. 0.730 324.155 ↓ 1.5 836 3 / 3

Nested Loop Left Join (cost=2.55..173,790.12 rows=559 width=259) (actual time=284.308..324.155 rows=836 loops=3)

7. 0.934 320.081 ↓ 1.5 836 3 / 3

Nested Loop (cost=2.12..169,150.68 rows=559 width=143) (actual time=284.296..320.081 rows=836 loops=3)

8. 0.312 295.739 ↓ 1.5 836 3 / 3

Hash Join (cost=1.56..165,639.63 rows=559 width=151) (actual time=283.612..295.739 rows=836 loops=3)

  • Hash Cond: (s.model_id = scm.id)
9. 0.925 295.417 ↓ 1.5 836 3 / 3

Nested Loop (cost=0.42..165,636.00 rows=559 width=135) (actual time=283.536..295.417 rows=836 loops=3)

10. 287.804 287.804 ↓ 1.5 836 3 / 3

Parallel Seq Scan on rental r (cost=0.00..161,304.59 rows=559 width=92) (actual time=283.502..287.804 rows=836 loops=3)

  • Filter: ((update_time < '2020-06-30 10:10:49.160373'::timestamp without time zone) AND (update_time >= '2020-06-30 09:10:48.413966'::timestamp without time zone))
  • Rows Removed by Filter: 2,163,484
11. 6.688 6.688 ↑ 1.0 1 2,508 / 3

Index Scan using scooter_id_pkey on scooter s (cost=0.42..7.75 rows=1 width=59) (actual time=0.008..0.008 rows=1 loops=2,508)

  • Index Cond: (id = r.scooter_id)
12. 0.003 0.010 ↑ 1.0 6 2 / 3

Hash (cost=1.06..1.06 rows=6 width=48) (actual time=0.015..0.015 rows=6 loops=2)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
13. 0.007 0.007 ↑ 1.0 6 2 / 3

Seq Scan on scooter_model scm (cost=0.00..1.06 rows=6 width=48) (actual time=0.009..0.010 rows=6 loops=2)

14. 23.408 23.408 ↑ 1.0 1 2,508 / 3

Index Scan using scooter_ext_status_history_id_pkey on scooter_ext_status_history sesh (cost=0.56..6.28 rows=1 width=24) (actual time=0.028..0.028 rows=1 loops=2,508)

  • Index Cond: (id = s.ext_status_history_id)
15. 3.344 3.344 ↑ 1.0 1 2,508 / 3

Index Scan using fki_billing_info_rental_id_fkey on billing_history bi (cost=0.43..8.29 rows=1 width=132) (actual time=0.004..0.004 rows=1 loops=2,508)

  • Index Cond: (r.id = rental_id)
16. 0.009 0.020 ↓ 1.1 74 2 / 3

Hash (cost=2.70..2.70 rows=70 width=20) (actual time=0.030..0.030 rows=74 loops=2)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
17. 0.011 0.011 ↓ 1.1 74 2 / 3

Seq Scan on billing_plan bp (cost=0.00..2.70 rows=70 width=20) (actual time=0.004..0.016 rows=74 loops=2)

18.          

SubPlan (for Gather)

19. 2.508 27.588 ↑ 1.0 1 2,508

Limit (cost=0.69..4.86 rows=1 width=24) (actual time=0.011..0.011 rows=1 loops=2,508)

20. 25.080 25.080 ↑ 9,635.0 1 2,508

Index Scan Backward using scooter_status_history_scooter_id_create_time_idx on scooter_status_history ssh2 (cost=0.69..40,149.39 rows=9,635 width=24) (actual time=0.010..0.010 rows=1 loops=2,508)

  • Index Cond: ((scooter_id = r.scooter_id) AND (create_time <= r.update_time))
21. 2.508 42.636 ↑ 1.0 1 2,508

Limit (cost=92.83..92.84 rows=1 width=24) (actual time=0.017..0.017 rows=1 loops=2,508)

22. 2.508 40.128 ↑ 7.0 1 2,508

Sort (cost=92.83..92.85 rows=7 width=24) (actual time=0.016..0.016 rows=1 loops=2,508)

  • Sort Key: re.create_time
  • Sort Method: quicksort Memory: 25kB
23. 2.407 37.620 ↑ 3.5 2 2,508

Nested Loop (cost=1.13..92.80 rows=7 width=24) (actual time=0.009..0.015 rows=2 loops=2,508)

24. 15.048 15.048 ↑ 3.5 2 2,508

Index Scan using rental_event_rental_id_action_create_time_idx on rental_event re (cost=0.56..32.70 rows=7 width=24) (actual time=0.005..0.006 rows=2 loops=2,508)

  • Index Cond: ((rental_id = r.id) AND (action = 2))
25. 20.165 20.165 ↑ 1.0 1 4,033

Index Scan using scooter_status_history_id_pkey on scooter_status_history ssh (cost=0.57..8.59 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=4,033)

  • Index Cond: (id = re.scooter_status_id)
26. 2.508 17.556 ↑ 1.0 1 2,508

Limit (cost=29.78..29.79 rows=1 width=24) (actual time=0.007..0.007 rows=1 loops=2,508)

27. 0.000 15.048 ↑ 2.0 1 2,508

Sort (cost=29.78..29.79 rows=2 width=24) (actual time=0.006..0.006 rows=1 loops=2,508)

  • Sort Key: re_1.create_time
  • Sort Method: quicksort Memory: 25kB
28. 3.307 15.048 ↑ 2.0 1 2,508

Nested Loop (cost=1.13..29.77 rows=2 width=24) (actual time=0.006..0.006 rows=1 loops=2,508)

29. 5.016 5.016 ↑ 2.0 1 2,508

Index Scan using rental_event_rental_id_action_create_time_idx on rental_event re_1 (cost=0.56..12.60 rows=2 width=24) (actual time=0.002..0.002 rows=1 loops=2,508)

  • Index Cond: ((rental_id = r.id) AND (action = 5))
30. 6.725 6.725 ↑ 1.0 1 1,345

Index Scan using scooter_status_history_id_pkey on scooter_status_history ssh_1 (cost=0.57..8.59 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=1,345)

  • Index Cond: (id = re_1.scooter_status_id)
31. 0.000 25.080 ↑ 1.0 1 2,508

Limit (cost=55.04..55.05 rows=1 width=10) (actual time=0.010..0.010 rows=1 loops=2,508)

32. 2.508 25.080 ↑ 4.0 1 2,508

Sort (cost=55.04..55.05 rows=4 width=10) (actual time=0.009..0.010 rows=1 loops=2,508)

  • Sort Key: re_2.create_time DESC
  • Sort Method: quicksort Memory: 25kB
33. 2.508 22.572 ↑ 4.0 1 2,508

Nested Loop Left Join (cost=1.13..55.02 rows=4 width=10) (actual time=0.008..0.009 rows=1 loops=2,508)

34. 7.524 7.524 ↑ 4.0 1 2,508

Index Scan using rental_event_rental_id_action_create_time_idx on rental_event re_2 (cost=0.56..20.64 rows=4 width=24) (actual time=0.002..0.003 rows=1 loops=2,508)

  • Index Cond: ((rental_id = r.id) AND (action = '-1'::integer))
35. 12.540 12.540 ↑ 1.0 1 2,508

Index Scan using scooter_ext_status_history_id_pkey on scooter_ext_status_history se (cost=0.56..8.58 rows=1 width=20) (actual time=0.005..0.005 rows=1 loops=2,508)

  • Index Cond: (re_2.scooter_ext_status_id = id)
36. 2.508 17.556 ↑ 1.0 1 2,508

Limit (cost=29.80..29.81 rows=1 width=10) (actual time=0.006..0.007 rows=1 loops=2,508)

37. 2.508 15.048 ↑ 2.0 1 2,508

Sort (cost=29.80..29.81 rows=2 width=10) (actual time=0.006..0.006 rows=1 loops=2,508)

  • Sort Key: re_3.create_time DESC
  • Sort Method: quicksort Memory: 25kB
38. 0.799 12.540 ↑ 2.0 1 2,508

Nested Loop Left Join (cost=1.13..29.79 rows=2 width=10) (actual time=0.005..0.005 rows=1 loops=2,508)

39. 5.016 5.016 ↑ 2.0 1 2,508

Index Scan using rental_event_rental_id_action_create_time_idx on rental_event re_3 (cost=0.56..12.60 rows=2 width=24) (actual time=0.002..0.002 rows=1 loops=2,508)

  • Index Cond: ((rental_id = r.id) AND (action = 5))
40. 6.725 6.725 ↑ 1.0 1 1,345

Index Scan using scooter_ext_status_history_id_pkey on scooter_ext_status_history se_1 (cost=0.56..8.58 rows=1 width=20) (actual time=0.005..0.005 rows=1 loops=1,345)

  • Index Cond: (re_3.scooter_ext_status_id = id)
41. 4.702 298.555 ↓ 1,364.0 1,364 1

Nested Loop Left Join (cost=1,001.55..123,969.38 rows=1 width=447) (actual time=164.324..298.555 rows=1,364 loops=1)

  • Join Filter: (bp_1.id = bi_1.price_plan_id)
  • Rows Removed by Join Filter: 2,120
42. 1.257 199.737 ↓ 1,364.0 1,364 1

Nested Loop (cost=1,001.55..123,753.32 rows=1 width=259) (actual time=164.080..199.737 rows=1,364 loops=1)

43. 0.865 191.660 ↓ 1,364.0 1,364 1

Nested Loop (cost=1,000.99..123,747.04 rows=1 width=267) (actual time=164.063..191.660 rows=1,364 loops=1)

44. 1.942 189.431 ↓ 1,364.0 1,364 1

Nested Loop (cost=1,000.86..123,746.88 rows=1 width=251) (actual time=164.052..189.431 rows=1,364 loops=1)

45. 1.700 176.577 ↓ 1,364.0 1,364 1

Nested Loop (cost=1,000.43..123,746.43 rows=1 width=208) (actual time=164.022..176.577 rows=1,364 loops=1)

46. 0.000 168.057 ↓ 1,364.0 1,364 1

Gather (cost=1,000.00..123,737.98 rows=1 width=132) (actual time=163.987..168.057 rows=1,364 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
47. 169.593 169.593 ↓ 455.0 455 3 / 3

Parallel Seq Scan on billing_history bi_1 (cost=0.00..122,737.88 rows=1 width=132) (actual time=159.534..169.593 rows=455 loops=3)

  • Filter: ((update_time < '2020-06-30 10:10:49.160373'::timestamp without time zone) AND (update_time >= '2020-06-30 09:10:48.413966'::timestamp without time zone))
  • Rows Removed by Filter: 1,310,610
48. 6.820 6.820 ↑ 1.0 1 1,364

Index Scan using rental_id_pkey on rental r_1 (cost=0.43..8.45 rows=1 width=92) (actual time=0.005..0.005 rows=1 loops=1,364)

  • Index Cond: (id = bi_1.rental_id)
49. 10.912 10.912 ↑ 1.0 1 1,364

Index Scan using scooter_id_pkey on scooter s_1 (cost=0.42..0.46 rows=1 width=59) (actual time=0.008..0.008 rows=1 loops=1,364)

  • Index Cond: (id = r_1.scooter_id)
50. 1.364 1.364 ↑ 1.0 1 1,364

Index Scan using scooter_model_id_pk on scooter_model scm_1 (cost=0.13..0.15 rows=1 width=48) (actual time=0.001..0.001 rows=1 loops=1,364)

  • Index Cond: (id = s_1.model_id)
51. 6.820 6.820 ↑ 1.0 1 1,364

Index Scan using scooter_ext_status_history_id_pkey on scooter_ext_status_history sesh_1 (cost=0.56..6.28 rows=1 width=24) (actual time=0.005..0.005 rows=1 loops=1,364)

  • Index Cond: (id = s_1.ext_status_history_id)
52. 1.364 1.364 ↑ 23.3 3 1,364

Seq Scan on billing_plan bp_1 (cost=0.00..2.70 rows=70 width=20) (actual time=0.001..0.001 rows=3 loops=1,364)

53.          

SubPlan (for Nested Loop Left Join)

54. 1.364 15.004 ↑ 1.0 1 1,364

Limit (cost=0.69..4.86 rows=1 width=24) (actual time=0.011..0.011 rows=1 loops=1,364)

55. 13.640 13.640 ↑ 9,635.0 1 1,364

Index Scan Backward using scooter_status_history_scooter_id_create_time_idx on scooter_status_history ssh2_1 (cost=0.69..40,149.39 rows=9,635 width=24) (actual time=0.010..0.010 rows=1 loops=1,364)

  • Index Cond: ((scooter_id = r_1.scooter_id) AND (create_time <= r_1.update_time))
56. 1.364 36.828 ↑ 1.0 1 1,364

Limit (cost=92.83..92.84 rows=1 width=24) (actual time=0.026..0.027 rows=1 loops=1,364)

57. 1.364 35.464 ↑ 7.0 1 1,364

Sort (cost=92.83..92.85 rows=7 width=24) (actual time=0.026..0.026 rows=1 loops=1,364)

  • Sort Key: re_4.create_time
  • Sort Method: top-N heapsort Memory: 25kB
58. 3.992 34.100 ↑ 2.3 3 1,364

Nested Loop (cost=1.13..92.80 rows=7 width=24) (actual time=0.012..0.025 rows=3 loops=1,364)

59. 9.548 9.548 ↑ 2.3 3 1,364

Index Scan using rental_event_rental_id_action_create_time_idx on rental_event re_4 (cost=0.56..32.70 rows=7 width=24) (actual time=0.005..0.007 rows=3 loops=1,364)

  • Index Cond: ((rental_id = r_1.id) AND (action = 2))
60. 20.560 20.560 ↑ 1.0 1 4,112

Index Scan using scooter_status_history_id_pkey on scooter_status_history ssh_2 (cost=0.57..8.59 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=4,112)

  • Index Cond: (id = re_4.scooter_status_id)
61. 1.364 13.640 ↑ 1.0 1 1,364

Limit (cost=29.78..29.79 rows=1 width=24) (actual time=0.010..0.010 rows=1 loops=1,364)

62. 0.000 12.276 ↑ 2.0 1 1,364

Sort (cost=29.78..29.79 rows=2 width=24) (actual time=0.009..0.009 rows=1 loops=1,364)

  • Sort Key: re_5.create_time
  • Sort Method: quicksort Memory: 25kB
63. 1.364 12.276 ↑ 2.0 1 1,364

Nested Loop (cost=1.13..29.77 rows=2 width=24) (actual time=0.008..0.009 rows=1 loops=1,364)

64. 4.092 4.092 ↑ 2.0 1 1,364

Index Scan using rental_event_rental_id_action_create_time_idx on rental_event re_5 (cost=0.56..12.60 rows=2 width=24) (actual time=0.003..0.003 rows=1 loops=1,364)

  • Index Cond: ((rental_id = r_1.id) AND (action = 5))
65. 6.820 6.820 ↑ 1.0 1 1,364

Index Scan using scooter_status_history_id_pkey on scooter_status_history ssh_3 (cost=0.57..8.59 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=1,364)

  • Index Cond: (id = re_5.scooter_status_id)
66. 0.000 13.640 ↑ 1.0 1 1,364

Limit (cost=55.04..55.05 rows=1 width=10) (actual time=0.010..0.010 rows=1 loops=1,364)

67. 1.364 13.640 ↑ 4.0 1 1,364

Sort (cost=55.04..55.05 rows=4 width=10) (actual time=0.010..0.010 rows=1 loops=1,364)

  • Sort Key: re_6.create_time DESC
  • Sort Method: quicksort Memory: 25kB
68. 1.364 12.276 ↑ 4.0 1 1,364

Nested Loop Left Join (cost=1.13..55.02 rows=4 width=10) (actual time=0.009..0.009 rows=1 loops=1,364)

69. 4.092 4.092 ↑ 4.0 1 1,364

Index Scan using rental_event_rental_id_action_create_time_idx on rental_event re_6 (cost=0.56..20.64 rows=4 width=24) (actual time=0.003..0.003 rows=1 loops=1,364)

  • Index Cond: ((rental_id = r_1.id) AND (action = '-1'::integer))
70. 6.820 6.820 ↑ 1.0 1 1,364

Index Scan using scooter_ext_status_history_id_pkey on scooter_ext_status_history se_2 (cost=0.56..8.58 rows=1 width=20) (actual time=0.005..0.005 rows=1 loops=1,364)

  • Index Cond: (re_6.scooter_ext_status_id = id)
71. 1.364 13.640 ↑ 1.0 1 1,364

Limit (cost=29.80..29.81 rows=1 width=10) (actual time=0.009..0.010 rows=1 loops=1,364)

72. 1.364 12.276 ↑ 2.0 1 1,364

Sort (cost=29.80..29.81 rows=2 width=10) (actual time=0.009..0.009 rows=1 loops=1,364)

  • Sort Key: re_7.create_time DESC
  • Sort Method: quicksort Memory: 25kB
73. 1.364 10.912 ↑ 2.0 1 1,364

Nested Loop Left Join (cost=1.13..29.79 rows=2 width=10) (actual time=0.008..0.008 rows=1 loops=1,364)

74. 2.728 2.728 ↑ 2.0 1 1,364

Index Scan using rental_event_rental_id_action_create_time_idx on rental_event re_7 (cost=0.56..12.60 rows=2 width=24) (actual time=0.002..0.002 rows=1 loops=1,364)

  • Index Cond: ((rental_id = r_1.id) AND (action = 5))
75. 6.820 6.820 ↑ 1.0 1 1,364

Index Scan using scooter_ext_status_history_id_pkey on scooter_ext_status_history se_3 (cost=0.56..8.58 rows=1 width=20) (actual time=0.005..0.005 rows=1 loops=1,364)

  • Index Cond: (re_7.scooter_ext_status_id = id)
Planning time : 5.149 ms
Execution time : 745.404 ms