explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qWU7

Settings
# exclusive inclusive rows x rows loops node
1. 0.017 19.477 ↓ 8.0 8 1

Nested Loop Left Join (cost=7,187.07..7,217.43 rows=1 width=1,699) (actual time=19.325..19.477 rows=8 loops=1)

2.          

CTE hb

3. 0.013 19.029 ↓ 8.0 8 1

Nested Loop (cost=210.86..7,156.99 rows=1 width=2,122) (actual time=5.877..19.029 rows=8 loops=1)

4. 0.070 0.802 ↑ 2.0 1 1

Hash Join (cost=187.81..206.29 rows=2 width=47) (actual time=0.700..0.802 rows=1 loops=1)

  • Hash Cond: ((c_1.id)::text = (h_1.country_id)::text)
5. 0.068 0.068 ↑ 1.0 459 1

Seq Scan on countries c_1 (cost=0.00..15.59 rows=459 width=27) (actual time=0.008..0.068 rows=459 loops=1)

6. 0.004 0.664 ↑ 3.0 1 1

Hash (cost=187.78..187.78 rows=3 width=62) (actual time=0.664..0.664 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
7. 0.017 0.660 ↑ 3.0 1 1

Bitmap Heap Scan on hotels h_1 (cost=176.02..187.78 rows=3 width=62) (actual time=0.660..0.660 rows=1 loops=1)

  • Recheck Cond: ((name)::text ~~* '%The Papandayan%'::text)
  • Heap Blocks: exact=1
8. 0.643 0.643 ↑ 3.0 1 1

Bitmap Index Scan on idx_trigram_hotel (cost=0.00..176.02 rows=3 width=0) (actual time=0.643..0.643 rows=1 loops=1)

  • Index Cond: ((name)::text ~~* '%The Papandayan%'::text)
9. 16.946 18.214 ↓ 4.0 8 1

Bitmap Heap Scan on hotel_bookings hb_3 (cost=23.04..3,475.33 rows=2 width=2,083) (actual time=5.170..18.214 rows=8 loops=1)

  • Recheck Cond: (hotel_id = h_1.id)
  • Filter: (((booking_status)::text = 'issued'::text) AND (date(created_date) >= '2020-09-02'::date) AND (date(created_date) <= '2020-09-02'::date))
  • Rows Removed by Filter: 9,409
  • Heap Blocks: exact=10,142
10. 1.268 1.268 ↓ 12.3 10,876 1

Bitmap Index Scan on hotel_bookings_hotel_id_idx (cost=0.00..23.04 rows=881 width=0) (actual time=1.268..1.268 rows=10,876 loops=1)

  • Index Cond: (hotel_id = h_1.id)
11. 0.005 19.460 ↓ 8.0 8 1

Nested Loop Left Join (cost=29.81..53.85 rows=1 width=1,646) (actual time=19.316..19.460 rows=8 loops=1)

12. 0.013 19.455 ↓ 8.0 8 1

Merge Right Join (cost=29.54..47.26 rows=1 width=1,661) (actual time=19.313..19.455 rows=8 loops=1)

  • Merge Cond: (hbd.itinerary_id = hp.itinerary_id)
13. 0.013 19.203 ↓ 1.1 8 1

GroupAggregate (cost=19.61..19.84 rows=7 width=40) (actual time=19.195..19.203 rows=8 loops=1)

  • Group Key: hbd.itinerary_id
14. 0.032 19.190 ↓ 2.4 17 1

Sort (cost=19.61..19.63 rows=7 width=31) (actual time=19.186..19.190 rows=17 loops=1)

  • Sort Key: hbd.itinerary_id
  • Sort Method: quicksort Memory: 26kB
15. 0.038 19.158 ↓ 2.4 17 1

Nested Loop (cost=0.43..19.51 rows=7 width=31) (actual time=5.901..19.158 rows=17 loops=1)

16. 19.072 19.072 ↓ 8.0 8 1

CTE Scan on hb hb_1 (cost=0.00..0.02 rows=1 width=25) (actual time=5.884..19.072 rows=8 loops=1)

17. 0.048 0.048 ↑ 3.5 2 8

Index Scan using hotel_booking_detail_hotel_booking_id_checkin_date_unique on hotel_booking_details hbd (cost=0.43..19.42 rows=7 width=14) (actual time=0.005..0.006 rows=2 loops=8)

  • Index Cond: (itinerary_id = hb_1.id)
18. 0.007 0.239 ↓ 8.0 8 1

Materialize (cost=9.93..27.32 rows=1 width=1,629) (actual time=0.114..0.239 rows=8 loops=1)

19. 0.013 0.232 ↓ 8.0 8 1

Nested Loop Left Join (cost=9.93..27.32 rows=1 width=1,629) (actual time=0.110..0.232 rows=8 loops=1)

  • Join Filter: (hb.id = e.itinerary_id)
  • Rows Removed by Join Filter: 56
20. 0.008 0.203 ↓ 8.0 8 1

Nested Loop (cost=9.93..26.16 rows=1 width=1,543) (actual time=0.100..0.203 rows=8 loops=1)

21. 0.008 0.147 ↓ 8.0 8 1

Nested Loop (cost=9.65..25.86 rows=1 width=1,568) (actual time=0.086..0.147 rows=8 loops=1)

22. 0.009 0.123 ↓ 8.0 8 1

Nested Loop (cost=9.36..17.55 rows=1 width=1,543) (actual time=0.075..0.123 rows=8 loops=1)

  • Join Filter: (hb.id = hp.itinerary_id)
23. 0.001 0.082 ↓ 8.0 8 1

Nested Loop (cost=8.94..17.02 rows=1 width=1,353) (actual time=0.058..0.082 rows=8 loops=1)

24. 0.007 0.049 ↓ 8.0 8 1

Merge Left Join (cost=8.50..8.56 rows=1 width=1,330) (actual time=0.046..0.049 rows=8 loops=1)

  • Merge Cond: (hb.id = hst.itinerary_id)
25. 0.015 0.021 ↓ 8.0 8 1

Sort (cost=0.03..0.04 rows=1 width=1,322) (actual time=0.021..0.021 rows=8 loops=1)

  • Sort Key: hb.id
  • Sort Method: quicksort Memory: 27kB
26. 0.006 0.006 ↓ 8.0 8 1

CTE Scan on hb (cost=0.00..0.02 rows=1 width=1,322) (actual time=0.002..0.006 rows=8 loops=1)

27. 0.001 0.021 ↓ 0.0 0 1

Materialize (cost=8.47..8.51 rows=1 width=16) (actual time=0.021..0.021 rows=0 loops=1)

28. 0.001 0.020 ↓ 0.0 0 1

GroupAggregate (cost=8.47..8.50 rows=1 width=16) (actual time=0.020..0.020 rows=0 loops=1)

  • Group Key: hst.itinerary_id
29. 0.002 0.019 ↓ 0.0 0 1

Sort (cost=8.47..8.48 rows=1 width=16) (actual time=0.019..0.019 rows=0 loops=1)

  • Sort Key: hst.itinerary_id
  • Sort Method: quicksort Memory: 25kB
30. 0.000 0.017 ↓ 0.0 0 1

Nested Loop (cost=0.42..8.46 rows=1 width=16) (actual time=0.017..0.017 rows=0 loops=1)

31. 0.001 0.001 ↓ 8.0 8 1

CTE Scan on hb hb_2 (cost=0.00..0.02 rows=1 width=8) (actual time=0.000..0.001 rows=8 loops=1)

32. 0.016 0.016 ↓ 0.0 0 8

Index Scan using hotel_softblock_itenary_id_idx on hotel_softblock_transactions hst (cost=0.42..8.44 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=8)

  • Index Cond: (itinerary_id = hb_2.id)
33. 0.032 0.032 ↑ 1.0 1 8

Index Scan using hotel_booking_traveller_hotel_booking_id_unique on hotel_booking_travellers hbt (cost=0.43..8.45 rows=1 width=23) (actual time=0.004..0.004 rows=1 loops=8)

  • Index Cond: (itinerary_id = hb.id)
34. 0.032 0.032 ↑ 1.0 1 8

Index Scan using hotel_payments_itinerary_id_unique on hotel_payments hp (cost=0.43..0.51 rows=1 width=198) (actual time=0.004..0.004 rows=1 loops=8)

  • Index Cond: (itinerary_id = hbt.itinerary_id)
  • Filter: (((payment_method)::text = 'BANK_TRANSFER'::text) AND ((payment_status)::text = 'PENDING'::text))
35. 0.016 0.016 ↑ 1.0 1 8

Index Scan using hotel_id_pk on hotels h (cost=0.29..8.31 rows=1 width=33) (actual time=0.002..0.002 rows=1 loops=8)

  • Index Cond: (id = hb.hotel_id)
36. 0.048 0.048 ↑ 1.0 1 8

Index Only Scan using country_id_pk on countries c (cost=0.27..0.29 rows=1 width=17) (actual time=0.006..0.006 rows=1 loops=8)

  • Index Cond: (id = (h.country_id)::text)
  • Heap Fetches: 8
37. 0.016 0.016 ↑ 1.0 7 8

Seq Scan on hotel_booking_entities e (cost=0.00..1.07 rows=7 width=102) (actual time=0.001..0.002 rows=7 loops=8)

38. 0.000 0.000 ↓ 0.0 0 8

Index Scan using country_id_pk on countries cc (cost=0.27..6.58 rows=1 width=27) (actual time=0.000..0.000 rows=0 loops=8)

  • Index Cond: ((e.country_id)::text = (id)::text)
39. 0.000 0.000 ↓ 0.0 0 8

Index Scan using country_id_pk on countries cp (cost=0.27..6.58 rows=1 width=27) (actual time=0.000..0.000 rows=0 loops=8)

  • Index Cond: ((e.country_pair_id)::text = (id)::text)