explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sscD

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

Aggregate (cost=72,710,668.21..72,710,668.22 rows=1 width=8) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Unique (cost=72,614,900.96..72,710,668.20 rows=1 width=4) (actual rows= loops=)

3. 0.000 0.000 ↓ 0.0

Nested Loop Semi Join (cost=72,614,900.96..72,710,668.19 rows=1 width=4) (actual rows= loops=)

  • Join Filter: (t7.id = v0.id)
4. 0.000 0.000 ↓ 0.0

Merge Join (cost=72,608,119.17..72,700,943.17 rows=60,549 width=8) (actual rows= loops=)

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

Merge Left Join (cost=72,608,056.26..72,662,535.59 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))
6. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=72,607,986.35..72,634,095.75 rows=143,422 width=8) (actual rows= loops=)

  • Merge Cond: (clients_client.id = clients_groupassignment.client_id)
7. 0.000 0.000 ↓ 0.0

Sort (cost=72,607,985.93..72,608,344.48 rows=143,422 width=4) (actual rows= loops=)

  • Sort Key: clients_client.id
8. 0.000 0.000 ↓ 0.0

Index Scan using clients_client_brokerage_id on clients_client (cost=0.43..72,595,701.90 rows=143,422 width=4) (actual rows= loops=)

  • Index Cond: (brokerage_id = 18,855)
  • Filter: ((status <> ALL ('{50,70}'::integer[])) AND (alternatives: SubPlan 4 or hashed SubPlan 7))
9.          

SubPlan (for Index Scan)

10. 0.000 0.000 ↓ 0.0

Nested Loop (cost=209.56..233.95 rows=1 width=0) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Nested Loop (cost=70.33..92.49 rows=1 width=16) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (client_id = clients_client.id)
  • Filter: ((NOT action_completed) AND (site_team_group_id = ANY ('{1283,1189,1194,1298,1304,1081}'::integer[])) AND ((((responsible_team = 20) OR (handoff_reference_time < '2020-10-13 19:17:36.849781'::timestamp without time zone)) AND (hashed SubPlan 2)) OR ((responsible_team = 10) AND (handoff_reference_time >= '2020-10-13 19:17:36.849781'::timestamp without time zone))))
13.          

SubPlan (for Index Scan)

14. 0.000 0.000 ↓ 0.0

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

15. 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)
16. 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)
17. 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))
18. 0.000 0.000 ↓ 0.0

Index Scan using accounts_employeegroup_pkey on accounts_employeegroup v3_1 (cost=139.23..141.46 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 19:17:36.849781'::timestamp without time zone)) AND (hashed SubPlan 2)) OR ((v0_1.responsible_team = 10) AND (v0_1.handoff_reference_time >= '2020-10-13 19:17:36.849781'::timestamp without time zone) AND (hashed SubPlan 3)))
19.          

SubPlan (for Index Scan)

20. 0.000 0.000 ↓ 0.0

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

21. 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)
22. 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)
23. 0.000 0.000 ↓ 0.0

Gather (cost=5,281.96..10,390.93 rows=373 width=4) (actual rows= loops=)

  • Workers Planned: 2
24. 0.000 0.000 ↓ 0.0

Nested Loop (cost=4,281.96..9,353.63 rows=155 width=4) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Hash Join (cost=4,281.53..7,690.77 rows=675 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 19:17:36.849781'::timestamp without time zone)) AND (hashed SubPlan 5)) OR ((v0_2.responsible_team = 10) AND (v0_2.handoff_reference_time >= '2020-10-13 19:17:36.849781'::timestamp without time zone) AND (hashed SubPlan 6)))
26. 0.000 0.000 ↓ 0.0

Parallel Bitmap Heap Scan on action_required_actionrequired v0_2 (cost=3,865.06..7,272.52 rows=676 width=20) (actual rows= loops=)

  • Recheck Cond: (site_team_group_id = ANY ('{1283,1189,1194,1298,1304,1081}'::integer[]))
  • Filter: ((NOT action_completed) AND ((((responsible_team = 20) OR (handoff_reference_time < '2020-10-13 19:17:36.849781'::timestamp without time zone)) AND (hashed SubPlan 5)) OR ((responsible_team = 10) AND (handoff_reference_time >= '2020-10-13 19:17:36.849781'::timestamp without time zone))))
27. 0.000 0.000 ↓ 0.0

BitmapAnd (cost=3,795.58..3,795.58 rows=3,244 width=0) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on action_required_actionrequired_site_team_group_id_c9564769 (cost=0.00..425.77 rows=31,932 width=0) (actual rows= loops=)

  • Index Cond: (site_team_group_id = ANY ('{1283,1189,1194,1298,1304,1081}'::integer[]))
29. 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)
30.          

SubPlan (for Parallel Bitmap Heap Scan)

31. 0.000 0.000 ↓ 0.0

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

32. 0.000 0.000 ↓ 0.0

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

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

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

  • Index Cond: (id = u1_3.employeegroup_id)
34. 0.000 0.000 ↓ 0.0

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

35. 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=)

36.          

SubPlan (for Hash Join)

37. 0.000 0.000 ↓ 0.0

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

38. 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)
39. 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)
40. 0.000 0.000 ↓ 0.0

Index Scan using clients_client_pkey on clients_client v1_2 (cost=0.43..2.46 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))
41. 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=)

42. 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=)

43.          

SubPlan (for Merge Left Join)

44. 0.000 0.000 ↓ 0.0

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

45. 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)
46. 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)
47. 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=)

48. 0.000 0.000 ↓ 0.0

Materialize (cost=6,781.78..7,000.33 rows=3 width=4) (actual rows= loops=)

49. 0.000 0.000 ↓ 0.0

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

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

Nested Loop (cost=1.42..215.58 rows=9 width=12) (actual rows= loops=)

51. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.99..203.24 rows=9 width=12) (actual rows= loops=)

52. 0.000 0.000 ↓ 0.0

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

53. 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)
54. 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)
55. 0.000 0.000 ↓ 0.0

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

  • Index Cond: ((group_id = v1.id) AND (group_id = ANY ('{1283,1189,1194,1298,1304,1081}'::integer[])))
  • Filter: ((status = ANY ('{10,20,15}'::integer[])) OR (status = 30))
56. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (id = v0.client_id)
57. 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)
58.          

SubPlan (for Nested Loop Left Join)

59. 0.000 0.000 ↓ 0.0

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

60. 0.000 0.000 ↓ 0.0

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

61. 0.000 0.000 ↓ 0.0

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

62. 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)
63. 0.000 0.000 ↓ 0.0

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

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

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

  • Index Cond: (unit_id = u1_5.id)
  • Filter: (lease_start_date >= '2020-09-14'::date)
65. 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_5.id)
  • Filter: (id IS NOT NULL)