explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2XBb

Settings
# exclusive inclusive rows x rows loops node
1. 190.445 18,677.440 ↑ 20.3 4,181 1

Hash Join (cost=597,874.39..736,687.32 rows=84,891 width=40) (actual time=17,788.239..18,677.440 rows=4,181 loops=1)

  • Hash Cond: (hotels."hotelID" = room_types."hotelId")
2. 700.452 700.452 ↓ 1.0 795,965 1

Seq Scan on hotels (cost=0.00..136,724.04 rows=795,751 width=40) (actual time=0.017..700.452 rows=795,965 loops=1)

3. 1.264 17,786.543 ↑ 20.3 4,181 1

Hash (cost=594,266.52..594,266.52 rows=84,891 width=4) (actual time=17,786.543..17,786.543 rows=4,181 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 1171kB
4. 6.548 17,785.279 ↑ 20.3 4,181 1

HashAggregate (cost=587,475.24..590,870.88 rows=84,891 width=4) (actual time=17,783.846..17,785.279 rows=4,181 loops=1)

  • Group Key: room_types."hotelId
5. 792.826 17,778.731 ↑ 8.5 10,010 1

Hash Join (cost=351,498.29..587,263.01 rows=84,891 width=4) (actual time=14,892.484..17,778.731 rows=10,010 loops=1)

  • Hash Cond: (room_types."roomTypeId" = room_availability."roomTypeId")
6. 2,094.185 2,094.185 ↓ 1.0 3,250,582 1

Seq Scan on room_types (cost=0.00..227,233.16 rows=3,250,104 width=8) (actual time=0.017..2,094.185 rows=3,250,582 loops=1)

7. 2.402 14,891.720 ↑ 8.5 10,010 1

Hash (cost=347,890.42..347,890.42 rows=84,891 width=4) (actual time=14,891.719..14,891.720 rows=10,010 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 1376kB
8. 366.060 14,889.318 ↑ 8.5 10,010 1

HashAggregate (cost=341,099.14..344,494.78 rows=84,891 width=4) (actual time=14,886.868..14,889.318 rows=10,010 loops=1)

  • Group Key: room_availability."roomTypeId
9. 243.597 14,523.258 ↓ 3.3 1,877,718 1

Nested Loop (cost=142,452.04..339,668.50 rows=572,256 width=4) (actual time=1,588.498..14,523.258 rows=1,877,718 loops=1)

10. 32.180 1,609.577 ↓ 2.1 16,916 1

HashAggregate (cost=142,451.48..142,779.68 rows=8,205 width=4) (actual time=1,588.441..1,609.577 rows=16,916 loops=1)

  • Group Key: room_types_1."roomTypeId
11. 46.970 1,577.397 ↓ 2.1 16,916 1

Gather (cost=1,000.43..142,430.96 rows=8,205 width=4) (actual time=3.170..1,577.397 rows=16,916 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
12. 156.335 1,530.427 ↓ 1.6 5,639 3

Nested Loop (cost=0.43..140,610.46 rows=3,419 width=4) (actual time=3.311..1,530.427 rows=5,639 loops=3)

13. 1,374.026 1,374.026 ↓ 2.7 2,301 3

Parallel Seq Scan on hotels hotels_1 (cost=0.00..119,814.33 rows=837 width=4) (actual time=3.224..1,374.026 rows=2,301 loops=3)

  • Filter: ("hotelDisplayable" AND (NOT "hotelDeleted") AND ("channelManagerPartnerID" = 13) AND ("hotelStatus" = 'Active'::"HotelStatus"))
  • Rows Removed by Filter: 263021
14. 0.066 0.066 ↑ 4.5 2 6,902

Index Scan using "room_types_Index01" on room_types room_types_1 (cost=0.43..24.49 rows=9 width=8) (actual time=0.044..0.066 rows=2 loops=6,902)

  • Index Cond: ("hotelId" = hotels_1."hotelID")
15. 12,670.084 12,670.084 ↓ 1.6 111 16,916

Index Scan using hotel_room_availability_pkey on room_availability (cost=0.57..21.20 rows=70 width=4) (actual time=0.438..0.749 rows=111 loops=16,916)

  • Index Cond: (("roomTypeId" = room_types_1."roomTypeId") AND (date > '2019-01-01'::date))
  • Filter: ((NOT locked) AND (capacity >= 1))
  • Rows Removed by Filter: 581