explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8zW

Settings
# exclusive inclusive rows x rows loops node
1. 0.462 509,361.704 ↑ 1.0 1 1

Aggregate (cost=364,171.31..364,171.32 rows=1 width=34) (actual time=509,361.703..509,361.704 rows=1 loops=1)

2. 79.237 509,361.242 ↓ 750.0 750 1

Subquery Scan on t (cost=364,171.27..364,171.30 rows=1 width=8) (actual time=508,942.762..509,361.242 rows=750 loops=1)

  • Filter: (t.r <= 750)
  • Rows Removed by Filter: 179632
3. 243.318 509,282.005 ↓ 180,382.0 180,382 1

WindowAgg (cost=364,171.27..364,171.29 rows=1 width=16) (actual time=508,942.756..509,282.005 rows=180,382 loops=1)

4. 520.950 509,038.687 ↓ 180,382.0 180,382 1

Sort (cost=364,171.27..364,171.28 rows=1 width=8) (actual time=508,942.693..509,038.687 rows=180,382 loops=1)

  • Sort Key: hpm.price
  • Sort Method: quicksort Memory: 13917kB
5. 499.022 508,517.737 ↓ 180,382.0 180,382 1

Nested Loop Left Join (cost=5.43..364,171.26 rows=1 width=8) (actual time=1,715.885..508,517.737 rows=180,382 loops=1)

  • Filter: ((1) IS NULL)
  • Rows Removed by Filter: 5610
6. 585.647 504,670.859 ↓ 6,642.6 185,992 1

Nested Loop (cost=1.56..359,910.89 rows=28 width=24) (actual time=1,715.400..504,670.859 rows=185,992 loops=1)

7. 57,194.127 503,527.236 ↓ 6,642.6 185,992 1

Nested Loop (cost=1.27..359,902.42 rows=28 width=28) (actual time=1,715.370..503,527.236 rows=185,992 loops=1)

  • Join Filter: (ho.id = hpm.hotel)
  • Rows Removed by Join Filter: 124370738
8. 58.027 118.365 ↓ 324.0 62,856 1

Nested Loop (cost=0.71..1,582.30 rows=194 width=8) (actual time=4.856..118.365 rows=62,856 loops=1)

9. 5.824 5.824 ↓ 8.8 194 1

Index Scan using ix5_hotelonline on hotelonline ho (cost=0.28..1,494.42 rows=22 width=8) (actual time=0.132..5.824 rows=194 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. 54.514 54.514 ↓ 36.0 324 194

Index Only Scan using ix_datetoarea3 on datetoarea dt (cost=0.43..3.90 rows=9 width=8) (actual time=0.046..0.281 rows=324 loops=194)

  • Index Cond: ((fromarea = 2449) AND (toarea = ho.area) AND (country = 33))
  • Heap Fetches: 2329
11. 108,529.032 446,214.744 ↑ 10.3 1,982 62,856

Append (cost=0.56..1,591.34 rows=20,454 width=28) (actual time=0.003..7.099 rows=1,982 loops=62,856)

12. 179,709.960 179,709.960 ↓ 1.0 13,392 5,820

Index Scan using hpm_2020_04_country_accdetail_checkindate_night_price_idx on hpm_2020_04 hpm (cost=0.56..877.90 rows=13,147 width=28) (actual time=0.008..30.878 rows=13,392 loops=5,820)

  • 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. 157,975.752 157,975.752 ↓ 1.1 7,751 6,014

Index Scan using hpm_2020_05_country_accdetail_checkindate_night_price_idx on hpm_2020_05 hpm_1 (cost=0.56..611.17 rows=7,307 width=28) (actual time=0.005..26.268 rows=7,751 loops=6,014)

  • 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. 557.976 557.976 ↑ 1.0 1 185,992

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=185,992)

  • Index Cond: (id = hpm.accdetail)
  • Heap Fetches: 0
15. 743.968 3,347.856 ↓ 0.0 0 185,992

Limit (cost=3.87..152.14 rows=1 width=4) (actual time=0.018..0.018 rows=0 loops=185,992)

16. 1,673.928 2,603.888 ↓ 0.0 0 185,992

Bitmap Heap Scan on stopsaleonlinegroup s (cost=3.87..152.14 rows=1 width=4) (actual time=0.014..0.014 rows=0 loops=185,992)

  • 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=193344
17. 929.960 929.960 ↑ 2.1 29 185,992

Bitmap Index Scan on stopsaleonlinegroup_hotel_fromtype_idx (cost=0.00..3.87 rows=60 width=0) (actual time=0.005..0.005 rows=29 loops=185,992)

  • Index Cond: (hotel = hpm.hotel)
Planning time : 16.886 ms
Execution time : 509,363.765 ms