explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FRsu

Settings
# exclusive inclusive rows x rows loops node
1. 364.179 27,840.856 ↓ 1,427.7 285,541 1

Unique (cost=3,031,841.53..3,067,802.16 rows=200 width=1,703) (actual time=25,813.282..27,840.856 rows=285,541 loops=1)

  • Buffers: shared hit=1500083 read=164994, temp read=63536 written=63536
2. 652.605 27,476.677 ↓ 1.1 285,769 1

Subquery Scan on lc (cost=3,031,841.53..3,067,136.22 rows=266,375 width=1,703) (actual time=25,813.277..27,476.677 rows=285,769 loops=1)

  • Buffers: shared hit=1500083 read=164994, temp read=63536 written=63536
3. 4,548.846 26,824.072 ↓ 1.1 285,769 1

Sort (cost=3,031,841.53..3,032,507.47 rows=266,375 width=1,591) (actual time=25,813.232..26,824.072 rows=285,769 loops=1)

  • Sort Key: (COALESCE(c."Id", l."Id"))
  • Sort Method: external merge Disk: 103440kB
  • Buffers: shared hit=1500005 read=164994, temp read=63536 written=63536
4. 382.850 22,275.226 ↓ 1.1 285,769 1

Unique (cost=2,588,064.89..2,640,008.01 rows=266,375 width=1,591) (actual time=20,882.465..22,275.226 rows=285,769 loops=1)

  • Buffers: shared hit=1500005 read=164994, temp read=39153 written=39153
5. 5,798.964 21,892.376 ↓ 1.1 304,884 1

Sort (cost=2,588,064.89..2,588,730.83 rows=266,375 width=1,591) (actual time=20,882.464..21,892.376 rows=304,884 loops=1)

  • Sort Key: l."Id", (COALESCE(c."Id", l."Id")), c."Id", (COALESCE(c."Student_ID__c", l."Student_ID__c")), (COALESCE(ac."Reporting_Name__c", al."Reporting_Name__c", ac."Name", al."Name")), (COALESCE(cp."Reporting_Name__c", ac."Reporting_Name__c", ap."Reporting_Name__c", al."Reporting_Name__c", ap."Name", al."Name")), (COALESCE(c."AccountId", l."University__c")), (CASE l."Status" WHEN 'Persisting'::text THEN 'Qualified'::text WHEN 'Start'::text THEN 'Qualified'::text WHEN 'Attempted Contact'::text THEN 'Qualified'::text WHEN 'Admitted'::text THEN 'Qualified'::text WHEN 'Requirements Complete'::text THEN 'Qualified'::text WHEN 'Nurturing'::text THEN 'Qualified'::text WHEN 'Graduated'::text THEN 'Graduated'::text WHEN 'Applied'::text THEN 'Qualified'::text ELSE l."Status" END), l."IsDeleted", l."IsConverted", c."IsDeleted", c."Student_Status__c", c."Actively_Enrolled__c", u."Name", (CASE WHEN (c."Removed_from_Pipeline__c" OR (lower(l."Status") = 'unqualified'::text)) THEN true ELSE false END), (COALESCE(c."Reason_Removed_from_Pipeline__c", l."Reason_Removed_from_Pipeline__c")), (COALESCE(c."Recovery_Student__c", l."Recovery_Student__c")), (COALESCE(c."Do_not_contact_DNC__c", l."Do_not_contact_DNC__c")), (COALESCE(c."Reason_Not_Interested__c", l."Reason_Not_Interested__c")), (COALESCE(c."Student_Motivation__c", l."Student_Motivation__c")), (COALESCE(c."Estimated_Start_Date__c", l."Estimated_Start_Date__c")), (COALESCE(c."Official_Start_Date__c", l."Official_Start_Date__c")), (COALESCE(c."Program_of_Study__c", l."Program_of_Study__c")), (COALESCE(c."Re_enrollment_Plans__c", l."Re_enrollment_Plans__c")), (COALESCE(c."Re_enrollment_Additional_Info__c", l."Re_enrollment_Additional_Info__c")), (COALESCE(c."Re_Enrollment_College__c", l."Re_enrollment_Plans__c")), (COALESCE(c."Last_Attendance__c", l."Last_Attendance__c")), (COALESCE(c."Original_Drop_Date__c", l."Original_Drop_Date__c")), (COALESCE(c."Last_Contact_Date__c", l."Last_Contact_Date__c")), l."Units_Completed__c", (COALESCE(c."Gender__c", l."Gender__c")), (COALESCE(l."Age__c__ti", l."DOB__c", c."DOB__c")), (COALESCE(c."MailingCity", l."City")), (COALESCE(c."MailingState", l."State")), (COALESCE("left"(c."MailingPostalCode", 5), "left"(l."PostalCode", 5))), (COALESCE(l."Outreach_Cohort__c", c."Outreach_Cohort__c")), (COALESCE(l."Original_Institution__c", c."Original_Institution__c")), (COALESCE(CASE WHEN (l."Status" = 'Unqualified'::text) THEN true ELSE NULL::boolean END, CASE WHEN (c."Reached_SMS__c" OR l."Reached_SMS__c") THEN true ELSE NULL::boolean END, CASE WHEN (c."Reached_Email__c" OR l."Reached_Email__c") THEN true ELSE NULL::boolean END, CASE WHEN (COALESCE(l."Last_Contact_Date__c", c."Last_Contact_Date__c") IS NOT NULL) THEN true ELSE NULL::boolean END)), (COALESCE(c."Graduated_Status_Date__c", c."Official_Graduation_Date__c", l."Official_Graduation_Date__c")), c."Coach_Projection__c", (COALESCE(c."Original_Drop_Reason__c", l."Original_Drop_Reason__c")), (COALESCE(c."Original_Drop_Reason_Subcategory__c", l."Original_Drop_Reason_Subcategory__c")), ((l."Reached_Email__c" OR c."Reached_Email__c")), ((l."Reached_SMS__c" OR c."Reached_SMS__c")), (COALESCE(c."University_Specific_Field__c", l."University_Specific_Field__c")), c."Primary_External_Barrier__c", c."Primary_Internal_Perspective__c", c."Primary_Coach_Next_Step__c", c."Secondary_External_Barrier__c", c."Secondary_Internal_Perspective__c", c."Secondary_Coach_Next_Step__c", (CASE WHEN (c."Official_Start_Date__c" IS NOT NULL) THEN true ELSE false END), (COALESCE(c."UG_GPA__c", l."UG_GPA__c")), c."Date_of_Next_Outreach__c", c."Date_of_Next_Meeting__c", (COALESCE(c."Conversion_Channel__c", l."Conversion_Channel__c")), (COALESCE(c."Current_Holds__c", l."Current_Holds__c")), (COALESCE(c."Units_Attempted__c", l."Units_Attempted__c")), (COALESCE(c."Append_Date__c", l."Append_Date__c")), uc."Name", l."CreatedDate", c."CreatedDate", (COALESCE(c."System_ID__c", l."System_ID__c")), (COALESCE(c."Inbound_Applicants__c", l."Inbound_Applicants__c")), l."Unqualified_Status_Date__c", (COALESCE(c."Enrollment_Modality__c", l."Enrollment_Modality__c")), c."Data_Discrepancy_Counter__c", c."Data_Discrepancy__c", c."Data_Discrepancy_Notes__c", (COALESCE(c."Contact_Verification__c", l."Contact_Verification__c")), (COALESCE(c."ReUp_ID__c", l."ReUp_ID__c")), (COALESCE(c."Append_Email__c__bo", l."Append_Email__c__bo")), (COALESCE(c."Append_Phone__c", l."Append_Phone__c")), (COALESCE(c."Ethnicity__c", l."Ethnicity__c")), (COALESCE(c."Race__c", l."Race__c")), (COALESCE(c."Marital_Status__c", l."Marital_Status__c")), (COALESCE(c."Military_Status__c", l."Military_Status__c"))
  • Sort Method: external merge Disk: 111632kB
  • Buffers: shared hit=1495021 read=164994, temp read=39153 written=39153
6. 136.336 16,093.412 ↓ 1.1 304,884 1

Append (cost=2,167,131.5..2,196,231.37 rows=266,375 width=1,591) (actual time=12,873.415..16,093.412 rows=304,884 loops=1)

  • Buffers: shared hit=1490031 read=164994, temp read=25195 written=25195
7. 159.339 14,468.025 ↓ 1.1 285,704 1

Unique (cost=2,167,131.5..2,168,460.64 rows=265,828 width=1,510) (actual time=12,873.413..14,468.025 rows=285,704 loops=1)

  • Buffers: shared hit=1107807 read=164994, temp read=24158 written=24158
8. 5,262.822 14,308.686 ↓ 1.1 285,704 1

Sort (cost=2,167,131.5..2,167,796.07 rows=265,828 width=1,510) (actual time=12,873.411..14,308.686 rows=285,704 loops=1)

  • Sort Key: l."Id"
  • Sort Method: external merge Disk: 103416kB
  • Buffers: shared hit=1107807 read=164994, temp read=24158 written=24158
9. 1,303.993 9,045.864 ↓ 1.1 285,704 1

Hash Join (cost=108.48..1,794,279.22 rows=265,828 width=1,510) (actual time=0.798..9,045.864 rows=285,704 loops=1)

  • Buffers: shared hit=1107807 read=164994
10. 293.777 7,741.819 ↓ 1.1 295,718 1

Hash Join (cost=106.11..1,781,974.67 rows=267,864 width=1,857) (actual time=0.724..7,741.819 rows=295,718 loops=1)

  • Buffers: shared hit=1107806 read=164994
11. 262.740 7,447.970 ↓ 1.1 295,718 1

Hash Join (cost=90.6..1,778,276.03 rows=267,864 width=1,862) (actual time=0.646..7,447.97 rows=295,718 loops=1)

  • Buffers: shared hit=1107792 read=164994
12. 291.573 7,185.135 ↓ 1.1 295,718 1

Hash Join (cost=77.49..1,776,118.58 rows=267,864 width=1,844) (actual time=0.546..7,185.135 rows=295,718 loops=1)

  • Buffers: shared hit=1107781 read=164994
13. 334.274 6,893.314 ↓ 1.1 295,718 1

Hash Join (cost=50.51..1,772,408.71 rows=267,864 width=1,794) (actual time=0.293..6,893.314 rows=295,718 loops=1)

  • Buffers: shared hit=1107759 read=164994
14. 817.814 6,558.943 ↓ 1.1 295,718 1

Nested Loop (cost=35..1,770,491.38 rows=267,864 width=1,818) (actual time=0.189..6,558.943 rows=295,718 loops=1)

  • Filter: (((NOT c."IsDeleted") AND l."IsConverted") OR ((NOT l."IsDeleted") AND (NOT l."IsConverted")) OR (hashed SubPlan 2))
  • Buffers: shared hit=1107745 read=164994
15. 378.989 1,581.106 ↓ 1.0 320,001 1

Hash Join (cost=13.12..201,443.56 rows=317,686 width=850) (actual time=0.137..1,581.106 rows=320,001 loops=1)

  • Buffers: shared hit=28038 read=164994
16. 1,201.996 1,201.996 ↑ 1.0 320,001 1

Seq Scan on Lead l (cost=0..197,053.14 rows=320,119 width=775) (actual time=0.009..1,201.996 rows=320,001 loops=1)

  • Filter: ((l."University__c" IS NOT NULL) AND (l."Status" <> 'Test'::text))
  • Buffers: shared hit=28027 read=164994
17. 0.055 0.121 ↑ 1.0 94 1

Hash (cost=11.94..11.94 rows=94 width=75) (actual time=0.12..0.121 rows=94 loops=1)

  • Buffers: shared hit=11
18. 0.066 0.066 ↑ 1.0 94 1

Seq Scan on Account al (cost=0..11.94 rows=94 width=75) (actual time=0.003..0.066 rows=94 loops=1)

  • Buffers: shared hit=11
19. 0.000 4,160.013 ↓ 0.0 0 320,001

Bitmap Heap Scan on Contact c (cost=0.88..4.91 rows=2 width=1,006) (actual time=0.013..0.013 rows=0 loops=320,001)

  • Heap Blocks: exact=40063
  • Buffers: shared hit=1079706
20. 640.002 4,160.013 ↓ 0.0 0 320,001

BitmapOr (cost=0.88..0.88 rows=2 width=0) (actual time=0.013..0.013 rows=0 loops=320,001)

  • Buffers: shared hit=1039643
21. 320.001 320.001 ↓ 0.0 0 320,001

Bitmap Index Scan on "Contact_pkey" (cost=0..0.45 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=320,001)

  • Index Cond: (l."ConvertedContactId" = c."Id")
  • Buffers: shared hit=78999
22. 3,200.010 3,200.010 ↓ 0.0 0 320,001

Bitmap Index Scan on "Contact_Lead_ID" (cost=0..0.44 rows=1 width=0) (actual time=0.01..0.01 rows=0 loops=320,001)

  • Index Cond: (c."Lead_Id__c" = l."Id")
  • Buffers: shared hit=960644
23.          

SubPlan (for Nested Loop)

24. 0.010 0.010 ↑ 46.3 19 1

Seq Scan on accounts_noreport accounts_noreport_1 (cost=0..18.8 rows=880 width=32) (actual time=0.005..0.01 rows=19 loops=1)

  • Buffers: shared hit=1
25. 0.020 0.097 ↑ 1.0 67 1

Hash (cost=14.67..14.67 rows=67 width=33) (actual time=0.096..0.097 rows=67 loops=1)

  • Buffers: shared hit=14
26. 0.077 0.077 ↑ 1.0 67 1

Seq Scan on User u (cost=0..14.67 rows=67 width=33) (actual time=0.004..0.077 rows=67 loops=1)

  • Buffers: shared hit=14
27. 0.041 0.248 ↑ 1.0 94 1

Hash (cost=25.81..25.81 rows=94 width=69) (actual time=0.248..0.248 rows=94 loops=1)

  • Buffers: shared hit=22
28. 0.084 0.207 ↑ 1.0 94 1

Hash Join (cost=13.12..25.81 rows=94 width=69) (actual time=0.099..0.207 rows=94 loops=1)

  • Buffers: shared hit=22
29. 0.030 0.030 ↑ 1.0 94 1

Seq Scan on Account ac (cost=0..11.94 rows=94 width=75) (actual time=0.002..0.03 rows=94 loops=1)

  • Buffers: shared hit=11
30. 0.036 0.093 ↑ 1.0 94 1

Hash (cost=11.94..11.94 rows=94 width=32) (actual time=0.093..0.093 rows=94 loops=1)

  • Buffers: shared hit=11
31. 0.057 0.057 ↑ 1.0 94 1

Seq Scan on Account cp (cost=0..11.94 rows=94 width=32) (actual time=0.001..0.057 rows=94 loops=1)

  • Buffers: shared hit=11
32. 0.031 0.095 ↑ 1.0 94 1

Hash (cost=11.94..11.94 rows=94 width=56) (actual time=0.095..0.095 rows=94 loops=1)

  • Buffers: shared hit=11
33. 0.064 0.064 ↑ 1.0 94 1

Seq Scan on Account ap (cost=0..11.94 rows=94 width=56) (actual time=0.002..0.064 rows=94 loops=1)

  • Buffers: shared hit=11
34. 0.021 0.072 ↑ 1.0 67 1

Hash (cost=14.67..14.67 rows=67 width=33) (actual time=0.072..0.072 rows=67 loops=1)

  • Buffers: shared hit=14
35. 0.051 0.051 ↑ 1.0 67 1

Seq Scan on User uc (cost=0..14.67 rows=67 width=33) (actual time=0.002..0.051 rows=67 loops=1)

  • Buffers: shared hit=14
36. 0.030 0.052 ↓ 1.0 64 1

Hash (cost=1.61..1.61 rows=61 width=19) (actual time=0.051..0.052 rows=64 loops=1)

  • Buffers: shared hit=1
37. 0.022 0.022 ↓ 1.0 64 1

Seq Scan on accounts_service aa (cost=0..1.61 rows=61 width=19) (actual time=0.008..0.022 rows=64 loops=1)

  • Filter: (aa.id IS NOT NULL)
  • Buffers: shared hit=1
38. 10.508 1,489.051 ↓ 35.1 19,180 1

Unique (cost=25,104.25..25,106.98 rows=547 width=1,510) (actual time=1,446.14..1,489.051 rows=19,180 loops=1)

  • Buffers: shared hit=382224, temp read=1037 written=1037
39. 315.047 1,478.543 ↓ 35.3 19,303 1

Sort (cost=25,104.25..25,105.61 rows=547 width=1,510) (actual time=1,446.138..1,478.543 rows=19,303 loops=1)

  • Sort Key: l_1."Id"
  • Sort Method: external merge Disk: 8280kB
  • Buffers: shared hit=382224, temp read=1037 written=1037
40. 88.014 1,163.496 ↓ 35.3 19,303 1

Hash Join (cost=423.89..25,079.37 rows=547 width=1,510) (actual time=9.677..1,163.496 rows=19,303 loops=1)

  • Buffers: shared hit=382224
41. 26.384 1,075.419 ↓ 35.0 19,303 1

Nested Loop (cost=421.52..25,051.69 rows=551 width=1,857) (actual time=9.571..1,075.419 rows=19,303 loops=1)

  • Buffers: shared hit=382223
42. 40.937 952.520 ↓ 35.0 19,303 1

Nested Loop (cost=421.25..24,889.02 rows=551 width=1,862) (actual time=9.558..952.52 rows=19,303 loops=1)

  • Buffers: shared hit=324314
43. 44.693 834.371 ↓ 35.0 19,303 1

Hash Join (cost=420.98..24,725.54 rows=551 width=1,886) (actual time=9.54..834.371 rows=19,303 loops=1)

  • Filter: ((l_1."University__c" = c_1."AccountId") OR (l_1."University__c" = ac_1."ParentId") OR (ac_1."ParentId" = al_1."ParentId"))
  • Buffers: shared hit=266405
44. 134.394 789.401 ↓ 12.1 68,554 1

Nested Loop (cost=394..24,578.1 rows=5,669 width=1,855) (actual time=9.251..789.401 rows=68,554 loops=1)

  • Filter: (((NOT c_1."IsDeleted") AND l_1."IsConverted") OR ((NOT l_1."IsDeleted") AND (NOT l_1."IsConverted")) OR (hashed SubPlan 1))
  • Buffers: shared hit=266383
45. 63.432 244.686 ↓ 10.2 68,385 1

Nested Loop (cost=372.58..16,335.64 rows=6,723 width=868) (actual time=9.196..244.686 rows=68,385 loops=1)

  • Buffers: shared hit=41150
46. 0.205 0.598 ↓ 7.0 14 1

Nested Loop (cost=0.54..157 rows=2 width=112) (actual time=0.295..0.598 rows=14 loops=1)

  • Filter: ((ap_1."Name" = 'CUNY'::text) OR (al_1."Name" = 'WGU'::text))
  • Buffers: shared hit=201
47. 0.111 0.111 ↑ 1.0 94 1

Index Scan using "Account_pkey" on Account al_1 (cost=0.27..64.8 rows=94 width=75) (actual time=0.013..0.111 rows=94 loops=1)

  • Buffers: shared hit=81
48. 0.282 0.282 ↓ 0.0 0 94

Index Scan using "Account_pkey" on Account ap_1 (cost=0.27..0.97 rows=1 width=56) (actual time=0.003..0.003 rows=0 loops=94)

  • Index Cond: (al_1."ParentId" = ap_1."Id")
  • Buffers: shared hit=120
49. 171.262 180.656 ↑ 1.1 4,885 14

Bitmap Heap Scan on Lead l_1 (cost=372.05..8,034.55 rows=5,477 width=756) (actual time=1.089..12.904 rows=4,885 loops=14)

  • Filter: (l_1."Status" <> 'Test'::text)
  • Heap Blocks: exact=40208
  • Buffers: shared hit=40949
50. 9.394 9.394 ↑ 1.1 4,885 14

Bitmap Index Scan on "Lead_University_id" (cost=0..370.68 rows=5,477 width=0) (actual time=0.671..0.671 rows=4,885 loops=14)

  • Index Cond: ((l_1."University__c" = al_1."Id") AND (l_1."University__c" IS NOT NULL))
  • Buffers: shared hit=741
51. 410.310 410.310 ↓ 0.0 0 68,385

Index Scan using "Contact_Student_id" on Contact c_1 (cost=0.41..1.21 rows=1 width=987) (actual time=0.006..0.006 rows=0 loops=68,385)

  • Index Cond: (l_1."Student_ID__c" = c_1."Student_ID__c")
  • Buffers: shared hit=225232
52.          

SubPlan (for Nested Loop)

53. 0.011 0.011 ↑ 46.3 19 1

Seq Scan on accounts_noreport accounts_noreport (cost=0..18.8 rows=880 width=32) (actual time=0.006..0.011 rows=19 loops=1)

  • Buffers: shared hit=1
54. 0.060 0.277 ↑ 1.0 94 1

Hash (cost=25.81..25.81 rows=94 width=88) (actual time=0.277..0.277 rows=94 loops=1)

  • Buffers: shared hit=22
55. 0.073 0.217 ↑ 1.0 94 1

Hash Join (cost=13.12..25.81 rows=94 width=88) (actual time=0.123..0.217 rows=94 loops=1)

  • Buffers: shared hit=22
56. 0.030 0.030 ↑ 1.0 94 1

Seq Scan on Account ac_1 (cost=0..11.94 rows=94 width=75) (actual time=0.003..0.03 rows=94 loops=1)

  • Buffers: shared hit=11
57. 0.046 0.114 ↑ 1.0 94 1

Hash (cost=11.94..11.94 rows=94 width=32) (actual time=0.114..0.114 rows=94 loops=1)

  • Buffers: shared hit=11
58. 0.068 0.068 ↑ 1.0 94 1

Seq Scan on Account cp_1 (cost=0..11.94 rows=94 width=32) (actual time=0.002..0.068 rows=94 loops=1)

  • Buffers: shared hit=11
59. 77.212 77.212 ↑ 1.0 1 19,303

Index Scan using "User_pkey" on User u_1 (cost=0.27..0.29 rows=1 width=33) (actual time=0.004..0.004 rows=1 loops=19,303)

  • Index Cond: (u_1."Id" = COALESCE(c_1."OwnerId", l_1."OwnerId"))
  • Buffers: shared hit=57909
60. 96.515 96.515 ↑ 1.0 1 19,303

Index Scan using "User_pkey" on User uc_1 (cost=0.27..0.29 rows=1 width=33) (actual time=0.004..0.005 rows=1 loops=19,303)

  • Index Cond: (uc_1."Id" = l_1."CreatedById")
  • Buffers: shared hit=57909
61. 0.022 0.063 ↓ 1.0 64 1

Hash (cost=1.61..1.61 rows=61 width=19) (actual time=0.063..0.063 rows=64 loops=1)

  • Buffers: shared hit=1
62. 0.041 0.041 ↓ 1.0 64 1

Seq Scan on accounts_service aa_1 (cost=0..1.61 rows=61 width=19) (actual time=0.018..0.041 rows=64 loops=1)

  • Filter: (aa_1.id IS NOT NULL)
  • Buffers: shared hit=1
Planning time : 10.609 ms
Execution time : 27,953.715 ms