explain.depesz.com

PostgreSQL's explain analyze made readable

Result: d7an

Settings
# exclusive inclusive rows x rows loops node
1. 195.967 542.417 ↓ 0.0 1 1

Custom Scan (Citus Router) (cost=0.00..0.00 rows=0 width=0) (actual time=542.416..542.417 rows=1 loops=1)

  • Task Count: 1
  • Tasks Shown: All
  • -> Task
  • Node: host=ec2-34-198-173-57.compute-1.amazonaws.com port=5432 dbname=citus
  • Planning Time: 4.640 ms
  • Execution Time: 346.616 ms
2. 0.006 346.450 ↑ 1.0 1 1

Limit (cost=212,864.97..212,865.02 rows=1 width=173) (actual time=346.444..346.450 rows=1 loops=1)

3. 0.054 346.444 ↑ 1.0 1 1

GroupAggregate (cost=212,864.97..212,865.02 rows=1 width=173) (actual time=346.444..346.444 rows=1 loops=1)

  • Group Key: (timezone('America/Los_Angeles'::text, timezone('UTC'::text, correspondences.sent_at))), correspondences.id, senders.value_2, recipients.value_2, gmail_msgid_mappings.gmail_msgid, email_trackers.id, correspondences.subject
4. 0.598 346.390 ↓ 23.0 23 1

Sort (cost=212,864.97..212,864.98 rows=1 width=155) (actual time=346.389..346.390 rows=23 loops=1)

  • Sort Key: (timezone('America/Los_Angeles'::text, timezone('UTC'::text, correspondences.sent_at))) DESC, correspondences.id, senders.value_2, recipients.value_2, gmail_msgid_mappings.gmail_msgid, email_trackers.id, correspondences.subject
  • Sort Method: quicksort Memory: 118kB
5. 0.276 345.792 ↓ 352.0 352 1

Nested Loop Left Join (cost=4.67..212,864.96 rows=1 width=155) (actual time=200.019..345.792 rows=352 loops=1)

6. 0.068 345.516 ↓ 352.0 352 1

Nested Loop Left Join (cost=4.10..212,842.98 rows=1 width=147) (actual time=200.005..345.516 rows=352 loops=1)

7. 0.072 345.096 ↓ 352.0 352 1

Nested Loop Left Join (cost=3.68..212,839.31 rows=1 width=139) (actual time=199.992..345.096 rows=352 loops=1)

  • Join Filter: (entity_table.id = entity_associations.source_entity_id)
8. 0.047 344.672 ↓ 352.0 352 1

Nested Loop Left Join (cost=3.25..212,834.85 rows=1 width=133) (actual time=199.982..344.672 rows=352 loops=1)

9. 0.098 344.433 ↓ 16.0 16 1

Nested Loop Left Join (cost=2.69..212,811.15 rows=1 width=125) (actual time=199.970..344.433 rows=16 loops=1)

  • Filter: ((lower(senders.value_2) ~~* '%sales@prosperworks.com%'::text) OR (lower(recipients.value_2) ~~* '%sales@prosperworks.com%'::text))
  • Rows Removed by Filter: 16
10. 0.018 344.047 ↓ 16.0 32 1

Nested Loop Left Join (cost=2.12..212,753.26 rows=2 width=110) (actual time=199.925..344.047 rows=32 loops=1)

11. 0.005 343.869 ↓ 16.0 16 1

Nested Loop Left Join (cost=1.56..212,724.33 rows=1 width=87) (actual time=199.889..343.869 rows=16 loops=1)

12. 0.002 0.035 ↑ 1.0 1 1

Nested Loop (cost=1.00..9.04 rows=1 width=99) (actual time=0.033..0.035 rows=1 loops=1)

13. 0.022 0.022 ↑ 1.0 1 1

Index Scan using entity_associations_source_to_target_107725 on entity_associations_107725 entity_associations (cost=0.56..4.59 rows=1 width=16) (actual time=0.020..0.022 rows=1 loops=1)

  • Index Cond: ((company_id = 2928) AND (source_entity_type = 7) AND (source_entity_id = 456781) AND (target_entity_type = 8))
14. 0.011 0.011 ↑ 1.0 1 1

Index Scan using virtual_pkey_a_id_correspondences_106125 on correspondences_106125 correspondences (cost=0.43..4.46 rows=1 width=83) (actual time=0.011..0.011 rows=1 loops=1)

  • Index Cond: (id = entity_associations.target_entity_id)
  • Filter: ((NOT is_deleted) AND (company_id = 2928))
15. 343.829 343.829 ↓ 3.2 16 1

Index Scan using temp_company_correspondence_hash_id_index_108045 on gmail_msgid_mappings_108045 gmail_msgid_mappings (cost=0.56..212,715.24 rows=5 width=46) (actual time=199.854..343.829 rows=16 loops=1)

  • Index Cond: (correspondence_hash_id = correspondences.hash_id)
16. 0.160 0.160 ↑ 2.0 2 16

Index Scan using index_typed_properties_on_owner_and_type_109709 on typed_properties_109709 recipients (cost=0.56..28.89 rows=4 width=31) (actual time=0.007..0.010 rows=2 loops=16)

  • Index Cond: ((company_id = 2928) AND (property_owner_type = 'Correspondence'::text) AND (property_owner_id = entity_associations.target_entity_id) AND (data_type = 4))
  • Filter: (category = 'RECIPIENT'::text)
  • Rows Removed by Filter: 21
17. 0.288 0.288 ↑ 3.0 1 32

Index Scan using index_typed_properties_on_owner_and_type_109709 on typed_properties_109709 senders (cost=0.56..28.89 rows=3 width=31) (actual time=0.005..0.009 rows=1 loops=32)

  • Index Cond: ((company_id = 2928) AND (property_owner_type = 'Correspondence'::text) AND (property_owner_id = entity_associations.target_entity_id) AND (data_type = 4))
  • Filter: (category = 'SENDER'::text)
  • Rows Removed by Filter: 22
18. 0.192 0.192 ↓ 3.7 22 16

Index Scan using index_typed_properties_on_owner_and_type_109709 on typed_properties_109709 recipients_counts (cost=0.56..23.64 rows=6 width=16) (actual time=0.005..0.012 rows=22 loops=16)

  • Index Cond: ((company_id = 2928) AND (property_owner_type = 'Correspondence'::text) AND (property_owner_id = correspondences.id) AND (data_type = 4))
  • Filter: ((category = 'RECIPIENT'::text) OR (category = 'CC_RECIPIENT'::text))
  • Rows Removed by Filter: 1
19. 0.352 0.352 ↓ 0.0 0 352

Index Scan using virtual_pkey_a_id_contacts_106765 on contacts_106765 entity_table (cost=0.42..4.45 rows=1 width=22) (actual time=0.001..0.001 rows=0 loops=352)

  • Index Cond: (id = 456781)
  • Filter: (company_id = 2928)
20. 0.352 0.352 ↓ 0.0 0 352

Index Scan using index_email_trackers_on_correspondence_id_107661 on email_trackers_107661 email_trackers (cost=0.43..3.66 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=352)

  • Index Cond: (correspondence_id = correspondences.id)
  • Filter: (company_id = 2928)
21. 0.000 0.000 ↓ 0.0 0 352

Index Scan using nb_tmp_idx_activity_logs_106253 on activity_logs_106253 activity_logs (cost=0.57..21.95 rows=3 width=16) (actual time=0.000..0.000 rows=0 loops=352)

  • Index Cond: ((company_id = 2928) AND (source_type = 35) AND (source_id = email_trackers.id))
  • Filter: (activity_type = 32)