explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Te2c

Settings
# exclusive inclusive rows x rows loops node
1. 0.507 464,990.119 ↑ 1.0 1 1

Aggregate (cost=360,153.44..360,153.45 rows=1 width=34) (actual time=464,990.118..464,990.119 rows=1 loops=1)

2. 82.632 464,989.612 ↓ 750.0 750 1

Subquery Scan on t (cost=360,153.40..360,153.43 rows=1 width=8) (actual time=464,554.419..464,989.612 rows=750 loops=1)

  • Filter: (t.r <= 750)
  • Rows Removed by Filter: 180400
3. 253.372 464,906.980 ↓ 181,150.0 181,150 1

WindowAgg (cost=360,153.40..360,153.42 rows=1 width=16) (actual time=464,554.413..464,906.980 rows=181,150 loops=1)

4. 592.516 464,653.608 ↓ 181,150.0 181,150 1

Sort (cost=360,153.40..360,153.40 rows=1 width=8) (actual time=464,554.318..464,653.608 rows=181,150 loops=1)

  • Sort Key: hpm.price
  • Sort Method: quicksort Memory: 13953kB
5. 512.411 464,061.092 ↓ 181,150.0 181,150 1

Nested Loop Left Join (cost=5.43..360,153.39 rows=1 width=8) (actual time=77.708..464,061.092 rows=181,150 loops=1)

  • Filter: ((1) IS NULL)
  • Rows Removed by Filter: 5610
6. 699.685 459,813.481 ↓ 6,024.5 186,760 1

Nested Loop (cost=1.56..355,436.55 rows=31 width=24) (actual time=77.625..459,813.481 rows=186,760 loops=1)

7. 59,300.883 458,553.516 ↓ 6,024.5 186,760 1

Nested Loop (cost=1.27..355,427.17 rows=31 width=28) (actual time=77.590..458,553.516 rows=186,760 loops=1)

  • Join Filter: (ho.id = hpm.hotel)
  • Rows Removed by Join Filter: 125012015
8. 62.052 116.883 ↓ 313.7 63,375 1

Nested Loop (cost=0.71..1,575.20 rows=202 width=8) (actual time=0.279..116.883 rows=63,375 loops=1)

9. 7.056 7.056 ↓ 8.9 195 1

Index Scan using ix5_hotelonline on hotelonline ho (cost=0.28..1,492.59 rows=22 width=8) (actual time=0.149..7.056 rows=195 loops=1)

  • Index Cond: (country = 33)
  • Filter: (place = ANY ('{870,873,875,879,881,2050,2052,2054,2056,2172,5734,8219,8731,9125,10276}'::integer[]))
  • Rows Removed by Filter: 535
10. 47.775 47.775 ↓ 32.5 325 195

Index Only Scan using ix_datetoarea3 on datetoarea dt (cost=0.43..3.66 rows=10 width=8) (actual time=0.025..0.245 rows=325 loops=195)

  • Index Cond: ((fromarea = 2449) AND (toarea = ho.area) AND (country = 33))
  • Heap Fetches: 0
11. 113,273.550 399,135.750 ↑ 10.0 1,976 63,375

Append (cost=0.56..1,504.09 rows=19,812 width=28) (actual time=0.003..6.298 rows=1,976 loops=63,375)

12. 180,981.450 180,981.450 ↓ 1.1 13,392 5,850

Index Scan using hpm_2020_04_country_accdetail_checkindate_night_price_idx on hpm_2020_04 hpm (cost=0.56..814.30 rows=12,479 width=28) (actual time=0.005..30.937 rows=13,392 loops=5,850)

  • Index Cond: ((country = 33) AND (accdetail = ANY ('{4,28016}'::integer[])) AND (checkindate = dt.checkindate) AND (checkindate >= '2020-04-26'::date) AND (checkindate <= '2020-05-03'::date) AND (night >= 7) AND (night <= 14))
13. 104,880.750 104,880.750 ↓ 1.1 7,751 6,045

Index Scan using hpm_2020_05_country_accdetail_checkindate_night_price_idx on hpm_2020_05 hpm_1 (cost=0.56..590.73 rows=7,333 width=28) (actual time=0.003..17.350 rows=7,751 loops=6,045)

  • Index Cond: ((country = 33) AND (accdetail = ANY ('{4,28016}'::integer[])) AND (checkindate = dt.checkindate) AND (checkindate >= '2020-04-26'::date) AND (checkindate <= '2020-05-03'::date) AND (night >= 7) AND (night <= 14))
14. 560.280 560.280 ↑ 1.0 1 186,760

Index Only Scan using pk_accdetail on accdetail acc (cost=0.29..0.30 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=186,760)

  • Index Cond: (id = hpm.accdetail)
  • Heap Fetches: 0
15. 933.800 3,735.200 ↓ 0.0 0 186,760

Limit (cost=3.87..152.14 rows=1 width=4) (actual time=0.020..0.020 rows=0 loops=186,760)

16. 1,494.080 2,801.400 ↓ 0.0 0 186,760

Bitmap Heap Scan on stopsaleonlinegroup s (cost=3.87..152.14 rows=1 width=4) (actual time=0.015..0.015 rows=0 loops=186,760)

  • Recheck Cond: (hotel = hpm.hotel)
  • Filter: ((enddate >= hpm.checkindate) AND (country = 33) AND ((room = hpm.room) OR (room = '-1'::integer)) AND (((NOT checkin) AND (begindate < hpm.checkoutdate)) OR (checkin AND (begindate <= hpm.checkoutdate))))
  • Rows Removed by Filter: 27
  • Heap Blocks: exact=194112
17. 1,307.320 1,307.320 ↑ 1.1 57 186,760

Bitmap Index Scan on stopsaleonlinegroup_hotel_fromtype_idx (cost=0.00..3.87 rows=60 width=0) (actual time=0.007..0.007 rows=57 loops=186,760)

  • Index Cond: (hotel = hpm.hotel)
Planning time : 7.952 ms
Execution time : 464,991.998 ms