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)

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: 7528
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. 122.756 1,524.495 ↓ 6,629.0 6,629 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. 45.257 1,401.722 ↓ 6,629.0 6,629 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. 36.777 1,356.459 ↓ 6,629.0 6,629 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

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

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: 2090kB
  • Worker 1: Sort Method: quicksort Memory: 615kB
12. 0.000 1,278.662 ↑ 1.7 6,673 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. 897.828 897.828 ↓ 1.7 10,884 1

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,2449
  • Filter: (price <= '2341'::numeric)
  • Rows Removed by Filter: 13032
  • -> Bitmap Index Scan on hpm_2019_11_accdetail_country_checkindate_night_idx (cost=0.00..3563.13 rows=26842 width=0) (actual time=448.423..448.423 rows=23916 loops
  • 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,
14. 978.034 978.034 ↑ 1.7 3,045 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,2449
  • Filter: (price <= '2341'::numeric)
  • Rows Removed by Filter: 2998
  • Heap Blocks: exact=4134
  • -> Bitmap Index Scan on hpm_2019_12_accdetail_country_checkindate_night_idx (cost=0.00..3149.22 rows=22168 width=0) (actual time=461.072..461.072 rows=18129 loops
  • 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,
15. 0.867 21.979 ↓ 120.1 961 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
16. 20.016 21.112 ↓ 120.1 961 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 = 2671)
  • Filter: (COALESCE(packagestoptype, 0) = 0)
  • Rows Removed by Filter: 725
  • Heap Blocks: exact=403
17. 1.096 1.096 ↓ 1.0 1,686 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 = 2671)
18. 0.004 0.004 ↑ 1.0 1 20,020

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))
19. 0.006 0.006 ↑ 1.0 1 19,886

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 = 2671) AND (toarea = ho.area) AND (country = 31) AND (checkindate = hpm.checkindate))
  • Heap Fetches: 19886
20. 0.017 0.017 ↓ 0.0 0 19,886

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))
21. 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)
22. 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)
23. 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)

24. 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))
25. 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)

26. 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 (be
  • 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 (be
  • Rows Removed by Filter: 4
  • Heap Blocks: exact=12457
27. 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)

28. 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))
29. 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))
30. 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: 12358
Planning time : 52.062 ms
Execution time : 2,508.592 ms