explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ihw9

Settings
# exclusive inclusive rows x rows loops node
1. 1,667.632 10,207.632 ↑ 1.3 11 1

Unique (cost=3.49..14,821.30 rows=14 width=7) (actual time=0.130..10,207.632 rows=11 loops=1)

2. 2,503.726 8,540.000 ↓ 18.7 40,568,176 1

Nested Loop (cost=3.49..9,410.78 rows=2,164,209 width=7) (actual time=0.129..8,540.000 rows=40,568,176 loops=1)

3. 1.006 176.922 ↓ 15.9 34,066 1

Nested Loop (cost=3.21..3,831.71 rows=2,146 width=11) (actual time=0.108..176.922 rows=34,066 loops=1)

4. 62.361 141.850 ↓ 13.2 34,066 1

Nested Loop (cost=3.06..3,412.43 rows=2,579 width=19) (actual time=0.100..141.850 rows=34,066 loops=1)

5. 23.291 79.489 ↓ 25.0 88,577 1

Nested Loop (cost=2.92..2,845.97 rows=3,539 width=15) (actual time=0.093..79.489 rows=88,577 loops=1)

6. 10.467 28.258 ↓ 25.8 27,940 1

Nested Loop (cost=2.63..2,287.72 rows=1,082 width=15) (actual time=0.084..28.258 rows=27,940 loops=1)

  • Join Filter: ((travel_admin_type_regions.type_id = travel_admin_types.id) AND CASE WHEN (array_length(travel_admin_packages.provider_ids, 1) > 0) THEN (mv_hotels.company_id = ANY (travel_admin_packages.provider_ids)) ELSE (travel_admin_packages.id > 0) END)
  • Rows Removed by Join Filter: 72620
7. 1.430 11.087 ↑ 1.1 6,704 1

Nested Loop (cost=0.42..1,541.08 rows=7,072 width=23) (actual time=0.030..11.087 rows=6,704 loops=1)

8. 2.680 2.680 ↑ 1.0 6,977 1

Index Scan using idx_mv_hotels_star_category_id on mv_hotels (cost=0.28..351.69 rows=6,977 width=23) (actual time=0.015..2.680 rows=6,977 loops=1)

  • Filter: active
  • Rows Removed by Filter: 55
9. 6.977 6.977 ↑ 1.0 1 6,977

Index Scan using idx_type_regions_region_id on travel_admin_type_regions (cost=0.14..0.16 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=6,977)

  • Index Cond: (region_id = mv_hotels.region_id)
10. 6.642 6.704 ↓ 5.0 15 6,704

Materialize (cost=2.21..4.09 rows=3 width=44) (actual time=0.000..0.001 rows=15 loops=6,704)

11. 0.014 0.062 ↓ 5.0 15 1

Hash Join (cost=2.21..4.07 rows=3 width=44) (actual time=0.049..0.062 rows=15 loops=1)

  • Hash Cond: (travel_admin_package_type_tours.type_id = travel_admin_types.id)
12. 0.020 0.037 ↓ 2.7 24 1

Hash Join (cost=1.12..2.95 rows=9 width=40) (actual time=0.027..0.037 rows=24 loops=1)

  • Hash Cond: (travel_admin_package_type_tours.package_id = travel_admin_packages.id)
13. 0.008 0.008 ↑ 1.0 64 1

Seq Scan on travel_admin_package_type_tours (cost=0.00..1.64 rows=64 width=8) (actual time=0.004..0.008 rows=64 loops=1)

14. 0.004 0.009 ↓ 2.2 9 1

Hash (cost=1.07..1.07 rows=4 width=36) (actual time=0.009..0.009 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
15. 0.005 0.005 ↓ 2.2 9 1

Seq Scan on travel_admin_packages (cost=0.00..1.07 rows=4 width=36) (actual time=0.003..0.005 rows=9 loops=1)

  • Filter: active
16. 0.005 0.011 ↓ 1.2 5 1

Hash (cost=1.04..1.04 rows=4 width=4) (actual time=0.011..0.011 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
17. 0.006 0.006 ↓ 1.2 5 1

Seq Scan on travel_admin_types (cost=0.00..1.04 rows=4 width=4) (actual time=0.005..0.006 rows=5 loops=1)

18. 27.940 27.940 ↑ 2.3 3 27,940

Index Scan using idx_board_group_board_id on travel_admin_board_groups (cost=0.29..0.45 rows=7 width=8) (actual time=0.001..0.001 rows=3 loops=27,940)

  • Index Cond: (board_id = mv_hotels.board_id)
19. 0.000 0.000 ↓ 0.0 0 88,577

Index Only Scan using travel_admin_food_categories_pkey on travel_admin_food_categories (cost=0.14..0.16 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=88,577)

  • Index Cond: (id = travel_admin_board_groups.food_id)
  • Heap Fetches: 34066
20. 34.066 34.066 ↑ 1.0 1 34,066

Index Only Scan using travel_admin_foods_pkey on travel_admin_foods (cost=0.14..0.16 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=34,066)

  • Index Cond: (id = travel_admin_board_groups.food_id)
  • Heap Fetches: 34066
21. 5,859.352 5,859.352 ↓ 14.2 1,191 34,066

Index Only Scan using idx_company_markets_market_id on travel_admin_company_markets (cost=0.28..1.76 rows=84 width=4) (actual time=0.010..0.172 rows=1,191 loops=34,066)

  • Index Cond: (market_id = mv_hotels.market_id)
  • Heap Fetches: 40568176
Planning time : 3.320 ms
Execution time : 10,207.739 ms