explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qGEW

Settings
# exclusive inclusive rows x rows loops node
1. 0.054 3,610.629 ↑ 7.2 8 1

Hash Left Join (cost=1,050,483.60..1,052,503.41 rows=58 width=1,588) (actual time=3,609.047..3,610.629 rows=8 loops=1)

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

CTE hb

3. 3,103.694 3,531.472 ↑ 2.9 3,788 1

Bitmap Heap Scan on hotel_bookings hb_3 (cost=44,363.33..854,115.69 rows=11,131 width=2,078) (actual time=1,040.770..3,531.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,968
  • Heap Blocks: exact=671,194
4. 427.778 427.778 ↓ 1.0 2,254,046 1

Bitmap Index Scan on hotel_bookings_booking_status_idx (cost=0.00..44,360.55 rows=2,226,149 width=0) (actual time=427.778..427.778 rows=2,254,046 loops=1)

  • Index Cond: ((booking_status)::text = 'issued'::text)
5. 0.050 3,610.341 ↑ 7.2 8 1

Hash Left Join (cost=196,346.59..198,365.16 rows=58 width=1,535) (actual time=3,608.772..3,610.341 rows=8 loops=1)

  • Hash Cond: ((e.country_id)::text = (cc.id)::text)
6. 0.476 3,610.069 ↑ 7.2 8 1

Hash Right Join (cost=196,325.26..198,343.04 rows=58 width=1,550) (actual time=3,608.503..3,610.069 rows=8 loops=1)

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

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

  • Group Key: hbd.itinerary_id
8. 3.890 12.700 ↑ 15.9 4,832 1

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

9. 1.234 1.234 ↑ 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.234 rows=3,788 loops=1)

10. 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)
11. 0.014 3,593.395 ↑ 7.2 8 1

Hash (cost=8,525.37..8,525.37 rows=58 width=1,518) (actual time=3,593.394..3,593.395 rows=8 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
12. 0.057 3,593.381 ↑ 7.2 8 1

Hash Right Join (cost=8,218.45..8,525.37 rows=58 width=1,518) (actual time=3,593.375..3,593.381 rows=8 loops=1)

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

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

  • Group Key: hst.itinerary_id
14. 1.483 51.547 ↑ 41.5 281 1

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

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

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

16. 17.834 48.902 ↑ 1.0 106,752 1

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

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

Seq Scan on hotel_softblock_transactions hst (cost=0.00..5,180.13 rows=109,413 width=16) (actual time=0.011..31.068 rows=109,408 loops=1)

18. 0.027 3,541.484 ↓ 8.0 8 1

Hash (cost=1,036.44..1,036.44 rows=1 width=1,518) (actual time=3,541.484..3,541.484 rows=8 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
19. 0.022 3,541.457 ↓ 8.0 8 1

Nested Loop Left Join (cost=757.02..1,036.44 rows=1 width=1,518) (actual time=1,224.629..3,541.457 rows=8 loops=1)

20. 0.007 3,541.403 ↓ 8.0 8 1

Nested Loop (cost=756.89..1,036.28 rows=1 width=1,424) (actual time=1,224.601..3,541.403 rows=8 loops=1)

21. 0.020 3,541.324 ↓ 8.0 8 1

Nested Loop (cost=756.61..1,027.98 rows=1 width=1,439) (actual time=1,224.582..3,541.324 rows=8 loops=1)

  • Join Filter: (hb.id = hp.itinerary_id)
22. 0.020 3,541.216 ↓ 8.0 8 1

Nested Loop (cost=756.19..1,027.46 rows=1 width=1,249) (actual time=1,224.534..3,541.216 rows=8 loops=1)

23. 0.836 3,541.108 ↓ 8.0 8 1

Hash Join (cost=755.75..1,020.13 rows=1 width=1,226) (actual time=1,224.504..3,541.108 rows=8 loops=1)

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

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

25. 0.012 3.046 ↑ 3.0 1 1

Hash (cost=755.72..755.72 rows=3 width=62) (actual time=3.045..3.046 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
26. 0.032 3.034 ↑ 3.0 1 1

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

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

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

  • Index Cond: ((name)::text ~~* '%The Papandayan%'::text)
28. 0.088 0.088 ↑ 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.011..0.011 rows=1 loops=8)

  • Index Cond: (itinerary_id = hb.id)
29. 0.088 0.088 ↑ 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.011..0.011 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.032 0.032 ↓ 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.004..0.004 rows=0 loops=8)

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

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

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

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

34. 0.129 0.234 ↑ 1.0 459 1

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

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

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