explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ERir

Settings
# exclusive inclusive rows x rows loops node
1. 5,120.251 5,120.251 ↓ 1.4 105 1

CTE Scan on ratings_to_translate (cost=6,321,926.42..6,321,944.41 rows=73 width=72) (actual time=5,120.214..5,120.251 rows=105 loops=1)

  • Filter: (ratingperhotel <= 9)
2.          

CTE ratings_to_translate

3. 0.053 5,120.217 ↑ 2.1 105 1

Sort (cost=6,321,925.88..6,321,926.42 rows=218 width=122) (actual time=5,120.211..5,120.217 rows=105 loops=1)

  • Sort Key: r."ratingDate" DESC
  • Sort Method: quicksort Memory: 59kB
4. 0.092 5,120.164 ↑ 2.1 105 1

WindowAgg (cost=6,321,906.51..6,321,917.41 rows=218 width=122) (actual time=5,120.075..5,120.164 rows=105 loops=1)

5. 0.230 5,120.072 ↑ 2.1 105 1

Sort (cost=6,321,906.51..6,321,907.05 rows=218 width=114) (actual time=5,120.063..5,120.072 rows=105 loops=1)

  • Sort Key: r."hotelID", r."ratingDate" DESC
  • Sort Method: quicksort Memory: 57kB
6. 38.681 5,119.842 ↑ 2.1 105 1

Nested Loop Anti Join (cost=0.42..6,321,898.04 rows=218 width=114) (actual time=25.139..5,119.842 rows=105 loops=1)

  • Join Filter: (r."ratingID" = tr."ratingID")
  • Rows Removed by Join Filter: 569100
7. 16.497 5,052.601 ↑ 2.1 105 1

Nested Loop (cost=0.42..6,268,075.59 rows=219 width=114) (actual time=23.351..5,052.601 rows=105 loops=1)

8. 246.952 246.952 ↑ 1.1 26,028 1

Seq Scan on ratings r (cost=0.00..101,874.86 rows=29,326 width=114) (actual time=0.010..246.952 rows=26,028 loops=1)

  • Filter: (("localeID" <> 2) AND (("ratingPositive" <> ''::text) OR ("ratingNegative" <> ''::text)))
  • Rows Removed by Filter: 915962
9. 103.752 4,789.152 ↓ 0.0 0 26,028

Index Scan using hotels_pkey on hotels h (cost=0.42..210.26 rows=1 width=4) (actual time=0.184..0.184 rows=0 loops=26,028)

  • Index Cond: ("hotelID" = r."hotelID")
  • Filter: (("localeID" = 2) AND ("hotelStatus" = 'Active'::"HotelStatus") AND ((SubPlan 1) < 3))
  • Rows Removed by Filter: 1
10.          

SubPlan (forIndex Scan)

11. 246.600 4,685.400 ↑ 1.0 1 12,330

Aggregate (cost=206.55..206.59 rows=1 width=8) (actual time=0.380..0.380 rows=1 loops=12,330)

12. 4,044.240 4,438.800 ↓ 49.8 249 12,330

Bitmap Heap Scan on ratings (cost=4.81..206.53 rows=5 width=0) (actual time=0.061..0.360 rows=249 loops=12,330)

  • Recheck Cond: ("hotelID" = h."hotelID")
  • Filter: ("localeID" = h."localeID")
  • Rows Removed by Filter: 20
  • Heap Blocks: exact=3106356
13. 394.560 394.560 ↓ 5.3 269 12,330

Bitmap Index Scan on ratings_hotel_idx (cost=0.00..4.81 rows=51 width=0) (actual time=0.032..0.032 rows=269 loops=12,330)

  • Index Cond: ("hotelID" = h."hotelID")
14. 27.749 28.560 ↑ 1.0 5,420 105

Materialize (cost=0.00..421.90 rows=5,420 width=4) (actual time=0.000..0.272 rows=5,420 loops=105)

15. 0.811 0.811 ↑ 1.0 5,420 1

Seq Scan on translated_ratings tr (cost=0.00..394.80 rows=5,420 width=4) (actual time=0.005..0.811 rows=5,420 loops=1)

Planning time : 0.667 ms
Execution time : 5,120.376 ms