explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NPIA

Settings
# exclusive inclusive rows x rows loops node
1. 0.014 3,419.735 ↓ 8.0 8 1

Nested Loop Left Join (cost=1,049,466.34..1,690,489.51 rows=1 width=521) (actual time=3,419.578..3,419.735 rows=8 loops=1)

2. 0.002 3,419.721 ↓ 8.0 8 1

Nested Loop Left Join (cost=1,049,466.07..1,690,482.92 rows=1 width=468) (actual time=3,419.569..3,419.721 rows=8 loops=1)

3. 0.230 3,419.711 ↓ 8.0 8 1

Merge Right Join (cost=1,049,465.79..1,690,476.34 rows=1 width=483) (actual time=3,419.567..3,419.711 rows=8 loops=1)

  • Merge Cond: (hbd.itinerary_id = hp.itinerary_id)
4. 1.154 2,984.863 ↑ 4.9 3,274 1

GroupAggregate (cost=1,041,699.15..1,042,217.17 rows=15,939 width=40) (actual time=2,983.340..2,984.863 rows=3,274 loops=1)

  • Group Key: hbd.itinerary_id
5. 1.926 2,983.709 ↑ 3.8 4,176 1

Sort (cost=1,041,699.15..1,041,739.00 rows=15,939 width=24) (actual time=2,983.331..2,983.709 rows=4,176 loops=1)

  • Sort Key: hbd.itinerary_id
  • Sort Method: quicksort Memory: 570kB
6. 4.813 2,981.783 ↑ 3.3 4,832 1

Nested Loop (cost=44,371.81..1,040,586.59 rows=15,939 width=24) (actual time=952.263..2,981.783 rows=4,832 loops=1)

7. 2,568.772 2,969.394 ↑ 2.9 3,788 1

Bitmap Heap Scan on hotel_bookings hb_1 (cost=44,371.38..854,319.86 rows=11,133 width=18) (actual time=952.247..2,969.394 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,243,396
  • Heap Blocks: exact=671,381
8. 400.622 400.622 ↓ 1.0 2,254,494 1

Bitmap Index Scan on hotel_bookings_booking_status_idx (cost=0.00..44,368.59 rows=2,226,688 width=0) (actual time=400.622..400.622 rows=2,254,494 loops=1)

  • Index Cond: ((booking_status)::text = 'issued'::text)
9. 7.576 7.576 ↑ 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.002..0.002 rows=1 loops=3,788)

  • Index Cond: (itinerary_id = hb_1.id)
10. 0.010 434.618 ↓ 8.0 8 1

Materialize (cost=7,766.64..648,059.91 rows=1 width=451) (actual time=434.515..434.618 rows=8 loops=1)

11. 0.004 434.608 ↓ 8.0 8 1

Nested Loop Left Join (cost=7,766.64..648,059.91 rows=1 width=451) (actual time=434.511..434.608 rows=8 loops=1)

12. 0.033 434.596 ↓ 8.0 8 1

Nested Loop (cost=7,766.51..648,059.75 rows=1 width=365) (actual time=434.505..434.596 rows=8 loops=1)

  • Join Filter: (hb.id = hbt.itinerary_id)
13. 0.003 434.539 ↓ 8.0 8 1

Nested Loop (cost=7,766.08..648,055.00 rows=1 width=350) (actual time=434.474..434.539 rows=8 loops=1)

14. 0.046 434.496 ↓ 8.0 8 1

Merge Left Join (cost=7,765.65..648,048.49 rows=1 width=152) (actual time=434.459..434.496 rows=8 loops=1)

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

Sort (cost=7,764.80..7,764.80 rows=1 width=144) (actual time=21.429..21.430 rows=8 loops=1)

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

Nested Loop (cost=855.28..7,764.79 rows=1 width=144) (actual time=8.337..21.409 rows=8 loops=1)

17. 0.008 3.101 ↑ 2.0 1 1

Nested Loop (cost=832.29..868.62 rows=2 width=47) (actual time=3.096..3.101 rows=1 loops=1)

18. 0.018 3.079 ↑ 3.0 1 1

Bitmap Heap Scan on hotels h (cost=832.02..843.72 rows=3 width=62) (actual time=3.078..3.079 rows=1 loops=1)

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

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

  • Index Cond: ((name)::text ~~* '%The Papandayan%'::text)
20. 0.014 0.014 ↑ 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.012..0.014 rows=1 loops=1)

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

Bitmap Heap Scan on hotel_bookings hb (cost=22.99..3,448.07 rows=2 width=105) (actual time=5.233..18.296 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. 1.555 1.555 ↓ 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=1.555..1.555 rows=10,860 loops=1)

  • Index Cond: (hotel_id = h.id)
23. 0.051 413.020 ↓ 1.1 250 1

Materialize (cost=0.85..640,283.09 rows=235 width=16) (actual time=411.529..413.020 rows=250 loops=1)

24. 0.183 412.969 ↓ 1.1 250 1

GroupAggregate (cost=0.85..640,280.15 rows=235 width=16) (actual time=411.524..412.969 rows=250 loops=1)

  • Group Key: hst.itinerary_id
25. 62.332 412.786 ↓ 1.1 253 1

Nested Loop (cost=0.85..640,276.04 rows=235 width=16) (actual time=411.487..412.786 rows=253 loops=1)

26. 38.208 38.208 ↑ 1.1 104,082 1

Index Scan using hotel_softblock_itenary_id_idx on hotel_softblock_transactions hst (cost=0.42..9,649.64 rows=109,440 width=16) (actual time=0.009..38.208 rows=104,082 loops=1)

27. 312.246 312.246 ↓ 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.003..0.003 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.040 0.040 ↑ 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.005..0.005 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.024 0.024 ↑ 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.003..0.003 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.008 0.008 ↓ 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.001 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)