explain.depesz.com

PostgreSQL's explain analyze made readable

Result: t3WR

Settings
# exclusive inclusive rows x rows loops node
1. 0.080 44,852.174 ↑ 10.6 47 1
→

Subquery Scan on subb (cost=320,236.75..1,264,928.18 rows=500 width=603) (actual time=44,530.897..44,852.174 rows=47 loops=1)

  • Buffers: shared hit=2409522
2. 0.012 44,851.530 ↑ 10.6 47 1
→

Limit (cost=320,236.75..1,235,794.73 rows=500 width=571) (actual time=44,530.877..44,851.530 rows=47 loops=1)

  • Buffers: shared hit=2409486
3. 0.204 44,851.518 ↑ 69.8 47 1
→

Result (cost=320,236.75..6,326,297.09 rows=3,280 width=571) (actual time=44,530.873..44,851.518 rows=47 loops=1)

  • Buffers: shared hit=2409486
4. 0.208 44,530.574 ↑ 69.8 47 1
→

Sort (cost=320,236.75..320,244.95 rows=3,280 width=539) (actual time=44,530.559..44,530.574 rows=47 loops=1)

  • Sort Key: c.id, users.id
  • Sort Method: quicksort Memory: 49kB
  • Buffers: shared hit=2294475
5. 0.161 44,530.366 ↑ 69.8 47 1
→

Nested Loop Left Join (cost=431.41..320,073.32 rows=3,280 width=539) (actual time=1,409.602..44,530.366 rows=47 loops=1)

  • Buffers: shared hit=2294475
6. 0.045 1.982 ↑ 69.8 47 1
→

Nested Loop (cost=422.45..104,979.82 rows=3,280 width=483) (actual time=1.334..1.982 rows=47 loops=1)

  • Buffers: shared hit=348
7. 0.006 1.497 ↑ 328.0 5 1
→

Nested Loop Left Join (cost=393.36..57,190.43 rows=1,640 width=371) (actual time=1.297..1.497 rows=5 loops=1)

  • Buffers: shared hit=146
8. 0.009 1.456 ↑ 328.0 5 1
→

Nested Loop Left Join (cost=392.93..54,688.87 rows=1,640 width=339) (actual time=1.288..1.456 rows=5 loops=1)

  • Buffers: shared hit=126
9. 0.005 1.447 ↑ 328.0 5 1
→

Nested Loop Left Join (cost=392.50..41,894.67 rows=1,640 width=282) (actual time=1.287..1.447 rows=5 loops=1)

  • Buffers: shared hit=126
10. 0.016 1.437 ↑ 328.0 5 1
→

Nested Loop Left Join (cost=392.22..41,198.37 rows=1,640 width=272) (actual time=1.285..1.437 rows=5 loops=1)

  • Buffers: shared hit=126
11. 0.018 1.396 ↑ 328.0 5 1
→

Nested Loop Left Join (cost=391.69..13,912.87 rows=1,640 width=175) (actual time=1.276..1.396 rows=5 loops=1)

  • Filter: ((cs.* IS NULL) OR cs.viewable)
  • Buffers: shared hit=116
12. 0.094 1.333 ↑ 328.0 5 1
→

Bitmap Heap Scan on contacts c (cost=391.40..8,120.07 rows=1,640 width=171) (actual time=1.260..1.333 rows=5 loops=1)

  • Recheck Cond: (to_person_search_doc(c.*) @@ (lower(unaccent('''gopi'':*'::text)))::tsquery)
  • Filter: (viewable AND (first_name IS NOT NULL) AND (last_name IS NOT NULL) AND (device_contacts_count > 0) AND ((first_name)::text <> ''::text) AND ((first_name)::text !~ '\\d+'::text) AND ((last_name)::text <> ''::text) AND ((last_name)::text !~ '\\d+'::text) AND (account_id = 3) AND (((company IS NOT NULL) AND ((company)::text ~ '[[:alpha:]]+'::text)) OR ((title IS NOT NULL) AND ((title)::text ~ '[[:alpha:]]+'::text))))
  • Rows Removed by Filter: 7
  • Heap Blocks: exact=13
  • Buffers: shared hit=101
13. 1.239 1.239 ↑ 153.2 20 1
→

Bitmap Index Scan on index_contacts_on_person_search_doc (cost=0.00..390.99 rows=3,064 width=0) (actual time=1.239..1.239 rows=20 loops=1)

  • Index Cond: (to_person_search_doc(c.*) @@ (lower(unaccent('''gopi'':*'::text)))::tsquery)
  • Buffers: shared hit=88
14. 0.045 0.045 ↑ 1.0 1 5
→

Index Scan using index_company_scores_on_id_and_account_id on company_scores cs (cost=0.29..3.52 rows=1 width=200) (actual time=0.008..0.009 rows=1 loops=5)

  • Index Cond: (id = c.company_score_id)
  • Buffers: shared hit=15
15. 0.005 0.025 ↓ 0.0 0 5
→

GroupAggregate (cost=0.54..16.62 rows=1 width=101) (actual time=0.005..0.005 rows=0 loops=5)

  • Group Key: crm_contacts.contact_id
  • Buffers: shared hit=10
16. 0.000 0.020 ↓ 0.0 0 5
→

Nested Loop Left Join (cost=0.54..16.59 rows=1 width=578) (actual time=0.004..0.004 rows=0 loops=5)

  • Join Filter: ((crm_accounts.account_id = crm_contacts.account_id) AND ((crm_accounts.crm_id)::text = (crm_contacts.account_crm_id)::text))
  • Buffers: shared hit=10
17. 0.020 0.020 ↓ 0.0 0 5
→

Index Scan using index_crm_contacts_on_contact_id on crm_contacts (cost=0.29..8.31 rows=1 width=66) (actual time=0.004..0.004 rows=0 loops=5)

  • Index Cond: (contact_id = c.id)
  • Filter: ((crm_id IS NOT NULL) AND (NOT is_converted) AND (account_id = c.account_id) AND (crm_details_id = 35))
  • Buffers: shared hit=10
18. 0.000 0.000 ↓ 0.0 0
→

Index Scan using index_crm_accounts_on_crm_id on crm_accounts (cost=0.25..8.27 rows=1 width=1,036) (never executed)

  • Filter: (account_id = c.account_id)
19. 0.005 0.005 ↓ 0.0 0 5
→

Index Scan using phone_numbers_pkey on phone_numbers work_phone (cost=0.28..0.41 rows=1 width=18) (actual time=0.001..0.001 rows=0 loops=5)

  • Index Cond: (id = c.work_phone_id)
20. 0.000 0.000 ↓ 0.0 0 5
→

Index Scan using addresses_pkey on addresses contact_addresses (cost=0.43..7.79 rows=1 width=65) (actual time=0.000..0.000 rows=0 loops=5)

  • Index Cond: (id = c.work_address_id)
21. 0.035 0.035 ↑ 1.0 1 5
→

Index Scan using companies_pkey on companies co (cost=0.43..1.52 rows=1 width=40) (actual time=0.006..0.007 rows=1 loops=5)

  • Index Cond: (id = cs.company_id)
  • Buffers: shared hit=20
22. 0.050 0.440 ↓ 4.5 9 5
→

Unique (cost=29.09..29.10 rows=2 width=152) (actual time=0.073..0.088 rows=9 loops=5)

  • Buffers: shared hit=202
23. 0.105 0.390 ↓ 4.5 9 5
→

Sort (cost=29.09..29.09 rows=2 width=152) (actual time=0.072..0.078 rows=9 loops=5)

  • Sort Key: users.id, (COALESCE(device_contacts.relationship_strength_score, '0'::numeric)) DESC
  • Sort Method: quicksort Memory: 29kB
  • Buffers: shared hit=202
24. 0.126 0.285 ↓ 4.5 9 5
→

Nested Loop (cost=0.69..29.08 rows=2 width=152) (actual time=0.017..0.057 rows=9 loops=5)

  • Buffers: shared hit=202
25. 0.065 0.065 ↓ 4.5 9 5
→

Index Scan using index_device_contacts_on_contact_id_and_user_id_and_rel_score on device_contacts (cost=0.42..12.45 rows=2 width=12) (actual time=0.006..0.013 rows=9 loops=5)

  • Index Cond: (contact_id = c.id)
  • Buffers: shared hit=61
26. 0.094 0.094 ↑ 1.0 1 47
→

Index Scan using index_users_on_id_account_id_and_active on users (cost=0.27..8.29 rows=1 width=45) (actual time=0.002..0.002 rows=1 loops=47)

  • Index Cond: (id = device_contacts.user_id)
  • Buffers: shared hit=141
27. 0.000 44,528.223 ↑ 1.0 1 47
→

Limit (cost=8.96..65.56 rows=1 width=13) (actual time=947.408..947.409 rows=1 loops=47)

  • Buffers: shared hit=2294127
28.          

Initplan (for Limit)
29. 0.094 1.316 ↑ 1.0 1 47
→

Limit (cost=0.27..8.54 rows=1 width=32) (actual time=0.027..0.028 rows=1 loops=47)

  • Buffers: shared hit=141
30. 1.222 1.222 ↑ 1.0 1 47
→

Index Scan using index_users_on_id_account_id_and_active on users users_1 (cost=0.27..8.54 rows=1 width=32) (actual time=0.026..0.026 rows=1 loops=47)

  • Index Cond: (id = $13)
  • Buffers: shared hit=141
31. 44,528.129 44,528.129 ↑ 1,532.0 1 47
→

Index Scan using contacts_pkey1 on contacts (cost=0.42..86,707.46 rows=1,532 width=13) (actual time=947.407..947.407 rows=1 loops=47)

  • Filter: (account_emails(contacts.*) && $14)
  • Rows Removed by Filter: 66960
  • Buffers: shared hit=2294127
32.          

SubPlan (for Result)
33. 0.000 0.000 ↓ 0.0 0
→

Index Scan using index_crm_contacts_on_contact_id on crm_contacts crm (cost=0.29..8.30 rows=1 width=0) (never executed)

  • Index Cond: (c.id = contact_id)
  • Filter: ((crm_id IS NOT NULL) AND (crm_details_id = 35))
34. 0.012 0.012 ↑ 5.7 3 1
→

Index Scan using index_crm_contacts_on_crm_details_id on crm_contacts crm_1 (cost=0.29..60.55 rows=17 width=4) (actual time=0.008..0.012 rows=3 loops=1)

  • Index Cond: (crm_details_id = 35)
  • Filter: (crm_id IS NOT NULL)
  • Buffers: shared hit=5
35. 0.235 0.611 ↑ 1.0 1 47
→

Aggregate (cost=16.79..16.80 rows=1 width=32) (actual time=0.012..0.013 rows=1 loops=47)

  • Buffers: shared hit=294
36. 0.047 0.376 ↓ 0.0 0 47
→

Nested Loop (cost=0.71..16.78 rows=1 width=5) (actual time=0.008..0.008 rows=0 loops=47)

  • Buffers: shared hit=294
37. 0.141 0.141 ↑ 1.0 1 47
→

Index Scan using index_company_scores_on_id_and_account_id on company_scores cs_1 (cost=0.29..8.31 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=47)

  • Index Cond: (id = c.company_score_id)
  • Buffers: shared hit=141
38. 0.188 0.188 ↓ 0.0 0 47
→

Index Only Scan using companies_industry_codes_company_id_code_kind_pk on companies_industry_codes cic (cost=0.42..8.46 rows=1 width=9) (actual time=0.004..0.004 rows=0 loops=47)

  • Index Cond: ((company_id = cs_1.company_id) AND (kind = 'naics'::text))
  • Heap Fetches: 12
  • Buffers: shared hit=153
39. 0.000 320.117 ↑ 1.0 1 47
→

Subquery Scan on email_address_events (cost=1,805.74..1,805.75 rows=1 width=32) (actual time=6.809..6.811 rows=1 loops=47)

  • Buffers: shared hit=114712
40. 0.188 320.117 ↑ 1.0 1 47
→

Limit (cost=1,805.74..1,805.74 rows=1 width=40) (actual time=6.808..6.811 rows=1 loops=47)

  • Buffers: shared hit=114712
41. 22.607 319.929 ↑ 172.0 1 47
→

Sort (cost=1,805.74..1,806.17 rows=172 width=40) (actual time=6.807..6.807 rows=1 loops=47)

  • Sort Key: ctp.month DESC
  • Sort Method: top-N heapsort Memory: 25kB
  • Buffers: shared hit=114712
42. 81.263 297.322 ↓ 12.9 2,211 47
→

HashAggregate (cost=1,803.16..1,804.88 rows=172 width=40) (actual time=5.999..6.326 rows=2,211 loops=47)

  • Group Key: ctp.touch_point, ctp.month
  • Buffers: shared hit=114712
43. 20.210 216.059 ↓ 19.4 3,332 47
→

Append (cost=85.45..1,802.30 rows=172 width=40) (actual time=1.060..4.597 rows=3,332 loops=47)

  • Buffers: shared hit=114712
44. 60.771 180.245 ↓ 19.6 3,251 47
→

Hash Join (cost=85.45..1,607.80 rows=166 width=33) (actual time=1.058..3.835 rows=3,251 loops=47)

  • Hash Cond: (ctp.user_id = u.id)
  • Buffers: shared hit=96558
45. 83.613 111.625 ↓ 8.0 3,251 47
→

Bitmap Heap Scan on contact_touch_points ctp (cost=48.68..1,567.86 rows=404 width=37) (actual time=0.885..2.375 rows=3,251 loops=47)

  • Recheck Cond: ((touch_point)::text = ANY ((c.emails)::text[]))
  • Heap Blocks: exact=94444
  • Buffers: shared hit=96040
46. 28.012 28.012 ↓ 8.0 3,251 47
→

Bitmap Index Scan on idx_contact_touch_points_tp_dp_month (cost=0.00..48.58 rows=404 width=0) (actual time=0.596..0.596 rows=3,251 loops=47)

  • Index Cond: ((touch_point)::text = ANY ((c.emails)::text[]))
  • Buffers: shared hit=1596
47. 3.619 7.849 ↓ 2.0 418 47
→

Hash (cost=34.15..34.15 rows=210 width=4) (actual time=0.167..0.167 rows=418 loops=47)

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
  • Buffers: shared hit=518
48. 4.230 4.230 ↓ 2.0 418 47
→

Index Only Scan using index_users_on_id_account_id_and_active on users u (cost=0.27..34.15 rows=210 width=4) (actual time=0.005..0.090 rows=418 loops=47)

  • Index Cond: (account_id = c.account_id)
  • Heap Fetches: 752
  • Buffers: shared hit=518
49. 0.846 15.604 ↓ 13.5 81 47
→

Subquery Scan on *SELECT* 2 (cost=1.27..192.83 rows=6 width=40) (actual time=0.015..0.332 rows=81 loops=47)

  • Buffers: shared hit=18154
50. 3.857 14.758 ↓ 13.5 81 47
→

Nested Loop (cost=1.27..192.77 rows=6 width=33) (actual time=0.015..0.314 rows=81 loops=47)

  • Buffers: shared hit=18154
51. 0.882 3.243 ↓ 13.5 81 47
→

Nested Loop (cost=0.84..189.95 rows=6 width=29) (actual time=0.011..0.069 rows=81 loops=47)

  • Buffers: shared hit=2838
52. 0.282 0.282 ↓ 2.0 2 47
→

Index Scan using index_contact_at_companies_on_contact_id on contact_at_companies cac (cost=0.42..8.45 rows=1 width=29) (actual time=0.004..0.006 rows=2 loops=47)

  • Index Cond: (contact_id = c.id)
  • Filter: (email = ANY ((c.emails)::text[]))
  • Buffers: shared hit=218
53. 2.079 2.079 ↑ 1.1 50 77
→

Index Scan using index_meeting_attendees_on_contact_at_company_id on meeting_attendees ma (cost=0.42..180.97 rows=53 width=8) (actual time=0.005..0.027 rows=50 loops=77)

  • Index Cond: (contact_at_company_id = cac.id)
  • Buffers: shared hit=2620
54. 7.658 7.658 ↑ 1.0 1 3,829
→

Index Scan using meetings_pkey on meetings m (cost=0.42..0.46 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=3,829)

  • Index Cond: (id = ma.meeting_id)
  • Buffers: shared hit=15316
55.          

SubPlan (for Subquery Scan)
56. 0.376 0.564 ↑ 1.0 1 47
→

Aggregate (cost=58.25..58.26 rows=1 width=32) (actual time=0.012..0.012 rows=1 loops=47)

  • Buffers: shared hit=36
57. 0.047 0.188 ↓ 0.0 0 47
→

Bitmap Heap Scan on industry_codes ic (cost=38.90..58.23 rows=6 width=36) (actual time=0.004..0.004 rows=0 loops=47)

  • Recheck Cond: ((kind = 'naics'::text) AND (code = ANY (string_to_array(subb.primary_code, ','::text))))
  • Heap Blocks: exact=12
  • Buffers: shared hit=36
58. 0.141 0.141 ↓ 0.0 0 47
→

Bitmap Index Scan on industry_codes_pkey (cost=0.00..38.90 rows=6 width=0) (actual time=0.003..0.003 rows=0 loops=47)

  • Index Cond: ((kind = 'naics'::text) AND (code = ANY (string_to_array(subb.primary_code, ','::text))))
  • Buffers: shared hit=24
Planning time : 9.064 ms
Execution time : 44,852.874 ms