explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pa7mh

Settings
# exclusive inclusive rows x rows loops node
1. 0.030 6,445.711 ↑ 7.2 8 1

Hash Left Join (cost=1,050,481.42..1,052,501.23 rows=58 width=1,588) (actual time=6,444.677..6,445.711 rows=8 loops=1)

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

CTE hb

3. 5,895.284 6,359.382 ↑ 2.9 3,788 1

Bitmap Heap Scan on hotel_bookings (cost=44,363.29..854,113.51 rows=11,131 width=2,078) (actual time=1,593.509..6,359.382 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,962
  • Heap Blocks: exact=671,193
4. 464.098 464.098 ↓ 1.0 2,254,040 1

Bitmap Index Scan on hotel_bookings_booking_status_idx (cost=0.00..44,360.50 rows=2,226,143 width=0) (actual time=464.098..464.098 rows=2,254,040 loops=1)

  • Index Cond: ((booking_status)::text = 'issued'::text)
5. 0.038 6,445.537 ↑ 7.2 8 1

Hash Left Join (cost=196,346.59..198,365.16 rows=58 width=1,535) (actual time=6,444.507..6,445.537 rows=8 loops=1)

  • Hash Cond: ((e.country_id)::text = (cc.id)::text)
6. 0.330 6,445.314 ↑ 7.2 8 1

Hash Right Join (cost=196,325.26..198,343.04 rows=58 width=1,550) (actual time=6,444.285..6,445.314 rows=8 loops=1)

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

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

  • Group Key: hbd.itinerary_id
8. 5.558 14.492 ↑ 15.9 4,832 1

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

9. 1.358 1.358 ↑ 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.358 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.017 6,427.475 ↑ 7.2 8 1

Hash (cost=8,525.37..8,525.37 rows=58 width=1,518) (actual time=6,427.475..6,427.475 rows=8 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
12. 0.053 6,427.458 ↑ 7.2 8 1

Hash Right Join (cost=8,218.45..8,525.37 rows=58 width=1,518) (actual time=6,427.453..6,427.458 rows=8 loops=1)

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

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

  • Group Key: hst.itinerary_id
14. 1.856 55.265 ↑ 41.5 281 1

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

  • Hash Cond: (hb_2.id = hst.itinerary_id)
15. 1.276 1.276 ↑ 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.276 rows=3,788 loops=1)

16. 18.646 52.133 ↑ 1.0 106,752 1

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

  • Buckets: 131,072 Batches: 1 Memory Usage: 6,028kB
17. 33.487 33.487 ↑ 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.010..33.487 rows=109,408 loops=1)

18. 0.047 6,371.862 ↓ 8.0 8 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
19. 0.014 6,371.815 ↓ 8.0 8 1

Nested Loop Left Join (cost=757.02..1,036.44 rows=1 width=1,518) (actual time=2,058.829..6,371.815 rows=8 loops=1)

20. 0.032 6,371.777 ↓ 8.0 8 1

Nested Loop (cost=756.89..1,036.28 rows=1 width=1,424) (actual time=2,058.819..6,371.777 rows=8 loops=1)

21. 0.018 6,371.649 ↓ 8.0 8 1

Nested Loop (cost=756.61..1,027.98 rows=1 width=1,439) (actual time=2,058.789..6,371.649 rows=8 loops=1)

  • Join Filter: (hb.id = hp.itinerary_id)
22. 0.028 6,371.511 ↓ 8.0 8 1

Nested Loop (cost=756.19..1,027.46 rows=1 width=1,249) (actual time=2,058.736..6,371.511 rows=8 loops=1)

23. 1.331 6,371.379 ↓ 8.0 8 1

Hash Join (cost=755.75..1,020.13 rows=1 width=1,226) (actual time=2,058.702..6,371.379 rows=8 loops=1)

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

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

25. 0.008 3.071 ↑ 3.0 1 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
26. 0.035 3.063 ↑ 3.0 1 1

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

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

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

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

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

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

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

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

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

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

34. 0.095 0.144 ↑ 1.0 459 1

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

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

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