explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Mzs

Settings
# exclusive inclusive rows x rows loops node
1. 0.013 1,193.774 ↑ 1.0 1 1

Sort (cost=184,913.83..184,913.84 rows=1 width=333) (actual time=1,193.774..1,193.774 rows=1 loops=1)

  • Sort Key: hb.created_date DESC
  • Sort Method: quicksort Memory: 25kB
2. 0.115 1,193.761 ↑ 1.0 1 1

Merge Right Join (cost=184,837.26..184,913.82 rows=1 width=333) (actual time=1,193.728..1,193.761 rows=1 loops=1)

  • Merge Cond: (hbp.itinerary_id = hb.id)
3. 3.509 1,176.533 ↓ 1.4 1,214 1

GroupAggregate (cost=175,102.41..175,122.50 rows=893 width=40) (actual time=1,172.693..1,176.533 rows=1,214 loops=1)

  • Group Key: hbp.itinerary_id
4. 3.054 1,173.024 ↓ 4.8 4,260 1

Sort (cost=175,102.41..175,104.64 rows=893 width=24) (actual time=1,172.635..1,173.024 rows=4,260 loops=1)

  • Sort Key: hbp.itinerary_id
  • Sort Method: quicksort Memory: 509kB
5. 510.214 1,169.970 ↓ 4.8 4,260 1

Hash Join (cost=1,424.69..175,058.64 rows=893 width=24) (actual time=2.936..1,169.970 rows=4,260 loops=1)

  • Hash Cond: (hbp.itinerary_id = hb_1.id)
6. 656.923 656.923 ↑ 1.0 4,962,032 1

Seq Scan on hotel_booking_promotions hbp (cost=0.00..154,893.56 rows=4,995,056 width=24) (actual time=0.011..656.923 rows=4,962,032 loops=1)

7. 0.230 2.833 ↓ 3.4 1,215 1

Hash (cost=1,420.17..1,420.17 rows=361 width=8) (actual time=2.833..2.833 rows=1,215 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 64kB
8. 2.376 2.603 ↓ 3.4 1,215 1

Bitmap Heap Scan on hotel_bookings hb_1 (cost=11.23..1,420.17 rows=361 width=8) (actual time=0.394..2.603 rows=1,215 loops=1)

  • Recheck Cond: (hotel_id = 23248746)
  • Heap Blocks: exact=1233
9. 0.227 0.227 ↓ 3.5 1,281 1

Bitmap Index Scan on hotel_bookings_hotel_id_idx (cost=0.00..11.13 rows=361 width=0) (actual time=0.227..0.227 rows=1,281 loops=1)

  • Index Cond: (hotel_id = 23248746)
10. 0.007 17.113 ↑ 1.0 1 1

Materialize (cost=9,734.86..9,780.14 rows=1 width=250) (actual time=17.093..17.113 rows=1 loops=1)

11. 0.011 17.106 ↑ 1.0 1 1

Nested Loop Left Join (cost=9,734.86..9,780.13 rows=1 width=250) (actual time=17.087..17.106 rows=1 loops=1)

  • Join Filter: (hst.itinerary_id = hb.id)
  • Rows Removed by Join Filter: 4
12. 0.004 11.873 ↑ 1.0 1 1

Nested Loop Left Join (cost=7,573.46..7,618.69 rows=1 width=242) (actual time=11.853..11.873 rows=1 loops=1)

  • Join Filter: (hbao.itinerary_id = hb.id)
13. 0.005 11.841 ↑ 1.0 1 1

Nested Loop (cost=6,378.00..6,423.17 rows=1 width=210) (actual time=11.823..11.841 rows=1 loops=1)

14. 0.004 11.822 ↑ 1.0 1 1

Nested Loop (cost=6,377.57..6,414.71 rows=1 width=192) (actual time=11.809..11.822 rows=1 loops=1)

15. 0.111 11.801 ↑ 1.0 1 1

Merge Left Join (cost=6,377.29..6,406.40 rows=1 width=193) (actual time=11.790..11.801 rows=1 loops=1)

  • Merge Cond: (hb.id = hbd.itinerary_id)
16. 0.018 0.169 ↑ 1.0 1 1

Sort (cost=8.46..8.47 rows=1 width=121) (actual time=0.169..0.169 rows=1 loops=1)

  • Sort Key: hb.id
  • Sort Method: quicksort Memory: 25kB
17. 0.151 0.151 ↑ 1.0 1 1

Index Scan using hotel_bookings_booking_status_start_date_idx on hotel_bookings hb (cost=0.43..8.45 rows=1 width=121) (actual time=0.038..0.151 rows=1 loops=1)

  • Index Cond: ((lower((booking_status)::text) = 'refund'::text) AND (start_date >= '2019-10-04'::date) AND (start_date <= '2019-10-04'::date))
  • Filter: (hotel_id = 23248746)
  • Rows Removed by Filter: 57
18. 0.187 11.521 ↓ 2.3 1,215 1

Materialize (cost=6,368.82..6,396.60 rows=529 width=72) (actual time=9.741..11.521 rows=1,215 loops=1)

19. 1.500 11.334 ↓ 2.3 1,215 1

GroupAggregate (cost=6,368.82..6,389.98 rows=529 width=72) (actual time=9.739..11.334 rows=1,215 loops=1)

  • Group Key: hbd.itinerary_id
20. 0.648 9.834 ↓ 3.2 1,693 1

Sort (cost=6,368.82..6,370.15 rows=529 width=33) (actual time=9.712..9.834 rows=1,693 loops=1)

  • Sort Key: hbd.itinerary_id
  • Sort Method: quicksort Memory: 181kB
21. 0.659 9.186 ↓ 3.2 1,693 1

Nested Loop (cost=11.66..6,344.89 rows=529 width=33) (actual time=0.393..9.186 rows=1,693 loops=1)

22. 2.247 2.452 ↓ 3.4 1,215 1

Bitmap Heap Scan on hotel_bookings hb_2 (cost=11.23..1,420.17 rows=361 width=8) (actual time=0.374..2.452 rows=1,215 loops=1)

  • Recheck Cond: (hotel_id = 23248746)
  • Heap Blocks: exact=1233
23. 0.205 0.205 ↓ 3.5 1,281 1

Bitmap Index Scan on hotel_bookings_hotel_id_idx (cost=0.00..11.13 rows=361 width=0) (actual time=0.205..0.205 rows=1,281 loops=1)

  • Index Cond: (hotel_id = 23248746)
24. 6.075 6.075 ↑ 3.0 1 1,215

Index Scan using hotel_booking_detail_hotel_booking_id_checkin_date_unique on hotel_booking_details hbd (cost=0.43..13.61 rows=3 width=33) (actual time=0.005..0.005 rows=1 loops=1,215)

  • Index Cond: (itinerary_id = hb_2.id)
25. 0.017 0.017 ↑ 1.0 1 1

Index Scan using hotel_id_pk on hotels h (cost=0.29..8.30 rows=1 width=15) (actual time=0.016..0.017 rows=1 loops=1)

  • Index Cond: (id = 23248746)
26. 0.014 0.014 ↑ 1.0 1 1

Index Scan using hotel_booking_traveller_hotel_booking_id_unique on hotel_booking_travellers hbt (cost=0.43..8.45 rows=1 width=26) (actual time=0.011..0.014 rows=1 loops=1)

  • Index Cond: (itinerary_id = hb.id)
27. 0.003 0.028 ↓ 0.0 0 1

GroupAggregate (cost=1,195.46..1,195.50 rows=1 width=40) (actual time=0.027..0.028 rows=0 loops=1)

  • Group Key: hbao.itinerary_id
28. 0.019 0.025 ↓ 0.0 0 1

Sort (cost=1,195.46..1,195.47 rows=1 width=68) (actual time=0.025..0.025 rows=0 loops=1)

  • Sort Key: hbao.itinerary_id
  • Sort Method: quicksort Memory: 25kB
29. 0.000 0.006 ↓ 0.0 0 1

Nested Loop (cost=0.43..1,195.45 rows=1 width=68) (actual time=0.006..0.006 rows=0 loops=1)

30. 0.006 0.006 ↓ 0.0 0 1

Seq Scan on hotel_booking_add_ons hbao (cost=0.00..11.40 rows=140 width=68) (actual time=0.006..0.006 rows=0 loops=1)

31. 0.000 0.000 ↓ 0.0 0

Index Scan using hotel_bookings_pkey on hotel_bookings hb_3 (cost=0.43..8.45 rows=1 width=8) (never executed)

  • Index Cond: (id = hbao.itinerary_id)
  • Filter: (hotel_id = 23248746)
32. 0.023 5.222 ↓ 5.0 5 1

GroupAggregate (cost=2,161.40..2,161.42 rows=1 width=16) (actual time=5.218..5.222 rows=5 loops=1)

  • Group Key: hst.itinerary_id
33. 0.016 5.199 ↓ 6.0 6 1

Sort (cost=2,161.40..2,161.40 rows=1 width=16) (actual time=5.198..5.199 rows=6 loops=1)

  • Sort Key: hst.itinerary_id
  • Sort Method: quicksort Memory: 25kB
34. 0.956 5.183 ↓ 6.0 6 1

Hash Join (cost=1,424.69..2,161.39 rows=1 width=16) (actual time=2.953..5.183 rows=6 loops=1)

  • Hash Cond: (hst.itinerary_id = hb_4.id)
35. 1.623 1.623 ↓ 1.1 5,603 1

Seq Scan on hotel_softblock_transactions hst (cost=0.00..717.41 rows=5,141 width=16) (actual time=0.006..1.623 rows=5,603 loops=1)

36. 0.203 2.604 ↓ 3.4 1,215 1

Hash (cost=1,420.17..1,420.17 rows=361 width=8) (actual time=2.604..2.604 rows=1,215 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 64kB
37. 2.179 2.401 ↓ 3.4 1,215 1

Bitmap Heap Scan on hotel_bookings hb_4 (cost=11.23..1,420.17 rows=361 width=8) (actual time=0.392..2.401 rows=1,215 loops=1)

  • Recheck Cond: (hotel_id = 23248746)
  • Heap Blocks: exact=1233
38. 0.222 0.222 ↓ 3.5 1,281 1

Bitmap Index Scan on hotel_bookings_hotel_id_idx (cost=0.00..11.13 rows=361 width=0) (actual time=0.222..0.222 rows=1,281 loops=1)

  • Index Cond: (hotel_id = 23248746)
Planning time : 2.202 ms
Execution time : 1,194.235 ms