explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2RXT

Settings
# exclusive inclusive rows x rows loops node
1. 0.019 16,285.953 ↓ 8.0 8 1

Nested Loop Left Join (cost=1,049,135.29..1,689,942.89 rows=1 width=521) (actual time=16,285.706..16,285.953 rows=8 loops=1)

2. 0.004 16,285.934 ↓ 8.0 8 1

Nested Loop Left Join (cost=1,049,135.01..1,689,936.29 rows=1 width=468) (actual time=16,285.694..16,285.934 rows=8 loops=1)

3. 0.311 16,285.930 ↓ 8.0 8 1

Merge Right Join (cost=1,049,134.74..1,689,929.71 rows=1 width=483) (actual time=16,285.690..16,285.930 rows=8 loops=1)

  • Merge Cond: (hbd.itinerary_id = hp.itinerary_id)
4. 1.903 14,741.117 ↑ 4.9 3,274 1

GroupAggregate (cost=1,041,456.12..1,041,974.04 rows=15,936 width=40) (actual time=14,738.515..14,741.117 rows=3,274 loops=1)

  • Group Key: hbd.itinerary_id
5. 3.360 14,739.214 ↑ 3.8 4,176 1

Sort (cost=1,041,456.12..1,041,495.96 rows=15,936 width=24) (actual time=14,738.504..14,739.214 rows=4,176 loops=1)

  • Sort Key: hbd.itinerary_id
  • Sort Method: quicksort Memory: 570kB
6. 5.737 14,735.854 ↑ 3.3 4,832 1

Nested Loop (cost=44,363.70..1,040,343.79 rows=15,936 width=24) (actual time=1,393.367..14,735.854 rows=4,832 loops=1)

7. 14,194.107 14,714.965 ↑ 2.9 3,788 1

Bitmap Heap Scan on hotel_bookings hb_1 (cost=44,363.27..854,112.42 rows=11,131 width=18) (actual time=1,393.335..14,714.965 rows=3,788 loops=1)

  • Recheck Cond: ((booking_status)::text = 'issued'::text)
  • Filter: ((date(created_date) >= '2020-09-02'::date) AND (date(created_date) <= '2020-09-02'::date))
  • Rows Removed by Filter: 2,242,960
  • Heap Blocks: exact=671,192
8. 520.858 520.858 ↓ 1.0 2,254,038 1

Bitmap Index Scan on hotel_bookings_booking_status_idx (cost=0.00..44,360.48 rows=2,226,140 width=0) (actual time=520.858..520.858 rows=2,254,038 loops=1)

  • Index Cond: ((booking_status)::text = 'issued'::text)
9. 15.152 15.152 ↑ 7.0 1 3,788

Index Scan using hotel_booking_detail_hotel_booking_id_checkin_date_unique on hotel_booking_details hbd (cost=0.43..16.66 rows=7 width=14) (actual time=0.004..0.004 rows=1 loops=3,788)

  • Index Cond: (itinerary_id = hb_1.id)
10. 0.012 1,544.502 ↓ 8.0 8 1

Materialize (cost=7,678.62..647,756.45 rows=1 width=451) (actual time=1,544.308..1,544.502 rows=8 loops=1)

11. 0.007 1,544.490 ↓ 8.0 8 1

Nested Loop Left Join (cost=7,678.62..647,756.45 rows=1 width=451) (actual time=1,544.302..1,544.490 rows=8 loops=1)

12. 0.030 1,544.475 ↓ 8.0 8 1

Nested Loop (cost=7,678.49..647,756.29 rows=1 width=365) (actual time=1,544.296..1,544.475 rows=8 loops=1)

  • Join Filter: (hb.id = hbt.itinerary_id)
13. 0.000 1,544.365 ↓ 8.0 8 1

Nested Loop (cost=7,678.05..647,751.54 rows=1 width=350) (actual time=1,544.261..1,544.365 rows=8 loops=1)

14. 0.053 1,544.293 ↓ 8.0 8 1

Merge Left Join (cost=7,677.63..647,745.03 rows=1 width=152) (actual time=1,544.241..1,544.293 rows=8 loops=1)

  • Merge Cond: (hb.id = hst.itinerary_id)
15. 0.041 49.086 ↓ 8.0 8 1

Sort (cost=7,676.78..7,676.78 rows=1 width=144) (actual time=49.084..49.086 rows=8 loops=1)

  • Sort Key: hb.id
  • Sort Method: quicksort Memory: 27kB
16. 0.014 49.045 ↓ 8.0 8 1

Nested Loop (cost=767.28..7,676.77 rows=1 width=144) (actual time=18.160..49.045 rows=8 loops=1)

17. 0.012 8.537 ↑ 2.0 1 1

Nested Loop (cost=744.29..780.62 rows=2 width=47) (actual time=8.533..8.537 rows=1 loops=1)

18. 0.018 8.503 ↑ 3.0 1 1

Bitmap Heap Scan on hotels h (cost=744.02..755.72 rows=3 width=62) (actual time=8.502..8.503 rows=1 loops=1)

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

Bitmap Index Scan on idx_trigram_hotel (cost=0.00..744.02 rows=3 width=0) (actual time=8.484..8.485 rows=1 loops=1)

  • Index Cond: ((name)::text ~~* '%The Papandayan%'::text)
20. 0.022 0.022 ↑ 1.0 1 1

Index Scan using country_id_pk on countries c (cost=0.27..8.29 rows=1 width=27) (actual time=0.019..0.022 rows=1 loops=1)

  • Index Cond: ((id)::text = (h.country_id)::text)
21. 37.938 40.494 ↓ 4.0 8 1

Bitmap Heap Scan on hotel_bookings hb (cost=22.99..3,448.05 rows=2 width=105) (actual time=9.621..40.494 rows=8 loops=1)

  • Recheck Cond: (hotel_id = h.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,401
  • Heap Blocks: exact=10,126
22. 2.556 2.556 ↓ 12.4 10,860 1

Bitmap Index Scan on hotel_bookings_hotel_id_idx (cost=0.00..22.99 rows=874 width=0) (actual time=2.556..2.556 rows=10,860 loops=1)

  • Index Cond: (hotel_id = h.id)
23. 0.075 1,495.154 ↓ 1.1 250 1

Materialize (cost=0.85..640,067.65 rows=235 width=16) (actual time=1,492.319..1,495.154 rows=250 loops=1)

24. 0.311 1,495.079 ↓ 1.1 250 1

GroupAggregate (cost=0.85..640,064.71 rows=235 width=16) (actual time=1,492.301..1,495.079 rows=250 loops=1)

  • Group Key: hst.itinerary_id
25. 40.664 1,494.768 ↓ 1.1 253 1

Nested Loop (cost=0.85..640,060.60 rows=235 width=16) (actual time=1,492.250..1,494.768 rows=253 loops=1)

26. 413.284 413.284 ↑ 1.1 104,082 1

Index Scan using hotel_softblock_itenary_id_idx on hotel_softblock_transactions hst (cost=0.42..9,646.96 rows=109,413 width=16) (actual time=0.010..413.284 rows=104,082 loops=1)

27. 1,040.820 1,040.820 ↓ 0.0 0 104,082

Index Scan using hotel_bookings_pkey on hotel_bookings hb_2 (cost=0.43..5.75 rows=1 width=8) (actual time=0.010..0.010 rows=0 loops=104,082)

  • Index Cond: (id = hst.itinerary_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: 1
28. 0.072 0.072 ↑ 1.0 1 8

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

  • Index Cond: (itinerary_id = hb.id)
  • Filter: (((payment_method)::text = 'BANK_TRANSFER'::text) AND ((payment_status)::text = 'PENDING'::text))
29. 0.080 0.080 ↑ 1.0 1 8

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

  • Index Cond: (itinerary_id = hp.itinerary_id)
30. 0.008 0.008 ↓ 0.0 0 8

Index Scan using hotel_booking_entities_itinerary_id_idx on hotel_booking_entities e (cost=0.13..0.15 rows=1 width=102) (actual time=0.001..0.001 rows=0 loops=8)

  • Index Cond: (hb.id = itinerary_id)
31. 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)
32. 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)