explain.depesz.com

PostgreSQL's explain analyze made readable

Result: x3If

Settings
# exclusive inclusive rows x rows loops node
1. 214.401 23,682.026 ↓ 0.0 1 1

Custom Scan (Citus Router) (cost=0.00..0.00 rows=0 width=0) (actual time=23,682.025..23,682.026 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: 6.088 ms
  • Execution Time: 23467.808 ms
  • Planning Time: 8.007 ms
  • Execution Time: 23682.053 ms
2. 0.008 23,467.625 ↑ 1.0 1 1

Limit (cost=1,609,278.64..1,609,278.69 rows=1 width=173) (actual time=23,467.618..23,467.625 rows=1 loops=1)

3. 0.069 23,467.617 ↑ 1.0 1 1

GroupAggregate (cost=1,609,278.64..1,609,278.69 rows=1 width=173) (actual time=23,467.617..23,467.617 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.629 23,467.548 ↓ 23.0 23 1

Sort (cost=1,609,278.64..1,609,278.64 rows=1 width=155) (actual time=23,467.547..23,467.548 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.395 23,466.919 ↓ 352.0 352 1

Nested Loop Left Join (cost=12.17..1,609,278.63 rows=1 width=155) (actual time=556.507..23,466.919 rows=352 loops=1)

6. 0.214 23,466.524 ↓ 352.0 352 1

Nested Loop Left Join (cost=11.60..1,609,256.64 rows=1 width=147) (actual time=556.492..23,466.524 rows=352 loops=1)

7. 0.254 23,465.958 ↓ 352.0 352 1

Nested Loop Left Join (cost=11.18..1,609,252.97 rows=1 width=139) (actual time=556.470..23,465.958 rows=352 loops=1)

  • Join Filter: (entity_table.id = entity_associations.source_entity_id)
8. 0.070 23,465.352 ↓ 352.0 352 1

Nested Loop Left Join (cost=10.75..1,609,248.52 rows=1 width=133) (actual time=556.457..23,465.352 rows=352 loops=1)

9. 0.190 23,464.994 ↓ 16.0 16 1

Nested Loop Left Join (cost=10.19..1,609,224.81 rows=1 width=125) (actual time=556.439..23,464.994 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.057 23,464.388 ↓ 16.0 32 1

Nested Loop Left Join (cost=9.62..1,609,166.92 rows=2 width=110) (actual time=556.371..23,464.388 rows=32 loops=1)

11. 1,106.041 23,464.011 ↓ 16.0 16 1

Hash Right Join (cost=9.06..1,609,138.00 rows=1 width=87) (actual time=556.327..23,464.011 rows=16 loops=1)

  • Hash Cond: (gmail_msgid_mappings.correspondence_hash_id = correspondences.hash_id)
12. 22,357.936 22,357.936 ↑ 1.0 11,147,848 1

Seq Scan on gmail_msgid_mappings_108045 gmail_msgid_mappings (cost=0.00..1,566,195.40 rows=11,448,942 width=46) (actual time=0.007..22,357.936 rows=11,147,848 loops=1)

  • Filter: (company_id = 2928)
  • Rows Removed by Filter: 18738980
13. 0.003 0.034 ↑ 1.0 1 1

Hash (cost=9.04..9.04 rows=1 width=99) (actual time=0.034..0.034 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 0.003 0.031 ↑ 1.0 1 1

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

15. 0.016 0.016 ↑ 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.016..0.016 rows=1 loops=1)

  • Index Cond: ((company_id = 2928) AND (source_entity_type = 7) AND (source_entity_id = 456781) AND (target_entity_type = 8))
16. 0.012 0.012 ↑ 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.012..0.012 rows=1 loops=1)

  • Index Cond: (id = entity_associations.target_entity_id)
  • Filter: ((NOT is_deleted) AND (company_id = 2928))
17. 0.320 0.320 ↑ 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.015..0.020 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
18. 0.416 0.416 ↑ 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.007..0.013 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
19. 0.288 0.288 ↓ 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.007..0.018 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
20. 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)
21. 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)
22. 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)