explain.depesz.com

PostgreSQL's explain analyze made readable

Result: c5bZ

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

Nested Loop (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)

  • Buffers: shared hit=2,923,414 read=12,589, local hit=16,339,809 read=628 written=628
2.          

CTE r

3. 108.978 303.321 ↓ 10.9 139,136 1

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

  • Buffers: shared read=1,310, local read=616 written=616
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)

  • Buffers: local read=616 written=616
5. 57.104 137.609 ↓ 1.0 139,136 1

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

  • Buffers: shared read=1,310
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
  • Buffers: shared read=1,310
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)
  • Buffers: shared read=383
8. 1,439.794 14,810.911 ↓ 1,400,056.0 1,400,056 1

Nested Loop (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)

  • Buffers: shared hit=2,923,414 read=12,589, local hit=12,139,648 read=621 written=621
9. 1,475.562 11,971.061 ↓ 1,400,056.0 1,400,056 1

Nested Loop (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)

  • Buffers: shared hit=2,923,414 read=12,589, local hit=8,246,116 read=621 written=621
10. 1,743.204 9,095.443 ↓ 1,400,056.0 1,400,056 1

Nested Loop (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)

  • Buffers: shared hit=2,923,414 read=12,589, local hit=4,045,952 read=617 written=617
11. 988.725 5,952.183 ↓ 1,400,056.0 1,400,056 1

Hash Join (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)

  • Buffers: shared hit=2,923,414 read=12,589, local read=616 written=616
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))
  • Buffers: shared read=8,918
13. 121.633 4,174.480 ↓ 139,136.0 139,136 1

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

  • Buffers: shared hit=2,923,414 read=3,671, local read=616 written=616
14. 137.113 4,052.847 ↓ 139,136.0 139,136 1

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

  • Buffers: shared hit=2,923,414 read=3,671, local read=616 written=616
15. 218.003 3,498.326 ↓ 139,136.0 139,136 1

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

  • Buffers: shared hit=2,506,009 read=3,668, local read=616 written=616
16. 201.193 2,862.915 ↓ 139,136.0 139,136 1

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

  • Buffers: shared hit=1,949,299 read=3,664, local read=616 written=616
17. 55.025 2,105.178 ↓ 139,136.0 139,136 1

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

  • Buffers: shared hit=1,392,138 read=3,451, local read=616 written=616
18. 65.495 1,632.745 ↓ 139,136.0 139,136 1

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

  • Buffers: shared hit=974,799 read=3,382, local read=616 written=616
19. 55.556 1,149.842 ↓ 34,784.0 139,136 1

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

  • Buffers: shared hit=557,404 read=3,369, local read=616 written=616
20. 113.285 537.742 ↓ 34,784.0 139,136 1

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

  • Buffers: shared hit=2 read=3,331, local read=616 written=616
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)

  • Buffers: shared read=1,310, local read=616 written=616
22. 9.127 47.591 ↑ 1.0 14,394 1

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

  • Buffers: shared hit=2 read=2,021
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)

  • Buffers: shared hit=2 read=2,021
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
  • Buffers: shared hit=557,402 read=38
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
  • Buffers: shared hit=417,395 read=13
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
  • Buffers: shared hit=417,339 read=69
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
  • Buffers: shared hit=557,161 read=213
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
  • Buffers: shared hit=556,710 read=4
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)
  • Buffers: shared hit=417,405 read=3
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 : 12.779 ms
Execution time : 18,645.734 ms