explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iJi8

Settings
# exclusive inclusive rows x rows loops node
1. 0.055 6,516.243 ↑ 7.2 8 1

Hash Left Join (cost=1,050,479.24..1,052,499.05 rows=58 width=1,588) (actual time=6,515.206..6,516.243 rows=8 loops=1)

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

CTE hb

3. 5,880.498 6,430.873 ↑ 2.9 3,788 1

Bitmap Heap Scan on hotel_bookings (cost=44,363.24..854,111.33 rows=11,131 width=2,078) (actual time=1,834.459..6,430.873 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,954
  • Heap Blocks: exact=671,189
4. 550.375 550.375 ↓ 1.0 2,254,031 1

Bitmap Index Scan on hotel_bookings_booking_status_idx (cost=0.00..44,360.46 rows=2,226,137 width=0) (actual time=550.375..550.375 rows=2,254,031 loops=1)

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

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

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

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

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

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

  • Group Key: hbd.itinerary_id
8. 5.061 13.858 ↑ 15.9 4,832 1

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

9. 1.221 1.221 ↑ 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.221 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 6,498.419 ↑ 7.2 8 1

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

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

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

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

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

  • Group Key: hst.itinerary_id
14. 1.559 53.882 ↑ 41.5 281 1

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

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

16. 18.037 51.181 ↑ 1.0 106,752 1

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

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

18. 0.032 6,444.160 ↓ 8.0 8 1

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

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

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

20. 0.027 6,444.090 ↓ 8.0 8 1

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

21. 0.017 6,443.991 ↓ 8.0 8 1

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

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

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

23. 1.116 6,443.740 ↓ 8.0 8 1

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

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

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

25. 0.009 5.283 ↑ 3.0 1 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
26. 0.031 5.274 ↑ 3.0 1 1

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

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

Bitmap Index Scan on idx_trigram_hotel (cost=0.00..744.02 rows=3 width=0) (actual time=5.243..5.243 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.104 0.104 ↑ 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.013 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.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.085 0.190 ↑ 1.0 459 1

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

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

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

34. 0.097 0.155 ↑ 1.0 459 1

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

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

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