explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rIAb

Settings
# exclusive inclusive rows x rows loops node
1. 7,009.283 20,660.586 ↓ 12,051.0 361,530 1

Hash Right Join (cost=193,033.83..193,356.60 rows=30 width=159) (actual time=13,650.674..20,660.586 rows=361,530 loops=1)

  • Hash Cond: ((f.source = x.source) AND (f.formula = x.formula) AND (pp.price = x.price))
2. 54.228 5,537.216 ↑ 5.4 1,864 1

HashAggregate (cost=187,796.58..187,897.44 rows=10,086 width=19) (actual time=5,536.242..5,537.216 rows=1,864 loops=1)

  • Group Key: f.source, f.formula, pp.price
3. 131.712 5,482.988 ↓ 12.6 126,739 1

Merge Join (cost=180,331.61..187,720.93 rows=10,086 width=19) (actual time=5,195.976..5,482.988 rows=126,739 loops=1)

  • Merge Cond: ((f.source = pp.roomrate) AND (f.day = pp.day))
4. 759.001 3,461.702 ↓ 1.9 468,510 1

Sort (cost=29,258.44..29,863.71 rows=242,108 width=18) (actual time=3,377.692..3,461.702 rows=468,510 loops=1)

  • Sort Key: f.source, f.day
  • Sort Method: external merge Disk: 12048kB
5. 97.131 2,702.701 ↓ 1.9 468,510 1

Nested Loop (cost=540.19..7,607.58 rows=242,108 width=18) (actual time=8.378..2,702.701 rows=468,510 loops=1)

6. 22.530 24.659 ↓ 145.0 28,999 1

HashAggregate (cost=540.19..542.19 rows=200 width=4) (actual time=8.335..24.659 rows=28,999 loops=1)

  • Group Key: t_roomrates_1.value
7. 2.129 2.129 ↑ 1.1 28,999 1

Seq Scan on t_roomrates_1 (cost=0.00..457.95 rows=32,895 width=4) (actual time=0.008..2.129 rows=28,999 loops=1)

8. 231.992 2,580.911 ↓ 5.3 16 28,999

Append (cost=0.00..35.30 rows=3 width=22) (actual time=0.016..0.089 rows=16 loops=28,999)

9. 0.000 0.000 ↓ 0.0 0 28,999

Seq Scan on roomratesplannerprice f (cost=0.00..0.00 rows=1 width=23) (actual time=0.000..0.000 rows=0 loops=28,999)

  • Filter: ((day >= '2019-11-27'::date) AND (day <= '2019-12-27'::date) AND (hotel = 14573) AND (t_roomrates_1.value = roomrate))
10. 173.994 318.989 ↓ 2.0 2 28,999

Bitmap Heap Scan on roomratesplannerprice_11_2019 f_1 (cost=0.58..4.64 rows=1 width=22) (actual time=0.008..0.011 rows=2 loops=28,999)

  • Recheck Cond: (roomrate = t_roomrates_1.value)
  • Filter: ((day >= '2019-11-27'::date) AND (day <= '2019-12-27'::date) AND (hotel = 14573))
  • Heap Blocks: exact=46851
11. 144.995 144.995 ↑ 1.5 2 28,999

Bitmap Index Scan on roomratesplannerprice_11_2019_index_4 (cost=0.00..0.58 rows=3 width=0) (actual time=0.005..0.005 rows=2 loops=28,999)

  • Index Cond: (roomrate = t_roomrates_1.value)
12. 1,826.937 2,029.930 ↓ 15.0 15 28,999

Bitmap Heap Scan on roomratesplannerprice_12_2019 f_2 (cost=2.21..30.65 rows=1 width=22) (actual time=0.011..0.070 rows=15 loops=28,999)

  • Recheck Cond: (roomrate = t_roomrates_1.value)
  • Filter: ((day >= '2019-11-27'::date) AND (day <= '2019-12-27'::date) AND (hotel = 14573))
  • Rows Removed by Filter: 2
  • Heap Blocks: exact=476235
13. 202.993 202.993 ↑ 1.8 16 28,999

Bitmap Index Scan on roomratesplannerprice_12_2019_index_4 (cost=0.00..2.21 rows=28 width=0) (actual time=0.007..0.007 rows=16 loops=28,999)

  • Index Cond: (roomrate = t_roomrates_1.value)
14. 1,071.907 1,889.574 ↓ 1.1 835,004 1

Sort (cost=151,073.17..152,897.38 rows=729,686 width=13) (actual time=1,769.295..1,889.574 rows=835,004 loops=1)

  • Sort Key: pp.roomrate, pp.day
  • Sort Method: external sort Disk: 18448kB
15. 580.963 817.667 ↑ 1.0 716,329 1

Bitmap Heap Scan on roomratesplanner pp (cost=15,483.71..80,013.00 rows=729,686 width=13) (actual time=246.814..817.667 rows=716,329 loops=1)

  • Recheck Cond: ((day >= '2019-11-27'::date) AND (day <= '2019-12-27'::date))
  • Heap Blocks: exact=47462
16. 236.704 236.704 ↑ 1.0 716,329 1

Bitmap Index Scan on roomratesplanner_index_tips_5 (cost=0.00..15,301.29 rows=729,686 width=0) (actual time=236.704..236.704 rows=716,329 loops=1)

  • Index Cond: ((day >= '2019-11-27'::date) AND (day <= '2019-12-27'::date))
17. 401.322 8,114.087 ↓ 12,051.0 361,530 1

Hash (cost=5,236.73..5,236.73 rows=30 width=163) (actual time=8,114.087..8,114.087 rows=361,530 loops=1)

  • Buckets: 262144 (originally 1024) Batches: 2 (originally 1) Memory Usage: 30721kB
18. 97.403 7,712.765 ↓ 12,051.0 361,530 1

Subquery Scan on x (cost=4,474.48..5,236.73 rows=30 width=163) (actual time=27.273..7,712.765 rows=361,530 loops=1)

19. 520.063 7,615.362 ↓ 12,051.0 361,530 1

Nested Loop Left Join (cost=4,474.48..5,236.43 rows=30 width=203) (actual time=27.272..7,615.362 rows=361,530 loops=1)

20.          

CTE r

21. 29.713 200.923 ↓ 1.9 28,999 1

Nested Loop (cost=540.62..2,205.69 rows=15,018 width=16) (actual time=9.761..200.923 rows=28,999 loops=1)

22. 23.505 26.215 ↓ 145.0 28,999 1

HashAggregate (cost=540.19..542.19 rows=200 width=4) (actual time=9.695..26.215 rows=28,999 loops=1)

  • Group Key: t_roomrates_1_1.value
23. 2.710 2.710 ↑ 1.1 28,999 1

Seq Scan on t_roomrates_1 t_roomrates_1_1 (cost=0.00..457.95 rows=32,895 width=4) (actual time=0.013..2.710 rows=28,999 loops=1)

24. 144.995 144.995 ↑ 1.0 1 28,999

Index Scan using roomrates_pkey on roomrates r_1 (cost=0.43..8.31 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=28,999)

  • Index Cond: (id = t_roomrates_1_1.value)
  • Filter: (hotel = 14573)
25. 302.465 6,733.769 ↓ 12,051.0 361,530 1

Nested Loop Left Join (cost=2,268.36..3,008.80 rows=30 width=188) (actual time=27.264..6,733.769 rows=361,530 loops=1)

26. 132.155 5,708.244 ↓ 12,051.0 361,530 1

Nested Loop Left Join (cost=2,267.93..2,994.26 rows=30 width=183) (actual time=27.260..5,708.244 rows=361,530 loops=1)

27. 336.220 4,853.029 ↓ 12,051.0 361,530 1

Nested Loop Left Join (cost=2,267.49..2,972.32 rows=30 width=185) (actual time=27.257..4,853.029 rows=361,530 loops=1)

28. 123.870 3,432.219 ↓ 12,051.0 361,530 1

Nested Loop (cost=2,267.06..2,763.48 rows=30 width=168) (actual time=27.248..3,432.219 rows=361,530 loops=1)

  • Join Filter: (e.hotel = f_3.hotel)
29. 38.353 866.238 ↓ 22,001.0 22,001 1

Nested Loop (cost=2,267.06..2,693.38 rows=1 width=158) (actual time=26.496..866.238 rows=22,001 loops=1)

  • Join Filter: (e.hotel = rr.hotel)
30. 16.817 761.882 ↓ 22,001.0 22,001 1

Nested Loop Left Join (cost=2,266.78..2,692.54 rows=1 width=143) (actual time=26.473..761.882 rows=22,001 loops=1)

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

Nested Loop Left Join (cost=2,266.35..2,688.74 rows=1 width=151) (actual time=26.465..679.062 rows=22,001 loops=1)

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

Nested Loop (cost=2,265.92..2,684.95 rows=1 width=151) (actual time=26.430..563.998 rows=22,001 loops=1)

33. 11.599 486.054 ↓ 22,001.0 22,001 1

Nested Loop (cost=2,265.63..2,684.52 rows=1 width=151) (actual time=26.405..486.054 rows=22,001 loops=1)

  • Join Filter: (e.hotel = c.hotel)
34. 32.296 394.073 ↓ 5,358.8 26,794 1

Nested Loop (cost=2,265.35..2,682.78 rows=5 width=139) (actual time=26.261..394.073 rows=26,794 loops=1)

35. 34.497 274.780 ↓ 5,799.8 28,999 1

Hash Join (cost=2,264.93..2,677.97 rows=5 width=131) (actual time=26.171..274.780 rows=28,999 loops=1)

  • Hash Cond: ((r.hotel = e.hotel) AND (r.rate = e.id))
36. 223.925 223.925 ↓ 1.9 28,999 1

CTE Scan on r (cost=0.00..300.36 rows=15,018 width=16) (actual time=9.763..223.925 rows=28,999 loops=1)

37. 3.407 16.358 ↑ 1.0 13,717 1

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

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

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

39. 86.997 86.997 ↑ 1.0 1 28,999

Index Scan using roomguests_pkey on roomguests b (cost=0.42..0.95 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
40. 80.382 80.382 ↑ 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.003 rows=1 loops=26,794)

  • Index Cond: (id = b.room)
  • Filter: enabled
  • Rows Removed by Filter: 0
41. 66.003 66.003 ↑ 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.003 rows=1 loops=22,001)

  • Index Cond: (id = b.guest)
  • Heap Fetches: 22001
42. 88.004 88.004 ↑ 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.004..0.004 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
43. 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
44. 66.003 66.003 ↑ 1.0 1 22,001

Index Scan using rates_pkey on rates rr (cost=0.29..0.83 rows=1 width=27) (actual time=0.003..0.003 rows=1 loops=22,001)

  • Index Cond: (id = COALESCE(a.rate, r.rate))
  • Filter: (r.hotel = hotel)
45. 264.012 2,442.111 ↓ 5.3 16 22,001

Append (cost=0.00..70.07 rows=3 width=26) (actual time=0.019..0.111 rows=16 loops=22,001)

46. 0.000 0.000 ↓ 0.0 0 22,001

Seq Scan on roomratesplannerprice f_3 (cost=0.00..0.00 rows=1 width=27) (actual time=0.000..0.000 rows=0 loops=22,001)

  • Filter: ((day >= '2019-11-27'::date) AND (day <= '2019-12-27'::date) AND (r.hotel = hotel) AND (r.id = roomrate))
47. 154.007 286.013 ↓ 2.0 2 22,001

Bitmap Heap Scan on roomratesplannerprice_11_2019 f_4 (cost=1.25..9.26 rows=1 width=26) (actual time=0.010..0.013 rows=2 loops=22,001)

  • Recheck Cond: (roomrate = r.id)
  • Filter: ((day >= '2019-11-27'::date) AND (day <= '2019-12-27'::date) AND (r.hotel = hotel))
  • Heap Blocks: exact=36153
48. 132.006 132.006 ↑ 1.5 2 22,001

Bitmap Index Scan on roomratesplannerprice_11_2019_index_4 (cost=0.00..1.25 rows=3 width=0) (actual time=0.006..0.006 rows=2 loops=22,001)

  • Index Cond: (roomrate = r.id)
49. 1,716.078 1,892.086 ↓ 15.0 15 22,001

Bitmap Heap Scan on roomratesplannerprice_12_2019 f_5 (cost=4.60..60.81 rows=1 width=26) (actual time=0.013..0.086 rows=15 loops=22,001)

  • Recheck Cond: (roomrate = r.id)
  • Filter: ((day >= '2019-11-27'::date) AND (day <= '2019-12-27'::date) AND (r.hotel = hotel))
  • Rows Removed by Filter: 2
  • Heap Blocks: exact=367269
50. 176.008 176.008 ↑ 1.6 17 22,001

Bitmap Index Scan on roomratesplannerprice_12_2019_index_4 (cost=0.00..4.60 rows=28 width=0) (actual time=0.008..0.008 rows=17 loops=22,001)

  • Index Cond: (roomrate = r.id)
51. 1,084.590 1,084.590 ↓ 0.0 0 361,530

Index Scan using roomratesplanner_index_tips_4 on roomratesplanner pl (cost=0.43..6.95 rows=1 width=29) (actual time=0.003..0.003 rows=0 loops=361,530)

  • Index Cond: ((day = f_3.day) AND (day >= '2019-11-27'::date) AND (day <= '2019-12-27'::date) AND (roomrate = r.id))
52. 723.060 723.060 ↑ 1.0 1 361,530

Index Scan using roomratesplanner_index_tips_4 on roomratesplanner pa (cost=0.43..0.72 rows=1 width=10) (actual time=0.002..0.002 rows=1 loops=361,530)

  • Index Cond: ((day = f_3.day) AND (day >= '2019-11-27'::date) AND (day <= '2019-12-27'::date) AND (roomrate = v.id))
53. 723.060 723.060 ↓ 0.0 0 361,530

Index Scan using roomratesplanner_index_tips_4 on roomratesplanner pp_1 (cost=0.43..0.47 rows=1 width=13) (actual time=0.002..0.002 rows=0 loops=361,530)

  • Index Cond: ((day = f_3.day) AND (day >= '2019-11-27'::date) AND (day <= '2019-12-27'::date) AND (roomrate = f_3.source))
54. 361.530 361.530 ↓ 0.0 0 361,530

Index Scan using roomratesplanner_index_tips_4 on roomratesplanner pr (cost=0.43..0.72 rows=1 width=27) (actual time=0.001..0.001 rows=0 loops=361,530)

  • Index Cond: ((day = f_3.day) AND (day >= '2019-11-27'::date) AND (day <= '2019-12-27'::date) AND (roomrate = a.id))
Planning time : 7.268 ms
Execution time : 20,687.479 ms