explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JqCG

Settings
# exclusive inclusive rows x rows loops node
1. 10.348 121,333.584 ↑ 1.7 41 1
→

GroupAggregate (cost=201,700.45..201,701.85 rows=70 width=24) (actual time=121,320.370..121,333.584 rows=41 loops=1)

  • Group Key: communication_histories.user_id
  • Buffers: shared hit=3883729 read=840753 dirtied=7489 written=5131
2. 13.161 121,323.236 ↓ 613.2 42,922 1
→

Sort (cost=201,700.45..201,700.63 rows=70 width=16) (actual time=121,320.329..121,323.236 rows=42,922 loops=1)

  • Sort Key: communication_histories.user_id
  • Sort Method: quicksort Memory: 3548kB
  • Buffers: shared hit=3883729 read=840753 dirtied=7489 written=5131
3. 3,138.538 121,310.075 ↓ 613.2 42,922 1
→

Hash Join (cost=109,061.94..201,698.31 rows=70 width=16) (actual time=19,652.947..121,310.075 rows=42,922 loops=1)

  • Hash Cond: (communication_histories.contact_history_id = contact_histories.id)
  • Buffers: shared hit=3883729 read=840753 dirtied=7489 written=5131
4. 1,488.074 112,617.176 ↓ 140.3 13,719,267 1
→

Nested Loop (cost=54.20..92,323.25 rows=97,764 width=8) (actual time=2.012..112,617.176 rows=13,719,267 loops=1)

  • Buffers: shared hit=3836007 read=825014 dirtied=7489 written=5131
5. 0.195 1.190 ↓ 19.4 136 1
→

HashAggregate (cost=53.63..53.70 rows=7 width=4) (actual time=1.021..1.190 rows=136 loops=1)

  • Group Key: device_contacts.user_id
  • Buffers: shared hit=1239
6. 0.110 0.995 ↓ 19.4 136 1
→

Sort (cost=53.53..53.54 rows=7 width=13) (actual time=0.982..0.995 rows=136 loops=1)

  • Sort Key: device_contacts.relationship_strength_score DESC NULLS LAST, users.active DESC, device_contacts.user_id
  • Sort Method: quicksort Memory: 31kB
  • Buffers: shared hit=1239
7. 0.029 0.885 ↓ 19.4 136 1
→

Nested Loop (cost=29.64..53.43 rows=7 width=13) (actual time=0.526..0.885 rows=136 loops=1)

  • Buffers: shared hit=1239
8. 0.044 0.584 ↓ 19.4 136 1
→

Subquery Scan on device_contacts (cost=29.23..29.37 rows=7 width=8) (actual time=0.521..0.584 rows=136 loops=1)

  • Filter: ((device_contacts.sources)::text[] <> '{phone_call}'::text[])
  • Buffers: shared hit=683
9. 0.020 0.540 ↓ 17.0 136 1
→

Unique (cost=29.23..29.27 rows=8 width=12,696) (actual time=0.514..0.540 rows=136 loops=1)

  • Buffers: shared hit=683
10. 0.059 0.520 ↓ 17.0 136 1
→

Sort (cost=29.23..29.25 rows=8 width=12,696) (actual time=0.513..0.520 rows=136 loops=1)

  • Sort Key: device_contacts_1.user_id, (COALESCE(device_contacts_1.relationship_strength_score, '0'::numeric)) DESC
  • Sort Method: quicksort Memory: 44kB
  • Buffers: shared hit=683
11. 0.098 0.461 ↓ 17.0 136 1
→

Nested Loop (cost=0.97..29.11 rows=8 width=12,696) (actual time=0.017..0.461 rows=136 loops=1)

  • Buffers: shared hit=683
12. 0.199 0.199 ↓ 20.5 164 1
→

Index Scan using index_device_contacts_on_contact_id_and_user_id_and_rel_score on device_contacts device_contacts_1 (cost=0.56..13.61 rows=8 width=37) (actual time=0.009..0.199 rows=164 loops=1)

  • Index Cond: (contact_id = 1676)
  • Buffers: shared hit=162
13. 0.164 0.164 ↑ 1.0 1 164
→

Index Only Scan using index_users_on_id_and_deleted_where_not_deleted on users users_1 (cost=0.41..1.93 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=164)

  • Index Cond: (id = device_contacts_1.user_id)
  • Heap Fetches: 22
  • Buffers: shared hit=521
14. 0.272 0.272 ↑ 1.0 1 136
→

Index Scan using index_users_on_id_account_id_and_active on users (cost=0.41..3.43 rows=1 width=5) (actual time=0.002..0.002 rows=1 loops=136)

  • Index Cond: (id = device_contacts.user_id)
  • Filter: (NOT deleted)
  • Buffers: shared hit=556
15. 111,127.912 111,127.912 ↓ 7.2 100,877 136
→

Index Scan using index_communication_histories_on_user_id on communication_histories (cost=0.57..13,041.70 rows=13,966 width=8) (actual time=0.503..817.117 rows=100,877 loops=136)

  • Index Cond: (user_id = device_contacts.user_id)
  • Buffers: shared hit=3834768 read=825014 dirtied=7489 written=5131
16. 24.036 5,554.361 ↓ 1.1 94,744 1
→

Hash (cost=107,899.01..107,899.01 rows=88,698 width=16) (actual time=5,554.361..5,554.361 rows=94,744 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 5836kB
  • Buffers: shared hit=47722 read=15739
17. 5,530.325 5,530.325 ↓ 1.1 94,744 1
→

Index Scan using index_contact_histories_on_contact_id_and_event_time on contact_histories (cost=0.57..107,899.01 rows=88,698 width=16) (actual time=0.013..5,530.325 rows=94,744 loops=1)

  • Index Cond: ((contact_id = 1676) AND (event_time <= '2019-12-02 05:10:46.299219'::timestamp without time zone))
  • Filter: (email_message_id IS NOT NULL)
  • Rows Removed by Filter: 225
  • Buffers: shared hit=47722 read=15739
Planning time : 3.858 ms
Execution time : 121,333.953 ms