explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ssHa

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 1.029 ↓ 0.0 0 1

Unique (cost=79.36..85.81 rows=5 width=25,668) (actual time=1.029..1.029 rows=0 loops=1)

2. 0.597 1.029 ↓ 0.0 0 1

Sort (cost=79.36..79.37 rows=5 width=25,668) (actual time=1.029..1.029 rows=0 loops=1)

  • Sort Key: boats_thread.last_message_created_at DESC, boats_thread.id DESC, boats_thread.from_profile_id, boats_thread.to_profile_id, boats_thread.participants_ids, boats_thread.parent_id, boats_thread.creation_date, boats_thread.is_active, boats_thread.is_archived, boats_thread.sms_channel_id, boats_thread.boat_id, boats_thread.start_date, boats_thread.end_date, boats_thread.dates_are_flexible, boats_thread.person_count, boats_thread.child_count, boats_thread.start_hour, boats_thread.end_hour, boats_thread.duration, boats_thread.booking_id, boats_thread.special_offer_id, boats_thread.event_id, boats_thread.status, boats_thread.message_overall_count, boats_thread.last_message_date, boats_thread.first_message_date, boats_thread.flagged_message_count, boats_thread.unread_message_count, boats_thread.last_message_id, boats_thread.last_approved_message_id, boats_thread.last_message_sender_id, boats_profile.staff_comments, boats_profile.staff_comments_last_updated, boats_profile.user_id, boats_profile.anonymous_profile_id, boats_profile.name_first, boats_profile.name_middle, boats_profile.name_last, boats_profile.gender, boats_profile.birth_date, boats_profile.address_id, boats_profile.address_captain_id, boats_profile.address_billing_id, boats_profile.contact_mobile, boats_profile.contact_mobile_check_timestamp, boats_profile.contact_mobile_check_secret, boats_profile.contact_mobile_is_verified, boats_profile.contact_email, boats_profile.description, boats_profile.tos_acceptance_date, boats_profile.tos_acceptance_ip, boats_profile.signup_tos_acceptance_date, boats_profile.hear_about_us, boats_profile.hear_about_us_representative, boats_profile.hear_about_us_other, boats_profile.hear_about_us_name, boats_profile.promo_code, boats_profile.promo_referrer, boats_profile.extra_signup_message, boats_profile.referral_code, boats_profile.credit_balance, boats_profile.credit_earned, boats_profile.credit_spent, boats_profile.credit_pending, boats_profile.is_captain, boats_profile.captain_headline, boats_profile.captain_description, boats_profile.captain_experience, boats_profile.captain_last_calendar_update, boats_profile.license_name_first, boats_profile.license_name_last, boats_profile.license_type, boats_profile.license_other_name, boats_profile.license_issue_date, boats_profile.license_exp_date, boats_profile.license_document_no, boats_profile.license_reference_no, boats_profile.license_scan, boats_profile.license_asa_school, boats_profile.license_asa_level, boats_profile.license_asa_id, boats_profile.license_ussbc_level, boats_profile.license_ussbc_id, boats_profile.captain_q1, boats_profile.captain_q2, boats_profile.captain_q3, boats_profile.captain_tb_power_loa, boats_profile.captain_tb_tp_a, boats_profile.captain_tb_tp_b, boats_profile.captain_tb_tp_c, boats_profile.captain_tb_tp_d, boats_profile.captain_tb_tp_e, boats_profile.captain_tb_tp_f, boats_profile.captain_tb_tp_g, boats_profile.captain_tb_tp_h, boats_profile.captain_tb_sail_loa, boats_profile.captain_tb_type_sale_a, boats_profile.captain_tb_type_sale_b, boats_profile.captain_tb_type_sale_c, boats_profile.captain_tb_type_sale_d, boats_profile.captain_tb_type_sale_e, boats_profile.captain_map_text, boats_profile.captain_map_lat, boats_profile.captain_map_long, boats_profile.captain_map_radius, boats_profile.captain_services_renter_other, boats_profile.captain_services_owner_other, boats_profile.captain_languages_other, boats_profile.has_saved_info, boats_profile.has_saved_services, boats_profile.has_saved_license, boats_profile.transfer_account_type, boats_profile.transfer_payouts_enabled, boats_profile.transfer_business_name, boats_profile.transfer_business_tax_id, boats_profile.transfer_business_email, boats_profile.transfer_business_phone, boats_profile.transfer_business_url, boats_profile.transfer_business_address_id, boats_profile.transfer_representative_id, boats_profile.transfer_are_additional_owners_required, boats_profile.transfer_are_directors_required, boats_profile.transfer_are_all_owners_provided, boats_profile.transfer_are_all_directors_provided, boats_profile.transfer_bank_account_country, boats_profile.transfer_bank_account_city, boats_profile.transfer_bank_name, boats_profile.transfer_bank_account_type, boats_profile.transfer_bank_account_currency, boats_profile.transfer_us_routing_number, boats_profile.transfer_us_account_number, boats_profile.transfer_intl_account_swift, boats_profile.transfer_intl_account_iban, boats_profile.transfer_other_account_bic, boats_profile.transfer_other_account_number, boats_profile.transfer_other_account_details, boats_profile.currency, boats_profile.is_bareboat_captain, boats_profile.source, boats_profile.staff_rating, boats_profile.flag_policy, boats_profile.flag_policy_last_change, boats_profile.response_time, boats_profile.response_rate, t4.staff_comments, t4.staff_comments_last_updated, t4.user_id, t4.anonymous_profile_id, t4.name_first, t4.name_middle, t4.name_last, t4.gender, t4.birth_date, t4.address_id, t4.address_captain_id, t4.address_billing_id, t4.contact_mobile, t4.contact_mobile_check_timestamp, t4.contact_mobile_check_secret, t4.contact_mobile_is_verified, t4.contact_email, t4.description, t4.tos_acceptance_date, t4.tos_acceptance_ip, t4.signup_tos_acceptance_date, t4.hear_about_us, t4.hear_about_us_representative, t4.hear_about_us_other, t4.hear_about_us_name, t4.promo_code, t4.promo_referrer, t4.extra_signup_message, t4.referral_code, t4.credit_balance, t4.credit_earned, t4.credit_spent, t4.credit_pending, t4.is_captain, t4.captain_headline, t4.captain_description, t4.captain_experience, t4.captain_last_calendar_update, t4.license_name_first, t4.license_name_last, t4.license_type, t4.license_other_name, t4.license_issue_date, t4.license_exp_date, t4.license_document_no, t4.license_reference_no, t4.license_scan, t4.license_asa_school, t4.license_asa_level, t4.license_asa_id, t4.license_ussbc_level, t4.license_ussbc_id, t4.captain_q1, t4.captain_q2, t4.captain_q3, t4.captain_tb_power_loa, t4.captain_tb_tp_a, t4.captain_tb_tp_b, t4.captain_tb_tp_c, t4.captain_tb_tp_d, t4.captain_tb_tp_e, t4.captain_tb_tp_f, t4.captain_tb_tp_g, t4.captain_tb_tp_h, t4.captain_tb_sail_loa, t4.captain_tb_type_sale_a, t4.captain_tb_type_sale_b, t4.captain_tb_type_sale_c, t4.captain_tb_type_sale_d, t4.captain_tb_type_sale_e, t4.captain_map_text, t4.captain_map_lat, t4.captain_map_long, t4.captain_map_radius, t4.captain_services_renter_other, t4.captain_services_owner_other, t4.captain_languages_other, t4.has_saved_info, t4.has_saved_services, t4.has_saved_license, t4.transfer_account_type, t4.transfer_payouts_enabled, t4.transfer_business_name, t4.transfer_business_tax_id, t4.transfer_business_email, t4.transfer_business_phone, t4.transfer_business_url, t4.transfer_business_address_id, t4.transfer_representative_id, t4.transfer_are_additional_owners_required, t4.transfer_are_directors_required, t4.transfer_are_all_owners_provided, t4.transfer_are_all_directors_provided, t4.transfer_bank_account_country, t4.transfer_bank_account_city, t4.transfer_bank_name, t4.transfer_bank_account_type, t4.transfer_bank_account_currency, t4.transfer_us_routing_number, t4.transfer_us_account_number, t4.transfer_intl_account_swift, t4.transfer_intl_account_iban, t4.transfer_other_account_bic, t4.transfer_other_account_number, t4.transfer_other_account_details, t4.currency, t4.is_bareboat_captain, t4.source, t4.staff_rating, t4.flag_policy, t4.flag_policy_last_change, t4.response_time, t4.response_rate, boats_boat.id, boats_boat.staff_comments, boats_boat.staff_comments_last_updated, boats_boat.amenities, boats_boat.boat_name, boats_boat.name, boats_boat.description, boats_boat.created, boats_boat.submitted_at, boats_boat.externalapi_id, boats_boat.remote_id, boats_boat.remote_provider, boats_boat.remote_status, boats_boat.is_imported, boats_boat.is_ghost, boats_boat.is_alias, boats_boat.alias_of_id, boats_boat.boat_type, boats_boat.boat_category, boats_boat.builder, boats_boat.model, boats_boat.build_year, boats_boat.length, boats_boat.guest_capacity, boats_boat.is_sailo_verified, boats_boat.affiliates_sub_id, boats_boat.affiliates_click_id, boats_boat.location_type, boats_boat.registration_address_id, boats_boat.marina_id, boats_boat.map_lat, boats_boat.map_long, boats_boat.location, boats_boat.pickup_instructions, boats_boat.region, boats_boat.location_country, boats_boat.location_state, boats_boat.location_city, boats_boat.cabins_no, boats_boat.bathrooms_no, boats_boat.single_beds_no, boats_boat.douuble_beds_no, boats_boat.fuel_policy, boats_boat.security_deposit, boats_boat.security_deposit_payment_time, boats_boat.currency, boats_boat.sailo_owner_percent, boats_boat.sailo_renter_percent, boats_boat.owner_insurance_address_id, boats_boat.is_charter_insured, boats_boat.engine_type, boats_boat.hull_material, boats_boat.mast_material, boats_boat.insurance_policy_company, boats_boat.insurance_policy_holder, boats_boat.insurance_policy_no, boats_boat.gross_tonnage, boats_boat.insured_value, boats_boat.engine_number, boats_boat.engine_hp, boats_boat.fuel_economy, boats_boat.boat_survey_file, boats_boat.boat_survey_date, boats_boat.boat_status, boats_boat.owner_id, boats_boat.original_owner_id, boats_boat.featured_status, boats_boat.last_published_date, boats_boat.last_unpublished_date, boats_boat.has_changed_has_own_crew, boats_boat.has_own_crew, boats_boat.crew_description, boats_boat.crew, boats_boat.charter_name, boats_boat.charter_url, boats_boat.charter_representative_name, boats_boat.charter_representative_email, boats_boat.charter_representative_phone, boats_boat.updated_at, boats_boat.has_saved_info, boats_boat.has_saved_location, boats_boat.has_saved_features, boats_boat.has_saved_insurance, boats_boat.last_calendar_update, boats_boat.score, boats_boat.percent_complete, boats_boat.min_price_per_day, boats_boat.min_price_per_half_day, boats_boat.min_price_per_quarter_day, boats_boat.min_price_per_week, boats_boat.max_discount_per_day, boats_boat.max_discount_per_half_day, boats_boat.max_discount_per_quarter_day, boats_boat.max_discount_per_week, boats_boat.min_sale_price_per_day, boats_boat.min_sale_price_per_half_day, boats_boat.min_sale_price_per_quarter_day, boats_boat.min_sale_price_per_week, boats_boat.date_of_min_price_per_day, boats_boat.date_of_min_price_per_half_day, boats_boat.date_of_min_price_per_quarter_day, boats_boat.date_of_min_price_per_week, boats_boat.min_abs_price, boats_boat.max_abs_discount, boats_boat.min_abs_sale_price, boats_boat.min_abs_price_duration, boats_boat.date_of_min_abs_price, boats_boat.has_simplified_pricing, boats_boat.default_price_per_day, boats_boat.default_price_per_half_day, boats_boat.default_price_per_quarter_day, boats_boat.default_price_per_week, boats_boat.max_discount_percent, boats_boat.operator_id, boats_boat.staff_rating, boats_boat.first_calendar_date, boats_boat.last_calendar_date, boats_boat.main_picture_id, boats_specialoffer.id, boats_specialoffer.start_date, boats_specialoffer.end_date, boats_specialoffer.duration, boats_specialoffer.start_hour, boats_specialoffer.end_hour, boats_specialoffer.person_count, boats_specialoffer.price, t9.id, t9.thread_id, t9.sender_id, t9.type, t9.creation_date, t9.read_date, t9.starred, t9.original_content, t9.content, t9.flagged, t9.status, t9.flag_reason, t9.flag_feedback, t9.special_offer_id, t9.source, t9.notes, t9.approved_at, t9.response_time, t10.id, t10.staff_comments, t10.staff_comments_last_updated, t10.user_id, t10.anonymous_profile_id, t10.name_first, t10.name_middle, t10.name_last, t10.gender, t10.birth_date, t10.address_id, t10.address_captain_id, t10.address_billing_id, t10.contact_mobile, t10.contact_mobile_check_timestamp, t10.contact_mobile_check_secret, t10.contact_mobile_is_verified, t10.contact_email, t10.description, t10.tos_acceptance_date, t10.tos_acceptance_ip, t10.signup_tos_acceptance_date, t10.hear_about_us, t10.hear_about_us_representative, t10.hear_about_us_other, t10.hear_about_us_name, t10.promo_code, t10.promo_referrer, t10.extra_signup_message, t10.referral_code, t10.credit_balance, t10.credit_earned, t10.credit_spent, t10.credit_pending, t10.is_captain, t10.captain_headline, t10.captain_description, t10.captain_experience, t10.captain_last_calendar_update, t10.license_name_first, t10.license_name_last, t10.license_type, t10.license_other_name, t10.license_issue_date, t10.license_exp_date, t10.license_document_no, t10.license_reference_no, t10.license_scan, t10.license_asa_school, t10.license_asa_level, t10.license_asa_id, t10.license_ussbc_level, t10.license_ussbc_id, t10.captain_q1, t10.captain_q2, t10.captain_q3, t10.captain_tb_power_loa, t10.captain_tb_tp_a, t10.captain_tb_tp_b, t10.captain_tb_tp_c, t10.captain_tb_tp_d, t10.captain_tb_tp_e, t10.captain_tb_tp_f, t10.captain_tb_tp_g, t10.captain_tb_tp_h, t10.captain_tb_sail_loa, t10.captain_tb_type_sale_a, t10.captain_tb_type_sale_b, t10.captain_tb_type_sale_c, t10.captain_tb_type_sale_d, t10.captain_tb_type_sale_e, t10.captain_map_text, t10.captain_map_lat, t10.captain_map_long, t10.captain_map_radius, t10.captain_services_renter_other, t10.captain_services_owner_other, t10.captain_languages_other, t10.has_saved_info, t10.has_saved_services, t10.has_saved_license, t10.transfer_account_type, t10.transfer_payouts_enabled, t10.transfer_business_name, t10.transfer_business_tax_id, t10.transfer_business_email, t10.transfer_business_phone, t10.transfer_business_url, t10.transfer_business_address_id, t10.transfer_representative_id, t10.transfer_are_additional_owners_required, t10.transfer_are_directors_required, t10.transfer_are_all_owners_provided, t10.transfer_are_all_directors_provided, t10.transfer_bank_account_country, t10.transfer_bank_account_city, t10.transfer_bank_name, t10.transfer_bank_account_type, t10.transfer_bank_account_currency, t10.transfer_us_routing_number, t10.transfer_us_account_number, t10.transfer_intl_account_swift, t10.transfer_intl_account_iban, t10.transfer_other_account_bic, t10.transfer_other_account_number, t10.transfer_other_account_details, t10.currency, t10.is_bareboat_captain, t10.source, t10.staff_rating, t10.flag_policy, t10.flag_policy_last_change, t10.response_time, t10.response_rate
  • Sort Method: quicksort Memory: 25kB
3. 0.000 0.432 ↓ 0.0 0 1

Nested Loop Left Join (cost=2.31..79.30 rows=5 width=25,668) (actual time=0.432..0.432 rows=0 loops=1)

4. 0.119 0.432 ↓ 0.0 0 1

Hash Left Join (cost=2.03..37.79 rows=5 width=18,875) (actual time=0.432..0.432 rows=0 loops=1)

  • Hash Cond: (boats_thread.boat_id = boats_boat.id)
  • Filter: ((upper(boats_threadmessage.content) ~~ '%DD%'::text) OR (upper((boats_profile.name_first)::text) ~~ '%DD%'::text) OR (upper((boats_profile.name_last)::text) ~~ '%DD%'::text) OR (upper((boats_profile.contact_email)::text) ~~ '%DD%'::text) OR (upper((boats_profile.contact_mobile)::text) ~~ '%DD%'::text) OR (upper((t4.name_first)::text) ~~ '%DD%'::text) OR (upper((t4.name_last)::text) ~~ '%DD%'::text) OR (upper((t4.contact_email)::text) ~~ '%DD%'::text) OR (upper((t4.contact_mobile)::text) ~~ '%DD%'::text) OR (upper((boats_boat.name)::text) ~~ '%DD%'::text) OR (upper((boats_thread.boat_id)::text) ~~ '%DD%'::text) OR (upper((boats_booking.ref_number)::text) ~~ '%DD%'::text) OR (upper((boats_thread.sms_channel_id)::text) ~~ '%DD%'::text))
  • Rows Removed by Filter: 10
5. 0.034 0.296 ↓ 2.0 10 1

Nested Loop Left Join (cost=1.00..36.59 rows=5 width=15,716) (actual time=0.096..0.296 rows=10 loops=1)

  • Join Filter: (boats_thread.id = boats_threadmessage.thread_id)
  • Rows Removed by Join Filter: 50
6. 0.032 0.232 ↓ 6.0 6 1

Nested Loop Left Join (cost=1.00..35.36 rows=1 width=15,684) (actual time=0.090..0.232 rows=6 loops=1)

  • Join Filter: (boats_thread.last_message_id = t9.id)
  • Rows Removed by Join Filter: 37
7. 0.014 0.170 ↓ 6.0 6 1

Nested Loop Left Join (cost=1.00..34.14 rows=1 width=15,004) (actual time=0.078..0.170 rows=6 loops=1)

8. 0.016 0.150 ↓ 6.0 6 1

Nested Loop Left Join (cost=0.86..25.94 rows=1 width=14,850) (actual time=0.070..0.150 rows=6 loops=1)

9. 0.019 0.128 ↓ 6.0 6 1

Nested Loop (cost=0.57..17.63 rows=1 width=14,843) (actual time=0.062..0.128 rows=6 loops=1)

10. 0.018 0.085 ↓ 6.0 6 1

Nested Loop (cost=0.29..9.33 rows=1 width=8,050) (actual time=0.047..0.085 rows=6 loops=1)

11. 0.025 0.025 ↓ 6.0 6 1

Seq Scan on boats_thread (cost=0.00..1.02 rows=1 width=1,257) (actual time=0.020..0.025 rows=6 loops=1)

  • Filter: (message_overall_count >= 1)
12. 0.042 0.042 ↑ 1.0 1 6

Index Scan using boats_profile_pkey on boats_profile (cost=0.29..8.30 rows=1 width=6,793) (actual time=0.007..0.007 rows=1 loops=6)

  • Index Cond: (id = boats_thread.from_profile_id)
13. 0.024 0.024 ↑ 1.0 1 6

Index Scan using boats_profile_pkey on boats_profile t4 (cost=0.29..8.30 rows=1 width=6,793) (actual time=0.004..0.004 rows=1 loops=6)

  • Index Cond: (id = boats_thread.to_profile_id)
14. 0.006 0.006 ↓ 0.0 0 6

Index Scan using boats_booking_pkey on boats_booking (cost=0.29..8.30 rows=1 width=11) (actual time=0.001..0.001 rows=0 loops=6)

  • Index Cond: (boats_thread.booking_id = id)
15. 0.006 0.006 ↓ 0.0 0 6

Index Scan using boats_specialoffer_pkey on boats_specialoffer (cost=0.15..8.17 rows=1 width=154) (actual time=0.001..0.001 rows=0 loops=6)

  • Index Cond: (boats_thread.special_offer_id = id)
16. 0.030 0.030 ↑ 1.4 7 6

Seq Scan on boats_threadmessage t9 (cost=0.00..1.10 rows=10 width=680) (actual time=0.003..0.005 rows=7 loops=6)

17. 0.030 0.030 ↑ 1.0 10 6

Seq Scan on boats_threadmessage (cost=0.00..1.10 rows=10 width=36) (actual time=0.001..0.005 rows=10 loops=6)

18. 0.009 0.017 ↑ 1.0 1 1

Hash (cost=1.01..1.01 rows=1 width=3,198) (actual time=0.017..0.017 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
19. 0.008 0.008 ↑ 1.0 1 1

Seq Scan on boats_boat (cost=0.00..1.01 rows=1 width=3,198) (actual time=0.007..0.008 rows=1 loops=1)

20. 0.000 0.000 ↓ 0.0 0

Index Scan using boats_profile_pkey on boats_profile t10 (cost=0.29..8.30 rows=1 width=6,793) (never executed)

  • Index Cond: (t9.sender_id = id)