explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IP5V

Settings
# exclusive inclusive rows x rows loops node
1. 0.017 3,264.702 ↑ 7.2 8 1

Hash Left Join (cost=1,050,830.80..1,052,851.16 rows=58 width=1,588) (actual time=3,263.718..3,264.702 rows=8 loops=1)

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

CTE hb

3. 2,665.775 3,192.434 ↑ 2.9 3,788 1

Bitmap Heap Scan on hotel_bookings (cost=44,371.49..854,325.31 rows=11,134 width=2,078) (actual time=1,127.545..3,192.434 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,409
  • Heap Blocks: exact=671,385
4. 526.659 526.659 ↓ 1.0 2,254,508 1

Bitmap Index Scan on hotel_bookings_booking_status_idx (cost=0.00..44,368.71 rows=2,226,703 width=0) (actual time=526.659..526.659 rows=2,254,508 loops=1)

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

Hash Left Join (cost=196,484.17..198,503.30 rows=58 width=1,535) (actual time=3,263.557..3,264.536 rows=8 loops=1)

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

Hash Right Join (cost=196,462.84..198,481.17 rows=58 width=1,550) (actual time=3,263.355..3,264.331 rows=8 loops=1)

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

HashAggregate (cost=187,846.82..188,807.92 rows=76,888 width=40) (actual time=14.989..15.744 rows=3,788 loops=1)

  • Group Key: hbd.itinerary_id
8. 3.946 12.702 ↑ 15.9 4,832 1

Nested Loop (cost=0.43..186,501.28 rows=76,888 width=31) (actual time=0.021..12.702 rows=4,832 loops=1)

9. 1.180 1.180 ↑ 2.9 3,788 1

CTE Scan on hb hb_1 (cost=0.00..222.68 rows=11,134 width=25) (actual time=0.001..1.180 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,248.294 ↑ 7.2 8 1

Hash (cost=8,615.30..8,615.30 rows=58 width=1,518) (actual time=3,248.294..3,248.294 rows=8 loops=1)

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

Hash Right Join (cost=8,308.30..8,615.30 rows=58 width=1,518) (actual time=3,248.274..3,248.280 rows=8 loops=1)

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

HashAggregate (cost=7,183.77..7,329.68 rows=11,673 width=16) (actual time=49.220..49.321 rows=279 loops=1)

  • Group Key: hst.itinerary_id
14. 1.530 49.002 ↑ 41.5 281 1

Hash Join (cost=6,549.40..7,125.41 rows=11,673 width=16) (actual time=46.988..49.002 rows=281 loops=1)

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

CTE Scan on hb hb_2 (cost=0.00..222.68 rows=11,134 width=8) (actual time=0.001..1.131 rows=3,788 loops=1)

16. 15.899 46.341 ↑ 1.0 106,778 1

Hash (cost=5,181.40..5,181.40 rows=109,440 width=16) (actual time=46.341..46.341 rows=106,778 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 6,030kB
17. 30.442 30.442 ↑ 1.0 109,434 1

Seq Scan on hotel_softblock_transactions hst (cost=0.00..5,181.40 rows=109,440 width=16) (actual time=0.010..30.442 rows=109,434 loops=1)

18. 0.017 3,198.928 ↓ 8.0 8 1

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

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

Nested Loop Left Join (cost=845.02..1,124.51 rows=1 width=1,518) (actual time=1,287.999..3,198.911 rows=8 loops=1)

20. 0.008 3,198.872 ↓ 8.0 8 1

Nested Loop (cost=844.89..1,124.35 rows=1 width=1,424) (actual time=1,287.994..3,198.872 rows=8 loops=1)

21. 0.017 3,198.784 ↓ 8.0 8 1

Nested Loop (cost=844.61..1,116.05 rows=1 width=1,439) (actual time=1,287.980..3,198.784 rows=8 loops=1)

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

Nested Loop (cost=844.19..1,115.53 rows=1 width=1,249) (actual time=1,287.963..3,198.711 rows=8 loops=1)

23. 1.005 3,198.639 ↓ 8.0 8 1

Hash Join (cost=843.75..1,108.20 rows=1 width=1,226) (actual time=1,287.946..3,198.639 rows=8 loops=1)

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

CTE Scan on hb (cost=0.00..222.68 rows=11,134 width=1,172) (actual time=1,127.549..3,194.463 rows=3,788 loops=1)

25. 0.002 3.171 ↑ 3.0 1 1

Hash (cost=843.72..843.72 rows=3 width=62) (actual time=3.171..3.171 rows=1 loops=1)

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

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

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

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

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

  • Index Cond: (itinerary_id = hb.id)
29. 0.056 0.056 ↑ 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.007..0.007 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.080 0.080 ↑ 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.010 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.092 0.189 ↑ 1.0 459 1

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

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

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

34. 0.082 0.149 ↑ 1.0 459 1

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

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

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