explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Jooh

Settings
# exclusive inclusive rows x rows loops node
1. 19.819 2,502.825 ↓ 6,520.0 6,520 1

Nested Loop (cost=145,885.17..145,996.66 rows=1 width=182) (actual time=1,322.144..2,502.825 rows=6,520 loops=1)

  • (country = 31) AND (checkindate >= '2019-11-27'::date) AND (checkindate <= '2019-12-03'::date) AND (night >= 7) AND (night <= 9))
  • (country = 31) AND (checkindate >= '2019-11-27'::date) AND (checkindate <= '2019-12-03'::date) AND (night >= 7) AND (night <= 9))
2. 28.210 2,421.216 ↓ 12,358.0 12,358 1

Nested Loop Left Join (cost=145,884.90..145,995.81 rows=1 width=154) (actual time=1,321.991..2,421.216 rows=12,358 loops=1)

  • Filter: ((1) IS NULL)
  • Rows Removed by Filter: 7,528
3. 21.561 1,856.084 ↓ 19,886.0 19,886 1

Nested Loop Left Join (cost=145,877.74..145,985.57 rows=1 width=171) (actual time=1,321.834..1,856.084 rows=19,886 loops=1)

4. 27.682 1,675.435 ↓ 19,886.0 19,886 1

Nested Loop (cost=145,877.46..145,979.19 rows=1 width=135) (actual time=1,321.725..1,675.435 rows=19,886 loops=1)

5. 31.954 1,588.095 ↓ 19,886.0 19,886 1

Nested Loop (cost=145,877.17..145,977.77 rows=1 width=100) (actual time=1,321.677..1,588.095 rows=19,886 loops=1)

6. 0.000 1,476.597 ↓ 19,886.0 19,886 1

Gather (cost=145,876.89..145,976.39 rows=1 width=77) (actual time=1,321.618..1,476.597 rows=19,886 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 10.086 1,524.495 ↓ 6,629.0 6,629 3 / 3

Nested Loop Left Join (cost=144,876.89..144,976.29 rows=1 width=77) (actual time=1,310.424..1,524.495 rows=6,629 loops=3)

8. 5.491 1,401.722 ↓ 6,629.0 6,629 3 / 3

Nested Loop (cost=144,876.32..144,969.83 rows=1 width=73) (actual time=1,309.993..1,401.722 rows=6,629 loops=3)

9. 10.088 1,356.459 ↓ 6,629.0 6,629 3 / 3

Nested Loop (cost=144,875.89..144,963.40 rows=1 width=73) (actual time=1,309.863..1,356.459 rows=6,629 loops=3)

10. 7.146 1,319.678 ↓ 6,673.0 6,673 3 / 3

Merge Left Join (cost=144,875.61..144,961.97 rows=1 width=52) (actual time=1,309.767..1,319.678 rows=6,673 loops=3)

  • Merge Cond: ((hpm.hotel = ps.hotel) AND (hpm.room = ps.room))
  • Join Filter: ((hpm.checkindate >= ps.begindate) AND (hpm.checkindate <= ps.enddate) AND (hpm.night >= ps.bn) AND (hpm.night <= ps.en))
  • Filter: (ps.id IS NULL)
11. 11.891 1,290.553 ↑ 1.7 6,673 3 / 3

Sort (cost=144,119.91..144,148.67 rows=11,504 width=52) (actual time=1,287.945..1,290.553 rows=6,673 loops=3)

  • Sort Key: hpm.hotel, hpm.room
  • Sort Method: quicksort Memory: 784kB
  • Worker 0: Sort Method: quicksort Memory: 2,090kB
  • Worker 1: Sort Method: quicksort Memory: 615kB
12. 1.352 1,278.662 ↑ 1.7 6,673 3 / 3

Parallel Append (cost=3,152.29..143,343.97 rows=11,504 width=52) (actual time=458.490..1,278.662 rows=6,673 loops=3)

13. 149.802 299.276 ↓ 1.7 10,884 1 / 3

Parallel Bitmap Heap Scan on hpm_2019_11 hpm (cost=3,566.96..76,516.27 rows=6,391 width=52) (actual time=453.253..897.828 rows=10,884 loops=1)

  • Recheck Cond: ((accdetail = ANY ('{26,26844,28677,26808,235,5290,4612,1282,208,6558,3253,6446,26843,13561,26847,28496,28510,28676,26846,24240,26223,28495,26222,24498,26807,24753,25694,28509,24289,24687,3122,143,28397,5535,7528,5166,26901,28396,21586,26268,24506,24817,25570,24983,25789,4149,230,27394,6199,7827,28216,24590,25480,27393,24275,24682,27686,28683,26173,6691,1018,28521,28749,28520,24323,24910,24840,26075,25879,27192,3032,27333,27953,5678,28774,82,28011,24489,24464,28010,25355,26732,27449,26914,207,27893,27922,870,6243,7654,9168,63,27862,27853,8415,28424,26251,27878,24257,28709,28029,28708,24537,27861,28028,21137,27113,27852,25801,25545,8620,7530,163,25218,24857,26751,25462,10901,5210,279,24827,28731,28292,25559,28476,25279,26207,28473,26014,26342,715,25067,25170,25169,24588,25583,26836,26682,24640,25202,1861,28372,26983,25391,25123,26298}'::integer[])) AND (country = 31) AND (checkindate >= '2019-11-27'::date) AND (checkindate <= '2019-12-03'::date) AND (night >= 7) AND (night <= 9))
  • Filter: (price <= '2341'::numeric)
  • Rows Removed by Filter: 13,032
14. 149.474 149.474 ↑ 1.1 23,916 1 / 3

Bitmap Index Scan on hpm_2019_11_accdetail_country_checkindate_night_idx (cost=0.00..3,563.13 rows=26,842 width=0) (actual time=448.423..448.423 rows=23,916 loops=1)

  • Index Cond: ((accdetail = ANY ('{26,26844,28677,26808,235,5290,4612,1282,208,6558,3253,6446,26843,13561,26847,28496,28510,28676,26846,24240,26223,28495,26222,24498,26807,24753,25694,28509,24289,24687,3122,143,28397,5535,7528,5166,26901,28396,21586,26268,24506,24817,25570,24983,25789,4149,230,27394,6199,7827,28216,24590,25480,27393,24275,24682,27686,28683,26173,6691,1018,28521,28749,28520,24323,24910,24840,26075,25879,27192,3032,27333,27953,5678,28774,82,28011,24489,24464,28010,25355,26732,27449,26914,207,27893,27922,870,6243,7654,9168,63,27862,27853,8415,28424,26251,27878,24257,28709,28029,28708,24537,27861,28028,21137,27113,27852,25801,25545,8620,7530,163,25218,24857,26751,25462,10901,5210,279,24827,28731,28292,25559,28476,25279,26207,28473,26014,26342,715,25067,25170,25169,24588,25583,26836,26682,24640,25202,1861,28372,26983,25391,25123,26298}'::integer[])) AND
15. 824.343 978.034 ↑ 1.7 3,045 3 / 3

Parallel Bitmap Heap Scan on hpm_2019_12 hpm_1 (cost=3,152.29..66,770.18 rows=5,113 width=52) (actual time=310.178..978.034 rows=3,045 loops=3)

  • Recheck Cond: ((accdetail = ANY ('{26,26844,28677,26808,235,5290,4612,1282,208,6558,3253,6446,26843,13561,26847,28496,28510,28676,26846,24240,26223,28495,26222,24498,26807,24753,25694,28509,24289,24687,3122,143,28397,5535,7528,5166,26901,28396,21586,26268,24506,24817,25570,24983,25789,4149,230,27394,6199,7827,28216,24590,25480,27393,24275,24682,27686,28683,26173,6691,1018,28521,28749,28520,24323,24910,24840,26075,25879,27192,3032,27333,27953,5678,28774,82,28011,24489,24464,28010,25355,26732,27449,26914,207,27893,27922,870,6243,7654,9168,63,27862,27853,8415,28424,26251,27878,24257,28709,28029,28708,24537,27861,28028,21137,27113,27852,25801,25545,8620,7530,163,25218,24857,26751,25462,10901,5210,279,24827,28731,28292,25559,28476,25279,26207,28473,26014,26342,715,25067,25170,25169,24588,25583,26836,26682,24640,25202,1861,28372,26983,25391,25123,26298}'::integer[])) AND (country = 31) AND (checkindate >= '2019-11-27'::date) AND (checkindate <= '2019-12-03'::date) AND (night >= 7) AND (night <= 9))
  • Filter: (price <= '2341'::numeric)
  • Rows Removed by Filter: 2,998
  • Heap Blocks: exact=4,134
16. 153.691 153.691 ↑ 1.2 18,129 1 / 3

Bitmap Index Scan on hpm_2019_12_accdetail_country_checkindate_night_idx (cost=0.00..3,149.22 rows=22,168 width=0) (actual time=461.072..461.072 rows=18,129 loops=1)

  • Index Cond: ((accdetail = ANY ('{26,26844,28677,26808,235,5290,4612,1282,208,6558,3253,6446,26843,13561,26847,28496,28510,28676,26846,24240,26223,28495,26222,24498,26807,24753,25694,28509,24289,24687,3122,143,28397,5535,7528,5166,26901,28396,21586,26268,24506,24817,25570,24983,25789,4149,230,27394,6199,7827,28216,24590,25480,27393,24275,24682,27686,28683,26173,6691,1018,28521,28749,28520,24323,24910,24840,26075,25879,27192,3032,27333,27953,5678,28774,82,28011,24489,24464,28010,25355,26732,27449,26914,207,27893,27922,870,6243,7654,9168,63,27862,27853,8415,28424,26251,27878,24257,28709,28029,28708,24537,27861,28028,21137,27113,27852,25801,25545,8620,7530,163,25218,24857,26751,25462,10901,5210,279,24827,28731,28292,25559,28476,25279,26207,28473,26014,26342,715,25067,25170,25169,24588,25583,26836,26682,24640,25202,1861,28372,26983,25391,25123,26298}'::integer[])) AND
17. 0.867 21.979 ↓ 120.1 961 3 / 3

Sort (cost=755.70..755.72 rows=8 width=24) (actual time=21.780..21.979 rows=961 loops=3)

  • Sort Key: ps.hotel, ps.room
  • Sort Method: quicksort Memory: 100kB
  • Worker 0: Sort Method: quicksort Memory: 100kB
  • Worker 1: Sort Method: quicksort Memory: 100kB
18. 20.016 21.112 ↓ 120.1 961 3 / 3

Bitmap Heap Scan on packagestop ps (cost=42.47..755.58 rows=8 width=24) (actual time=1.175..21.112 rows=961 loops=3)

  • Recheck Cond: (fromarea = 2,671)
  • Filter: (COALESCE(packagestoptype, 0) = 0)
  • Rows Removed by Filter: 725
  • Heap Blocks: exact=403
19. 1.096 1.096 ↓ 1.0 1,686 3 / 3

Bitmap Index Scan on ix_packagestop (cost=0.00..42.47 rows=1,607 width=0) (actual time=1.096..1.096 rows=1,686 loops=3)

  • Index Cond: (fromarea = 2,671)
20. 26.693 26.693 ↑ 1.0 1 20,020 / 3

Index Scan using ix5_hotelonline on hotelonline ho (cost=0.28..1.42 rows=1 width=25) (actual time=0.004..0.004 rows=1 loops=20,020)

  • Index Cond: ((id = hpm.hotel) AND (country = 31))
21. 39.772 39.772 ↑ 1.0 1 19,886 / 3

Index Only Scan using ix_datetoarea3 on datetoarea dt (cost=0.43..6.43 rows=1 width=8) (actual time=0.005..0.006 rows=1 loops=19,886)

  • Index Cond: ((fromarea = 2,671) AND (toarea = ho.area) AND (country = 31) AND (checkindate = hpm.checkindate))
  • Heap Fetches: 19,886
22. 112.687 112.687 ↓ 0.0 0 19,886 / 3

Index Scan using ix1_allotmentstatus on allotmentstatus s (cost=0.57..6.45 rows=1 width=20) (actual time=0.017..0.017 rows=0 loops=19,886)

  • Index Cond: ((hotel = hpm.hotel) AND (room = hpm.room) AND (checkindate = hpm.checkindate) AND (checkoutdate = hpm.checkoutdate) AND (country = 31))
23. 79.544 79.544 ↑ 1.0 1 19,886

Index Scan using pk_accdetail on accdetail acc (cost=0.29..1.38 rows=1 width=27) (actual time=0.004..0.004 rows=1 loops=19,886)

  • Index Cond: (id = hpm.accdetail)
24. 59.658 59.658 ↑ 1.0 1 19,886

Index Scan using pk_accname_1 on accname accn (cost=0.29..1.42 rows=1 width=43) (actual time=0.003..0.003 rows=1 loops=19,886)

  • Index Cond: (id = hpm.accnameid)
25. 39.772 159.088 ↑ 1.0 1 19,886

Limit (cost=0.29..6.36 rows=1 width=36) (actual time=0.008..0.008 rows=1 loops=19,886)

26. 119.316 119.316 ↑ 1.0 1 19,886

Index Scan using ix_hfhotelprice on hfhotelprice hfs (cost=0.29..6.36 rows=1 width=36) (actual time=0.006..0.006 rows=1 loops=19,886)

  • Index Cond: ((hotel = hpm.hotel) AND (begindate <= hpm.checkindate) AND (enddate >= hpm.checkindate))
27. 99.430 536.922 ↓ 0.0 0 19,886

Limit (cost=7.16..10.21 rows=1 width=4) (actual time=0.027..0.027 rows=0 loops=19,886)

28. 99.430 437.492 ↓ 0.0 0 19,886

Bitmap Heap Scan on stopsaleonlinegroup s_1 (cost=7.16..10.21 rows=1 width=4) (actual time=0.022..0.022 rows=0 loops=19,886)

  • Recheck Cond: (((NOT checkin) AND (hotel = hpm.hotel) AND (enddate >= hpm.checkindate) AND (begindate < hpm.checkoutdate) AND (country = 31)) OR (checkin AND (hotel = hpm.hotel) AND (enddate >= hpm.checkindate) AND (begindate <= hpm.checkoutdate) AND (country = 31)))
  • Filter: (((room = hpm.room) OR (room = '-1'::integer)) AND ((meal = hpm.meal) OR (meal = '-1'::integer)) AND ((adl = acc.adult) OR (adl = 99)) AND (((NOT checkin) AND (begindate < hpm.checkoutdate)) OR (checkin AND (begindate <= hpm.checkoutdate))) AND (((chd = acc.child) AND (acc.c1max >= c1min) AND (acc.c1max <= c1max) AND (acc.c2max >= c2min) AND (acc.c2max <= c2max) AND (acc.c3max >= c3min) AND (acc.c3max <= c3max)) OR (chd = 99)))
  • Rows Removed by Filter: 4
  • Heap Blocks: exact=12,457
29. 39.772 338.062 ↓ 0.0 0 19,886

BitmapOr (cost=7.16..7.16 rows=1 width=0) (actual time=0.017..0.017 rows=0 loops=19,886)

30. 238.632 238.632 ↓ 13.0 13 19,886

Bitmap Index Scan on ix_stopsaleonlinegroup (cost=0.00..3.72 rows=1 width=0) (actual time=0.012..0.012 rows=13 loops=19,886)

  • Index Cond: ((checkin = false) AND (hotel = hpm.hotel) AND (enddate >= hpm.checkindate) AND (begindate < hpm.checkoutdate) AND (country = 31))
31. 59.658 59.658 ↓ 0.0 0 19,886

Bitmap Index Scan on ix_stopsaleonlinegroup (cost=0.00..3.44 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=19,886)

  • Index Cond: ((checkin = true) AND (hotel = hpm.hotel) AND (enddate >= hpm.checkindate) AND (begindate <= hpm.checkoutdate) AND (country = 31))
32. 61.790 61.790 ↑ 1.0 1 12,358

Index Only Scan using ix_mealonline3 on mealonline mo (cost=0.28..0.84 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=12,358)

  • Index Cond: (id = hpm.meal)
  • Filter: (mealcategory = ANY ('{3,4}'::integer[]))
  • Rows Removed by Filter: 0
  • Heap Fetches: 12,358
Planning time : 52.062 ms
Execution time : 2,508.592 ms