explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ulBP

Settings
# exclusive inclusive rows x rows loops node
1. 46.495 9,176.611 ↓ 503.0 503 1

Unique (cost=1,096.44..1,096.45 rows=1 width=17) (actual time=9,035.857..9,176.611 rows=503 loops=1)

2. 372.647 9,130.116 ↓ 550,620.0 550,620 1

Sort (cost=1,096.44..1,096.45 rows=1 width=17) (actual time=9,035.856..9,130.116 rows=550,620 loops=1)

  • Sort Key: travel_admin_hotels.group_hotel_id
  • Sort Method: external merge Disk: 14024kB
3. 290.097 8,757.469 ↓ 550,620.0 550,620 1

Nested Loop Left Join (cost=553.75..1,096.43 rows=1 width=17) (actual time=99.607..8,757.469 rows=550,620 loops=1)

  • Join Filter: (travel_transport_stations.id = ANY (prepared_transfer.station_ids))
4. 121.177 7,916.752 ↓ 550,620.0 550,620 1

Nested Loop (cost=553.61..1,096.23 rows=1 width=53) (actual time=99.591..7,916.752 rows=550,620 loops=1)

5. 27.477 1,101.195 ↓ 9,660.0 28,980 1

Nested Loop (cost=542.90..1,060.70 rows=3 width=57) (actual time=99.008..1,101.195 rows=28,980 loops=1)

6. 29.343 1,044.738 ↓ 9,660.0 28,980 1

Nested Loop (cost=542.76..1,060.22 rows=3 width=61) (actual time=98.985..1,044.738 rows=28,980 loops=1)

7. 29.458 957.435 ↓ 9,660.0 28,980 1

Nested Loop (cost=542.48..1,059.33 rows=3 width=65) (actual time=98.961..957.435 rows=28,980 loops=1)

8. 10.866 898.997 ↓ 9,660.0 28,980 1

Merge Join (cost=542.35..1,058.47 rows=3 width=56) (actual time=98.936..898.997 rows=28,980 loops=1)

  • Merge Cond: (travel_admin_foods.food_category_id = travel_admin_food_categories.id)
9. 332.576 888.078 ↓ 4,830.0 28,980 1

Nested Loop (cost=540.83..1,572.82 rows=6 width=60) (actual time=98.889..888.078 rows=28,980 loops=1)

  • Join Filter: (travel_admin_board_groups.food_id = travel_admin_foods.id)
  • Rows Removed by Join Filter: 3622500
10. 0.346 0.346 ↑ 1.0 126 1

Index Scan using idx_foods_food_cat_id on travel_admin_foods (cost=0.14..6.42 rows=126 width=8) (actual time=0.027..0.346 rows=126 loops=1)

11. 479.745 555.156 ↓ 4,830.0 28,980 126

Materialize (cost=540.69..1,555.07 rows=6 width=60) (actual time=0.296..4.406 rows=28,980 loops=126)

12. 9.164 75.411 ↓ 4,830.0 28,980 1

Nested Loop (cost=540.69..1,555.04 rows=6 width=60) (actual time=36.927..75.411 rows=28,980 loops=1)

  • Join Filter: (travel_admin_regions.id = prepared_transport.region_id)
  • Rows Removed by Join Filter: 28980
13. 0.151 0.151 ↓ 1.8 36 1

Index Scan using idx_session_prepared_modules on travel_book_prepared_modules prepared_transport (cost=0.42..81.03 rows=20 width=4) (actual time=0.036..0.151 rows=36 loops=1)

  • Index Cond: (session = '2512121950'::bigint)
  • Filter: ((module_id)::text = 'transport'::text)
  • Rows Removed by Filter: 108
14. 4.633 66.096 ↓ 115.0 1,610 36

Materialize (cost=540.27..1,469.84 rows=14 width=68) (actual time=0.759..1.836 rows=1,610 loops=36)

15. 1.251 61.463 ↓ 115.0 1,610 1

Hash Join (cost=540.27..1,469.77 rows=14 width=68) (actual time=27.329..61.463 rows=1,610 loops=1)

  • Hash Cond: (travel_admin_resorts.id = prepared_transfer.depart_id)
  • Join Filter: ((prepared_transfer.company_id = travel_admin_hotels.company_id) OR (prepared_transfer.company_id IS NULL))
  • Rows Removed by Join Filter: 4349
16. 1.060 60.052 ↓ 7.9 1,650 1

Nested Loop (cost=458.85..1,379.44 rows=209 width=44) (actual time=27.128..60.052 rows=1,650 loops=1)

17. 0.253 56.534 ↓ 7.9 1,229 1

Merge Left Join (cost=458.56..1,325.99 rows=156 width=52) (actual time=27.065..56.534 rows=1,229 loops=1)

  • Merge Cond: (travel_admin_regions.id = travel_admin_prepared_commisions.region_id)
18. 0.349 56.257 ↓ 7.9 1,229 1

Merge Join (cost=455.16..1,322.16 rows=156 width=52) (actual time=27.038..56.257 rows=1,229 loops=1)

  • Merge Cond: (travel_admin_resorts.region_id = travel_admin_regions.id)
19. 18.743 55.861 ↓ 3.0 1,229 1

Nested Loop (cost=453.28..2,704.31 rows=406 width=44) (actual time=26.987..55.861 rows=1,229 loops=1)

  • Join Filter: (travel_admin_hotels.resort_id = travel_admin_resorts.id)
  • Rows Removed by Join Filter: 158541
20. 0.068 0.068 ↑ 1.0 130 1

Index Scan using idx_resorts_region_id on travel_admin_resorts (cost=0.14..5.39 rows=130 width=8) (actual time=0.020..0.068 rows=130 loops=1)

21. 11.016 37.050 ↓ 3.0 1,229 130

Materialize (cost=453.14..1,908.23 rows=406 width=36) (actual time=0.142..0.285 rows=1,229 loops=130)

22. 0.293 26.034 ↓ 3.0 1,229 1

Merge Join (cost=453.14..1,906.20 rows=406 width=36) (actual time=18.433..26.034 rows=1,229 loops=1)

  • Merge Cond: (travel_admin_stars.star_category_id = travel_admin_star_categories.id)
23. 5.088 25.702 ↓ 1.7 1,229 1

Nested Loop (cost=451.94..3,005.46 rows=716 width=40) (actual time=18.395..25.702 rows=1,229 loops=1)

  • Join Filter: (travel_admin_hotels.star_id = travel_admin_stars.id)
  • Rows Removed by Join Filter: 35641
24. 0.034 0.034 ↑ 1.0 30 1

Index Scan using idx_stars_star_cat_id on travel_admin_stars (cost=0.14..3.89 rows=30 width=8) (actual time=0.020..0.034 rows=30 loops=1)

25. 3.947 20.580 ↓ 1.7 1,229 30

Materialize (cost=451.80..2,680.71 rows=717 width=40) (actual time=0.158..0.686 rows=1,229 loops=30)

26. 0.496 16.633 ↓ 1.7 1,229 1

Hash Join (cost=451.80..2,677.13 rows=717 width=40) (actual time=4.744..16.633 rows=1,229 loops=1)

  • Hash Cond: (travel_admin_hotels.market_id = travel_admin_markets.id)
27. 0.507 16.122 ↓ 1.7 1,229 1

Hash Join (cost=450.76..2,670.22 rows=717 width=44) (actual time=4.694..16.122 rows=1,229 loops=1)

  • Hash Cond: (travel_admin_hotels.company_id = travel_admin_companies.id)
28. 0.536 15.451 ↓ 1.7 1,229 1

Hash Join (cost=439.65..2,657.19 rows=718 width=44) (actual time=4.502..15.451 rows=1,229 loops=1)

  • Hash Cond: (travel_admin_hotels.group_hotel_id = travel_admin_group_hotels.id)
29. 0.621 13.431 ↓ 1.7 1,229 1

Hash Join (cost=154.48..2,370.13 rows=720 width=44) (actual time=2.991..13.431 rows=1,229 loops=1)

  • Hash Cond: (travel_admin_boards.hotel_id = travel_admin_hotels.id)
30. 0.605 10.969 ↓ 1.7 1,229 1

Hash Join (cost=53.74..2,267.50 rows=722 width=20) (actual time=1.116..10.969 rows=1,229 loops=1)

  • Hash Cond: (travel_admin_board_groups.board_id = travel_admin_boards.id)
31. 0.984 9.375 ↓ 1.7 1,229 1

Nested Loop (cost=1.00..2,212.86 rows=722 width=20) (actual time=0.096..9.375 rows=1,229 loops=1)

32. 1.280 5.933 ↓ 1.7 1,229 1

Nested Loop (cost=0.72..1,993.58 rows=722 width=12) (actual time=0.069..5.933 rows=1,229 loops=1)

33. 0.966 0.966 ↓ 1.7 1,229 1

Index Scan using idx_book_hotel_prices_session on travel_book_hotel_prices (cost=0.42..812.76 rows=722 width=8) (actual time=0.039..0.966 rows=1,229 loops=1)

  • Index Cond: (session = '2512121950'::bigint)
34. 3.687 3.687 ↑ 1.0 1 1,229

Index Scan using travel_admin_board_group_placements_pkey on travel_admin_board_group_placements (cost=0.29..1.64 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=1,229)

  • Index Cond: (id = travel_book_hotel_prices.bgp_id)
35. 2.458 2.458 ↑ 1.0 1 1,229

Index Scan using travel_admin_board_groups_pkey on travel_admin_board_groups (cost=0.29..0.30 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=1,229)

  • Index Cond: (id = travel_admin_board_group_placements.board_group_id)
36. 0.541 0.989 ↑ 1.0 1,855 1

Hash (cost=29.55..29.55 rows=1,855 width=8) (actual time=0.988..0.989 rows=1,855 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 89kB
37. 0.448 0.448 ↑ 1.0 1,855 1

Seq Scan on travel_admin_boards (cost=0.00..29.55 rows=1,855 width=8) (actual time=0.013..0.448 rows=1,855 loops=1)

38. 0.723 1.841 ↑ 1.0 1,850 1

Hash (cost=77.58..77.58 rows=1,853 width=28) (actual time=1.840..1.841 rows=1,850 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 125kB
39. 1.118 1.118 ↑ 1.0 1,850 1

Seq Scan on travel_admin_hotels (cost=0.00..77.58 rows=1,853 width=28) (actual time=0.011..1.118 rows=1,850 loops=1)

  • Filter: active
  • Rows Removed by Filter: 5
40. 0.511 1.484 ↑ 1.0 1,963 1

Hash (cost=260.63..260.63 rows=1,963 width=4) (actual time=1.484..1.484 rows=1,963 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 86kB
41. 0.973 0.973 ↑ 1.0 1,963 1

Seq Scan on travel_admin_group_hotels (cost=0.00..260.63 rows=1,963 width=4) (actual time=0.010..0.973 rows=1,963 loops=1)

42. 0.080 0.164 ↑ 1.0 227 1

Hash (cost=8.27..8.27 rows=227 width=4) (actual time=0.164..0.164 rows=227 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
43. 0.084 0.084 ↑ 1.0 227 1

Seq Scan on travel_admin_companies (cost=0.00..8.27 rows=227 width=4) (actual time=0.009..0.084 rows=227 loops=1)

44. 0.005 0.015 ↑ 1.0 2 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
45. 0.010 0.010 ↑ 1.0 2 1

Seq Scan on travel_admin_markets (cost=0.00..1.02 rows=2 width=4) (actual time=0.009..0.010 rows=2 loops=1)

46. 0.031 0.039 ↑ 1.3 6 1

Sort (cost=1.20..1.22 rows=8 width=4) (actual time=0.035..0.039 rows=6 loops=1)

  • Sort Key: travel_admin_star_categories.id
  • Sort Method: quicksort Memory: 25kB
47. 0.008 0.008 ↑ 1.0 8 1

Seq Scan on travel_admin_star_categories (cost=0.00..1.08 rows=8 width=4) (actual time=0.007..0.008 rows=8 loops=1)

48. 0.030 0.047 ↑ 8.7 3 1

Sort (cost=1.87..1.94 rows=26 width=8) (actual time=0.046..0.047 rows=3 loops=1)

  • Sort Key: travel_admin_regions.id
  • Sort Method: quicksort Memory: 26kB
49. 0.017 0.017 ↑ 1.0 26 1

Seq Scan on travel_admin_regions (cost=0.00..1.26 rows=26 width=8) (actual time=0.013..0.017 rows=26 loops=1)

50. 0.014 0.024 ↓ 0.0 0 1

Sort (cost=3.41..3.41 rows=2 width=4) (actual time=0.024..0.024 rows=0 loops=1)

  • Sort Key: travel_admin_prepared_commisions.region_id
  • Sort Method: quicksort Memory: 25kB
51. 0.003 0.010 ↓ 0.0 0 1

Bitmap Heap Scan on travel_admin_prepared_commisions (cost=1.26..3.40 rows=2 width=4) (actual time=0.010..0.010 rows=0 loops=1)

  • Recheck Cond: (session = '2512121950'::bigint)
52. 0.007 0.007 ↓ 0.0 0 1

Bitmap Index Scan on idx_session_prepared_commisions (cost=0.00..1.26 rows=2 width=0) (actual time=0.007..0.007 rows=0 loops=1)

  • Index Cond: (session = '2512121950'::bigint)
53. 2.458 2.458 ↑ 1.0 1 1,229

Index Only Scan using idx_hotel_foods_hotel_id on travel_admin_hotel_foods (cost=0.28..0.33 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1,229)

  • Index Cond: (hotel_id = travel_admin_hotels.id)
  • Heap Fetches: 1650
54. 0.050 0.160 ↓ 2.5 76 1

Hash (cost=81.03..81.03 rows=31 width=40) (actual time=0.160..0.160 rows=76 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
55. 0.110 0.110 ↓ 2.5 76 1

Index Scan using idx_session_prepared_modules on travel_book_prepared_modules prepared_transfer (cost=0.42..81.03 rows=31 width=40) (actual time=0.038..0.110 rows=76 loops=1)

  • Index Cond: (session = '2512121950'::bigint)
  • Filter: ((module_id)::text = 'transfer'::text)
  • Rows Removed by Filter: 68
56. 0.041 0.053 ↑ 1.3 13 1

Sort (cost=1.52..1.56 rows=17 width=4) (actual time=0.042..0.053 rows=13 loops=1)

  • Sort Key: travel_admin_food_categories.id
  • Sort Method: quicksort Memory: 25kB
57. 0.012 0.012 ↑ 1.0 17 1

Seq Scan on travel_admin_food_categories (cost=0.00..1.17 rows=17 width=4) (actual time=0.009..0.012 rows=17 loops=1)

58. 28.980 28.980 ↑ 1.0 1 28,980

Index Scan using travel_admin_states_pkey on travel_admin_states (cost=0.14..0.28 rows=1 width=17) (actual time=0.001..0.001 rows=1 loops=28,980)

  • Index Cond: (id = travel_admin_regions.state_id)
59. 57.960 57.960 ↑ 1.0 1 28,980

Index Only Scan using travel_admin_type_rooms_pkey on travel_admin_type_rooms (cost=0.28..0.30 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=28,980)

  • Index Cond: (id = travel_admin_board_groups.type_room_id)
  • Heap Fetches: 28980
60. 28.980 28.980 ↑ 1.0 1 28,980

Index Only Scan using travel_admin_placements_pkey on travel_admin_placements (cost=0.14..0.16 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=28,980)

  • Index Cond: (id = travel_admin_board_group_placements.placement_id)
  • Heap Fetches: 28980
61. 434.700 6,694.380 ↓ 19.0 19 28,980

Bitmap Heap Scan on travel_book_prepared_modules prepared_insurance (cost=10.71..11.83 rows=1 width=4) (actual time=0.220..0.231 rows=19 loops=28,980)

  • Recheck Cond: ((session = '2512121950'::bigint) AND (from_date = travel_book_hotel_prices.from_date))
  • Filter: ((nights = 7) AND (region_id = 420))
  • Rows Removed by Filter: 25
  • Heap Blocks: exact=173880
62. 57.960 6,259.680 ↓ 0.0 0 28,980

BitmapAnd (cost=10.71..10.71 rows=1 width=0) (actual time=0.216..0.216 rows=0 loops=28,980)

63. 231.840 231.840 ↓ 2.0 144 28,980

Bitmap Index Scan on idx_session_prepared_modules (cost=0.00..2.05 rows=71 width=0) (actual time=0.008..0.008 rows=144 loops=28,980)

  • Index Cond: (session = '2512121950'::bigint)
64. 5,969.880 5,969.880 ↓ 3.0 2,733 28,980

Bitmap Index Scan on idx_book_prepared_modules_from_date (cost=0.00..8.41 rows=909 width=0) (actual time=0.206..0.206 rows=2,733 loops=28,980)

  • Index Cond: (from_date = travel_book_hotel_prices.from_date)
65. 550.620 550.620 ↑ 1.0 1 550,620

Index Scan using idx_transport_stations_resort_id on travel_transport_stations (cost=0.14..0.18 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=550,620)

  • Index Cond: (resort_id = travel_admin_resorts.id)
Planning time : 27.765 ms
Execution time : 9,181.907 ms