explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WdAJ

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0 0 1

Hash Join (cost=0..0 rows=0 width=0) (actual rows=0 loops=1)

2.          

CTE r

3. 0.000 0.000 ↓ 0.0 139,136 1

Hash Join (cost=0..0 rows=0 width=0) (actual rows=139,136 loops=1)

4. 0.000 0.000 ↓ 0.0 139,136 1

Seq Scan on t_roomrates t (cost=0..0 rows=0 width=0) (actual rows=139,136 loops=1)

5. 0.000 0.000 ↓ 0.0 139,136 1

Hash (cost=0..0 rows=0 width=0) (actual rows=139,136 loops=1)

6. 0.000 0.000 ↓ 0.0 139,136 1

Bitmap Heap Scan on roomrates r (cost=0..0 rows=0 width=0) (actual rows=139,136 loops=1)

  • Heap Blocks: exact=927
7. 0.000 0.000 ↓ 0.0 139,136 1

Bitmap Index Scan on roomrates_hotel_index (cost=0..0 rows=0 width=0) (actual rows=139,136 loops=1)

  • Index Cond: (hotel = 14054)
8.          

CTE x

9. 0.000 0.000 ↓ 0.0 0 1

Nested Loop (cost=0..0 rows=0 width=0) (actual rows=0 loops=1)

10. 0.000 0.000 ↓ 0.0 0 1

Nested Loop (cost=0..0 rows=0 width=0) (actual rows=0 loops=1)

11. 0.000 0.000 ↓ 0.0 0 1

Nested Loop (cost=0..0 rows=0 width=0) (actual rows=0 loops=1)

12. 0.000 0.000 ↓ 0.0 0 1

Nested Loop (cost=0..0 rows=0 width=0) (actual rows=0 loops=1)

13. 0.000 0.000 ↓ 0.0 0 1

Nested Loop (cost=0..0 rows=0 width=0) (actual rows=0 loops=1)

14. 0.000 0.000 ↓ 0.0 139,136 1

Nested Loop (cost=0..0 rows=0 width=0) (actual rows=139,136 loops=1)

15. 0.000 0.000 ↓ 0.0 139,136 1

Nested Loop (cost=0..0 rows=0 width=0) (actual rows=139,136 loops=1)

16. 0.000 0.000 ↓ 0.0 139,136 1

Nested Loop (cost=0..0 rows=0 width=0) (actual rows=139,136 loops=1)

17. 0.000 0.000 ↓ 0.0 139,136 1

Nested Loop (cost=0..0 rows=0 width=0) (actual rows=139,136 loops=1)

18. 0.000 0.000 ↓ 0.0 139,136 1

Nested Loop (cost=0..0 rows=0 width=0) (actual rows=139,136 loops=1)

19. 0.000 0.000 ↓ 0.0 139,136 1

Nested Loop (cost=0..0 rows=0 width=0) (actual rows=139,136 loops=1)

20. 0.000 0.000 ↓ 0.0 139,136 1

Hash Join (cost=0..0 rows=0 width=0) (actual rows=139,136 loops=1)

21. 0.000 0.000 ↓ 0.0 139,136 1

CTE Scan on r r_1 (cost=0..0 rows=0 width=0) (actual rows=139,136 loops=1)

22. 0.000 0.000 ↓ 0.0 14,394 1

Hash (cost=0..0 rows=0 width=0) (actual rows=14,394 loops=1)

23. 0.000 0.000 ↓ 0.0 14,394 1

Seq Scan on rates e (cost=0..0 rows=0 width=0) (actual rows=14,394 loops=1)

24. 0.000 0.000 ↓ 0.0 1 139,136

Index Scan using roomguests_pkey on roomguests b (cost=0..0 rows=0 width=0) (actual rows=1 loops=139,136)

  • Index Cond: (id = r_1.roomguest)
  • Filter: enabled
25. 0.000 0.000 ↓ 0.0 1 139,136

Index Scan using rooms_pkey on rooms c (cost=0..0 rows=0 width=0) (actual rows=1 loops=139,136)

  • Index Cond: (id = b.room)
  • Filter: enabled
26. 0.000 0.000 ↓ 0.0 1 139,136

Index Only Scan using guests_pkey on guests g (cost=0..0 rows=0 width=0) (actual rows=1 loops=139,136)

  • Index Cond: (id = b.guest)
  • Heap Fetches: 139136
27. 0.000 0.000 ↓ 0.0 1 139,136

Index Scan using roomrates_unique_key on roomrates a (cost=0..0 rows=0 width=0) (actual rows=1 loops=139,136)

  • Index Cond: ((rate = COALESCE(e.restrictionsref, e.id)) AND (roomguest = COALESCE(c.restrictionsref, b.id)))
  • Filter: enabled
28. 0.000 0.000 ↓ 0.0 1 139,136

Index Scan using roomrates_unique_key on roomrates v (cost=0..0 rows=0 width=0) (actual rows=1 loops=139,136)

  • Index Cond: ((rate = COALESCE(e.availref, e.id)) AND (roomguest = COALESCE(c.availref, b.id)))
  • Filter: enabled
29. 0.000 0.000 ↓ 0.0 1 139,136

Index Scan using rates_pkey on rates rr (cost=0..0 rows=0 width=0) (actual rows=1 loops=139,136)

  • Index Cond: (id = COALESCE(a.rate, r_1.rate))
  • Filter: (r_1.hotel = hotel)
30. 0.000 0.000 ↓ 0.0 0 139,136

Append (cost=0..0 rows=0 width=0) (actual rows=0 loops=139,136)

31. 0.000 0.000 ↓ 0.0 0 139,136

Seq Scan on roomratesplannerprice f (cost=0..0 rows=0 width=0) (actual rows=0 loops=139,136)

  • Filter: ((day >= '2020-09-01'::date) AND (day <= '2020-09-30'::date) AND (day >= '2020-09-01'::date) AND (day <= '2020-09-30'::date) AND (r_1.hotel = hotel) AND (r_1.id = roomrate))
32. 0.000 0.000 ↓ 0.0 0 139,136

Index Scan using roomratesplannerprice_9_2020_index_4 on roomratesplannerprice_9_2020 f_1 (cost=0..0 rows=0 width=0) (actual rows=0 loops=139,136)

  • Index Cond: (roomrate = r_1.id)
  • Filter: ((day >= '2020-09-01'::date) AND (day <= '2020-09-30'::date) AND (day >= '2020-09-01'::date) AND (day <= '2020-09-30'::date) AND (r_1.hotel = hotel))
33. 0.000 0.000 ↓ 0.0 0 0

Index Scan using roomratesplanner_roomrate_day on roomratesplanner pl (cost=0..0 rows=0 width=0) (never executed)

  • Index Cond: ((roomrate = r_1.id) AND (day = f.day) AND (day >= '2020-09-01'::date) AND (day <= '2020-09-30'::date))
34. 0.000 0.000 ↓ 0.0 0 0

Index Scan using roomratesplanner_roomrate_day on roomratesplanner pa (cost=0..0 rows=0 width=0) (never executed)

  • Index Cond: ((roomrate = v.id) AND (day = f.day) AND (day >= '2020-09-01'::date) AND (day <= '2020-09-30'::date))
35. 0.000 0.000 ↓ 0.0 0 0

Index Scan using roomratesplanner_roomrate_day on roomratesplanner pp (cost=0..0 rows=0 width=0) (never executed)

  • Index Cond: ((roomrate = f.source) AND (day = f.day) AND (day >= '2020-09-01'::date) AND (day <= '2020-09-30'::date))
36. 0.000 0.000 ↓ 0.0 0 0

Index Scan using roomratesplanner_roomrate_day on roomratesplanner pr (cost=0..0 rows=0 width=0) (never executed)

  • Index Cond: ((roomrate = a.id) AND (day = f.day) AND (day >= '2020-09-01'::date) AND (day <= '2020-09-30'::date))
37.          

CTE z

38. 0.000 0.000 ↓ 0.0 0 0

Unique (cost=0..0 rows=0 width=0) (never executed)

39. 0.000 0.000 ↓ 0.0 0 0

Sort (cost=0..0 rows=0 width=0) (never executed)

  • Sort Key: f_2.source, f_2.formula, pp_1.price
40. 0.000 0.000 ↓ 0.0 0 0

Merge Join (cost=0..0 rows=0 width=0) (never executed)

41. 0.000 0.000 ↓ 0.0 0 0

Sort (cost=0..0 rows=0 width=0) (never executed)

  • Sort Key: t_1.value
42. 0.000 0.000 ↓ 0.0 0 0

Seq Scan on t_roomrates t_1 (cost=0..0 rows=0 width=0) (never executed)

43. 0.000 0.000 ↓ 0.0 0 0

Materialize (cost=0..0 rows=0 width=0) (never executed)

44. 0.000 0.000 ↓ 0.0 0 0

Sort (cost=0..0 rows=0 width=0) (never executed)

  • Sort Key: f_2.roomrate
45. 0.000 0.000 ↓ 0.0 0 0

Merge Join (cost=0..0 rows=0 width=0) (never executed)

46. 0.000 0.000 ↓ 0.0 0 0

Sort (cost=0..0 rows=0 width=0) (never executed)

  • Sort Key: f_2.source, f_2.day
47. 0.000 0.000 ↓ 0.0 0 0

Append (cost=0..0 rows=0 width=0) (never executed)

48. 0.000 0.000 ↓ 0.0 0 0

Seq Scan on roomratesplannerprice f_2 (cost=0..0 rows=0 width=0) (never executed)

  • Filter: ((day >= '2020-09-01'::date) AND (day <= '2020-09-30'::date) AND (hotel = 14054))
49. 0.000 0.000 ↓ 0.0 0 0

Bitmap Heap Scan on roomratesplannerprice_9_2020 f_3 (cost=0..0 rows=0 width=0) (never executed)

  • Filter: ((day >= '2020-09-01'::date) AND (day <= '2020-09-30'::date))
50. 0.000 0.000 ↓ 0.0 0 0

Bitmap Index Scan on roomratesplannerprice_9_2020_index_1 (cost=0..0 rows=0 width=0) (never executed)

  • Index Cond: (hotel = 14054)
51. 0.000 0.000 ↓ 0.0 0 0

Materialize (cost=0..0 rows=0 width=0) (never executed)

52. 0.000 0.000 ↓ 0.0 0 0

Sort (cost=0..0 rows=0 width=0) (never executed)

  • Sort Key: pp_1.roomrate, pp_1.day
53. 0.000 0.000 ↓ 0.0 0 0

Bitmap Heap Scan on roomratesplanner pp_1 (cost=0..0 rows=0 width=0) (never executed)

54. 0.000 0.000 ↓ 0.0 0 0

Bitmap Index Scan on roomratesplanner_index_tips_4 (cost=0..0 rows=0 width=0) (never executed)

  • Index Cond: ((day >= '2020-09-01'::date) AND (day <= '2020-09-30'::date))
55.          

CTE y

56. 0.000 0.000 ↓ 0.0 0 0

CTE Scan on z z (cost=0..0 rows=0 width=0) (never executed)

57. 0.000 0.000 ↓ 0.0 0 0

CTE Scan on y y (cost=0..0 rows=0 width=0) (never executed)

58. 0.000 0.000 ↓ 0.0 0 1

Hash (cost=0..0 rows=0 width=0) (actual rows=0 loops=1)

59. 0.000 0.000 ↓ 0.0 0 1

CTE Scan on x x (cost=0..0 rows=0 width=0) (actual rows=0 loops=1)

Planning time : 25.015 ms
Execution time : 42,244.687 ms