explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rCb3

Settings
# exclusive inclusive rows x rows loops node
1. 2.409 2,300.776 ↑ 44,289.5 1,479 1

Hash Join (cost=54,616.46..1,052,348.71 rows=65,504,171 width=289) (actual time=2,247.734..2,300.776 rows=1,479 loops=1)

  • Buffers: shared hit=147644
2.          

CTE r

3. 2,034.896 2,034.896 ↓ 1.0 1,479 1

Index Scan using reservations_hotel_index on reservations r_6 (cost=0.42..1,551.22 rows=1,424 width=1,034) (actual time=2,033.032..2,034.896 rows=1,479 loops=1)

  • Index Cond: (r_6.hotel = 14054)
  • Buffers: shared hit=1318
4. 0.983 2,214.766 ↑ 161.7 1,479 1

Hash Join (cost=49,162.11..63,690.58 rows=239,206 width=226) (actual time=2,163.984..2,214.766 rows=1,479 loops=1)

  • Buffers: shared hit=146041
5. 1.002 2,170.720 ↑ 161.7 1,479 1

Hash Join (cost=36,652.01..50,279.9 rows=239,206 width=214) (actual time=2,120.873..2,170.72 rows=1,479 loops=1)

  • Buffers: shared hit=102319
6. 1.009 2,126.214 ↑ 161.7 1,479 1

Hash Join (cost=24,141.49..36,868.65 rows=239,206 width=202) (actual time=2,077.32..2,126.214 rows=1,479 loops=1)

  • Buffers: shared hit=58597
7. 1.444 2,096.862 ↑ 1.7 1,479 1

Hash Join (cost=3,732.88..16,453.37 rows=2,485 width=201) (actual time=2,048.907..2,096.862 rows=1,479 loops=1)

  • Buffers: shared hit=52276
8. 3.673 46.590 ↓ 2.1 1,479 1

Nested Loop (cost=0.72..12,501.49 rows=703 width=16) (actual time=0.057..46.59 rows=1,479 loops=1)

  • Buffers: shared hit=43722
9. 2.034 17.941 ↓ 1.6 12,488 1

Nested Loop (cost=0.43..9,987.95 rows=8,049 width=20) (actual time=0.036..17.941 rows=12,488 loops=1)

  • Buffers: shared hit=6258
10. 1.117 1.117 ↓ 1.0 1,479 1

CTE Scan on r r_2 (cost=0..28.48 rows=1,424 width=4) (actual time=0.001..1.117 rows=1,479 loops=1)

11. 14.790 14.790 ↓ 1.3 8 1,479

Index Scan using reservationsform_reservation_index on reservationsform nrf1 (cost=0.43..6.93 rows=6 width=20) (actual time=0.007..0.01 rows=8 loops=1,479)

  • Index Cond: (nrf1.reservation = r_2.id)
  • Buffers: shared hit=6258
12. 24.976 24.976 ↓ 0.0 0 12,488

Index Scan using bookforms_pkey on bookforms nbf1 (cost=0.29..0.31 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=12,488)

  • Index Cond: (nbf1.id = nrf1.bookform)
  • Filter: (nbf1.type = 2)
  • Buffers: shared hit=37464
13. 0.803 2,048.828 ↑ 1.7 1,479 1

Hash (cost=3,701.09..3,701.09 rows=2,485 width=189) (actual time=2,048.828..2,048.828 rows=1,479 loops=1)

  • Buffers: shared hit=8554
14. 0.453 2,048.025 ↑ 1.7 1,479 1

Hash Join (cost=3,665.06..3,701.09 rows=2,485 width=189) (actual time=2,043.169..2,048.025 rows=1,479 loops=1)

  • Buffers: shared hit=8554
15. 0.745 2,037.568 ↓ 1.0 1,479 1

Hash Join (cost=4.77..36.99 rows=1,424 width=185) (actual time=2,033.147..2,037.568 rows=1,479 loops=1)

  • Buffers: shared hit=1323
16. 2,036.760 2,036.760 ↓ 1.0 1,479 1

CTE Scan on r r (cost=0..28.48 rows=1,424 width=174) (actual time=2,033.045..2,036.76 rows=1,479 loops=1)

  • Buffers: shared hit=1318
17. 0.006 0.063 ↑ 1.0 3 1

Hash (cost=4.73..4.73 rows=3 width=19) (actual time=0.063..0.063 rows=3 loops=1)

  • Buffers: shared hit=5
18. 0.057 0.057 ↑ 1.0 3 1

Index Scan using layouts_hotel_index on layouts y (cost=0.29..4.73 rows=3 width=19) (actual time=0.047..0.057 rows=3 loops=1)

  • Index Cond: (y.hotel = 14054)
  • Buffers: shared hit=5
19. 0.297 10.004 ↓ 4.2 1,476 1

Hash (cost=3,655.93..3,655.93 rows=349 width=8) (actual time=10.003..10.004 rows=1,476 loops=1)

  • Buffers: shared hit=7231
20. 0.200 9.707 ↓ 4.2 1,476 1

Subquery Scan on r_1 (cost=3,642.84..3,655.93 rows=349 width=8) (actual time=8.68..9.707 rows=1,476 loops=1)

  • Buffers: shared hit=7231
21. 0.720 9.507 ↓ 4.2 1,476 1

GroupAggregate (cost=3,642.84..3,652.44 rows=349 width=19) (actual time=8.677..9.507 rows=1,476 loops=1)

  • Group Key: r_1_1.id
  • Buffers: shared hit=7231
22. 0.758 8.787 ↓ 4.2 1,476 1

Sort (cost=3,642.84..3,643.71 rows=349 width=20) (actual time=8.647..8.787 rows=1,476 loops=1)

  • Sort Key: r_1_1.id
  • Sort Method: quicksort Memory: 145kB
  • Buffers: shared hit=7231
23. 0.221 8.029 ↓ 4.2 1,476 1

Nested Loop (cost=0.84..3,628.1 rows=349 width=20) (actual time=0.049..8.029 rows=1,476 loops=1)

  • Buffers: shared hit=7231
24. 1.892 1.892 ↓ 1.0 1,479 1

Index Scan using reservations_hotel_index on reservations r_1_1 (cost=0.42..1,551.22 rows=1,424 width=4) (actual time=0.022..1.892 rows=1,479 loops=1)

  • Index Cond: (r_1_1.hotel = 14054)
  • Buffers: shared hit=1318
25. 5.916 5.916 ↑ 1.0 1 1,479

Index Scan using reservationspay_reservation_index on reservationspay p (cost=0.42..1.45 rows=1 width=20) (actual time=0.003..0.004 rows=1 loops=1,479)

  • Index Cond: (p.reservation = r_1_1.id)
  • Buffers: shared hit=5913
26. 0.396 28.343 ↑ 13.0 1,479 1

Hash (cost=20,167.95..20,167.95 rows=19,253 width=5) (actual time=28.343..28.343 rows=1,479 loops=1)

  • Buffers: shared hit=6321
27. 0.182 27.947 ↑ 13.0 1,479 1

Subquery Scan on a (cost=19,782.89..20,167.95 rows=19,253 width=5) (actual time=27.25..27.947 rows=1,479 loops=1)

  • Buffers: shared hit=6321
28. 5.640 27.765 ↑ 13.0 1,479 1

HashAggregate (cost=19,782.89..19,975.42 rows=19,253 width=6) (actual time=27.247..27.765 rows=1,479 loops=1)

  • Group Key: r1.reservation
  • Buffers: shared hit=6321
29. 3.414 22.125 ↓ 1.3 25,535 1

Nested Loop (cost=0.43..19,686.63 rows=19,253 width=8) (actual time=0.028..22.125 rows=25,535 loops=1)

  • Buffers: shared hit=6321
30. 0.963 0.963 ↓ 1.0 1,479 1

CTE Scan on r r_5 (cost=0..28.48 rows=1,424 width=4) (actual time=0.001..0.963 rows=1,479 loops=1)

31. 17.748 17.748 ↓ 1.2 17 1,479

Index Scan using reservationscart_reservation_index on reservationscart r1 (cost=0.43..13.66 rows=14 width=8) (actual time=0.007..0.012 rows=17 loops=1,479)

  • Index Cond: (r1.reservation = r_5.id)
  • Buffers: shared hit=6321
32. 0.749 43.504 ↓ 2.0 1,479 1

Hash (cost=12,501.49..12,501.49 rows=722 width=16) (actual time=43.504..43.504 rows=1,479 loops=1)

  • Buffers: shared hit=43722
33. 1.318 42.755 ↓ 2.0 1,479 1

Nested Loop (cost=0.72..12,501.49 rows=722 width=16) (actual time=0.082..42.755 rows=1,479 loops=1)

  • Buffers: shared hit=43722
34. 2.086 16.461 ↓ 1.6 12,488 1

Nested Loop (cost=0.43..9,987.95 rows=8,049 width=20) (actual time=0.036..16.461 rows=12,488 loops=1)

  • Buffers: shared hit=6258
35. 1.064 1.064 ↓ 1.0 1,479 1

CTE Scan on r r_4 (cost=0..28.48 rows=1,424 width=4) (actual time=0.002..1.064 rows=1,479 loops=1)

36. 13.311 13.311 ↓ 1.3 8 1,479

Index Scan using reservationsform_reservation_index on reservationsform nrf1_2 (cost=0.43..6.93 rows=6 width=20) (actual time=0.006..0.009 rows=8 loops=1,479)

  • Index Cond: (nrf1_2.reservation = r_4.id)
  • Buffers: shared hit=6258
37. 24.976 24.976 ↓ 0.0 0 12,488

Index Scan using bookforms_pkey on bookforms nbf1_2 (cost=0.29..0.31 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=12,488)

  • Index Cond: (nbf1_2.id = nrf1_2.bookform)
  • Filter: (nbf1_2.type = 4)
  • Buffers: shared hit=37464
38. 0.729 43.063 ↓ 2.1 1,479 1

Hash (cost=12,501.49..12,501.49 rows=689 width=16) (actual time=43.063..43.063 rows=1,479 loops=1)

  • Buffers: shared hit=43722
39. 1.063 42.334 ↓ 2.1 1,479 1

Nested Loop (cost=0.72..12,501.49 rows=689 width=16) (actual time=0.046..42.334 rows=1,479 loops=1)

  • Buffers: shared hit=43722
40. 1.927 16.295 ↓ 1.6 12,488 1

Nested Loop (cost=0.43..9,987.95 rows=8,049 width=20) (actual time=0.026..16.295 rows=12,488 loops=1)

  • Buffers: shared hit=6258
41. 1.057 1.057 ↓ 1.0 1,479 1

CTE Scan on r r_3 (cost=0..28.48 rows=1,424 width=4) (actual time=0.002..1.057 rows=1,479 loops=1)

42. 13.311 13.311 ↓ 1.3 8 1,479

Index Scan using reservationsform_reservation_index on reservationsform nrf1_1 (cost=0.43..6.93 rows=6 width=20) (actual time=0.006..0.009 rows=8 loops=1,479)

  • Index Cond: (nrf1_1.reservation = r_3.id)
  • Buffers: shared hit=6258
43. 24.976 24.976 ↓ 0.0 0 12,488

Index Scan using bookforms_pkey on bookforms nbf1_1 (cost=0.29..0.31 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=12,488)

  • Index Cond: (nbf1_1.id = nrf1_1.bookform)
  • Filter: (nbf1_1.type = 3)
  • Buffers: shared hit=37464
44. 12.417 83.601 ↑ 1.0 52,808 1

Hash (cost=3,218.53..3,218.53 rows=54,768 width=12) (actual time=83.601..83.601 rows=52,808 loops=1)

  • Buffers: shared hit=1603
45. 6.562 71.184 ↑ 1.0 52,808 1

Subquery Scan on rp (cost=2,123.17..3,218.53 rows=54,768 width=12) (actual time=48.523..71.184 rows=52,808 loops=1)

  • Buffers: shared hit=1603
46. 43.189 64.622 ↑ 1.0 52,808 1

HashAggregate (cost=2,123.17..2,670.85 rows=54,768 width=12) (actual time=48.52..64.622 rows=52,808 loops=1)

  • Group Key: rp_1.reservation
  • Buffers: shared hit=1603
47. 21.433 21.433 ↑ 1.0 56,440 1

Index Scan using reservationspay_payed on reservationspay rp_1 (cost=0.42..1,839.63 rows=56,709 width=12) (actual time=0.028..21.433 rows=56,440 loops=1)

  • Index Cond: (rp_1.payed IS NULL)
  • Buffers: shared hit=1603
Planning time : 5.232 ms
Execution time : 2,322.142 ms