explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZyMh

Settings
# exclusive inclusive rows x rows loops node
1. 0.039 43.524 ↓ 3.5 14 1

Sort (cost=5,566.44..5,566.45 rows=4 width=1,576) (actual time=43.523..43.524 rows=14 loops=1)

  • Sort Key: lea.name, (''::character varying), (CASE WHEN ((contype.contact_type_name)::text = 'SPECIAL_EDUCATION_COORDINATOR'::text) THEN 'Special Education Coordinator'::character varying WHEN ((contype.contact_type_name)::text = 'TECH_COORDINATOR_SUB_CONTACT'::text) THEN 'Technology Coordinator'::character varying ELSE contype.contact_type_name END), contact.first_name
  • Sort Method: quicksort Memory: 28kB
2. 0.033 43.485 ↓ 3.5 14 1

HashAggregate (cost=5,566.36..5,566.40 rows=4 width=1,576) (actual time=43.480..43.485 rows=14 loops=1)

  • Group Key: lea.name, lea.org_code, (''::character varying), (''::character varying), (''::character varying), ('CAASPP'::text), (CASE WHEN ((contype.contact_type_name)::text = 'SPECIAL_EDUCATION_COORDINATOR'::text) THEN 'Special Education Coordinator'::character varying WHEN ((contype.contact_type_name)::text = 'TECH_COORDINATOR_SUB_CONTACT'::text) THEN 'Technology Coordinator'::character varying ELSE contype.contact_type_name END), contact.first_name, contact.last_name, (CASE WHEN (((contact.phone_area_code)::text <> ''::text) IS TRUE) THEN ((((('('::text || (contact.phone_area_code)::text) || ') '::text) || (COALESCE(contact.phone_exchange, ' '::character varying))::text) || '-'::text) || (COALESCE(contact.phone_suffix, ' '::character varying))::text) ELSE ''::text END), contact.email, (CASE WHEN (((contact.fax_area_code)::text <> ''::text) IS TRUE) THEN ((((('('::text || (contact.fax_area_code)::text) || ') '::text) || (COALESCE(contact.fax_exchange, ' '::character varying))::text) || '-'::text) || (COALESCE(contact.fax_suffix, ' '::character varying))::text) ELSE ''::text END), (''::text), (''::text), (CASE WHEN (nvl((caasppprimary.primary_user_id)::numeric, '0'::numeric) = '0'::numeric) THEN ''::character varying ELSE caasppcoord.firstname END), (CASE WHEN (nvl((caasppprimary.primary_user_id)::numeric, '0'::numeric) = '0'::numeric) THEN ''::character varying ELSE caasppcoord.lastname END), (CASE WHEN (nvl((caasppprimary.primary_user_id)::numeric, '0'::numeric) = '0'::numeric) THEN ''::text ELSE CASE WHEN (((max((DECODE( user_attribute_vals.user_attribute_id , 1 , user_attribute_vals.string_value , NULL::character varying ))::text)) <> ''::text) IS TRUE) THEN (((((((('('::text || (max((DECODE( user_attribute_vals.user_attribute_id , 1 , user_attribute_vals.string_value , NULL::character varying ))::text))) || ') '::text) || (max((DECODE( user_attribute_vals.user_attribute_id , 2 , user_attribute_vals.string_value , NULL::character varying ))::text))) || '-'::text) || (max((DECODE( user_attribute_vals.user_attribute_id , 3 , user_attribute_vals.string_value , NULL::character varying ))::text))) || ' '::text) || ' '::text) || (max((DECODE( user_attribute_vals.user_attribute_id , 4 , user_attribute_vals.string_value , NULL::character varying ))::text))) ELSE ''::text END END), (CASE WHEN (nvl((caasppprimary.primary_user_id)::numeric, '0'::numeric) = '0'::numeric) THEN ''::text ELSE (max((DECODE( user_attribute_vals.user_attribute_id , 12 , user_attribute_vals.string_value , NULL::character varying ))::text)) END), (CASE WHEN (nvl((caasppprimary.primary_user_id)::numeric, '0'::numeric) = '0'::numeric) THEN ''::text ELSE CASE WHEN (((max((DECODE( user_attribute_vals.user_attribute_id , 5 , user_attribute_vals.string_value , NULL::character varying ))::text)) <> ''::text) IS TRUE) THEN ((((('('::text || (max((DECODE( user_attribute_vals.user_attribute_id , 5 , user_attribute_vals.string_value , NULL::character varying ))::text))) || ') '::text) || (max((DECODE( user_attribute_vals.user_attribute_id , 6 , user_attribute_vals.string_value , NULL::character varying ))::text))) || '-'::text) || (max((DECODE( user_attribute_vals.user_attribute_id , 7 , user_attribute_vals.string_value , NULL::character varying ))::text))) ELSE ''::text END END)
3. 8.371 43.452 ↓ 3.5 14 1

Append (cost=519.38..5,566.17 rows=4 width=1,576) (actual time=18.658..43.452 rows=14 loops=1)

4. 0.000 0.043 ↓ 0.0 0 1

Nested Loop (cost=519.38..559.80 rows=1 width=512) (actual time=0.043..0.043 rows=0 loops=1)

  • Join Filter: (contact.sub_contact_type = contype.contact_type_id)
5.          

Initplan (for Nested Loop)

6. 0.000 0.000 ↓ 0.0 0

Seq Scan on programs programs_8 (cost=0.00..2.02 rows=1 width=4) (never executed)

  • Filter: ((name)::text = 'CAASPP'::text)
7. 0.000 0.000 ↓ 0.0 0

Seq Scan on programs programs_9 (cost=0.00..2.02 rows=1 width=4) (never executed)

  • Filter: ((name)::text = 'ELPAC'::text)
8. 0.002 0.043 ↓ 0.0 0 1

Nested Loop (cost=515.33..553.60 rows=1 width=360) (actual time=0.043..0.043 rows=0 loops=1)

  • Join Filter: (lea.id = contact.organization_id)
  • Rows Removed by Join Filter: 9
9. 0.002 0.036 ↑ 1.0 1 1

Nested Loop Left Join (cost=515.33..551.40 rows=1 width=319) (actual time=0.035..0.036 rows=1 loops=1)

10. 0.002 0.028 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.29..8.35 rows=1 width=47) (actual time=0.027..0.028 rows=1 loops=1)

  • Join Filter: ((caasppprimary.org_code)::text = (lea.org_code)::text)
11. 0.018 0.018 ↑ 1.0 1 1

Index Scan using organizations_ak on organizations lea (cost=0.29..6.31 rows=1 width=43) (actual time=0.017..0.018 rows=1 loops=1)

  • Index Cond: (((org_code)::text = '01611270000000'::text) AND (ayc_id = 6))
12. 0.008 0.008 ↓ 0.0 0 1

Seq Scan on lea_primary_user caasppprimary (cost=0.00..2.04 rows=1 width=520) (actual time=0.008..0.008 rows=0 loops=1)

  • Filter: (((org_code)::text = '01611270000000'::text) AND (program_id = $9) AND (ayc_id = 6))
  • Rows Removed by Filter: 2
13. 0.001 0.006 ↓ 0.0 0 1

Merge Left Join (cost=515.04..543.03 rows=1 width=276) (actual time=0.006..0.006 rows=0 loops=1)

  • Merge Cond: (caasppcoord.id = user_attribute_vals.user_id)
14. 0.005 0.005 ↓ 0.0 0 1

Index Scan using users_pkey on users caasppcoord (cost=0.28..6.29 rows=1 width=20) (actual time=0.005..0.005 rows=0 loops=1)

  • Index Cond: (id = caasppprimary.primary_user_id)
15. 0.000 0.000 ↓ 0.0 0

Sort (cost=514.77..518.43 rows=1,464 width=260) (never executed)

  • Sort Key: user_attribute_vals.user_id
16. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=423.15..437.79 rows=1,464 width=260) (never executed)

  • Group Key: user_attribute_vals.user_id
17. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=2.50..336.86 rows=3,835 width=16) (never executed)

  • Hash Cond: (user_attribute_vals.user_attribute_id = user_attributes.id)
18. 0.000 0.000 ↓ 0.0 0

Seq Scan on user_attribute_vals (cost=0.00..303.87 rows=9,587 width=16) (never executed)

19. 0.000 0.000 ↓ 0.0 0

Hash (cost=2.40..2.40 rows=8 width=4) (never executed)

20. 0.000 0.000 ↓ 0.0 0

Seq Scan on user_attributes (cost=0.00..2.40 rows=8 width=4) (never executed)

  • Filter: ((name)::text = ANY ('{PHONE_AREA_CODE,PHONE_EXCHANGE,PHONE_SUFFIX,PHONE_EXT,FAX_AREA_CODE,FAX_EXCHANGE,FAX_SUFFIX,EMAIL}'::text[]))
21. 0.005 0.005 ↓ 1.3 9 1

Seq Scan on contacts contact (cost=0.00..2.11 rows=7 width=49) (actual time=0.003..0.005 rows=9 loops=1)

  • Filter: (sub_contact_type = ANY ('{1,2}'::integer[]))
22. 0.000 0.000 ↓ 0.0 0

Seq Scan on contact_type contype (cost=0.00..2.02 rows=2 width=30) (never executed)

23. 0.004 18.624 ↓ 11.0 11 1

Subquery Scan on *SELECT* 2 (cost=1,669.38..1,669.43 rows=1 width=470) (actual time=18.614..18.624 rows=11 loops=1)

24. 0.006 18.620 ↓ 11.0 11 1

Unique (cost=1,669.38..1,669.42 rows=1 width=470) (actual time=18.613..18.620 rows=11 loops=1)

25.          

Initplan (for Unique)

26. 0.000 0.000 ↓ 0.0 0

Seq Scan on programs programs_6 (cost=0.00..2.02 rows=1 width=4) (never executed)

  • Filter: ((name)::text = 'CAASPP'::text)
27. 0.000 0.000 ↓ 0.0 0

Seq Scan on programs programs_7 (cost=0.00..2.02 rows=1 width=4) (never executed)

  • Filter: ((name)::text = 'ELPAC'::text)
28. 0.055 18.614 ↓ 11.0 11 1

Sort (cost=1,665.33..1,665.33 rows=1 width=470) (actual time=18.613..18.614 rows=11 loops=1)

  • Sort Key: lea_1.name, sch.name, sch.org_code, sch.charter_number, (CASE WHEN (roles.program_name = 'CAASPP'::program_name_type) THEN 'CAASPP'::text WHEN (roles.program_name = 'ELPAC'::program_name_type) THEN 'ELPAC'::text WHEN (roles.program_name = 'BOTH'::program_name_type) THEN 'CAASPP/ELPAC'::text ELSE NULL::text END), roles.name, users.firstname, users.lastname, (CASE WHEN (((max((DECODE( user_attribute_vals_1.user_attribute_id , 1 , user_attribute_vals_1.string_value , NULL::character varying ))::text)) <> ''::text) IS TRUE) THEN ((((((('('::text || (max((DECODE( user_attribute_vals_1.user_attribute_id , 1 , user_attribute_vals_1.string_value , NULL::character varying ))::text))) || ') '::text) || COALESCE((max((DECODE( user_attribute_vals_1.user_attribute_id , 2 , user_attribute_vals_1.string_value , NULL::character varying ))::text)), ' '::text)) || '-'::text) || COALESCE((max((DECODE( user_attribute_vals_1.user_attribute_id , 3 , user_attribute_vals_1.string_value , NULL::character varying ))::text)), ' '::text)) || ' '::text) || (max((DECODE( user_attribute_vals_1.user_attribute_id , 4 , user_attribute_vals_1.string_value , NULL::character varying ))::text))) ELSE ''::text END), (max((DECODE( user_attribute_vals_1.user_attribute_id , 12 , user_attribute_vals_1.string_value , NULL::character varying ))::text)), (CASE WHEN (((max((DECODE( user_attribute_vals_1.user_attribute_id , 5 , user_attribute_vals_1.string_value , NULL::character varying ))::text)) <> ''::text) IS TRUE) THEN ((((('('::text || (max((DECODE( user_attribute_vals_1.user_attribute_id , 5 , user_attribute_vals_1.string_value , NULL::character varying ))::text))) || ') '::text) || COALESCE((max((DECODE( user_attribute_vals_1.user_attribute_id , 6 , user_attribute_vals_1.string_value , NULL::character varying ))::text)), ' '::text)) || '-'::text) || COALESCE((max((DECODE( user_attribute_vals_1.user_attribute_id , 7 , user_attribute_vals_1.string_value , NULL::character varying ))::text)), ' '::text)) ELSE ''::text END), (CASE WHEN (roles.program_name = 'ELPAC'::program_name_type) THEN CASE WHEN (nvl((elpacprimary.primary_user_id)::numeric, '0'::numeric) = '0'::numeric) THEN ''::character varying ELSE elpaccoord.firstname END ELSE CASE WHEN (nvl((caasppprimary_1.primary_user_id)::numeric, '0'::numeric) = '0'::numeric) THEN ''::character varying ELSE caasppcoord_1.firstname END END), (CASE WHEN (roles.program_name = 'ELPAC'::program_name_type) THEN CASE WHEN (nvl((elpacprimary.primary_user_id)::numeric, '0'::numeric) = '0'::numeric) THEN ''::character varying ELSE elpaccoord.lastname END ELSE CASE WHEN (nvl((caasppprimary_1.primary_user_id)::numeric, '0'::numeric) = '0'::numeric) THEN ''::character varying ELSE caasppcoord_1.lastname END END), (CASE WHEN (roles.program_name = 'ELPAC'::program_name_type) THEN CASE WHEN (nvl((elpacprimary.primary_user_id)::numeric, '0'::numeric) = '0'::numeric) THEN ''::text ELSE CASE WHEN (((max((DECODE( user_attribute_vals_2.user_attribute_id , 5 , user_attribute_vals_2.string_value , NULL::character varying ))::text)) <> ''::text) IS TRUE) THEN ((((((('('::text || (max((DECODE( user_attribute_vals_2.user_attribute_id , 1 , user_attribute_vals_2.string_value , NULL::character varying ))::text))) || ') '::text) || COALESCE((max((DECODE( user_attribute_vals_2.user_attribute_id , 2 , user_attribute_vals_2.string_value , NULL::character varying ))::text)), ' '::text)) || '-'::text) || COALESCE((max((DECODE( user_attribute_vals_2.user_attribute_id , 3 , user_attribute_vals_2.string_value , NULL::character varying ))::text)), ' '::text)) || ' '::text) || COALESCE((max((DECODE( user_attribute_vals_2.user_attribute_id , 4 , user_attribute_vals_2.string_value , NULL::character varying ))::text)), ' '::text)) ELSE ''::text END END ELSE CASE WHEN (nvl((caasppprimary_1.primary_user_id)::numeric, '0'::numeric) = '0'::numeric) THEN ''::text ELSE CASE WHEN (((max((DECODE( user_attribute_vals_3.user_attribute_id , 5 , user_attribute_vals_3.string_value , NULL::character varying ))::text)) <> ''::text) IS TRUE) THEN ((((((('('::text || (max((DECODE( user_attribute_vals_3.user_attribute_id , 1 , user_attribute_vals_3.string_value , NULL::character varying ))::text))) || ') '::text) || COALESCE((max((DECODE( user_attribute_vals_3.user_attribute_id , 2 , user_attribute_vals_3.string_value , NULL::character varying ))::text)), ' '::text)) || '-'::text) || COALESCE((max((DECODE( user_attribute_vals_3.user_attribute_id , 3 , user_attribute_vals_3.string_value , NULL::character varying ))::text)), ' '::text)) || ' '::text) || COALESCE((max((DECODE( user_attribute_vals_3.user_attribute_id , 4 , user_attribute_vals_3.string_value , NULL::character varying ))::text)), ' '::text)) ELSE ''::text END END END), (CASE WHEN (roles.program_name = 'ELPAC'::program_name_type) THEN CASE WHEN (nvl((elpacprimary.primary_user_id)::numeric, '0'::numeric) = '0'::numeric) THEN ''::text ELSE (max((DECODE( user_attribute_vals_2.user_attribute_id , 12 , user_attribute_vals_2.string_value , NULL::character varying ))::text)) END ELSE CASE WHEN (nvl((caasppprimary_1.primary_user_id)::numeric, '0'::numeric) = '0'::numeric) THEN ''::text ELSE (max((DECODE( user_attribute_vals_3.user_attribute_id , 12 , user_attribute_vals_3.string_value , NULL::character varying ))::text)) END END), (CASE WHEN (roles.program_name = 'ELPAC'::program_name_type) THEN CASE WHEN (nvl((elpacprimary.primary_user_id)::numeric, '0'::numeric) = '0'::numeric) THEN ''::text ELSE CASE WHEN (((max((DECODE( user_attribute_vals_2.user_attribute_id , 5 , user_attribute_vals_2.string_value , NULL::character varying ))::text)) <> ''::text) IS TRUE) THEN ((((('('::text || (max((DECODE( user_attribute_vals_2.user_attribute_id , 5 , user_attribute_vals_2.string_value , NULL::character varying ))::text))) || ') '::text) || COALESCE((max((DECODE( user_attribute_vals_2.user_attribute_id , 6 , user_attribute_vals_2.string_value , NULL::character varying ))::text)), ' '::text)) || '-'::text) || COALESCE((max((DECODE( user_attribute_vals_2.user_attribute_id , 7 , user_attribute_vals_2.string_value , NULL::character varying ))::text)), ' '::text)) ELSE ''::text END END ELSE CASE WHEN (nvl((caasppprimary_1.primary_user_id)::numeric, '0'::numeric) = '0'::numeric) THEN ''::text ELSE CASE WHEN (((max((DECODE( user_attribute_vals_3.user_attribute_id , 5 , user_attribute_vals_3.string_value , NULL::character varying ))::text)) <> ''::text) IS TRUE) THEN ((((('('::text || (max((DECODE( user_attribute_vals_3.user_attribute_id , 5 , user_attribute_vals_3.string_value , NULL::character varying ))::text))) || ') '::text) || COALESCE((max((DECODE( user_attribute_vals_3.user_attribute_id , 6 , user_attribute_vals_3.string_value , NULL::character varying ))::text)), ' '::text)) || '-'::text) || COALESCE((max((DECODE( user_attribute_vals_3.user_attribute_id , 7 , user_attribute_vals_3.string_value , NULL::character varying ))::text)), ' '::text)) ELSE ''::text END END END)
  • Sort Method: quicksort Memory: 27kB
29. 1.058 18.559 ↓ 11.0 11 1

Nested Loop Left Join (cost=1,578.54..1,665.32 rows=1 width=470) (actual time=16.786..18.559 rows=11 loops=1)

  • Join Filter: (user_attribute_vals_3.user_id = caasppcoord_1.id)
  • Rows Removed by Join Filter: 16093
30. 0.001 8.547 ↓ 11.0 11 1

Nested Loop Left Join (cost=1,063.77..1,113.75 rows=1 width=678) (actual time=8.400..8.547 rows=11 loops=1)

31. 0.090 8.535 ↓ 11.0 11 1

Merge Left Join (cost=548.73..570.70 rows=1 width=406) (actual time=8.396..8.535 rows=11 loops=1)

  • Merge Cond: (users.id = user_attribute_vals_1.user_id)
32. 0.018 0.189 ↓ 11.0 11 1

Sort (cost=33.96..33.97 rows=1 width=154) (actual time=0.188..0.189 rows=11 loops=1)

  • Sort Key: users.id
  • Sort Method: quicksort Memory: 27kB
33. 0.000 0.171 ↓ 11.0 11 1

Nested Loop Left Join (cost=1.41..33.95 rows=1 width=154) (actual time=0.065..0.171 rows=11 loops=1)

34. 0.005 0.160 ↓ 11.0 11 1

Nested Loop Left Join (cost=1.13..27.65 rows=1 width=134) (actual time=0.058..0.160 rows=11 loops=1)

  • Join Filter: ((elpacprimary.org_code)::text = (lea_1.org_code)::text)
35. 0.005 0.144 ↓ 11.0 11 1

Nested Loop Left Join (cost=1.13..25.60 rows=1 width=130) (actual time=0.056..0.144 rows=11 loops=1)

  • Join Filter: ((caasppprimary_1.org_code)::text = (lea_1.org_code)::text)
36. 0.020 0.128 ↓ 11.0 11 1

Nested Loop (cost=1.13..23.55 rows=1 width=126) (actual time=0.054..0.128 rows=11 loops=1)

  • Join Filter: (our.role_id = roles.id)
  • Rows Removed by Join Filter: 132
37. 0.002 0.075 ↓ 11.0 11 1

Nested Loop (cost=1.13..20.57 rows=1 width=107) (actual time=0.047..0.075 rows=11 loops=1)

38. 0.002 0.051 ↓ 11.0 11 1

Nested Loop (cost=0.85..18.96 rows=1 width=91) (actual time=0.039..0.051 rows=11 loops=1)

39. 0.003 0.025 ↓ 12.0 12 1

Nested Loop (cost=0.57..18.59 rows=1 width=87) (actual time=0.019..0.025 rows=12 loops=1)

40. 0.009 0.009 ↑ 1.0 1 1

Index Scan using organizations_ak on organizations lea_1 (cost=0.29..6.31 rows=1 width=43) (actual time=0.009..0.009 rows=1 loops=1)

  • Index Cond: (((org_code)::text = '01611270000000'::text) AND (ayc_id = 6))
41. 0.013 0.013 ↓ 3.0 12 1

Index Scan using org_parent_index on organizations sch (cost=0.29..12.24 rows=4 width=52) (actual time=0.008..0.013 rows=12 loops=1)

  • Index Cond: (parent_organization_id = lea_1.id)
  • Filter: (ayc_id = 6)
42. 0.024 0.024 ↑ 1.0 1 12

Index Only Scan using org_user_role_ayc_ak on org_user_roles our (cost=0.28..0.36 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=12)

  • Index Cond: ((organization_id = sch.id) AND (ayc_id = 6))
  • Heap Fetches: 0
43. 0.022 0.022 ↑ 1.0 1 11

Index Scan using users_pkey on users (cost=0.28..1.61 rows=1 width=20) (actual time=0.002..0.002 rows=1 loops=11)

  • Index Cond: (id = our.user_id)
  • Filter: (status = 1)
44. 0.033 0.033 ↑ 1.5 13 11

Seq Scan on roles (cost=0.00..2.74 rows=20 width=27) (actual time=0.001..0.003 rows=13 loops=11)

  • Filter: (id = ANY ('{32,9,30,3,36,33,35,34,84,6,4,81,57,58,2,56,8,83,10,60}'::integer[]))
  • Rows Removed by Filter: 1
45. 0.011 0.011 ↓ 0.0 0 11

Seq Scan on lea_primary_user caasppprimary_1 (cost=0.00..2.04 rows=1 width=520) (actual time=0.001..0.001 rows=0 loops=11)

  • Filter: (((org_code)::text = '01611270000000'::text) AND (program_id = $7) AND (ayc_id = 6))
  • Rows Removed by Filter: 2
46. 0.011 0.011 ↓ 0.0 0 11

Seq Scan on lea_primary_user elpacprimary (cost=0.00..2.04 rows=1 width=520) (actual time=0.001..0.001 rows=0 loops=11)

  • Filter: (((org_code)::text = '01611270000000'::text) AND (program_id = $8) AND (ayc_id = 6))
  • Rows Removed by Filter: 2
47. 0.011 0.011 ↓ 0.0 0 11

Index Scan using users_pkey on users caasppcoord_1 (cost=0.28..6.29 rows=1 width=20) (actual time=0.001..0.001 rows=0 loops=11)

  • Index Cond: (id = caasppprimary_1.primary_user_id)
48. 0.560 8.256 ↑ 1.2 1,197 1

Sort (cost=514.77..518.43 rows=1,464 width=260) (actual time=8.182..8.256 rows=1,197 loops=1)

  • Sort Key: user_attribute_vals_1.user_id
  • Sort Method: quicksort Memory: 190kB
49. 4.757 7.696 ↑ 1.0 1,463 1

HashAggregate (cost=423.15..437.79 rows=1,464 width=260) (actual time=7.162..7.696 rows=1,463 loops=1)

  • Group Key: user_attribute_vals_1.user_id
50. 2.283 2.939 ↓ 2.4 9,058 1

Hash Join (cost=2.50..336.86 rows=3,835 width=16) (actual time=0.030..2.939 rows=9,058 loops=1)

  • Hash Cond: (user_attribute_vals_1.user_attribute_id = user_attributes_1.id)
51. 0.640 0.640 ↑ 1.0 9,587 1

Seq Scan on user_attribute_vals user_attribute_vals_1 (cost=0.00..303.87 rows=9,587 width=16) (actual time=0.003..0.640 rows=9,587 loops=1)

52. 0.006 0.016 ↑ 1.0 8 1

Hash (cost=2.40..2.40 rows=8 width=4) (actual time=0.016..0.016 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
53. 0.010 0.010 ↑ 1.0 8 1

Seq Scan on user_attributes user_attributes_1 (cost=0.00..2.40 rows=8 width=4) (actual time=0.005..0.010 rows=8 loops=1)

  • Filter: ((name)::text = ANY ('{PHONE_AREA_CODE,PHONE_EXCHANGE,PHONE_SUFFIX,PHONE_EXT,FAX_AREA_CODE,FAX_EXCHANGE,FAX_SUFFIX,EMAIL}'::text[]))
  • Rows Removed by Filter: 12
54. 0.011 0.011 ↓ 0.0 0 11

Merge Left Join (cost=515.04..543.03 rows=1 width=276) (actual time=0.001..0.001 rows=0 loops=11)

  • Merge Cond: (elpaccoord.id = user_attribute_vals_2.user_id)
55. 0.000 0.000 ↓ 0.0 0 11

Index Scan using users_pkey on users elpaccoord (cost=0.28..6.29 rows=1 width=20) (actual time=0.000..0.000 rows=0 loops=11)

  • Index Cond: (id = elpacprimary.primary_user_id)
56. 0.000 0.000 ↓ 0.0 0

Sort (cost=514.77..518.43 rows=1,464 width=260) (never executed)

  • Sort Key: user_attribute_vals_2.user_id
57. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=423.15..437.79 rows=1,464 width=260) (never executed)

  • Group Key: user_attribute_vals_2.user_id
58. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=2.50..336.86 rows=3,835 width=16) (never executed)

  • Hash Cond: (user_attribute_vals_2.user_attribute_id = user_attributes_2.id)
59. 0.000 0.000 ↓ 0.0 0

Seq Scan on user_attribute_vals user_attribute_vals_2 (cost=0.00..303.87 rows=9,587 width=16) (never executed)

60. 0.000 0.000 ↓ 0.0 0

Hash (cost=2.40..2.40 rows=8 width=4) (never executed)

61. 0.000 0.000 ↓ 0.0 0

Seq Scan on user_attributes user_attributes_2 (cost=0.00..2.40 rows=8 width=4) (never executed)

  • Filter: ((name)::text = ANY ('{PHONE_AREA_CODE,PHONE_EXCHANGE,PHONE_SUFFIX,PHONE_EXT,FAX_AREA_CODE,FAX_EXCHANGE,FAX_SUFFIX,EMAIL}'::text[]))
62. 1.229 8.954 ↑ 1.0 1,463 11

Sort (cost=514.77..518.43 rows=1,464 width=260) (actual time=0.747..0.814 rows=1,463 loops=11)

  • Sort Key: user_attribute_vals_3.user_id
  • Sort Method: quicksort Memory: 190kB
63. 4.821 7.725 ↑ 1.0 1,463 1

HashAggregate (cost=423.15..437.79 rows=1,464 width=260) (actual time=7.197..7.725 rows=1,463 loops=1)

  • Group Key: user_attribute_vals_3.user_id
64. 2.243 2.904 ↓ 2.4 9,058 1

Hash Join (cost=2.50..336.86 rows=3,835 width=16) (actual time=0.029..2.904 rows=9,058 loops=1)

  • Hash Cond: (user_attribute_vals_3.user_attribute_id = user_attributes_3.id)
65. 0.649 0.649 ↑ 1.0 9,587 1

Seq Scan on user_attribute_vals user_attribute_vals_3 (cost=0.00..303.87 rows=9,587 width=16) (actual time=0.006..0.649 rows=9,587 loops=1)

66. 0.004 0.012 ↑ 1.0 8 1

Hash (cost=2.40..2.40 rows=8 width=4) (actual time=0.012..0.012 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
67. 0.008 0.008 ↑ 1.0 8 1

Seq Scan on user_attributes user_attributes_3 (cost=0.00..2.40 rows=8 width=4) (actual time=0.003..0.008 rows=8 loops=1)

  • Filter: ((name)::text = ANY ('{PHONE_AREA_CODE,PHONE_EXCHANGE,PHONE_SUFFIX,PHONE_EXT,FAX_AREA_CODE,FAX_EXCHANGE,FAX_SUFFIX,EMAIL}'::text[]))
  • Rows Removed by Filter: 12
68. 0.002 16.414 ↑ 1.0 1 1

Subquery Scan on *SELECT* 3 (cost=1,665.30..1,665.35 rows=1 width=522) (actual time=16.413..16.414 rows=1 loops=1)

69. 0.001 16.412 ↑ 1.0 1 1

Unique (cost=1,665.30..1,665.34 rows=1 width=522) (actual time=16.412..16.412 rows=1 loops=1)

70.          

Initplan (for Unique)

71. 0.000 0.000 ↓ 0.0 0

Seq Scan on programs programs_4 (cost=0.00..2.02 rows=1 width=4) (never executed)

  • Filter: ((name)::text = 'CAASPP'::text)
72. 0.000 0.000 ↓ 0.0 0

Seq Scan on programs programs_5 (cost=0.00..2.02 rows=1 width=4) (never executed)

  • Filter: ((name)::text = 'ELPAC'::text)
73. 0.023 16.411 ↑ 1.0 1 1

Sort (cost=1,661.25..1,661.26 rows=1 width=522) (actual time=16.411..16.411 rows=1 loops=1)

  • Sort Key: lea_2.name, (CASE WHEN (roles_1.program_name = 'CAASPP'::program_name_type) THEN 'CAASPP'::text WHEN (roles_1.program_name = 'ELPAC'::program_name_type) THEN 'ELPAC'::text WHEN (roles_1.program_name = 'BOTH'::program_name_type) THEN 'CAASPP/ELPAC'::text ELSE NULL::text END), roles_1.name, users_1.firstname, users_1.lastname, (CASE WHEN (((max((DECODE( user_attribute_vals_4.user_attribute_id , 1 , user_attribute_vals_4.string_value , NULL::character varying ))::text)) <> ''::text) IS TRUE) THEN ((((((('('::text || (max((DECODE( user_attribute_vals_4.user_attribute_id , 1 , user_attribute_vals_4.string_value , NULL::character varying ))::text))) || ') '::text) || COALESCE((max((DECODE( user_attribute_vals_4.user_attribute_id , 2 , user_attribute_vals_4.string_value , NULL::character varying ))::text)), ' '::text)) || '-'::text) || COALESCE((max((DECODE( user_attribute_vals_4.user_attribute_id , 3 , user_attribute_vals_4.string_value , NULL::character varying ))::text)), ' '::text)) || ' '::text) || (max((DECODE( user_attribute_vals_4.user_attribute_id , 4 , user_attribute_vals_4.string_value , NULL::character varying ))::text))) ELSE ''::text END), (max((DECODE( user_attribute_vals_4.user_attribute_id , 12 , user_attribute_vals_4.string_value , NULL::character varying ))::text)), (CASE WHEN (((max((DECODE( user_attribute_vals_4.user_attribute_id , 5 , user_attribute_vals_4.string_value , NULL::character varying ))::text)) <> ''::text) IS TRUE) THEN ((((('('::text || (max((DECODE( user_attribute_vals_4.user_attribute_id , 5 , user_attribute_vals_4.string_value , NULL::character varying ))::text))) || ') '::text) || COALESCE((max((DECODE( user_attribute_vals_4.user_attribute_id , 6 , user_attribute_vals_4.string_value , NULL::character varying ))::text)), ' '::text)) || '-'::text) || COALESCE((max((DECODE( user_attribute_vals_4.user_attribute_id , 7 , user_attribute_vals_4.string_value , NULL::character varying ))::text)), ' '::text)) ELSE ''::text END), (CASE WHEN our_1.new_user_flag THEN 'Y'::text ELSE ''::text END), (CASE WHEN (roles_1.program_name = 'ELPAC'::program_name_type) THEN CASE WHEN (nvl((elpacprimary_1.primary_user_id)::numeric, '0'::numeric) = '0'::numeric) THEN ''::character varying ELSE elpaccoord_1.firstname END ELSE CASE WHEN (nvl((caasppprimary_2.primary_user_id)::numeric, '0'::numeric) = '0'::numeric) THEN ''::character varying ELSE caasppcoord_2.firstname END END), (CASE WHEN (roles_1.program_name = 'ELPAC'::program_name_type) THEN CASE WHEN (nvl((elpacprimary_1.primary_user_id)::numeric, '0'::numeric) = '0'::numeric) THEN ''::character varying ELSE elpaccoord_1.lastname END ELSE CASE WHEN (nvl((caasppprimary_2.primary_user_id)::numeric, '0'::numeric) = '0'::numeric) THEN ''::character varying ELSE caasppcoord_2.lastname END END), (CASE WHEN (roles_1.program_name = 'ELPAC'::program_name_type) THEN CASE WHEN (nvl((elpacprimary_1.primary_user_id)::numeric, '0'::numeric) = '0'::numeric) THEN ''::text ELSE CASE WHEN (((max((DECODE( user_attribute_vals_6.user_attribute_id , 5 , user_attribute_vals_6.string_value , NULL::character varying ))::text)) <> ''::text) IS TRUE) THEN ((((((('('::text || (max((DECODE( user_attribute_vals_6.user_attribute_id , 1 , user_attribute_vals_6.string_value , NULL::character varying ))::text))) || ') '::text) || COALESCE((max((DECODE( user_attribute_vals_6.user_attribute_id , 2 , user_attribute_vals_6.string_value , NULL::character varying ))::text)), ' '::text)) || '-'::text) || COALESCE((max((DECODE( user_attribute_vals_6.user_attribute_id , 3 , user_attribute_vals_6.string_value , NULL::character varying ))::text)), ' '::text)) || ' '::text) || COALESCE((max((DECODE( user_attribute_vals_6.user_attribute_id , 4 , user_attribute_vals_6.string_value , NULL::character varying ))::text)), ' '::text)) ELSE ''::text END END ELSE CASE WHEN (nvl((caasppprimary_2.primary_user_id)::numeric, '0'::numeric) = '0'::numeric) THEN ''::text ELSE CASE WHEN (((max((DECODE( user_attribute_vals_5.user_attribute_id , 5 , user_attribute_vals_5.string_value , NULL::character varying ))::text)) <> ''::text) IS TRUE) THEN ((((((('('::text || (max((DECODE( user_attribute_vals_5.user_attribute_id , 1 , user_attribute_vals_5.string_value , NULL::character varying ))::text))) || ') '::text) || COALESCE((max((DECODE( user_attribute_vals_5.user_attribute_id , 2 , user_attribute_vals_5.string_value , NULL::character varying ))::text)), ' '::text)) || '-'::text) || COALESCE((max((DECODE( user_attribute_vals_5.user_attribute_id , 3 , user_attribute_vals_5.string_value , NULL::character varying ))::text)), ' '::text)) || ' '::text) || COALESCE((max((DECODE( user_attribute_vals_5.user_attribute_id , 4 , user_attribute_vals_5.string_value , NULL::character varying ))::text)), ' '::text)) ELSE ''::text END END END), (CASE WHEN (roles_1.program_name = 'ELPAC'::program_name_type) THEN CASE WHEN (nvl((elpacprimary_1.primary_user_id)::numeric, '0'::numeric) = '0'::numeric) THEN ''::text ELSE (max((DECODE( user_attribute_vals_6.user_attribute_id , 12 , user_attribute_vals_6.string_value , NULL::character varying ))::text)) END ELSE CASE WHEN (nvl((caasppprimary_2.primary_user_id)::numeric, '0'::numeric) = '0'::numeric) THEN ''::text ELSE (max((DECODE( user_attribute_vals_5.user_attribute_id , 12 , user_attribute_vals_5.string_value , NULL::character varying ))::text)) END END), (CASE WHEN (roles_1.program_name = 'ELPAC'::program_name_type) THEN CASE WHEN (nvl((elpacprimary_1.primary_user_id)::numeric, '0'::numeric) = '0'::numeric) THEN ''::text ELSE CASE WHEN (((max((DECODE( user_attribute_vals_6.user_attribute_id , 5 , user_attribute_vals_6.string_value , NULL::character varying ))::text)) <> ''::text) IS TRUE) THEN ((((('('::text || (max((DECODE( user_attribute_vals_6.user_attribute_id , 5 , user_attribute_vals_6.string_value , NULL::character varying ))::text))) || ') '::text) || COALESCE((max((DECODE( user_attribute_vals_6.user_attribute_id , 6 , user_attribute_vals_6.string_value , NULL::character varying ))::text)), ' '::text)) || '-'::text) || COALESCE((max((DECODE( user_attribute_vals_6.user_attribute_id , 7 , user_attribute_vals_6.string_value , NULL::character varying ))::text)), ' '::text)) ELSE ''::text END END ELSE CASE WHEN (nvl((caasppprimary_2.primary_user_id)::numeric, '0'::numeric) = '0'::numeric) THEN ''::text ELSE CASE WHEN (((max((DECODE( user_attribute_vals_5.user_attribute_id , 5 , user_attribute_vals_5.string_value , NULL::character varying ))::text)) <> ''::text) IS TRUE) THEN ((((('('::text || (max((DECODE( user_attribute_vals_5.user_attribute_id , 5 , user_attribute_vals_5.string_value , NULL::character varying ))::text))) || ') '::text) || COALESCE((max((DECODE( user_attribute_vals_5.user_attribute_id , 6 , user_attribute_vals_5.string_value , NULL::character varying ))::text)), ' '::text)) || '-'::text) || COALESCE((max((DECODE( user_attribute_vals_5.user_attribute_id , 7 , user_attribute_vals_5.string_value , NULL::character varying ))::text)), ' '::text)) ELSE ''::text END END END)
  • Sort Method: quicksort Memory: 25kB
74. 8.298 16.388 ↑ 1.0 1 1

Nested Loop Left Join (cost=1,602.47..1,661.24 rows=1 width=522) (actual time=16.387..16.388 rows=1 loops=1)

  • Join Filter: (user_attribute_vals_6.user_id = elpaccoord_1.id)
  • Rows Removed by Join Filter: 1463
75. 0.002 8.090 ↑ 1.0 1 1

Merge Left Join (cost=1,087.70..1,109.68 rows=1 width=635) (actual time=8.089..8.090 rows=1 loops=1)

  • Merge Cond: (caasppcoord_2.id = user_attribute_vals_5.user_id)
76. 0.010 8.088 ↑ 1.0 1 1

Sort (cost=572.93..572.94 rows=1 width=383) (actual time=8.088..8.088 rows=1 loops=1)

  • Sort Key: caasppcoord_2.id
  • Sort Method: quicksort Memory: 25kB
77. 0.018 8.078 ↑ 1.0 1 1

Merge Left Join (cost=550.94..572.92 rows=1 width=383) (actual time=8.077..8.078 rows=1 loops=1)

  • Merge Cond: (users_1.id = user_attribute_vals_4.user_id)
78. 0.010 0.122 ↑ 1.0 1 1

Sort (cost=36.18..36.18 rows=1 width=131) (actual time=0.121..0.122 rows=1 loops=1)

  • Sort Key: users_1.id
  • Sort Method: quicksort Memory: 25kB
79. 0.001 0.112 ↑ 1.0 1 1

Nested Loop Left Join (cost=3.67..36.17 rows=1 width=131) (actual time=0.111..0.112 rows=1 loops=1)

80. 0.001 0.105 ↑ 1.0 1 1

Nested Loop Left Join (cost=3.39..29.86 rows=1 width=111) (actual time=0.104..0.105 rows=1 loops=1)

81. 0.001 0.100 ↑ 1.0 1 1

Nested Loop Left Join (cost=3.11..23.56 rows=1 width=91) (actual time=0.099..0.100 rows=1 loops=1)

  • Join Filter: ((elpacprimary_1.org_code)::text = (lea_2.org_code)::text)
82. 0.001 0.096 ↑ 1.0 1 1

Nested Loop Left Join (cost=3.11..21.51 rows=1 width=87) (actual time=0.095..0.096 rows=1 loops=1)

  • Join Filter: ((caasppprimary_2.org_code)::text = (lea_2.org_code)::text)
83. 0.006 0.092 ↑ 1.0 1 1

Nested Loop (cost=3.11..19.47 rows=1 width=83) (actual time=0.091..0.092 rows=1 loops=1)

  • Join Filter: (our_1.role_id = roles_1.id)
  • Rows Removed by Join Filter: 49
84. 0.001 0.053 ↓ 3.0 3 1

Nested Loop (cost=0.84..14.29 rows=1 width=64) (actual time=0.047..0.053 rows=3 loops=1)

85. 0.002 0.040 ↓ 3.0 3 1

Nested Loop (cost=0.57..12.68 rows=1 width=48) (actual time=0.037..0.040 rows=3 loops=1)

86. 0.016 0.016 ↑ 1.0 1 1

Index Scan using organizations_ak on organizations lea_2 (cost=0.29..6.31 rows=1 width=43) (actual time=0.016..0.016 rows=1 loops=1)

  • Index Cond: (((org_code)::text = '01611270000000'::text) AND (ayc_id = 6))
87. 0.022 0.022 ↓ 3.0 3 1

Index Scan using org_user_role_ayc_ak on org_user_roles our_1 (cost=0.28..6.36 rows=1 width=13) (actual time=0.020..0.022 rows=3 loops=1)

  • Index Cond: ((organization_id = lea_2.id) AND (ayc_id = 6))
88. 0.012 0.012 ↑ 1.0 1 3

Index Scan using users_pkey on users users_1 (cost=0.28..1.61 rows=1 width=20) (actual time=0.004..0.004 rows=1 loops=3)

  • Index Cond: (id = our_1.user_id)
  • Filter: (status = 1)
89. 0.027 0.033 ↓ 1.7 17 3

Seq Scan on roles roles_1 (cost=2.27..5.05 rows=10 width=27) (actual time=0.007..0.011 rows=17 loops=3)

  • Filter: ((NOT (hashed SubPlan 7)) AND (id = ANY ('{32,9,30,3,36,33,35,34,84,6,4,81,57,58,2,56,8,83,10,60}'::integer[])))
  • Rows Removed by Filter: 3
90.          

SubPlan (for Seq Scan)

91. 0.006 0.006 ↑ 1.0 2 1

Seq Scan on roles roles_4 (cost=0.00..2.26 rows=2 width=4) (actual time=0.003..0.006 rows=2 loops=1)

  • Filter: ((name)::text = ANY ('{"LEA ELPAC Coordinator","LEA CAASPP Coordinator"}'::text[]))
  • Rows Removed by Filter: 19
92. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on lea_primary_user caasppprimary_2 (cost=0.00..2.04 rows=1 width=520) (actual time=0.003..0.003 rows=0 loops=1)

  • Filter: (((org_code)::text = '01611270000000'::text) AND (program_id = $4) AND (ayc_id = 6))
  • Rows Removed by Filter: 2
93. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on lea_primary_user elpacprimary_1 (cost=0.00..2.04 rows=1 width=520) (actual time=0.003..0.003 rows=0 loops=1)

  • Filter: (((org_code)::text = '01611270000000'::text) AND (program_id = $5) AND (ayc_id = 6))
  • Rows Removed by Filter: 2
94. 0.004 0.004 ↓ 0.0 0 1

Index Scan using users_pkey on users caasppcoord_2 (cost=0.28..6.29 rows=1 width=20) (actual time=0.004..0.004 rows=0 loops=1)

  • Index Cond: (id = caasppprimary_2.primary_user_id)
95. 0.006 0.006 ↓ 0.0 0 1

Index Scan using users_pkey on users elpaccoord_1 (cost=0.28..6.29 rows=1 width=20) (actual time=0.006..0.006 rows=0 loops=1)

  • Index Cond: (id = elpacprimary_1.primary_user_id)
96. 0.501 7.938 ↑ 7.4 198 1

Sort (cost=514.77..518.43 rows=1,464 width=260) (actual time=7.929..7.938 rows=198 loops=1)

  • Sort Key: user_attribute_vals_4.user_id
  • Sort Method: quicksort Memory: 190kB
97. 4.708 7.437 ↑ 1.0 1,463 1

HashAggregate (cost=423.15..437.79 rows=1,464 width=260) (actual time=6.907..7.437 rows=1,463 loops=1)

  • Group Key: user_attribute_vals_4.user_id
98. 2.729 2.729 ↓ 2.4 9,058 1

Hash Join (cost=2.50..336.86 rows=3,835 width=16) (actual time=0.026..2.729 rows=9,058 loops=1)