explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mSfF

Settings
# exclusive inclusive rows x rows loops node
1. 1,200.861 37,148.417 ↓ 1,497,990.0 1,497,990 1

Hash Join (cost=2,279,224.32..2,408,440.23 rows=1 width=165) (actual time=34,089.784..37,148.417 rows=1,497,990 loops=1)

2.          

CTE x

3. 1,705.075 18,750.738 ↓ 2,413,140.0 2,413,140 1

Nested Loop (cost=8,804.62..11,867.66 rows=1 width=213) (actual time=909.849..18,750.738 rows=2,413,140 loops=1)

4. 1,210.432 14,632.523 ↓ 2,413,140.0 2,413,140 1

Nested Loop (cost=8,804.34..11,867.35 rows=1 width=198) (actual time=909.84..14,632.523 rows=2,413,140 loops=1)

5. 1,319.590 11,008.951 ↓ 2,413,140.0 2,413,140 1

Nested Loop (cost=8,804.06..11,867.03 rows=1 width=178) (actual time=909.836..11,008.951 rows=2,413,140 loops=1)

6. 1,179.371 7,276.221 ↓ 2,413,140.0 2,413,140 1

Nested Loop (cost=8,803.77..11,866.72 rows=1 width=180) (actual time=909.832..7,276.221 rows=2,413,140 loops=1)

7. 630.047 3,683.710 ↓ 2,413,140.0 2,413,140 1

Nested Loop (cost=8,803.49..11,866.4 rows=1 width=163) (actual time=909.825..3,683.71 rows=2,413,140 loops=1)

8. 91.391 1,801.439 ↓ 139,136.0 139,136 1

Nested Loop (cost=8,803.06..11,864.37 rows=1 width=158) (actual time=909.8..1,801.439 rows=139,136 loops=1)

9. 91.649 1,431.776 ↓ 139,136.0 139,136 1

Nested Loop (cost=8,802.78..11,863.93 rows=1 width=143) (actual time=909.783..1,431.776 rows=139,136 loops=1)

10. 136.284 1,061.855 ↓ 139,136.0 139,136 1

Hash Join (cost=8,802.48..11,863.5 rows=1 width=143) (actual time=909.749..1,061.855 rows=139,136 loops=1)

11. 16.131 16.131 ↓ 1.9 129,047 1

Seq Scan on r v (cost=0..2,278.36 rows=69,568 width=16) (actual time=0.014..16.131 rows=129,047 loops=1)

  • Filter: enabled
12. 38.631 909.440 ↓ 139,136.0 139,136 1

Hash (cost=8,802.47..8,802.47 rows=1 width=151) (actual time=909.44..909.44 rows=139,136 loops=1)

13. 102.295 870.809 ↓ 139,136.0 139,136 1

Hash Join (cost=5,741.45..8,802.47 rows=1 width=151) (actual time=750.177..870.809 rows=139,136 loops=1)

14. 18.404 18.404 ↓ 1.9 129,047 1

Seq Scan on r a (cost=0..2,278.36 rows=69,568 width=16) (actual time=0.05..18.404 rows=129,047 loops=1)

  • Filter: enabled
15. 61.115 750.110 ↓ 139,136.0 139,136 1

Hash (cost=5,741.44..5,741.44 rows=1 width=151) (actual time=750.11..750.11 rows=139,136 loops=1)

16. 136.664 688.995 ↓ 139,136.0 139,136 1

Nested Loop (cost=2,380.73..5,741.44 rows=1 width=151) (actual time=15.155..688.995 rows=139,136 loops=1)

17. 31.892 413.195 ↓ 3,091.9 139,136 1

Nested Loop (cost=2,380.45..5,725.82 rows=45 width=139) (actual time=15.147..413.195 rows=139,136 loops=1)

18. 64.372 103.031 ↓ 3,024.7 139,136 1

Hash Join (cost=2,380.03..5,702.36 rows=46 width=131) (actual time=15.131..103.031 rows=139,136 loops=1)

19. 23.589 23.589 ↑ 1.0 139,136 1

Seq Scan on r r (cost=0..2,278.36 rows=139,136 width=16) (actual time=0.032..23.589 rows=139,136 loops=1)

20. 2.986 15.070 ↑ 1.0 14,394 1

Hash (cost=2,164.01..2,164.01 rows=14,401 width=115) (actual time=15.07..15.07 rows=14,394 loops=1)

21. 12.084 12.084 ↑ 1.0 14,394 1

Seq Scan on rates e (cost=0..2,164.01 rows=14,401 width=115) (actual time=0.007..12.084 rows=14,394 loops=1)

22. 278.272 278.272 ↑ 1.0 1 139,136

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

  • Index Cond: (id = r.roomguest)
  • Filter: enabled
23. 139.136 139.136 ↑ 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.001..0.001 rows=1 loops=139,136)

  • Index Cond: (id = b.room)
  • Filter: enabled
24. 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: (id = b.guest)
  • Heap Fetches: 139136
25. 278.272 278.272 ↑ 1.0 1 139,136

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

  • Index Cond: (id = COALESCE(a.rate, r.rate))
  • Filter: (r.hotel = hotel)
26. 1,252.224 1,252.224 ↑ 1.8 17 139,136

Index Scan using q_f1_1 on q_f f (cost=0.43..1.66 rows=30 width=21) (actual time=0.003..0.009 rows=17 loops=139,136)

  • Index Cond: (roomrate = r.id)
  • Filter: (r.hotel = hotel)
27. 2,413.140 2,413.140 ↓ 0.0 0 2,413,140

Index Scan using t_rp_idx on t_rp pl (cost=0.28..0.3 rows=1 width=29) (actual time=0.001..0.001 rows=0 loops=2,413,140)

  • Index Cond: ((roomrate = r.id) AND (day = f.day))
28. 2,413.140 2,413.140 ↑ 1.0 1 2,413,140

Index Scan using t_rp_idx on t_rp pa (cost=0.28..0.31 rows=1 width=10) (actual time=0.001..0.001 rows=1 loops=2,413,140)

  • Index Cond: ((roomrate = v.id) AND (day = f.day))
29. 2,413.140 2,413.140 ↑ 1.0 1 2,413,140

Index Scan using t_rp_idx on t_rp pp (cost=0.28..0.3 rows=1 width=28) (actual time=0.001..0.001 rows=1 loops=2,413,140)

  • Index Cond: ((roomrate = f.source) AND (day = f.day))
30. 2,413.140 2,413.140 ↓ 0.0 0 2,413,140

Index Scan using t_rp_idx on t_rp pr (cost=0.28..0.31 rows=1 width=27) (actual time=0.001..0.001 rows=0 loops=2,413,140)

  • Index Cond: ((roomrate = a.id) AND (day = f.day))
31.          

CTE z

32. 386.399 13,119.419 ↑ 133.8 30,898 1

Unique (cost=1,099,244.83..1,140,593.92 rows=4,134,909 width=29) (actual time=11,303.52..13,119.419 rows=30,898 loops=1)

33. 6,371.978 12,733.020 ↑ 1.7 2,413,140 1

Sort (cost=1,099,244.83..1,109,582.1 rows=4,134,909 width=29) (actual time=11,303.519..12,733.02 rows=2,413,140 loops=1)

  • Sort Key: f_1.source, f_1.formula, pp_1.price
  • Sort Method: external merge Disk: 53304kB
34. 785.047 6,361.042 ↑ 1.7 2,413,140 1

Hash Join (cost=412,338.1..545,899.23 rows=4,134,909 width=29) (actual time=5,564.251..6,361.042 rows=2,413,140 loops=1)

35. 12.724 12.724 ↑ 1.0 139,136 1

Seq Scan on t_roomrates t (cost=0..2,007.36 rows=139,136 width=4) (actual time=0.032..12.724 rows=139,136 loops=1)

36. 508.859 5,563.271 ↑ 1.0 2,413,140 1

Hash (cost=363,320.85..363,320.85 rows=2,413,140 width=33) (actual time=5,563.271..5,563.271 rows=2,413,140 loops=1)

37. 509.082 5,054.412 ↑ 1.0 2,413,140 1

Merge Join (cost=345,149.91..363,320.85 rows=2,413,140 width=33) (actual time=3,637.628..5,054.412 rows=2,413,140 loops=1)

38. 4,125.455 4,449.321 ↑ 1.0 2,413,140 1

Sort (cost=344,815.17..350,848.02 rows=2,413,140 width=17) (actual time=3,635.293..4,449.321 rows=2,413,140 loops=1)

  • Sort Key: f_1.source, f_1.day
  • Sort Method: external merge Disk: 66928kB
39. 323.866 323.866 ↑ 1.0 2,413,140 1

Seq Scan on q_f f_1 (cost=0..39,502.4 rows=2,413,140 width=17) (actual time=0.011..323.866 rows=2,413,140 loops=1)

40. 95.370 96.009 ↓ 348.0 1,501,916 1

Sort (cost=334.75..345.54 rows=4,316 width=28) (actual time=2.33..96.009 rows=1,501,916 loops=1)

  • Sort Key: pp_1.roomrate, pp_1.day
  • Sort Method: quicksort Memory: 395kB
41. 0.639 0.639 ↑ 1.0 4,316 1

Seq Scan on t_rp pp_1 (cost=0..74.16 rows=4,316 width=28) (actual time=0.012..0.639 rows=4,316 loops=1)

42.          

CTE y

43. 13,152.869 13,152.869 ↑ 133.8 30,898 1

CTE Scan on z z (cost=0..1,126,762.7 rows=4,134,909 width=56) (actual time=11,303.659..13,152.869 rows=30,898 loops=1)

44. 13,161.455 13,161.455 ↑ 133.8 30,898 1

CTE Scan on y y (cost=0..82,698.18 rows=4,134,909 width=76) (actual time=11,303.661..13,161.455 rows=30,898 loops=1)

45. 1,276.175 22,786.101 ↓ 1,497,990.0 1,497,990 1

Hash (cost=0.02..0.02 rows=1 width=201) (actual time=22,786.101..22,786.101 rows=1,497,990 loops=1)

46. 21,509.926 21,509.926 ↓ 2,413,140.0 2,413,140 1

CTE Scan on x x (cost=0..0.02 rows=1 width=201) (actual time=909.854..21,509.926 rows=2,413,140 loops=1)

Planning time : 5.621 ms
Execution time : 37,228.633 ms