explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Lood

Settings
# exclusive inclusive rows x rows loops node
1. 7,265.972 23,294.901 ↓ 362,111.0 362,111 1

Merge Right Join (cost=303,182.00..326,351.35 rows=1 width=155) (actual time=15,508.636..23,294.901 rows=362,111 loops=1)

  • Merge Cond: ((f.source = x.source) AND (f.formula = x.formula) AND (pp.price = x.price))
2. 70.422 7,602.397 ↑ 75.7 11,127 1

Unique (cost=290,292.74..298,717.86 rows=842,512 width=15) (actual time=7,302.188..7,602.397 rows=11,127 loops=1)

3. 869.811 7,531.975 ↑ 1.8 469,278 1

Sort (cost=290,292.74..292,399.02 rows=842,512 width=15) (actual time=7,302.188..7,531.975 rows=469,278 loops=1)

  • Sort Key: f.source, f.formula, pp.price
  • Sort Method: external merge Disk: 10096kB
4. 145.944 6,662.164 ↑ 1.8 469,278 1

Hash Join (cost=175,078.64..192,972.29 rows=842,512 width=15) (actual time=6,513.745..6,662.164 rows=469,278 loops=1)

  • Hash Cond: (t.value = f.roomrate)
5. 2.619 2.619 ↑ 1.0 28,999 1

Seq Scan on t_roomrates_1 t (cost=0.00..418.99 rows=28,999 width=4) (actual time=0.008..2.619 rows=28,999 loops=1)

6. 119.426 6,513.601 ↑ 1.0 469,278 1

Hash (cost=166,406.67..166,406.67 rows=472,317 width=19) (actual time=6,513.601..6,513.601 rows=469,278 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 3380kB
7. 442.834 6,394.175 ↑ 1.0 469,278 1

Merge Left Join (cost=73,654.60..166,406.67 rows=472,317 width=19) (actual time=4,293.360..6,394.175 rows=469,278 loops=1)

  • Merge Cond: ((f.day = pp.day) AND (f.source = pp.roomrate))
8. 192.298 192.298 ↑ 1.0 469,278 1

Index Scan using roomratesplannerprice_new_hotel_index_5 on roomratesplannerprice f (cost=0.42..37,902.20 rows=472,317 width=18) (actual time=0.137..192.298 rows=469,278 loops=1)

  • Index Cond: ((day >= '2019-11-27'::date) AND (day <= '2019-12-27'::date))
  • Filter: (hotel = 14573)
  • Rows Removed by Filter: 35154
9. 5,759.043 5,759.043 ↑ 3.6 2,225,645 1

Index Scan using roomratesplanner_index_tips_4 on roomratesplanner pp (cost=0.43..423,498.04 rows=8,054,300 width=13) (actual time=0.021..5,759.043 rows=2,225,645 loops=1)

10. 2,194.070 8,426.532 ↓ 362,111.0 362,111 1

Sort (cost=12,889.26..12,889.26 rows=1 width=159) (actual time=8,206.309..8,426.532 rows=362,111 loops=1)

  • Sort Key: x.source, x.formula, x.price
  • Sort Method: external sort Disk: 45456kB
11. 72.886 6,232.462 ↓ 362,111.0 362,111 1

Subquery Scan on x (cost=12,825.41..12,889.25 rows=1 width=159) (actual time=35.760..6,232.462 rows=362,111 loops=1)

12. 405.441 6,159.576 ↓ 362,111.0 362,111 1

Nested Loop Left Join (cost=12,825.41..12,889.24 rows=1 width=199) (actual time=35.758..6,159.576 rows=362,111 loops=1)

13.          

CTE r

14. 22.941 43.683 ↓ 45.5 28,999 1

Hash Join (cost=10,022.07..10,556.19 rows=638 width=16) (actual time=13.866..43.683 rows=28,999 loops=1)

  • Hash Cond: (t_1.value = r_1.id)
15. 6.928 6.928 ↑ 1.0 28,999 1

Seq Scan on t_roomrates_1 t_1 (cost=0.00..418.99 rows=28,999 width=4) (actual time=0.009..6.928 rows=28,999 loops=1)

16. 5.014 13.814 ↑ 1.0 28,999 1

Hash (cost=9,646.64..9,646.64 rows=30,035 width=16) (actual time=13.814..13.814 rows=28,999 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1616kB
17. 6.594 8.800 ↑ 1.0 28,999 1

Bitmap Heap Scan on roomrates r_1 (cost=565.20..9,646.64 rows=30,035 width=16) (actual time=2.289..8.800 rows=28,999 loops=1)

  • Recheck Cond: (hotel = 14573)
  • Heap Blocks: exact=693
18. 2.206 2.206 ↑ 1.0 28,999 1

Bitmap Index Scan on roomrates_hotel_index (cost=0.00..557.69 rows=30,035 width=0) (actual time=2.206..2.206 rows=28,999 loops=1)

  • Index Cond: (hotel = 14573)
19. 214.659 5,392.024 ↓ 362,111.0 362,111 1

Nested Loop Left Join (cost=2,268.78..2,331.98 rows=1 width=184) (actual time=35.751..5,392.024 rows=362,111 loops=1)

20. 383.634 4,453.143 ↓ 362,111.0 362,111 1

Nested Loop Left Join (cost=2,268.35..2,330.92 rows=1 width=179) (actual time=35.745..4,453.143 rows=362,111 loops=1)

21. 329.242 3,707.398 ↓ 362,111.0 362,111 1

Nested Loop Left Join (cost=2,267.92..2,329.86 rows=1 width=181) (actual time=35.742..3,707.398 rows=362,111 loops=1)

22. 123.126 2,653.934 ↓ 362,111.0 362,111 1

Nested Loop (cost=2,267.49..2,321.47 rows=1 width=164) (actual time=35.731..2,653.934 rows=362,111 loops=1)

  • Join Filter: (e.hotel = f_1.hotel)
23. 325.210 2,090.788 ↓ 22,001.0 22,001 1

Nested Loop (cost=2,267.06..2,298.12 rows=1 width=158) (actual time=35.709..2,090.788 rows=22,001 loops=1)

  • Join Filter: ((rr.id = COALESCE(a.rate, r.rate)) AND (e.hotel = rr.hotel))
  • Rows Removed by Join Filter: 2640120
24. 12.054 533.522 ↓ 22,001.0 22,001 1

Nested Loop Left Join (cost=2,266.78..2,297.12 rows=1 width=143) (actual time=35.687..533.522 rows=22,001 loops=1)

  • Join Filter: ((v.id <> r.id) AND (v.hotel = r.hotel))
  • Rows Removed by Join Filter: 66
25. 30.499 455.465 ↓ 22,001.0 22,001 1

Nested Loop Left Join (cost=2,266.35..2,293.33 rows=1 width=151) (actual time=35.679..455.465 rows=22,001 loops=1)

  • Join Filter: ((a.id <> r.id) AND (a.hotel = r.hotel))
  • Rows Removed by Join Filter: 305
26. 24.825 358.963 ↓ 22,001.0 22,001 1

Nested Loop (cost=2,265.92..2,289.54 rows=1 width=151) (actual time=35.649..358.963 rows=22,001 loops=1)

27. 22.976 290.136 ↓ 22,001.0 22,001 1

Nested Loop (cost=2,265.63..2,289.10 rows=1 width=151) (actual time=35.642..290.136 rows=22,001 loops=1)

  • Join Filter: (e.hotel = c.hotel)
28. 21.891 213.572 ↓ 26,794.0 26,794 1

Nested Loop (cost=2,265.35..2,288.76 rows=1 width=139) (actual time=35.633..213.572 rows=26,794 loops=1)

29. 22.895 104.684 ↓ 28,999.0 28,999 1

Hash Join (cost=2,264.93..2,282.48 rows=1 width=131) (actual time=35.622..104.684 rows=28,999 loops=1)

  • Hash Cond: ((r.hotel = e.hotel) AND (r.rate = e.id))
30. 60.064 60.064 ↓ 45.5 28,999 1

CTE Scan on r (cost=0.00..12.76 rows=638 width=16) (actual time=13.868..60.064 rows=28,999 loops=1)

31. 3.862 21.725 ↑ 1.0 13,717 1

Hash (cost=2,059.17..2,059.17 rows=13,717 width=115) (actual time=21.725..21.725 rows=13,717 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 980kB
32. 17.863 17.863 ↑ 1.0 13,717 1

Seq Scan on rates e (cost=0.00..2,059.17 rows=13,717 width=115) (actual time=0.014..17.863 rows=13,717 loops=1)

33. 86.997 86.997 ↑ 1.0 1 28,999

Index Scan using roomguests_pkey on roomguests b (cost=0.42..6.27 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=28,999)

  • Index Cond: (id = r.roomguest)
  • Filter: enabled
  • Rows Removed by Filter: 0
34. 53.588 53.588 ↑ 1.0 1 26,794

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=26,794)

  • Index Cond: (id = b.room)
  • Filter: enabled
  • Rows Removed by Filter: 0
35. 44.002 44.002 ↑ 1.0 1 22,001

Index Only Scan using guests_pkey on guests g (cost=0.29..0.42 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=22,001)

  • Index Cond: (id = b.guest)
  • Heap Fetches: 22001
36. 66.003 66.003 ↑ 1.0 1 22,001

Index Scan using roomrates_unique_key on roomrates a (cost=0.43..3.78 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=22,001)

  • Index Cond: ((rate = COALESCE(e.restrictionsref, e.id)) AND (roomguest = COALESCE(c.restrictionsref, b.id)))
  • Filter: enabled
  • Rows Removed by Filter: 0
37. 66.003 66.003 ↑ 1.0 1 22,001

Index Scan using roomrates_unique_key on roomrates v (cost=0.43..3.78 rows=1 width=16) (actual time=0.002..0.003 rows=1 loops=22,001)

  • Index Cond: ((rate = COALESCE(e.availref, e.id)) AND (roomguest = COALESCE(c.availref, b.id)))
  • Filter: enabled
  • Rows Removed by Filter: 0
38. 1,232.056 1,232.056 ↓ 24.2 121 22,001

Index Scan using rates_hotel_index on rates rr (cost=0.29..0.92 rows=5 width=27) (actual time=0.002..0.056 rows=121 loops=22,001)

  • Index Cond: (hotel = c.hotel)
39. 440.020 440.020 ↑ 1.0 16 22,001

Index Scan using roomratesplannerprice_new_hotel_index_2 on roomratesplannerprice f_1 (cost=0.42..23.15 rows=16 width=22) (actual time=0.003..0.020 rows=16 loops=22,001)

  • Index Cond: ((hotel = r.hotel) AND (roomrate = r.id))
  • Filter: ((day >= '2019-11-27'::date) AND (day <= '2019-12-27'::date))
  • Rows Removed by Filter: 2
40. 724.222 724.222 ↓ 0.0 0 362,111

Index Scan using roomratesplanner_index_tips_4 on roomratesplanner pl (cost=0.43..8.38 rows=1 width=29) (actual time=0.002..0.002 rows=0 loops=362,111)

  • Index Cond: ((day = f_1.day) AND (roomrate = r.id))
41. 362.111 362.111 ↑ 1.0 1 362,111

Index Scan using roomratesplanner_index_tips_4 on roomratesplanner pa (cost=0.43..1.05 rows=1 width=10) (actual time=0.001..0.001 rows=1 loops=362,111)

  • Index Cond: ((day = f_1.day) AND (roomrate = v.id))
42. 724.222 724.222 ↓ 0.0 0 362,111

Index Scan using roomratesplanner_index_tips_4 on roomratesplanner pp_1 (cost=0.43..1.05 rows=1 width=13) (actual time=0.002..0.002 rows=0 loops=362,111)

  • Index Cond: ((day = f_1.day) AND (roomrate = f_1.source))
43. 362.111 362.111 ↓ 0.0 0 362,111

Index Scan using roomratesplanner_index_tips_4 on roomratesplanner pr (cost=0.43..1.05 rows=1 width=27) (actual time=0.001..0.001 rows=0 loops=362,111)

  • Index Cond: ((day = f_1.day) AND (roomrate = a.id))
Planning time : 8.270 ms
Execution time : 23,322.476 ms