explain.depesz.com

PostgreSQL's explain analyze made readable

Result: r3uZ

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Aggregate (cost=129,132,799.89..129,132,799.90 rows=1 width=8) (actual rows= loops=)

  • -> Unique (cost=129,017,502.18..129132799.87 rows=1 width=4) = 18,855
2. 0.000 0.000 ↓ 0.0

Nested Loop Semi Join (cost=129,017,502.18..129,132,799.87 rows=1 width=4) (actual rows= loops=)

  • Join Filter: (t7.id = v0.id) "clients_client"."status" IN (50,
3. 0.000 0.000 ↓ 0.0

Merge Join (cost=129,010,720.40..129,103,546.68 rows=60,549 width=8) (actual rows= loops=)

  • Merge Cond: (clients_client.id = t7.client_id)
4. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=129,010,657.48..129,065,136.82 rows=71,725 width=4) (actual rows= loops=)

  • Merge Cond: (clients_client.id = clients_client_agents.client_id)
  • Filter: ((clients_client_agents.user_id = 29,532) OR (hashed SubPlan 1))
5. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=129,010,587.58..129,036,696.98 rows=143,422 width=8) (actual rows= loops=)

  • Merge Cond: (clients_client.id = clients_groupassignment.client_id)
  • -> Sort (cost=129,010,587.15..129010945.71 rows=143,422 width=4)ployees" U1
  • Sort Key: clients_client.id (
6. 0.000 0.000 ↓ 0.0

Index Scan using clients_client_brokerage_id on clients_client (cost=0.43..128,998,303.12 rows=143,422 width=4) (actual rows= loops=)

  • Index Cond: (brokerage_id = 18,855)id" = 29,532))
  • Filter: ((status <> ALL ('{50,70}'::integer[])) AND (alternatives: SubPlan 6 or hashed SubPlan 11))
7.          

SubPlan (for Index Scan)

8. 0.000 0.000 ↓ 0.0

Nested Loop (cost=390.48..415.82 rows=1 width=0) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Nested Loop (cost=160.79..183.88 rows=1 width=16) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Index Scan using action_required_actionrequired_client_id_7c6f959f on action_required_actionrequired v0_1 (cost=160.37..181.22 rows=1 width=20) (actual rows= loops=)

  • Index Cond: (client_id = clients_client.id)
  • Filter: ((NOT action_completed) AND ((((responsible_team = 20) OR (handoff_reference_time < '2020-10-13 18:29:53.600957+00'::timestamp with time zone)) AND (hashed SubPlan 2)) OR ((responsible_team = 10) AND (handoff_reference_time >= '2020-10-13 18:29:53.600957+00'::timestamp with time zone)) OR (hashed SubPlan 4) OR (hashed SubPlan 5)) AND (site_team_group_id = ANY ('{1280,1283,1156,1159,1162,1292,1294,1298,1174,1304,1305,1179,1182,1184,1188,1189,1201,1193,1194,1224,1081,1083,1086,1217,1092,1094,1096,1098,1099,1230,1231,1110,1112,1241,1114,1117,1124,1126,1254,1130,1259,1266,1267,1143,1276,1278}'::integer[])))
11.          

SubPlan (for Index Scan)

12. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.56..69.36 rows=46 width=4) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Index Scan using accounts_employeegroup_employees_e8701ad4 on accounts_employeegroup_employees u1_1 (cost=0.29..43.69 rows=46 width=4) (actual rows= loops=)

  • Index Cond: (user_id = 29,532)
14. 0.000 0.000 ↓ 0.0

Index Only Scan using accounts_employeegroup_pkey on accounts_employeegroup u0_1 (cost=0.28..0.56 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (id = u1_1.employeegroup_id)
15. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.56..20.99 rows=1 width=4) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Index Scan using accounts_employeegroup_team_type_a13504aa on accounts_employeegroup u0_3 (cost=0.28..4.35 rows=11 width=4) (actual rows= loops=)

  • Index Cond: (team_type = 20)
17. 0.000 0.000 ↓ 0.0

Index Only Scan using accounts_employeegroup_employees_employeegroup_id_user_id_key on accounts_employeegroup_employees u1_3 (cost=0.29..1.51 rows=1 width=4) (actual rows= loops=)

  • Index Cond: ((employeegroup_id = u0_3.id) AND (user_id = 29,532))
18. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.56..69.36 rows=46 width=4) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Index Scan using accounts_employeegroup_employees_e8701ad4 on accounts_employeegroup_employees u1_4 (cost=0.29..43.69 rows=46 width=4) (actual rows= loops=)

  • Index Cond: (user_id = 29,532)
20. 0.000 0.000 ↓ 0.0

Index Only Scan using accounts_employeegroup_pkey on accounts_employeegroup u0_4 (cost=0.28..0.56 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (id = u1_4.employeegroup_id)
21. 0.000 0.000 ↓ 0.0

Index Scan using clients_client_pkey on clients_client v1_1 (cost=0.43..2.65 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (id = clients_client.id)
  • Filter: ((status <> ALL ('{50,70}'::integer[])) AND (brokerage_id = 18,855))
22. 0.000 0.000 ↓ 0.0

Index Scan using accounts_employeegroup_pkey on accounts_employeegroup v3_1 (cost=229.69..231.93 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (id = v0_1.site_team_group_id)
  • Filter: ((((v0_1.responsible_team = 20) OR (v0_1.handoff_reference_time < '2020-10-13 18:29:53.600957+00'::timestamp with time zone)) AND (hashed SubPlan 2)) OR ((v0_1.responsible_team = 10) AND (v0_1.handoff_reference_time >= '2020-10-13 18:29:53.600957+00'::timestamp with time zone) AND (hashed SubPlan 3)) OR (hashed SubPlan 4) OR ((central_leasing_team_id IS NULL) AND (hashed SubPlan 5)))
23.          

SubPlan (for Index Scan)

24. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.56..69.36 rows=46 width=4) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Index Scan using accounts_employeegroup_employees_e8701ad4 on accounts_employeegroup_employees u1_2 (cost=0.29..43.69 rows=46 width=4) (actual rows= loops=)

  • Index Cond: (user_id = 29,532)
26. 0.000 0.000 ↓ 0.0

Index Only Scan using accounts_employeegroup_pkey on accounts_employeegroup u0_2 (cost=0.28..0.56 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (id = u1_2.employeegroup_id)
27. 0.000 0.000 ↓ 0.0

Gather (cost=8,174.69..43,104.58 rows=4,819 width=4) (actual rows= loops=)

  • Workers Planned: 2
28. 0.000 0.000 ↓ 0.0

Nested Loop (cost=7,174.69..41,622.68 rows=2,008 width=4) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Hash Join (cost=7,174.26..28,076.86 rows=8,721 width=4) (actual rows= loops=)

  • Hash Cond: (v0_2.site_team_group_id = v3_2.id)
  • Join Filter: ((((v0_2.responsible_team = 20) OR (v0_2.handoff_reference_time < '2020-10-13 18:29:53.600957+00'::timestamp with time zone)) AND (hashed SubPlan 7)) OR ((v0_2.responsible_team = 10) AND (v0_2.handoff_reference_time >= '2020-10-13 18:29:53.600957+00'::timestamp with time zone) AND (hashed SubPlan 8)) OR (hashed SubPlan 9) OR ((v3_2.central_leasing_team_id IS NULL) AND (hashed SubPlan 10)))
30. 0.000 0.000 ↓ 0.0

Parallel Bitmap Heap Scan on action_required_actionrequired v0_2 (cost=6,667.32..27,546.42 rows=8,907 width=20) (actual rows= loops=)

  • Recheck Cond: (site_team_group_id = ANY ('{1280,1283,1156,1159,1162,1292,1294,1298,1174,1304,1305,1179,1182,1184,1188,1189,1201,1193,1194,1224,1081,1083,1086,1217,1092,1094,1096,1098,1099,1230,1231,1110,1112,1241,1114,1117,1124,1126,1254,1130,1259,1266,1267,1143,1276,1278}'::integer[]))
  • Filter: ((NOT action_completed) AND ((((responsible_team = 20) OR (handoff_reference_time < '2020-10-13 18:29:53.600957+00'::timestamp with time zone)) AND (hashed SubPlan 7)) OR ((responsible_team = 10) AND (handoff_reference_time >= '2020-10-13 18:29:53.600957+00'::timestamp with time zone)) OR (hashed SubPlan 9) OR (hashed SubPlan 10)))
31. 0.000 0.000 ↓ 0.0

BitmapAnd (cost=6,507.39..6,507.39 rows=24,430 width=0) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on action_required_actionrequired_site_team_group_id_c9564769 (cost=0.00..3,127.70 rows=240,449 width=0) (actual rows= loops=)

  • Index Cond: (site_team_group_id = ANY ('{1280,1283,1156,1159,1162,1292,1294,1298,1174,1304,1305,1179,1182,1184,1188,1189,1201,1193,1194,1224,1081,1083,1086,1217,1092,1094,1096,1098,1099,1230,1231,1110,1112,1241,1114,1117,1124,1126,1254,1130,1259,1266,1267,1143,1276,1278}'::integer[]))
33. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on action_required_actionre_action_completed_polymor_bc9ca40d_idx (cost=0.00..3,368.75 rows=258,443 width=0) (actual rows= loops=)

  • Index Cond: (action_completed = false)
34.          

SubPlan (for Parallel Bitmap Heap Scan)

35. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.56..69.36 rows=46 width=4) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Index Scan using accounts_employeegroup_employees_e8701ad4 on accounts_employeegroup_employees u1_5 (cost=0.29..43.69 rows=46 width=4) (actual rows= loops=)

  • Index Cond: (user_id = 29,532)
37. 0.000 0.000 ↓ 0.0

Index Only Scan using accounts_employeegroup_pkey on accounts_employeegroup u0_5 (cost=0.28..0.56 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (id = u1_5.employeegroup_id)
38. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.56..20.99 rows=1 width=4) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Index Scan using accounts_employeegroup_team_type_a13504aa on accounts_employeegroup u0_7 (cost=0.28..4.35 rows=11 width=4) (actual rows= loops=)

  • Index Cond: (team_type = 20)
40. 0.000 0.000 ↓ 0.0

Index Only Scan using accounts_employeegroup_employees_employeegroup_id_user_id_key on accounts_employeegroup_employees u1_7 (cost=0.29..1.51 rows=1 width=4) (actual rows= loops=)

  • Index Cond: ((employeegroup_id = u0_7.id) AND (user_id = 29,532))
41. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.56..69.36 rows=46 width=4) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Index Scan using accounts_employeegroup_employees_e8701ad4 on accounts_employeegroup_employees u1_8 (cost=0.29..43.69 rows=46 width=4) (actual rows= loops=)

  • Index Cond: (user_id = 29,532)
43. 0.000 0.000 ↓ 0.0

Index Only Scan using accounts_employeegroup_pkey on accounts_employeegroup u0_8 (cost=0.28..0.56 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (id = u1_8.employeegroup_id)
44. 0.000 0.000 ↓ 0.0

Hash (cost=253.90..253.90 rows=1,890 width=8) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Seq Scan on accounts_employeegroup v3_2 (cost=0.00..253.90 rows=1,890 width=8) (actual rows= loops=)

46.          

SubPlan (for Hash Join)

47. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.56..69.36 rows=46 width=4) (actual rows= loops=)

48. 0.000 0.000 ↓ 0.0

Index Scan using accounts_employeegroup_employees_e8701ad4 on accounts_employeegroup_employees u1_6 (cost=0.29..43.69 rows=46 width=4) (actual rows= loops=)

  • Index Cond: (user_id = 29,532)
49. 0.000 0.000 ↓ 0.0

Index Only Scan using accounts_employeegroup_pkey on accounts_employeegroup u0_6 (cost=0.28..0.56 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (id = u1_6.employeegroup_id)
50. 0.000 0.000 ↓ 0.0

Index Scan using clients_client_pkey on clients_client v1_2 (cost=0.43..1.55 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (id = v0_2.client_id)
  • Filter: ((status <> ALL ('{50,70}'::integer[])) AND (brokerage_id = 18,855))
51. 0.000 0.000 ↓ 0.0

Index Only Scan using clients_groupassignment_client_id_group_id_55417b62_uniq on clients_groupassignment (cost=0.43..21,552.61 rows=1,051,742 width=8) (actual rows= loops=)

52. 0.000 0.000 ↓ 0.0

Index Only Scan using clients_client_agents_client_id_37afacd828f1e2f6_uniq on clients_client_agents (cost=0.43..23,165.51 rows=1,146,568 width=8) (actual rows= loops=)

53.          

SubPlan (for Merge Left Join)

54. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.56..69.36 rows=46 width=4) (actual rows= loops=)

55. 0.000 0.000 ↓ 0.0

Index Scan using accounts_employeegroup_employees_e8701ad4 on accounts_employeegroup_employees u1 (cost=0.29..43.69 rows=46 width=4) (actual rows= loops=)

  • Index Cond: (user_id = 29,532)
56. 0.000 0.000 ↓ 0.0

Index Only Scan using accounts_employeegroup_pkey on accounts_employeegroup u0 (cost=0.28..0.56 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (id = u1.employeegroup_id)
57. 0.000 0.000 ↓ 0.0

Index Scan using clients_groupassignment_client_id_group_id_55417b62_uniq on clients_groupassignment t7 (cost=0.43..35,022.37 rows=1,051,742 width=8) (actual rows= loops=)

58. 0.000 0.000 ↓ 0.0

Materialize (cost=6,781.78..7,455.61 rows=24 width=4) (actual rows= loops=)

59. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=6,781.78..7,455.49 rows=24 width=4) (actual rows= loops=)

  • Filter: ((v0.status = ANY ('{10,20,15}'::integer[])) OR ((v0.status = 30) AND (hashed SubPlan 12)))
60. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.42..641.99 rows=63 width=12) (actual rows= loops=)

61. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.99..593.50 rows=63 width=12) (actual rows= loops=)

62. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.56..114.58 rows=6 width=8) (actual rows= loops=)

63. 0.000 0.000 ↓ 0.0

Index Scan using accounts_employeegroup_employees_e8701ad4 on accounts_employeegroup_employees v3 (cost=0.29..43.69 rows=46 width=4) (actual rows= loops=)

  • Index Cond: (user_id = 29,532)
64. 0.000 0.000 ↓ 0.0

Index Scan using accounts_employeegroup_pkey on accounts_employeegroup v1 (cost=0.28..1.54 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (id = v3.employeegroup_id)
  • Filter: (company_id = 18,855)
65. 0.000 0.000 ↓ 0.0

Index Scan using clients_groupassignment_group_id_692cd6dd on clients_groupassignment v0 (cost=0.43..79.64 rows=18 width=16) (actual rows= loops=)

  • Index Cond: ((group_id = v1.id) AND (group_id = ANY ('{1280,1283,1156,1159,1162,1292,1294,1298,1174,1304,1305,1179,1182,1184,1188,1189,1201,1193,1194,1224,1081,1083,1086,1217,1092,1094,1096,1098,1099,1230,1231,1110,1112,1241,1114,1117,1124,1126,1254,1130,1259,1266,1267,1143,1276,1278}'::integer[])))
  • Filter: ((status = ANY ('{10,20,15}'::integer[])) OR (status = 30))
66. 0.000 0.000 ↓ 0.0

Index Only Scan using clients_client_pkey on clients_client v5 (cost=0.43..0.77 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (id = v0.client_id)
67. 0.000 0.000 ↓ 0.0

Index Scan using units_tenant_client_id on units_tenant v6 (cost=0.42..0.48 rows=3 width=8) (actual rows= loops=)

  • Index Cond: (v5.id = client_id)
68.          

SubPlan (for Nested Loop Left Join)

69. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.83..6,779.91 rows=13 width=4) (actual rows= loops=)

70. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.28..6,777.20 rows=1 width=4) (actual rows= loops=)

71. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.86..6,587.91 rows=385 width=4) (actual rows= loops=)

72. 0.000 0.000 ↓ 0.0

Index Scan using units_building_management_company_id on units_building u2 (cost=0.43..161.94 rows=144 width=4) (actual rows= loops=)

  • Index Cond: (management_company_id = 18,855)
73. 0.000 0.000 ↓ 0.0

Index Scan using units_unit_4c63c6ae on units_unit u1_9 (cost=0.43..44.23 rows=39 width=8) (actual rows= loops=)

  • Index Cond: (building_id = u2.id)
74. 0.000 0.000 ↓ 0.0

Index Scan using units_tenant_e8175980 on units_tenant u0_9 (cost=0.42..0.48 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (unit_id = u1_9.id)
  • Filter: (lease_start_date >= '2020-09-14'::date)
75. 0.000 0.000 ↓ 0.0

Index Scan using units_unit_current_tenant_id_key on units_unit u4 (cost=0.56..2.71 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (current_tenant_id = u0_9.id)
  • Filter: (id IS NOT NULL)