explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wAEn : mv_reservations

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.048 1,781.821 ↑ 1.0 100 1

Limit (cost=423,261.05..423,417.05 rows=100 width=2,371) (actual time=1,735.506..1,781.821 rows=100 loops=1)

2. 47.532 1,781.773 ↑ 37.5 100 1

Result (cost=423,261.05..429,115.73 rows=3,753 width=2,371) (actual time=1,735.503..1,781.773 rows=100 loops=1)

3. 1.031 1,734.241 ↑ 37.5 100 1

Sort (cost=423,261.05..423,270.43 rows=3,753 width=2,299) (actual time=1,734.195..1,734.241 rows=100 loops=1)

  • Sort Key: r.id
  • Sort Method: top-N heapsort Memory: 124kB
4. 4.786 1,733.210 ↑ 3.6 1,035 1

Nested Loop Left Join (cost=233,663.41..423,117.61 rows=3,753 width=2,299) (actual time=565.441..1,733.210 rows=1,035 loops=1)

5. 1.038 1,679.779 ↑ 3.6 1,035 1

Nested Loop Left Join (cost=233,662.99..399,068.42 rows=3,753 width=2,307) (actual time=565.409..1,679.779 rows=1,035 loops=1)

6. 0.876 1,676.671 ↑ 3.6 1,035 1

Nested Loop Left Join (cost=233,662.56..373,003.05 rows=3,753 width=2,277) (actual time=565.397..1,676.671 rows=1,035 loops=1)

7. 188.648 1,642.675 ↑ 3.6 1,035 1

Hash Join (cost=233,662.14..345,149.30 rows=3,753 width=2,178) (actual time=565.236..1,642.675 rows=1,035 loops=1)

  • Hash Cond: (loc.id = r.origin_id)
8. 433.283 972.756 ↑ 1.0 1,538,303 1

Hash Left Join (cost=720.06..106,394.90 rows=1,539,944 width=150) (actual time=4.488..972.756 rows=1,538,303 loops=1)

  • Hash Cond: (loc.cityareaid = are.id)
9. 535.011 535.011 ↑ 1.0 1,538,303 1

Seq Scan on md_location loc (cost=0.00..86,132.44 rows=1,539,944 width=118) (actual time=0.011..535.011 rows=1,538,303 loops=1)

10. 1.158 4.462 ↑ 1.1 6,449 1

Hash (cost=629.47..629.47 rows=7,247 width=40) (actual time=4.462..4.462 rows=6,449 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 536kB
11. 3.304 3.304 ↑ 1.1 6,449 1

Seq Scan on md_area are (cost=0.00..629.47 rows=7,247 width=40) (actual time=0.003..3.304 rows=6,449 loops=1)

12. 1.655 481.271 ↑ 3.6 1,035 1

Hash (cost=232,895.17..232,895.17 rows=3,753 width=2,036) (actual time=481.270..481.271 rows=1,035 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 627kB
13. 1.231 479.616 ↑ 3.6 1,035 1

Nested Loop Left Join (cost=3,345.77..232,895.17 rows=3,753 width=2,036) (actual time=35.575..479.616 rows=1,035 loops=1)

14. 1.088 476.315 ↑ 3.6 1,035 1

Nested Loop Left Join (cost=3,345.34..218,745.06 rows=3,753 width=2,006) (actual time=35.570..476.315 rows=1,035 loops=1)

15. 1.003 473.157 ↑ 3.6 1,035 1

Nested Loop Left Join (cost=3,345.05..197,833.04 rows=3,753 width=1,975) (actual time=35.556..473.157 rows=1,035 loops=1)

16. 0.568 470.084 ↑ 3.6 1,035 1

Nested Loop Left Join (cost=3,344.63..185,051.98 rows=3,753 width=1,940) (actual time=35.551..470.084 rows=1,035 loops=1)

17. 0.835 456.061 ↑ 3.6 1,035 1

Nested Loop Left Join (cost=3,343.22..133,173.12 rows=3,753 width=1,248) (actual time=35.517..456.061 rows=1,035 loops=1)

18. 0.807 446.946 ↑ 3.6 1,035 1

Hash Join (cost=3,342.23..94,075.33 rows=3,753 width=680) (actual time=35.488..446.946 rows=1,035 loops=1)

  • Hash Cond: (f.vehicletype_id = t.translatable_id)
19. 0.804 445.876 ↑ 2.6 1,035 1

Nested Loop Left Join (cost=3,283.07..93,945.00 rows=2,691 width=663) (actual time=35.203..445.876 rows=1,035 loops=1)

20. 0.857 284.802 ↑ 2.6 1,034 1

Hash Left Join (cost=3,282.65..31,625.90 rows=2,675 width=655) (actual time=35.179..284.802 rows=1,034 loops=1)

  • Hash Cond: (r.vehicle_id = v.id)
21. 0.732 249.102 ↑ 2.6 1,034 1

Hash Join (cost=36.95..28,365.03 rows=2,675 width=315) (actual time=0.257..249.102 rows=1,034 loops=1)

  • Hash Cond: (f.vehicletype_id = vt.id)
22. 1.104 248.178 ↑ 2.6 1,034 1

Nested Loop (cost=0.85..28,292.15 rows=2,675 width=205) (actual time=0.058..248.178 rows=1,034 loops=1)

23. 22.696 22.696 ↑ 2.6 1,034 1

Index Scan using idx_abstract_reservation_status on md_abstract_reservation r (cost=0.42..8,833.71 rows=2,675 width=193) (actual time=0.041..22.696 rows=1,034 loops=1)

  • Index Cond: ((status)::text = 'ACCEPTED'::text)
  • Filter: ((selectedstartdate >= '2018-04-11 10:00:00+00'::timestamp with time zone) AND (selectedstartdate <= '2020-05-11 18:45:00+00'::timestamp with time zone))
  • Rows Removed by Filter: 61
24. 224.378 224.378 ↑ 1.0 1 1,034

Index Scan using md_offer_pkey on md_offer f (cost=0.42..7.26 rows=1 width=12) (actual time=0.217..0.217 rows=1 loops=1,034)

  • Index Cond: (id = r.selectedoffer_id)
25. 0.066 0.192 ↓ 1.0 364 1

Hash (cost=31.60..31.60 rows=360 width=110) (actual time=0.192..0.192 rows=364 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 60kB
26. 0.126 0.126 ↓ 1.0 364 1

Seq Scan on md_vehicle_type vt (cost=0.00..31.60 rows=360 width=110) (actual time=0.004..0.126 rows=364 loops=1)

27. 13.425 34.843 ↓ 1.0 64,435 1

Hash (cost=2,440.31..2,440.31 rows=64,431 width=348) (actual time=34.843..34.843 rows=64,435 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 4669kB
28. 21.418 21.418 ↓ 1.0 64,435 1

Seq Scan on md_vehicle v (cost=0.00..2,440.31 rows=64,431 width=348) (actual time=0.006..21.418 rows=64,435 loops=1)

29. 160.270 160.270 ↑ 6.0 1 1,034

Index Scan using idx_60741d0f53c674ee on md_item i (cost=0.43..23.24 rows=6 width=16) (actual time=0.154..0.155 rows=1 loops=1,034)

  • Index Cond: (f.id = offer_id)
  • Filter: (itemcode = ANY ('{101,102}'::integer[]))
  • Rows Removed by Filter: 1
30. 0.070 0.263 ↑ 1.0 364 1

Hash (cost=54.61..54.61 rows=364 width=29) (actual time=0.263..0.263 rows=364 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 31kB
31. 0.160 0.193 ↑ 1.0 364 1

Bitmap Heap Scan on md_vehicle_type_translation t (cost=10.06..54.61 rows=364 width=29) (actual time=0.042..0.193 rows=364 loops=1)

  • Recheck Cond: ((locale)::text = 'en'::text)
  • Heap Blocks: exact=40
32. 0.033 0.033 ↑ 1.0 364 1

Bitmap Index Scan on idx_vehicle_type_translation_en (cost=0.00..9.97 rows=364 width=0) (actual time=0.033..0.033 rows=364 loops=1)

33. 1.035 8.280 ↑ 1.0 1 1,035

Nested Loop Left Join (cost=0.98..10.41 rows=1 width=576) (actual time=0.007..0.008 rows=1 loops=1,035)

34. 1.035 7.245 ↑ 1.0 1 1,035

Nested Loop Left Join (cost=0.85..10.25 rows=1 width=64) (actual time=0.006..0.007 rows=1 loops=1,035)

35. 5.175 5.175 ↑ 1.0 1 1,035

Index Scan using md_character_pkey on md_character c_1 (cost=0.42..6.94 rows=1 width=30) (actual time=0.005..0.005 rows=1 loops=1,035)

  • Index Cond: (r.travellingcustomer_id = id)
  • Filter: ((type = 'customer'::charactertypeenum) OR (type = 'guest'::charactertypeenum))
36. 1.035 1.035 ↓ 0.0 0 1,035

Index Scan using md_character_pkey on md_character cm_1 (cost=0.42..3.30 rows=1 width=42) (actual time=0.001..0.001 rows=0 loops=1,035)

  • Index Cond: (c_1.company_id = id)
37. 0.000 0.000 ↓ 0.0 0 1,035

Index Scan using md_character_channel_pkey on md_character_channel ch_1 (cost=0.14..0.15 rows=1 width=520) (actual time=0.000..0.000 rows=0 loops=1,035)

  • Index Cond: (cm_1.channel_id = id)
38. 1.035 13.455 ↑ 1.0 1 1,035

Nested Loop Left Join (cost=1.41..13.81 rows=1 width=696) (actual time=0.012..0.013 rows=1 loops=1,035)

39. 2.070 6.210 ↑ 1.0 1 1,035

Nested Loop Left Join (cost=0.98..10.41 rows=1 width=621) (actual time=0.005..0.006 rows=1 loops=1,035)

40. 0.000 4.140 ↑ 1.0 1 1,035

Nested Loop Left Join (cost=0.85..10.25 rows=1 width=109) (actual time=0.004..0.004 rows=1 loops=1,035)

41. 3.105 3.105 ↑ 1.0 1 1,035

Index Scan using md_character_pkey on md_character c (cost=0.42..6.94 rows=1 width=31) (actual time=0.002..0.003 rows=1 loops=1,035)

  • Index Cond: (r.bookingcustomer_id = id)
  • Filter: ((type = 'customer'::charactertypeenum) OR (type = 'guest'::charactertypeenum))
42. 1.035 1.035 ↓ 0.0 0 1,035

Index Scan using md_character_pkey on md_character cm (cost=0.42..3.30 rows=1 width=83) (actual time=0.001..0.001 rows=0 loops=1,035)

  • Index Cond: (c.company_id = id)
43. 0.000 0.000 ↓ 0.0 0 1,035

Index Scan using md_character_channel_pkey on md_character_channel ch (cost=0.14..0.15 rows=1 width=520) (actual time=0.000..0.000 rows=0 loops=1,035)

  • Index Cond: (cm.channel_id = id)
44. 6.210 6.210 ↑ 1.0 1 1,035

Index Scan using md_user_pkey on md_user u (cost=0.42..3.40 rows=1 width=51) (actual time=0.005..0.006 rows=1 loops=1,035)

  • Index Cond: (c.user_id = id)
45. 2.070 2.070 ↑ 1.0 1 1,035

Index Scan using md_user_pkey on md_user u_1 (cost=0.42..3.40 rows=1 width=43) (actual time=0.002..0.002 rows=1 loops=1,035)

  • Index Cond: (c_1.user_id = id)
46. 2.070 2.070 ↑ 1.0 1 1,035

Index Scan using idx_character_driver on md_character c_2 (cost=0.29..5.56 rows=1 width=35) (actual time=0.002..0.002 rows=1 loops=1,035)

  • Index Cond: (r.driver_id = id)
47. 2.070 2.070 ↑ 1.0 1 1,035

Index Scan using md_character_pkey on md_character cm_2 (cost=0.42..3.76 rows=1 width=34) (actual time=0.002..0.002 rows=1 loops=1,035)

  • Index Cond: (c_2.company_id = id)
48. 33.120 33.120 ↑ 1.0 1 1,035

Index Scan using md_location_pkey on md_location loc_1 (cost=0.43..7.41 rows=1 width=111) (actual time=0.032..0.032 rows=1 loops=1,035)

  • Index Cond: (r.destination_id = id)
49. 2.070 2.070 ↑ 1.0 1 1,035

Index Scan using md_character_pkey on md_character dcm2 (cost=0.42..6.94 rows=1 width=34) (actual time=0.002..0.002 rows=1 loops=1,035)

  • Index Cond: (r.drivercompany_id = id)
50. 48.645 48.645 ↑ 1.0 1 1,035

Index Scan using md_profile_pkey on md_profile p (cost=0.42..6.37 rows=1 width=8) (actual time=0.047..0.047 rows=1 loops=1,035)

  • Index Cond: (r.profile_id = id)