explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WjNw

Settings
# exclusive inclusive rows x rows loops node
1. 0.032 20,377.435 ↑ 7.2 8 1

Hash Left Join (cost=1,050,464.70..1,052,484.51 rows=58 width=1,588) (actual time=20,376.105..20,377.435 rows=8 loops=1)

  • Hash Cond: ((e.country_pair_id)::text = (cp.id)::text)
2.          

CTE hb

3. 19,826.878 20,223.472 ↑ 2.9 3,788 1

Bitmap Heap Scan on hotel_bookings hb_3 (cost=44,363.11..854,104.79 rows=11,131 width=2,078) (actual time=1,319.412..20,223.472 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,944
  • Heap Blocks: exact=671,187
4. 396.594 396.594 ↓ 1.0 2,254,021 1

Bitmap Index Scan on hotel_bookings_booking_status_idx (cost=0.00..44,360.33 rows=2,226,119 width=0) (actual time=396.594..396.594 rows=2,254,021 loops=1)

  • Index Cond: ((booking_status)::text = 'issued'::text)
5. 0.035 20,377.241 ↑ 7.2 8 1

Hash Left Join (cost=196,338.59..198,357.16 rows=58 width=1,535) (actual time=20,375.916..20,377.241 rows=8 loops=1)

  • Hash Cond: ((e.country_id)::text = (cc.id)::text)
6. 0.298 20,376.960 ↑ 7.2 8 1

Hash Right Join (cost=196,317.26..198,335.04 rows=58 width=1,550) (actual time=20,375.636..20,376.960 rows=8 loops=1)

  • Hash Cond: (hbd.itinerary_id = hp.itinerary_id)
7. 3.694 54.771 ↑ 20.3 3,788 1

HashAggregate (cost=187,799.17..188,760.00 rows=76,867 width=40) (actual time=53.653..54.771 rows=3,788 loops=1)

  • Group Key: hbd.itinerary_id
8. 4.451 51.077 ↑ 15.9 4,832 1

Nested Loop (cost=0.43..186,453.99 rows=76,867 width=31) (actual time=0.023..51.077 rows=4,832 loops=1)

9. 1.170 1.170 ↑ 2.9 3,788 1

CTE Scan on hb hb_1 (cost=0.00..222.62 rows=11,131 width=25) (actual time=0.001..1.170 rows=3,788 loops=1)

10. 45.456 45.456 ↑ 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.010..0.012 rows=1 loops=3,788)

  • Index Cond: (itinerary_id = hb_1.id)
11. 0.016 20,321.891 ↑ 7.2 8 1

Hash (cost=8,517.37..8,517.37 rows=58 width=1,518) (actual time=20,321.890..20,321.891 rows=8 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
12. 0.044 20,321.875 ↑ 7.2 8 1

Hash Right Join (cost=8,210.45..8,517.37 rows=58 width=1,518) (actual time=20,321.871..20,321.875 rows=8 loops=1)

  • Hash Cond: (hst.itinerary_id = hb.id)
13. 0.353 88.147 ↑ 41.8 279 1

HashAggregate (cost=7,182.00..7,327.87 rows=11,670 width=16) (actual time=88.065..88.147 rows=279 loops=1)

  • Group Key: hst.itinerary_id
14. 2.056 87.794 ↑ 41.5 281 1

Hash Join (cost=6,547.79..7,123.65 rows=11,670 width=16) (actual time=85.623..87.794 rows=281 loops=1)

  • Hash Cond: (hb_2.id = hst.itinerary_id)
15. 1.152 1.152 ↑ 2.9 3,788 1

CTE Scan on hb hb_2 (cost=0.00..222.62 rows=11,131 width=8) (actual time=0.001..1.152 rows=3,788 loops=1)

16. 21.176 84.586 ↑ 1.0 106,751 1

Hash (cost=5,180.13..5,180.13 rows=109,413 width=16) (actual time=84.586..84.586 rows=106,751 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 6,028kB
17. 63.410 63.410 ↑ 1.0 109,407 1

Seq Scan on hotel_softblock_transactions hst (cost=0.00..5,180.13 rows=109,413 width=16) (actual time=0.010..63.410 rows=109,407 loops=1)

18. 0.038 20,233.684 ↓ 8.0 8 1

Hash (cost=1,028.44..1,028.44 rows=1 width=1,518) (actual time=20,233.684..20,233.684 rows=8 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
19. 0.019 20,233.646 ↓ 8.0 8 1

Nested Loop Left Join (cost=749.02..1,028.44 rows=1 width=1,518) (actual time=2,692.601..20,233.646 rows=8 loops=1)

20. 0.013 20,233.611 ↓ 8.0 8 1

Nested Loop (cost=748.89..1,028.28 rows=1 width=1,424) (actual time=2,692.596..20,233.611 rows=8 loops=1)

21. 0.022 20,233.526 ↓ 8.0 8 1

Nested Loop (cost=748.61..1,019.98 rows=1 width=1,439) (actual time=2,692.583..20,233.526 rows=8 loops=1)

  • Join Filter: (hb.id = hp.itinerary_id)
22. 0.036 20,233.408 ↓ 8.0 8 1

Nested Loop (cost=748.19..1,019.46 rows=1 width=1,249) (actual time=2,692.564..20,233.408 rows=8 loops=1)

23. 1.444 20,233.300 ↓ 8.0 8 1

Hash Join (cost=747.75..1,012.13 rows=1 width=1,226) (actual time=2,692.546..20,233.300 rows=8 loops=1)

  • Hash Cond: (hb.hotel_id = h.id)
24. 20,229.069 20,229.069 ↑ 2.9 3,788 1

CTE Scan on hb (cost=0.00..222.62 rows=11,131 width=1,172) (actual time=1,319.417..20,229.069 rows=3,788 loops=1)

25. 0.006 2.787 ↑ 3.0 1 1

Hash (cost=747.72..747.72 rows=3 width=62) (actual time=2.787..2.787 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
26. 0.019 2.781 ↑ 3.0 1 1

Bitmap Heap Scan on hotels h (cost=736.02..747.72 rows=3 width=62) (actual time=2.780..2.781 rows=1 loops=1)

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

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

  • Index Cond: ((name)::text ~~* '%The Papandayan%'::text)
28. 0.072 0.072 ↑ 1.0 1 8

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

  • Index Cond: (itinerary_id = hb.id)
29. 0.096 0.096 ↑ 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.012..0.012 rows=1 loops=8)

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

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

  • Index Cond: ((id)::text = (h.country_id)::text)
31. 0.016 0.016 ↓ 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.002..0.002 rows=0 loops=8)

  • Index Cond: (hb.id = itinerary_id)
32. 0.153 0.246 ↑ 1.0 459 1

Hash (cost=15.59..15.59 rows=459 width=27) (actual time=0.245..0.246 rows=459 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 35kB
33. 0.093 0.093 ↑ 1.0 459 1

Seq Scan on countries cc (cost=0.00..15.59 rows=459 width=27) (actual time=0.007..0.093 rows=459 loops=1)

34. 0.105 0.162 ↑ 1.0 459 1

Hash (cost=15.59..15.59 rows=459 width=27) (actual time=0.162..0.162 rows=459 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 35kB
35. 0.057 0.057 ↑ 1.0 459 1

Seq Scan on countries cp (cost=0.00..15.59 rows=459 width=27) (actual time=0.003..0.057 rows=459 loops=1)