# Result: c5bZ

Settings
# exclusive inclusive rows x rows loops node
1. 2,319.419 18,530.386 ↓ 73,687.2 1,400,056 1

(cost=22,221.05..69,645.93 rows=19 width=212) (actual time=4,174.855..18,530.386 rows=1,400,056 loops=1)

2.

CTE r

3. 108.978 303.321 ↓ 10.9 139,136 1

(cost=16,565.84..19,469.29 rows=12,760 width=16) (actual time=137.979..303.321 rows=139,136 loops=1)

4. 56.734 56.734 ↑ 1.1 139,136 1

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

5. 57.104 137.609 ↓ 1.0 139,136 1

(cost=14,871.02..14,871.02 rows=135,585 width=16) (actual time=137.609..137.609 rows=139,136 loops=1)

6. 64.896 80.505 ↓ 1.0 139,136 1

Bitmap Heap Scan on roomrates r_1 (cost=2,543.21..14,871.02 rows=135,585 width=16) (actual time=15.783..80.505 rows=139,136 loops=1)

• Heap Blocks: exact=927
7. 15.609 15.609 ↓ 1.0 139,136 1

Bitmap Index Scan on roomrates_hotel_index (cost=0..2,509.31 rows=135,585 width=0) (actual time=15.609..15.609 rows=139,136 loops=1)

• Index Cond: (r_1.hotel = 14,054)
8. 1,439.794 14,810.911 ↓ 1,400,056.0 1,400,056 1

(cost=2,751.34..50,172.68 rows=1 width=195) (actual time=4,174.842..14,810.911 rows=1,400,056 loops=1)

9. 1,475.562 11,971.061 ↓ 1,400,056.0 1,400,056 1

(cost=2,750.92..50,171.66 rows=1 width=180) (actual time=4,174.838..11,971.061 rows=1,400,056 loops=1)

10. 1,743.204 9,095.443 ↓ 1,400,056.0 1,400,056 1

(cost=2,750.49..50,170.64 rows=1 width=160) (actual time=4,174.732..9,095.443 rows=1,400,056 loops=1)

11. 988.725 5,952.183 ↓ 1,400,056.0 1,400,056 1

(cost=2,750.07..50,169.62 rows=1 width=162) (actual time=4,174.72..5,952.183 rows=1,400,056 loops=1)

12. 788.978 788.978 ↑ 1.0 1,400,056 1

Seq Scan on q_f f (cost=0..36,919.12 rows=1,400,056 width=20) (actual time=0.213..788.978 rows=1,400,056 loops=1)

• Filter: ((f.day >= '2020-01-01'::date) AND (f.day <= '2020-01-31'::date) AND (f.day >= '2020-01-01'::date) AND (f.day <= '2020-01-31'::date))
13. 121.633 4,174.480 ↓ 139,136.0 139,136 1

(cost=2,750.05..2,750.05 rows=1 width=158) (actual time=4,174.48..4,174.48 rows=139,136 loops=1)

14. 137.113 4,052.847 ↓ 139,136.0 139,136 1

(cost=2,385.71..2,750.05 rows=1 width=158) (actual time=185.931..4,052.847 rows=139,136 loops=1)

15. 218.003 3,498.326 ↓ 139,136.0 139,136 1

(cost=2,385.43..2,749.09 rows=1 width=143) (actual time=185.897..3,498.326 rows=139,136 loops=1)

16. 201.193 2,862.915 ↓ 139,136.0 139,136 1

(cost=2,385..2,744.97 rows=1 width=151) (actual time=185.857..2,862.915 rows=139,136 loops=1)

17. 55.025 2,105.178 ↓ 139,136.0 139,136 1

(cost=2,384.57..2,740.85 rows=1 width=151) (actual time=185.792..2,105.178 rows=139,136 loops=1)

18. 65.495 1,632.745 ↓ 139,136.0 139,136 1

(cost=2,384.28..2,740.42 rows=1 width=151) (actual time=185.726..1,632.745 rows=139,136 loops=1)

19. 55.556 1,149.842 ↓ 34,784.0 139,136 1

(cost=2,383.99..2,739.03 rows=4 width=139) (actual time=185.679..1,149.842 rows=139,136 loops=1)

20. 113.285 537.742 ↓ 34,784.0 139,136 1

(cost=2,383.57..2,734.51 rows=4 width=131) (actual time=185.618..537.742 rows=139,136 loops=1)

21. 376.866 376.866 ↓ 10.9 139,136 1

CTE Scan on r r (cost=0..255.2 rows=12,760 width=16) (actual time=137.984..376.866 rows=139,136 loops=1)

22. 9.127 47.591 ↑ 1.0 14,394 1

(cost=2,167.23..2,167.23 rows=14,423 width=115) (actual time=47.591..47.591 rows=14,394 loops=1)

23. 38.464 38.464 ↑ 1.0 14,394 1

Seq Scan on rates e (cost=0..2,167.23 rows=14,423 width=115) (actual time=0.025..38.464 rows=14,394 loops=1)

24. 556.544 556.544 ↑ 1.0 1 139,136

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

• Index Cond: (b.id = r.roomguest)
• Filter: b.enabled
25. 417.408 417.408 ↑ 1.0 1 139,136

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

• Index Cond: (c.id = b.room)
• Filter: c.enabled
26. 417.408 417.408 ↑ 1.0 1 139,136

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

• Index Cond: (g.id = b.guest)
• Heap Fetches: 139,136
27. 556.544 556.544 ↑ 1.0 1 139,136

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

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

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

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

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

• Index Cond: (rr.id = COALESCE(a.rate, r.rate))
• Filter: (r.hotel = rr.hotel)
30. 1,400.056 1,400.056 ↓ 0.0 0 1,400,056

Index Scan using troomratesplanner_1 on troomratesplanner pa (cost=0.42..0.83 rows=19 width=10) (actual time=0.001..0.001 rows=0 loops=1,400,056)

• Index Cond: ((pa.roomrate = v.id) AND (pa.day = f.day))
• Buffers: local hit=4,045,952 read=1 written=1
31. 1,400.056 1,400.056 ↓ 0.0 0 1,400,056

Index Scan using troomratesplanner_1 on troomratesplanner pp (cost=0.42..0.83 rows=19 width=28) (actual time=0.001..0.001 rows=0 loops=1,400,056)

• Index Cond: ((pp.roomrate = f.source) AND (pp.day = f.day))
• Buffers: local hit=4,200,164 read=4 written=4
32. 1,400.056 1,400.056 ↓ 0.0 0 1,400,056

Index Scan using troomratesplanner_1 on troomratesplanner pr (cost=0.42..0.83 rows=19 width=27) (actual time=0.001..0.001 rows=0 loops=1,400,056)

• Index Cond: ((pr.roomrate = a.id) AND (pr.day = f.day))
• Buffers: local hit=3,893,532
33. 1,400.056 1,400.056 ↓ 0.0 0 1,400,056

Index Scan using troomratesplanner_1 on troomratesplanner pl (cost=0.42..3.77 rows=19 width=29) (actual time=0.001..0.001 rows=0 loops=1,400,056)

• Index Cond: ((pl.roomrate = r.id) AND (pl.day = f.day))
• Buffers: local hit=4,200,161 read=7 written=7
Planning time Execution time : 12.779 ms : 18,645.734 ms