explain.depesz.com

PostgreSQL's explain analyze made readable

Result: A5Wh

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 3,460.800 ↑ 21.3 17 1

Unique (cost=1,094.94..1,101.28 rows=362 width=208) (actual time=3,460.793..3,460.800 rows=17 loops=1)

2.          

CTE grupo_papel_usuario

3. 0.014 0.177 ↓ 24.0 48 1

Nested Loop (cost=21.02..35.10 rows=2 width=8) (actual time=0.094..0.177 rows=48 loops=1)

4. 0.003 0.136 ↓ 9.0 9 1

Nested Loop (cost=20.75..34.68 rows=1 width=8) (actual time=0.088..0.136 rows=9 loops=1)

5. 0.040 0.122 ↑ 1.0 1 1

Hash Right Join (cost=20.47..26.38 rows=1 width=8) (actual time=0.082..0.122 rows=1 loops=1)

  • Hash Cond: (groups_users_1.user_id = users.id)
6. 0.025 0.025 ↓ 1.2 334 1

Seq Scan on groups_users groups_users_1 (cost=0.00..4.83 rows=283 width=8) (actual time=0.004..0.025 rows=334 loops=1)

7. 0.000 0.057 ↑ 1.0 1 1

Hash (cost=20.46..20.46 rows=1 width=4) (actual time=0.057..0.057 rows=1 loops=1)

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

Seq Scan on users (cost=0.00..20.46 rows=1 width=4) (actual time=0.004..0.057 rows=1 loops=1)

  • Filter: ((login)::text = 'admin'::text)
  • Rows Removed by Filter: 436
9. 0.011 0.011 ↓ 9.0 9 1

Index Scan using index_members_on_user_id_and_project_id on members (cost=0.27..8.29 rows=1 width=8) (actual time=0.005..0.011 rows=9 loops=1)

  • Index Cond: (user_id = users.id)
10. 0.027 0.027 ↓ 2.5 5 9

Index Scan using index_member_roles_on_member_id on member_roles (cost=0.28..0.41 rows=2 width=8) (actual time=0.002..0.003 rows=5 loops=9)

  • Index Cond: (member_id = members.id)
11.          

Initplan (forUnique)

12. 0.000 0.000 ↓ 0.0 0

Seq Scan on users users_1 (cost=0.00..20.46 rows=1 width=4) (never executed)

  • Filter: ((login)::text = 'admin'::text)
13. 0.003 0.003 ↑ 1.0 1 1

CTE Scan on grupo_papel_usuario grupo_papel_usuario_3 (cost=0.00..0.04 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=1)

  • Filter: (role_id = 17)
  • Rows Removed by Filter: 12
14. 0.039 3,460.795 ↑ 21.3 17 1

Sort (cost=1,039.33..1,040.24 rows=362 width=208) (actual time=3,460.793..3,460.795 rows=17 loops=1)

  • Sort Key: f.id DESC, (((('<div style="width:100%" class="text-center"><b>'::text || (f.servico)::text) || '</b><br><br></div>'::text) || f.acoes)), (COALESCE(f.informacoes, (i.subject)::text)), f.estado, ((((u.firstname)::text || ' '::text) || (u.lastname)::text)), f.data_de_criacao
  • Sort Method: quicksort Memory: 39kB
15. 0.077 3,460.756 ↑ 21.3 17 1

Hash Left Join (cost=377.03..1,023.95 rows=362 width=208) (actual time=3,460.370..3,460.756 rows=17 loops=1)

  • Hash Cond: (COALESCE((campo_servidor.value)::integer, u.id) = groups_users.user_id)
  • Filter: ((((hashed SubPlan 2) AND (i.status_id = ANY ('{20,10,26}'::integer[]))) OR (i.status_id <> ALL ('{20,10,26}'::integer[]))) AND CASE WHEN ((i.tracker_id = 34) AND (i.project_id = 8)) THEN ((u.login)::text = 'admin'::text) WHEN ((i.tracker_id = 17) AND (i.status_id = ANY ('{21,17}'::integer[]))) THEN (hashed SubPlan 4) WHEN ((i.tracker_id = 18) AND (i.status_id = 17)) THEN (hashed SubPlan 5) WHEN ((i.tracker_id = 15) AND (i.status_id = ANY ('{21,17}'::integer[]))) THEN $7 ELSE true END)
  • Rows Removed by Filter: 11
16. 0.016 3,460.389 ↑ 28.1 24 1

Hash Left Join (cost=368.52..959.55 rows=675 width=247) (actual time=3,460.058..3,460.389 rows=24 loops=1)

  • Hash Cond: (campo_solicitante.value = (u.login)::text)
17. 0.016 3,460.178 ↑ 10.4 24 1

Nested Loop Left Join (cost=343.68..922.35 rows=250 width=221) (actual time=3,459.855..3,460.178 rows=24 loops=1)

18. 0.065 3,460.018 ↑ 10.4 24 1

Hash Right Join (cost=343.39..475.21 rows=250 width=214) (actual time=3,459.844..3,460.018 rows=24 loops=1)

  • Hash Cond: (campo_servidor.customized_id = i.id)
19. 0.178 0.210 ↓ 10.9 348 1

Bitmap Heap Scan on custom_values campo_servidor (cost=4.66..136.34 rows=32 width=15) (actual time=0.046..0.210 rows=348 loops=1)

  • Recheck Cond: (custom_field_id = 77)
  • Filter: ((customized_type)::text = 'Issue'::text)
  • Heap Blocks: exact=96
20. 0.032 0.032 ↓ 7.9 377 1

Bitmap Index Scan on index_custom_values_on_custom_field_id (cost=0.00..4.65 rows=48 width=0) (actual time=0.032..0.032 rows=377 loops=1)

  • Index Cond: (custom_field_id = 77)
21. 0.012 3,459.743 ↑ 10.4 24 1

Hash (cost=335.61..335.61 rows=250 width=203) (actual time=3,459.743..3,459.743 rows=24 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
22. 0.015 3,459.731 ↑ 10.4 24 1

Hash Right Join (cost=203.74..335.61 rows=250 width=203) (actual time=3,459.721..3,459.731 rows=24 loops=1)

  • Hash Cond: (campo_setor_providencias.customized_id = i.id)
  • Filter: CASE WHEN (i.project_id = 11) THEN (hashed SubPlan 7) ELSE true END
23. 0.001 0.006 ↓ 0.0 0 1

Bitmap Heap Scan on custom_values campo_setor_providencias (cost=4.66..136.34 rows=32 width=15) (actual time=0.006..0.006 rows=0 loops=1)

  • Recheck Cond: (custom_field_id = 93)
  • Filter: ((customized_type)::text = 'Issue'::text)
  • Heap Blocks: exact=1
24. 0.005 0.005 ↑ 48.0 1 1

Bitmap Index Scan on index_custom_values_on_custom_field_id (cost=0.00..4.65 rows=48 width=0) (actual time=0.005..0.005 rows=1 loops=1)

  • Index Cond: (custom_field_id = 93)
25. 0.010 3,459.710 ↑ 20.8 24 1

Hash (cost=161.83..161.83 rows=500 width=203) (actual time=3,459.710..3,459.710 rows=24 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
26. 0.027 3,459.700 ↑ 20.8 24 1

Hash Join (cost=143.08..161.83 rows=500 width=203) (actual time=3,459.682..3,459.700 rows=24 loops=1)

  • Hash Cond: (f.id = i.id)
27. 3,458.055 3,458.055 ↑ 41.7 24 1

Function Scan on cs_fila_por_usuario f (cost=0.25..10.25 rows=1,000 width=164) (actual time=3,458.054..3,458.055 rows=24 loops=1)

28. 0.672 1.618 ↓ 2.0 3,601 1

Hash (cost=120.55..120.55 rows=1,782 width=39) (actual time=1.618..1.618 rows=3,601 loops=1)

  • Buckets: 4096 (originally 2048) Batches: 1 (originally 1) Memory Usage: 284kB
29. 0.946 0.946 ↓ 2.0 3,601 1

Seq Scan on issues i (cost=0.00..120.55 rows=1,782 width=39) (actual time=0.006..0.946 rows=3,601 loops=1)

  • Filter: CASE WHEN (assigned_to_id IS NOT NULL) THEN (assigned_to_id = $4) ELSE true END
30.          

SubPlan (forHash Right Join)

31. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.69..31.01 rows=1 width=13) (never executed)

32. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.55..30.58 rows=2 width=4) (never executed)

33. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.27..28.77 rows=1 width=4) (never executed)

34. 0.000 0.000 ↓ 0.0 0

Seq Scan on users users_2 (cost=0.00..20.46 rows=1 width=4) (never executed)

  • Filter: ((login)::text = 'admin'::text)
35. 0.000 0.000 ↓ 0.0 0

Index Scan using index_members_on_user_id_and_project_id on members members_1 (cost=0.27..8.29 rows=1 width=8) (never executed)

  • Index Cond: ((user_id = users_2.id) AND (project_id = 11))
36. 0.000 0.000 ↓ 0.0 0

Index Scan using index_member_roles_on_member_id on member_roles member_roles_1 (cost=0.28..1.79 rows=2 width=8) (never executed)

  • Index Cond: (member_id = members_1.id)
37. 0.000 0.000 ↓ 0.0 0

Index Scan using roles_pkey on roles (cost=0.14..0.21 rows=1 width=17) (never executed)

  • Index Cond: (id = member_roles_1.role_id)
  • Filter: ((name)::text ~~* '%-P.'::text)
38. 0.144 0.144 ↑ 1.0 1 24

Index Scan using custom_values_customized on custom_values campo_solicitante (cost=0.29..1.78 rows=1 width=15) (actual time=0.005..0.006 rows=1 loops=24)

  • Index Cond: (((customized_type)::text = 'Issue'::text) AND (customized_id = i.id))
  • Filter: (custom_field_id = 14)
  • Rows Removed by Filter: 10
39. 0.115 0.195 ↑ 1.0 437 1

Hash (cost=19.37..19.37 rows=437 width=37) (actual time=0.195..0.195 rows=437 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 39kB
40. 0.080 0.080 ↑ 1.0 437 1

Seq Scan on users u (cost=0.00..19.37 rows=437 width=37) (actual time=0.002..0.080 rows=437 loops=1)

41. 0.043 0.093 ↓ 1.2 334 1

Hash (cost=4.83..4.83 rows=283 width=8) (actual time=0.093..0.093 rows=334 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
42. 0.050 0.050 ↓ 1.2 334 1

Seq Scan on groups_users (cost=0.00..4.83 rows=283 width=8) (actual time=0.006..0.050 rows=334 loops=1)

43.          

SubPlan (forHash Left Join)

44. 0.191 0.191 ↓ 2.0 2 1

CTE Scan on grupo_papel_usuario (cost=0.00..0.04 rows=1 width=4) (actual time=0.114..0.191 rows=2 loops=1)

  • Filter: (role_id = 16)
  • Rows Removed by Filter: 46
45. 0.006 0.006 ↓ 0.0 0 1

CTE Scan on grupo_papel_usuario grupo_papel_usuario_1 (cost=0.00..0.04 rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=1)

  • Filter: (role_id = 23)
  • Rows Removed by Filter: 48
46. 0.000 0.000 ↓ 0.0 0

CTE Scan on grupo_papel_usuario grupo_papel_usuario_2 (cost=0.00..0.04 rows=1 width=4) (never executed)

  • Filter: (role_id = 23)
Planning time : 2.155 ms
Execution time : 3,460.973 ms