explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ACfY

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 0.182 ↑ 1.0 2 1

Append (cost=2.95..13.10 rows=2 width=182) (actual time=0.107..0.182 rows=2 loops=1)

  • Buffers: shared hit=36
2.          

CTE cte

3. 0.002 0.002 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=1)

  • Output: 'e61d0fd8-97c8-e6fa-9804-621e4089af29'::uuid, '66964ee7-4dca-55ab-9431-450b2e74b9ca'::uuid
4.          

CTE cte2

5. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=1)

  • Output: '66964ee7-4dca-55ab-9431-450b2e74b9ca'::uuid, 'e61d0fd8-97c8-e6fa-9804-621e4089af29'::uuid
6.          

CTE cte3

7. 0.021 0.021 ↑ 1.0 1 1

Index Scan using mutual_other_member_guid_idx on source_v2.mutual (cost=0.06..1.27 rows=1 width=8) (actual time=0.021..0.021 rows=1 loops=1)

  • Output: mutual.created_tstamp
  • Index Cond: (mutual.other_member_guid = 'e61d0fd8-97c8-e6fa-9804-621e4089af29'::uuid)
  • Filter: (mutual.member_guid = '66964ee7-4dca-55ab-9431-450b2e74b9ca'::uuid)
  • Buffers: shared hit=3
8.          

CTE cte4

9. 0.018 0.018 ↓ 0.0 0 1

Index Scan using mutual_other_member_guid_idx on source_v2.mutual mutual_1 (cost=0.06..1.27 rows=1 width=8) (actual time=0.018..0.018 rows=0 loops=1)

  • Output: mutual_1.created_tstamp
  • Index Cond: (mutual_1.other_member_guid = '66964ee7-4dca-55ab-9431-450b2e74b9ca'::uuid)
  • Filter: (mutual_1.member_guid = 'e61d0fd8-97c8-e6fa-9804-621e4089af29'::uuid)
  • Rows Removed by Filter: 13
  • Buffers: shared hit=5
10. 0.004 0.108 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.39..5.26 rows=1 width=182) (actual time=0.107..0.108 rows=1 loops=1)

  • Output: cte.member_guid, cte.other_member_guid, $4, c.latest_message_sent_tstamp, c.is_priority, c.conversation_depth, c.is_latest_message_sender, mp.gender_identity_type, mp.seeking_gender, mp.date_of_birth, mp.is_upgraded, mp.home_country_id, mp.home_state_id, mp.legacy_profile_id, mp.member_ghosted_tstamp, mp.member_deleted_tstamp, mp.main_image_thumbnail_url, mr.restricted_to_gender_identity, mr.restricted_to_country, mr.minimum_msg_length, mr.minimum_age, mr.maximum_age, mr.restricted_to_members_with_profile_image, mr.restricted_to_upgraded_members, mb.inserted_tstamp
  • Inner Unique: true
  • Buffers: shared hit=17
11.          

Initplan (for Nested Loop Left Join)

12. 0.022 0.022 ↑ 1.0 1 1

CTE Scan on cte3 (cost=0.00..0.02 rows=1 width=8) (actual time=0.021..0.022 rows=1 loops=1)

  • Output: cte3.created_tstamp
  • Buffers: shared hit=3
13. 0.001 0.067 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.26..3.91 rows=1 width=166) (actual time=0.066..0.067 rows=1 loops=1)

  • Output: cte.member_guid, cte.other_member_guid, c.latest_message_sent_tstamp, c.is_priority, c.conversation_depth, c.is_latest_message_sender, mp.gender_identity_type, mp.seeking_gender, mp.date_of_birth, mp.is_upgraded, mp.home_country_id, mp.home_state_id, mp.legacy_profile_id, mp.member_ghosted_tstamp, mp.member_deleted_tstamp, mp.main_image_thumbnail_url, mr.restricted_to_gender_identity, mr.restricted_to_country, mr.minimum_msg_length, mr.minimum_age, mr.maximum_age, mr.restricted_to_members_with_profile_image, mr.restricted_to_upgraded_members
  • Inner Unique: true
  • Buffers: shared hit=10
14. 0.001 0.052 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.17..2.61 rows=1 width=154) (actual time=0.051..0.052 rows=1 loops=1)

  • Output: cte.member_guid, cte.other_member_guid, c.latest_message_sent_tstamp, c.is_priority, c.conversation_depth, c.is_latest_message_sender, mp.gender_identity_type, mp.seeking_gender, mp.date_of_birth, mp.is_upgraded, mp.home_country_id, mp.home_state_id, mp.legacy_profile_id, mp.member_ghosted_tstamp, mp.member_deleted_tstamp, mp.main_image_thumbnail_url
  • Inner Unique: true
  • Buffers: shared hit=7
15. 0.003 0.029 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.06..1.29 rows=1 width=46) (actual time=0.028..0.029 rows=1 loops=1)

  • Output: cte.member_guid, cte.other_member_guid, c.latest_message_sent_tstamp, c.is_priority, c.conversation_depth, c.is_latest_message_sender
  • Inner Unique: true
  • Buffers: shared hit=2
16. 0.005 0.005 ↑ 1.0 1 1

CTE Scan on cte (cost=0.00..0.02 rows=1 width=32) (actual time=0.004..0.005 rows=1 loops=1)

  • Output: cte.member_guid, cte.other_member_guid
17. 0.021 0.021 ↓ 0.0 0 1

Index Scan using conversations_pk on projection.conversations c (cost=0.06..1.27 rows=1 width=46) (actual time=0.020..0.021 rows=0 loops=1)

  • Output: c.latest_message_sent_tstamp, c.is_priority, c.conversation_depth, c.is_latest_message_sender, c.member_guid, c.other_member_guid
  • Index Cond: ((c.member_guid = cte.member_guid) AND (c.other_member_guid = cte.other_member_guid))
  • Buffers: shared hit=2
18. 0.022 0.022 ↑ 1.0 1 1

Index Scan using member_profiles_pk on source.member_profiles mp (cost=0.11..1.32 rows=1 width=124) (actual time=0.022..0.022 rows=1 loops=1)

  • Output: mp.gender_identity_type, mp.seeking_gender, mp.date_of_birth, mp.is_upgraded, mp.home_country_id, mp.home_state_id, mp.legacy_profile_id, mp.member_ghosted_tstamp, mp.member_deleted_tstamp, mp.main_image_thumbnail_url, mp.member_guid
  • Index Cond: (mp.member_guid = cte.member_guid)
  • Buffers: shared hit=5
19. 0.014 0.014 ↓ 0.0 0 1

Index Scan using member_messaging_restrictions_pk on source.member_messaging_restrictions mr (cost=0.09..1.30 rows=1 width=28) (actual time=0.014..0.014 rows=0 loops=1)

  • Output: mr.member_guid, mr.maximum_distance_km, mr.restricted_to_gender_identity, mr.restricted_to_country, mr.minimum_msg_length, mr.minimum_age, mr.maximum_age, mr.restricted_to_members_with_profile_image, mr.restricted_to_upgraded_members
  • Index Cond: (mr.member_guid = cte.member_guid)
  • Buffers: shared hit=3
20. 0.015 0.015 ↓ 0.0 0 1

Index Scan using member_blocked_pk on source.member_blocked mb (cost=0.11..1.33 rows=1 width=40) (actual time=0.015..0.015 rows=0 loops=1)

  • Output: mb.member_guid, mb.other_member_guid, mb.inserted_tstamp
  • Index Cond: ((mb.member_guid = cte.member_guid) AND (mb.other_member_guid = cte.other_member_guid))
  • Buffers: shared hit=4
21. 0.002 0.073 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.39..5.26 rows=1 width=182) (actual time=0.072..0.073 rows=1 loops=1)

  • Output: cte2.member_guid, cte2.other_member_guid, $5, c_1.latest_message_sent_tstamp, c_1.is_priority, c_1.conversation_depth, c_1.is_latest_message_sender, mp_1.gender_identity_type, mp_1.seeking_gender, mp_1.date_of_birth, mp_1.is_upgraded, mp_1.home_country_id, mp_1.home_state_id, mp_1.legacy_profile_id, mp_1.member_ghosted_tstamp, mp_1.member_deleted_tstamp, mp_1.main_image_thumbnail_url, mr_1.restricted_to_gender_identity, mr_1.restricted_to_country, mr_1.minimum_msg_length, mr_1.minimum_age, mr_1.maximum_age, mr_1.restricted_to_members_with_profile_image, mr_1.restricted_to_upgraded_members, mb_1.inserted_tstamp
  • Inner Unique: true
  • Buffers: shared hit=19
22.          

Initplan (for Nested Loop Left Join)

23. 0.018 0.018 ↓ 0.0 0 1

CTE Scan on cte4 (cost=0.00..0.02 rows=1 width=8) (actual time=0.018..0.018 rows=0 loops=1)

  • Output: cte4.created_tstamp
  • Buffers: shared hit=5
24. 0.001 0.040 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.26..3.91 rows=1 width=166) (actual time=0.040..0.040 rows=1 loops=1)

  • Output: cte2.member_guid, cte2.other_member_guid, c_1.latest_message_sent_tstamp, c_1.is_priority, c_1.conversation_depth, c_1.is_latest_message_sender, mp_1.gender_identity_type, mp_1.seeking_gender, mp_1.date_of_birth, mp_1.is_upgraded, mp_1.home_country_id, mp_1.home_state_id, mp_1.legacy_profile_id, mp_1.member_ghosted_tstamp, mp_1.member_deleted_tstamp, mp_1.main_image_thumbnail_url, mr_1.restricted_to_gender_identity, mr_1.restricted_to_country, mr_1.minimum_msg_length, mr_1.minimum_age, mr_1.maximum_age, mr_1.restricted_to_members_with_profile_image, mr_1.restricted_to_upgraded_members
  • Inner Unique: true
  • Buffers: shared hit=10
25. 0.001 0.028 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.17..2.61 rows=1 width=154) (actual time=0.027..0.028 rows=1 loops=1)

  • Output: cte2.member_guid, cte2.other_member_guid, c_1.latest_message_sent_tstamp, c_1.is_priority, c_1.conversation_depth, c_1.is_latest_message_sender, mp_1.gender_identity_type, mp_1.seeking_gender, mp_1.date_of_birth, mp_1.is_upgraded, mp_1.home_country_id, mp_1.home_state_id, mp_1.legacy_profile_id, mp_1.member_ghosted_tstamp, mp_1.member_deleted_tstamp, mp_1.main_image_thumbnail_url
  • Inner Unique: true
  • Buffers: shared hit=7
26. 0.001 0.010 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.06..1.29 rows=1 width=46) (actual time=0.010..0.010 rows=1 loops=1)

  • Output: cte2.member_guid, cte2.other_member_guid, c_1.latest_message_sent_tstamp, c_1.is_priority, c_1.conversation_depth, c_1.is_latest_message_sender
  • Inner Unique: true
  • Buffers: shared hit=2
27. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on cte2 (cost=0.00..0.02 rows=1 width=32) (actual time=0.001..0.002 rows=1 loops=1)

  • Output: cte2.member_guid, cte2.other_member_guid
28. 0.007 0.007 ↓ 0.0 0 1

Index Scan using conversations_pk on projection.conversations c_1 (cost=0.06..1.27 rows=1 width=46) (actual time=0.007..0.007 rows=0 loops=1)

  • Output: c_1.latest_message_sent_tstamp, c_1.is_priority, c_1.conversation_depth, c_1.is_latest_message_sender, c_1.member_guid, c_1.other_member_guid
  • Index Cond: ((c_1.member_guid = cte2.member_guid) AND (c_1.other_member_guid = cte2.other_member_guid))
  • Buffers: shared hit=2
29. 0.017 0.017 ↑ 1.0 1 1

Index Scan using member_profiles_pk on source.member_profiles mp_1 (cost=0.11..1.32 rows=1 width=124) (actual time=0.017..0.017 rows=1 loops=1)

  • Output: mp_1.gender_identity_type, mp_1.seeking_gender, mp_1.date_of_birth, mp_1.is_upgraded, mp_1.home_country_id, mp_1.home_state_id, mp_1.legacy_profile_id, mp_1.member_ghosted_tstamp, mp_1.member_deleted_tstamp, mp_1.main_image_thumbnail_url, mp_1.member_guid
  • Index Cond: (mp_1.member_guid = cte2.member_guid)
  • Buffers: shared hit=5
30. 0.011 0.011 ↓ 0.0 0 1

Index Scan using member_messaging_restrictions_pk on source.member_messaging_restrictions mr_1 (cost=0.09..1.30 rows=1 width=28) (actual time=0.011..0.011 rows=0 loops=1)

  • Output: mr_1.member_guid, mr_1.maximum_distance_km, mr_1.restricted_to_gender_identity, mr_1.restricted_to_country, mr_1.minimum_msg_length, mr_1.minimum_age, mr_1.maximum_age, mr_1.restricted_to_members_with_profile_image, mr_1.restricted_to_upgraded_members
  • Index Cond: (mr_1.member_guid = cte2.member_guid)
  • Buffers: shared hit=3
31. 0.013 0.013 ↓ 0.0 0 1

Index Scan using member_blocked_pk on source.member_blocked mb_1 (cost=0.11..1.33 rows=1 width=40) (actual time=0.013..0.013 rows=0 loops=1)

  • Output: mb_1.member_guid, mb_1.other_member_guid, mb_1.inserted_tstamp
  • Index Cond: ((mb_1.member_guid = cte2.member_guid) AND (mb_1.other_member_guid = cte2.other_member_guid))
  • Buffers: shared hit=4
Planning time : 1.745 ms