explain.depesz.com

PostgreSQL's explain analyze made readable

Result: djVqN

Settings
# exclusive inclusive rows x rows loops node
1. 1,962.948 23,069.119 ↓ 1,400,056.0 1,400,056 1

Nested Loop (cost=22,221.09..69,648.24 rows=1 width=197) (actual time=3,717.574..23,069.119 rows=1,400,056 loops=1)

  • Buffers: shared hit=19,371,951 read=6,774, local hit=616
2.          

CTE r

3. 96.384 207.286 ↓ 10.9 139,136 1

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

  • Buffers: shared hit=1,310, local hit=616
4. 30.857 30.857 ↑ 1.1 139,136 1

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

  • Buffers: local hit=616
5. 35.485 80.045 ↓ 1.0 139,136 1

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

  • Buffers: shared hit=1,310
6. 33.295 44.560 ↓ 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=11.412..44.56 rows=139,136 loops=1)

  • Heap Blocks: exact=927
  • Buffers: shared hit=1,310
7. 11.265 11.265 ↓ 1.0 139,136 1

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

  • Index Cond: (r_1.hotel = 14,054)
  • Buffers: shared hit=383
8. 1,373.953 18,306.059 ↓ 1,400,056.0 1,400,056 1

Nested Loop (cost=2,751.37..50,178.25 rows=1 width=182) (actual time=3,717.565..18,306.059 rows=1,400,056 loops=1)

  • Buffers: shared hit=15,442,241 read=6,774, local hit=616
9. 1,580.491 14,131.994 ↓ 1,400,056.0 1,400,056 1

Nested Loop (cost=2,750.94..50,177.55 rows=1 width=177) (actual time=3,717.537..14,131.994 rows=1,400,056 loops=1)

  • Buffers: shared hit=11,211,637 read=6,774, local hit=616
10. 1,371.943 9,751.391 ↓ 1,400,056.0 1,400,056 1

Nested Loop (cost=2,750.5..50,176.84 rows=1 width=179) (actual time=3,717.533..9,751.391 rows=1,400,056 loops=1)

  • Buffers: shared hit=7,137,470 read=6,774, local hit=616
11. 1,053.369 5,579.336 ↓ 1,400,056.0 1,400,056 1

Hash Join (cost=2,750.07..50,169.62 rows=1 width=162) (actual time=3,717.503..5,579.336 rows=1,400,056 loops=1)

  • Buffers: shared hit=2,929,229 read=6,774, local hit=616
12. 808.550 808.550 ↑ 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.062..808.55 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 hit=2,144 read=6,774
13. 109.599 3,717.417 ↓ 139,136.0 139,136 1

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

  • Buffers: shared hit=2,927,085, local hit=616
14. 90.470 3,607.818 ↓ 139,136.0 139,136 1

Nested Loop (cost=2,385.71..2,750.05 rows=1 width=158) (actual time=98.883..3,607.818 rows=139,136 loops=1)

  • Buffers: shared hit=2,927,085, local hit=616
15. 176.873 3,099.940 ↓ 139,136.0 139,136 1

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

  • Buffers: shared hit=2,509,677, local hit=616
16. 128.203 2,505.659 ↓ 139,136.0 139,136 1

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

  • Buffers: shared hit=1,952,963, local hit=616
17. 147.807 1,820.912 ↓ 139,136.0 139,136 1

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

  • Buffers: shared hit=1,395,589, local hit=616
18. 166.371 1,394.833 ↓ 139,136.0 139,136 1

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

  • Buffers: shared hit=978,181, local hit=616
19. 136.716 950.190 ↓ 34,784.0 139,136 1

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

  • Buffers: shared hit=560,773, local hit=616
20. 103.092 396.066 ↓ 34,784.0 139,136 1

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

  • Buffers: shared hit=3,333, local hit=616
21. 274.609 274.609 ↓ 10.9 139,136 1

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

  • Buffers: shared hit=1,310, local hit=616
22. 4.388 18.365 ↑ 1.0 14,394 1

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

  • Buffers: shared hit=2,023
23. 13.977 13.977 ↑ 1.0 14,394 1

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

  • Buffers: shared hit=2,023
24. 417.408 417.408 ↑ 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.003 rows=1 loops=139,136)

  • Index Cond: (b.id = r.roomguest)
  • Filter: b.enabled
  • Buffers: shared hit=557,440
25. 278.272 278.272 ↑ 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.002 rows=1 loops=139,136)

  • Index Cond: (c.id = b.room)
  • Filter: c.enabled
  • Buffers: shared hit=417,408
26. 278.272 278.272 ↑ 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.002..0.002 rows=1 loops=139,136)

  • Index Cond: (g.id = b.guest)
  • Heap Fetches: 139,136
  • Buffers: shared hit=417,408
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,374
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,714
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.002..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,408
30. 2,800.112 2,800.112 ↓ 0.0 0 1,400,056

Index Scan using roomratesplanner_roomrate_day on roomratesplanner pl (cost=0.43..7.22 rows=1 width=29) (actual time=0.002..0.002 rows=0 loops=1,400,056)

  • Index Cond: ((pl.roomrate = r.id) AND (pl.day = f.day) AND (pl.day >= '2020-01-01'::date) AND (pl.day <= '2020-01-31'::date))
  • Buffers: shared hit=4,208,241
31. 2,800.112 2,800.112 ↓ 0.0 0 1,400,056

Index Scan using roomratesplanner_index_tips_4 on roomratesplanner pa (cost=0.43..0.7 rows=1 width=10) (actual time=0.002..0.002 rows=0 loops=1,400,056)

  • Index Cond: ((pa.day = f.day) AND (pa.day >= '2020-01-01'::date) AND (pa.day <= '2020-01-31'::date) AND (pa.roomrate = v.id))
  • Buffers: shared hit=4,074,167
32. 2,800.112 2,800.112 ↓ 0.0 0 1,400,056

Index Scan using roomratesplanner_index_tips_4 on roomratesplanner pp (cost=0.43..0.69 rows=1 width=13) (actual time=0.002..0.002 rows=0 loops=1,400,056)

  • Index Cond: ((pp.day = f.day) AND (pp.day >= '2020-01-01'::date) AND (pp.day <= '2020-01-31'::date) AND (pp.roomrate = f.source))
  • Buffers: shared hit=4,230,604
33. 2,800.112 2,800.112 ↓ 0.0 0 1,400,056

Index Scan using roomratesplanner_index_tips_4 on roomratesplanner pr (cost=0.43..0.7 rows=1 width=27) (actual time=0.002..0.002 rows=0 loops=1,400,056)

  • Index Cond: ((pr.day = f.day) AND (pr.day >= '2020-01-01'::date) AND (pr.day <= '2020-01-31'::date) AND (pr.roomrate = a.id))
  • Buffers: shared hit=3,929,710
Planning time : 8.477 ms
Execution time : 23,195.987 ms