explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iyIB

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 0.257 ↑ 1.0 2 1

Append (cost=1.66..13.06 rows=2 width=181) (actual time=0.147..0.257 rows=2 loops=1)

  • Buffers: shared hit=36
2.          

CTE cte

3. 0.003 0.003 ↑ 1.0 1 1

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

  • Output: '66964ee7-4dca-55ab-9431-450b2e74b9ca'::uuid, 'e61d0fd8-97c8-e6fa-9804-621e4089af29'::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: 'e61d0fd8-97c8-e6fa-9804-621e4089af29'::uuid, '66964ee7-4dca-55ab-9431-450b2e74b9ca'::uuid
6. 0.003 0.147 ↑ 1.0 1 1

Nested Loop Left Join (cost=1.64..6.51 rows=1 width=181) (actual time=0.146..0.147 rows=1 loops=1)

  • Output: cte.member_guid, cte.other_member_guid, $2, 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
7.          

Initplan (for Nested Loop Left Join)

8. 0.022 0.022 ↑ 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.022 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
9. 0.002 0.100 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.26..3.91 rows=1 width=165) (actual time=0.099..0.100 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
10. 0.002 0.079 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.17..2.61 rows=1 width=153) (actual time=0.078..0.079 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
11. 0.004 0.038 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.06..1.29 rows=1 width=46) (actual time=0.038..0.038 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
12. 0.006 0.006 ↑ 1.0 1 1

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

  • Output: cte.member_guid, cte.other_member_guid
13. 0.028 0.028 ↓ 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.028..0.028 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
14. 0.039 0.039 ↑ 1.0 1 1

Index Scan using member_profiles_pk on source.member_profiles mp (cost=0.11..1.32 rows=1 width=123) (actual time=0.039..0.039 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
15. 0.019 0.019 ↓ 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.019..0.019 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
16. 0.022 0.022 ↓ 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.022..0.022 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
17. 0.002 0.108 ↑ 1.0 1 1

Nested Loop Left Join (cost=1.64..6.51 rows=1 width=181) (actual time=0.107..0.108 rows=1 loops=1)

  • Output: cte2.member_guid, cte2.other_member_guid, $3, 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
18.          

Initplan (for Nested Loop Left Join)

19. 0.040 0.040 ↓ 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.040..0.040 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
20. 0.001 0.052 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.26..3.91 rows=1 width=165) (actual time=0.051..0.052 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
21. 0.001 0.031 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.17..2.61 rows=1 width=153) (actual time=0.030..0.031 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
22. 0.002 0.013 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.06..1.29 rows=1 width=46) (actual time=0.012..0.013 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
23. 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
24. 0.009 0.009 ↓ 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.009..0.009 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
25. 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=123) (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
26. 0.020 0.020 ↓ 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.020..0.020 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
27. 0.014 0.014 ↓ 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.014..0.014 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.689 ms