explain.depesz.com

PostgreSQL's explain analyze made readable

Result: G7Ql

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 4,632.936 ↓ 0.0 0 1

Nested Loop (cost=24,745.84..25,866.73 rows=23 width=202) (actual time=4,632.936..4,632.936 rows=0 loops=1)

2.          

CTE r

3. 164.510 248.329 ↓ 10.4 139,136 1

Hash Join (cost=17,532.74..22,363.11 rows=13,352 width=16) (actual time=56.763..248.329 rows=139,136 loops=1)

4. 27.480 27.480 ↑ 1.1 139,136 1

Seq Scan on t_roomrates t (cost=0..2,186.8 rows=157,080 width=4) (actual time=0.033..27.48 rows=139,136 loops=1)

5. 26.524 56.339 ↑ 1.0 139,136 1

Hash (cost=15,066.34..15,066.34 rows=141,872 width=16) (actual time=56.339..56.339 rows=139,136 loops=1)

6. 21.353 29.815 ↑ 1.0 139,136 1

Bitmap Heap Scan on roomrates r_1 (cost=2,659.94..15,066.34 rows=141,872 width=16) (actual time=8.557..29.815 rows=139,136 loops=1)

  • Heap Blocks: exact=927
7. 8.462 8.462 ↑ 1.0 139,136 1

Bitmap Index Scan on roomrates_hotel_index (cost=0..2,624.47 rows=141,872 width=0) (actual time=8.462..8.462 rows=139,136 loops=1)

  • Index Cond: (hotel = 14054)
8. 0.000 4,632.935 ↓ 0.0 0 1

Nested Loop (cost=2,382.29..3,358.57 rows=23 width=187) (actual time=4,632.935..4,632.935 rows=0 loops=1)

9. 0.000 4,632.935 ↓ 0.0 0 1

Nested Loop (cost=2,381.86..3,213.73 rows=23 width=182) (actual time=4,632.935..4,632.935 rows=0 loops=1)

10. 0.001 4,632.935 ↓ 0.0 0 1

Nested Loop (cost=2,381.42..3,068.68 rows=23 width=184) (actual time=4,632.935..4,632.935 rows=0 loops=1)

11. 10.148 4,632.934 ↓ 0.0 0 1

Nested Loop (cost=2,380.99..2,901.39 rows=23 width=167) (actual time=4,632.934..4,632.934 rows=0 loops=1)

12. 134.843 3,092.290 ↓ 139,136.0 139,136 1

Nested Loop (cost=2,380.99..2,820.49 rows=1 width=158) (actual time=82.066..3,092.29 rows=139,136 loops=1)

13. 186.782 2,679.175 ↓ 139,136.0 139,136 1

Nested Loop (cost=2,380.7..2,814.39 rows=1 width=143) (actual time=82.057..2,679.175 rows=139,136 loops=1)

14. 123.959 2,214.121 ↓ 139,136.0 139,136 1

Nested Loop (cost=2,380.28..2,807.94 rows=1 width=151) (actual time=82.051..2,214.121 rows=139,136 loops=1)

15. 94.075 1,672.754 ↓ 139,136.0 139,136 1

Nested Loop (cost=2,379.85..2,801.48 rows=1 width=151) (actual time=82.039..1,672.754 rows=139,136 loops=1)

16. 85.019 1,300.407 ↓ 139,136.0 139,136 1

Nested Loop (cost=2,379.56..2,795.37 rows=1 width=151) (actual time=82.03..1,300.407 rows=139,136 loops=1)

17. 73.934 937.116 ↓ 34,784.0 139,136 1

Nested Loop (cost=2,379.27..2,771.16 rows=4 width=139) (actual time=82.01..937.116 rows=139,136 loops=1)

18. 95.054 445.774 ↓ 34,784.0 139,136 1

Hash Join (cost=2,378.85..2,746.07 rows=4 width=131) (actual time=81.994..445.774 rows=139,136 loops=1)

19. 325.543 325.543 ↓ 10.4 139,136 1

CTE Scan on r r (cost=0..267.04 rows=13,352 width=16) (actual time=56.765..325.543 rows=139,136 loops=1)

20. 4.139 25.177 ↑ 1.0 14,394 1

Hash (cost=2,162.94..2,162.94 rows=14,394 width=115) (actual time=25.177..25.177 rows=14,394 loops=1)

21. 21.038 21.038 ↑ 1.0 14,394 1

Seq Scan on rates e (cost=0..2,162.94 rows=14,394 width=115) (actual time=0.015..21.038 rows=14,394 loops=1)

22. 417.408 417.408 ↑ 1.0 1 139,136

Index Scan using roomguests_pkey on roomguests b (cost=0.42..6.26 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=139,136)

  • Index Cond: (id = r.roomguest)
  • Filter: enabled
23. 278.272 278.272 ↑ 1.0 1 139,136

Index Scan using rooms_pkey on rooms c (cost=0.29..6.04 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=139,136)

  • Index Cond: (id = b.room)
  • Filter: enabled
24. 278.272 278.272 ↑ 1.0 1 139,136

Index Only Scan using guests_pkey on guests g (cost=0.29..6.11 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=139,136)

  • Index Cond: (id = b.guest)
  • Heap Fetches: 139136
25. 417.408 417.408 ↑ 1.0 1 139,136

Index Scan using roomrates_unique_key on roomrates a (cost=0.43..6.44 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=139,136)

  • Index Cond: ((rate = COALESCE(e.restrictionsref, e.id)) AND (roomguest = COALESCE(c.restrictionsref, b.id)))
  • Filter: enabled
26. 278.272 278.272 ↑ 1.0 1 139,136

Index Scan using roomrates_unique_key on roomrates v (cost=0.43..6.44 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=139,136)

  • Index Cond: ((rate = COALESCE(e.availref, e.id)) AND (roomguest = COALESCE(c.availref, b.id)))
  • Filter: enabled
27. 278.272 278.272 ↑ 1.0 1 139,136

Index Scan using rates_pkey on rates rr (cost=0.29..6.09 rows=1 width=27) (actual time=0.002..0.002 rows=1 loops=139,136)

  • Index Cond: (id = COALESCE(a.rate, r.rate))
  • Filter: (r.hotel = hotel)
28. 556.544 1,530.496 ↓ 0.0 0 139,136

Append (cost=0..80.88 rows=2 width=36) (actual time=0.011..0.011 rows=0 loops=139,136)

29. 0.000 0.000 ↓ 0.0 0 139,136

Seq Scan on roomratesplannerprice f (cost=0..0 rows=1 width=48) (actual time=0..0 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.hotel = hotel) AND (r.id = roomrate))
30. 973.952 973.952 ↓ 0.0 0 139,136

Index Scan using roomratesplannerprice_9_2020_index_4 on roomratesplannerprice_9_2020 f_1 (cost=0.43..80.88 rows=1 width=25) (actual time=0.007..0.007 rows=0 loops=139,136)

  • Index Cond: (roomrate = r.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.hotel = hotel))
31. 0.000 0.000 ↓ 0.0 0 0

Index Scan using roomratesplanner_roomrate_day on roomratesplanner pl (cost=0.43..7.26 rows=1 width=29) (never executed)

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

Index Scan using roomratesplanner_roomrate_day on roomratesplanner pa (cost=0.43..6.3 rows=1 width=10) (never executed)

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

Index Scan using roomratesplanner_roomrate_day on roomratesplanner pp (cost=0.43..6.29 rows=1 width=13) (never executed)

  • Index Cond: ((roomrate = f.source) 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 pr (cost=0.43..6.3 rows=1 width=27) (never executed)

  • Index Cond: ((roomrate = a.id) AND (day = f.day) AND (day >= '2020-09-01'::date) AND (day <= '2020-09-30'::date))
Planning time : 72.898 ms
Execution time : 4,633.871 ms