explain.depesz.com

A tool for finding a real cause for slow queries.

Result: RjRH

options
Did it help? Consider supporting us - Bitcoin address: 12v2hUztAk2LgzQ9H9LMwuU32urHMjZQnq
# exclusive inclusive rows x rows loops node
1. 0.094 1422.359 ↓ 36.0 36 1

Sort (cost=655976.00..655976.01 rows=1 width=132) (actual time=1422.355..1422.359 rows=36 loops=1)

  • Sort Key: (lower(CASE WHEN (public.r_user_integration.last_name IS NULL) THEN ((public.m_user.last_name || ', '::text) || public.m_user.first_name) ELSE (((((((public.r_user_integration.last_name)::text || ', '::text) || (public.r_user_integration.firs
  • Sort Method: quicksort Memory: 32kB
2. 15.683 1422.265 ↓ 36.0 36 1

HashAggregate (cost=655975.96..655975.99 rows=1 width=132) (actual time=1422.228..1422.265 rows=36 loops=1)

3. 8.046 1406.582 ↓ 13951.0 13951 1

Nested Loop Left Join (cost=655797.10..655975.93 rows=1 width=132) (actual time=1302.674..1406.582 rows=13951 loops=1)

  • Join Filter: ((public.r_class_api.id)::text = (public.r_user_integration.cid)::text)
4. 1.264 1398.536 ↓ 13951.0 13951 1

Nested Loop Left Join (cost=655797.10..655946.70 rows=1 width=80) (actual time=1302.671..1398.536 rows=13951 loops=1)

5. 6.755 1369.370 ↓ 13951.0 13951 1

Nested Loop Left Join (cost=655797.10..655946.39 rows=1 width=70) (actual time=1302.665..1369.370 rows=13951 loops=1)

6. 2.964 1320.762 ↓ 13951.0 13951 1

Nested Loop Left Join (cost=655797.10..655945.99 rows=1 width=68) (actual time=1302.655..1320.762 rows=13951 loops=1)

7. 0.027 1307.958 ↓ 40.0 40 1

Nested Loop (cost=655797.10..655945.24 rows=1 width=68) (actual time=1302.646..1307.958 rows=40 loops=1)

8. 0.044 1307.731 ↓ 40.0 40 1

Nested Loop (cost=655797.10..655941.18 rows=1 width=30) (actual time=1302.635..1307.731 rows=40 loops=1)

  • Join Filter: (ur.account_id = rfa.accountid)
9. 0.030 1302.849 ↓ 3.3 82 1

Nested Loop (cost=655797.10..655861.60 rows=25 width=16) (actual time=1302.624..1302.849 rows=82 loops=1)

10. 0.006 1302.619 ↑ 1.0 25 1

Limit (cost=655797.10..655797.16 rows=25 width=49) (actual time=1302.604..1302.619 rows=25 loops=1)

11. 25.967 1302.613 ↑ 824.5 25 1

Sort (cost=655797.10..655848.63 rows=20612 width=49) (actual time=1302.604..1302.613 rows=25 loops=1)

  • Sort Key: (min(lower(CASE WHEN (public.r_user_integration.last_name IS NULL) THEN ((public.m_user.last_name || ', '::text) || public.m_user.first_name) ELSE (((((((public.r_user_integration.la
  • Sort Method: top-N heapsort Memory: 26kB
12. 258.593 1276.646 ↑ 1.2 17832 1

HashAggregate (cost=654494.02..655215.44 rows=20612 width=49) (actual time=1272.812..1276.646 rows=17832 loops=1)

13. 34.206 1018.053 ↓ 6.0 124227 1

Merge Left Join (cost=651343.23..654390.96 rows=20612 width=49) (actual time=970.964..1018.053 rows=124227 loops=1)

  • Merge Cond: (((public.r_class_api.id)::text = (public.r_user_integration.cid)::text) AND (public.r_class_api.account = public.r_user_integration.account))
14. 53.549 983.847 ↓ 6.0 124227 1

Sort (cost=604120.19..604171.72 rows=20612 width=32) (actual time=970.961..983.847 rows=124227 loops=1)

  • Sort Key: public.r_class_api.id, public.r_class_api.account
  • Sort Method: quicksort Memory: 12775kB
15. 0.000 930.298 ↓ 6.0 124227 1

Nested Loop Left Join (cost=2411.42..602643.21 rows=20612 width=32) (actual time=16.054..930.298 rows=124227 loops=1)

16. 115.621 684.384 ↓ 6.0 124227 1

Nested Loop Left Join (cost=2411.42..596266.61 rows=20612 width=22) (actual time=16.049..684.384 rows=124227 loops=1)

17. 125.314 320.309 ↓ 6.0 124227 1

Nested Loop Left Join (cost=2411.42..588002.88 rows=20612 width=22) (actual time=16.042..320.309 rows=124227 loops=1)

18. 9.590 186.685 ↓ 2.1 18385 1

Nested Loop (cost=2411.42..581570.78 rows=8586 width=22) (actual time=16.035..186.685 rows=18385 loops=1)

19. 10.376 121.940 ↑ 2.6 18385 1

Nested Loop (cost=2411.42..393125.13 rows=47995 width=12) (actual time=16.027..121.940 rows=18385 loops=1)

20. 32.249 46.923 ↑ 8.0 21547 1

Bitmap Heap Scan on r_faculty_account rfa (cost=2411.42..194711.04 rows=172001 width=4) (actual time=15.977..46.923 rows=21547 loops=1)

  • Recheck Cond: (accountid = ANY ('{78029,64869,64782,65012,64872,59276,65010,64870,64862,65011,59277,64867,65013,59275,77207,77208,77212,77
  • Filter: active_flag
21. 14.674 14.674 ↑ 1.2 174049 1

Bitmap Index Scan on ri2_faculty_account (cost=0.00..2368.42 rows=207825 width=0) (actual time=14.674..14.674 rows=174049 loops=1)

  • Index Cond: (accountid = ANY ('{78029,64869,64782,65012,64872,59276,65010,64870,64862,65011,59277,64867,65013,59275,77207,77208,7721
22. 64.641 64.641 ↑ 1.0 1 21547

Index Scan using user_role_user_id_key on user_role ur (cost=0.00..1.14 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=21547)

  • Index Cond: (ur.user_id = rfa.userid)
  • Filter: ((ur.active IS TRUE) AND (ur.role_id = ANY ('{3,6,7}'::integer[])))
23. 55.155 55.155 ↑ 1.0 1 18385

Index Scan using m_user_pkey on m_user (cost=0.00..3.91 rows=1 width=18) (actual time=0.003..0.003 rows=1 loops=18385)

  • Index Cond: (public.m_user.id = rfa.userid)
24. 8.310 8.310 ↑ 1.7 7 1385

Index Scan using user_rights_class_user_role_id_idx on user_rights_class urc (cost=0.00..0.60 rows=12 width=8) (actual time=0.002..0.006 rows=7 loops=1385)

  • Index Cond: (ur.id = urc.user_role_id)
  • Filter: (urc.active IS TRUE)
25. 248.454 248.454 ↑ 1.0 1 124227

Index Scan using m_class_pkey on m_class (cost=0.00..0.39 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=124227)

  • Index Cond: (urc.class_id = public.m_class.id)
26. 248.454 248.454 ↓ 0.0 0 124227

Index Scan using r_class_api_classid_idx on r_class_api (cost=0.00..0.30 rows=1 width=18) (actual time=0.002..0.002 rows=0 loops=124227)

  • Index Cond: (public.m_class.id = public.r_class_api.classid)
27. 0.000 0.000 ↓ 0.0 0

Sort (cost=47220.41..48184.66 rows=385700 width=47) (actual time=.. rows= loops=0)

  • Sort Key: public.r_user_integration.cid, public.r_user_integration.account
28. 0.000 0.000 ↓ 0.0 0

Seq Scan on r_user_integration (cost=0.00..11433.00 rows=385700 width=47) (actual time=.. rows= loops=0)

29. 0.200 0.200 ↓ 3.0 3 25

Index Scan using user_role_user_id_key on user_role ur (cost=0.00..2.56 rows=1 width=12) (actual time=0.005..0.008 rows=3 loops=25)

  • Index Cond: (ur.user_id = public.m_user.id)
  • Filter: ((ur.active IS TRUE) AND (ur.role_id = ANY ('{3,6,7}'::integer[])))
30. 4.838 4.838 ↑ 1.0 1 82

Index Scan using ri1_faculty_account on r_faculty_account rfa (cost=0.00..3.17 rows=1 width=18) (actual time=0.022..0.059 rows=1 loops=82)

  • Index Cond: (rfa.userid = ur.user_id)
  • Filter: (rfa.active_flag AND (rfa.accountid = ANY ('{78029,64869,64782,65012,64872,59276,65010,64870,64862,65011,59277,64867,65013,59275,77207,77208,77212,77215,77213,77206,77211,77513,77486,77514,77491,7
31. 0.200 0.200 ↑ 1.0 1 40

Index Scan using m_user_pkey on m_user (cost=0.00..4.05 rows=1 width=50) (actual time=0.005..0.005 rows=1 loops=40)

  • Index Cond: (public.m_user.id = rfa.userid)
32. 9.840 9.840 ↓ 29.0 348 40

Index Scan using user_rights_class_user_role_id_idx on user_rights_class urc (cost=0.00..0.60 rows=12 width=8) (actual time=0.005..0.246 rows=348 loops=40)

  • Index Cond: (urc.user_role_id = ur.id)
  • Filter: (urc.active IS TRUE)
33. 41.853 41.853 ↑ 1.0 1 13951

Index Scan using m_class_pkey on m_class (cost=0.00..0.39 rows=1 width=6) (actual time=0.003..0.003 rows=1 loops=13951)

  • Index Cond: (urc.class_id = public.m_class.id)
34. 27.902 27.902 ↓ 0.0 0 13951

Index Scan using r_class_api_classid_idx on r_class_api (cost=0.00..0.30 rows=1 width=18) (actual time=0.002..0.002 rows=0 loops=13951)

  • Index Cond: (public.m_class.id = public.r_class_api.classid)
35. 0.000 0.000 ↓ 0.0 0 13951

Index Scan using r_user_integration_account_idx on r_user_integration (cost=0.00..15.86 rows=891 width=82) (actual time=0.000..0.000 rows=0 loops=13951)

  • Index Cond: (public.r_class_api.account = public.r_user_integration.account)