explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4aET

Settings
# exclusive inclusive rows x rows loops node
1. 113.907 10,979.222 ↑ 1.0 1 1

Aggregate (cost=82,104.91..82,104.92 rows=1 width=8) (actual time=10,979.211..10,979.222 rows=1 loops=1)

2. 868.978 10,865.315 ↓ 2,195.0 17,560 1

Unique (cost=82,104.77..82,104.81 rows=8 width=4) (actual time=9,242.667..10,865.315 rows=17,560 loops=1)

3. 1,506.821 9,996.337 ↓ 14,434.4 115,475 1

Sort (cost=82,104.77..82,104.79 rows=8 width=4) (actual time=9,242.652..9,996.337 rows=115,475 loops=1)

  • Sort Key: action_required_actionrequired.client_id
  • Sort Method: quicksort Memory: 8,485kB
4. 11.125 8,489.516 ↓ 14,434.4 115,475 1

Gather (cost=28,153.89..82,104.65 rows=8 width=4) (actual time=5,676.992..8,489.516 rows=115,475 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 838.062 8,478.391 ↓ 12,830.7 38,492 3 / 3

Hash Semi Join (cost=27,153.89..81,103.85 rows=3 width=4) (actual time=5,669.841..8,478.391 rows=38,492 loops=3)

  • Hash Cond: (action_required_actionrequired.client_id = w0.id)
6. 1,177.524 2,001.328 ↑ 5.6 91,194 3 / 3

Hash Join (cost=1,137.39..53,736.08 rows=514,760 width=4) (actual time=30.810..2,001.328 rows=91,194 loops=3)

  • Hash Cond: (action_required_actionrequired.site_team_group_id = accounts_employeegroup.id)
  • Join Filter: ((((action_required_actionrequired.responsible_team = 20) OR (action_required_actionrequired.handoff_reference_time < '2020-10-13 19:48:05.870097'::timestamp without time zone)) AND (hashed SubPlan 1)) OR ((action_required_actionrequired.responsible_team = 10) AND (action_required_actionrequired.handoff_reference_time >= '2020-10-13 19:48:05.870097'::timestamp without time zone) AND (hashed SubPlan 2)))
7. 787.635 796.803 ↑ 5.6 91,194 3 / 3

Parallel Seq Scan on action_required_actionrequired (cost=306.33..51,550.15 rows=514,774 width=20) (actual time=1.900..796.803 rows=91,194 loops=3)

  • Filter: ((((responsible_team = 20) OR (handoff_reference_time < '2020-10-13 19:48:05.870097'::timestamp without time zone)) AND (hashed SubPlan 1)) OR ((responsible_team = 10) AND (handoff_reference_time >= '2020-10-13 19:48:05.870097'::timestamp without time zone)))
  • Rows Removed by Filter: 732,444
8.          

SubPlan (for Parallel Seq Scan)

9. 5.310 9.168 ↑ 1.0 46 18 / 3

Nested Loop (cost=4.92..306.21 rows=46 width=4) (actual time=0.072..1.528 rows=46 loops=18)

10. 1.806 1.926 ↑ 1.0 46 18 / 3

Bitmap Heap Scan on accounts_employeegroup_employees u1 (cost=4.64..96.64 rows=46 width=4) (actual time=0.033..0.321 rows=46 loops=18)

  • Recheck Cond: (user_id = 29,532)
  • Heap Blocks: exact=2
11. 0.120 0.120 ↑ 1.0 46 18 / 3

Bitmap Index Scan on accounts_employeegroup_employees_e8701ad4 (cost=0.00..4.63 rows=46 width=0) (actual time=0.014..0.020 rows=46 loops=18)

  • Index Cond: (user_id = 29,532)
12. 1.932 1.932 ↑ 1.0 1 828 / 3

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

  • Index Cond: (id = u1.employeegroup_id)
  • Heap Fetches: 92
13. 12.714 27.001 ↑ 1.0 1,885 3 / 3

Hash (cost=194.85..194.85 rows=1,885 width=8) (actual time=26.996..27.001 rows=1,885 loops=3)

  • Buckets: 2,048 Batches: 1 Memory Usage: 84kB
14. 14.287 14.287 ↑ 1.0 1,885 3 / 3

Seq Scan on accounts_employeegroup (cost=0.00..194.85 rows=1,885 width=8) (actual time=0.023..14.287 rows=1,885 loops=3)

15.          

SubPlan (for Hash Join)

16. 0.000 0.000 ↓ 0.0 0 / 3

Nested Loop (cost=4.92..306.21 rows=46 width=4) (never executed)

17. 0.000 0.000 ↓ 0.0 0 / 3

Bitmap Heap Scan on accounts_employeegroup_employees u1_1 (cost=4.64..96.64 rows=46 width=4) (never executed)

  • Recheck Cond: (user_id = 29,532)
18. 0.000 0.000 ↓ 0.0 0 / 3

Bitmap Index Scan on accounts_employeegroup_employees_e8701ad4 (cost=0.00..4.63 rows=46 width=0) (never executed)

  • Index Cond: (user_id = 29,532)
19. 0.000 0.000 ↓ 0.0 0 / 3

Index Only Scan using accounts_employeegroup_pkey on accounts_employeegroup u0_1 (cost=0.28..4.56 rows=1 width=4) (never executed)

  • Index Cond: (id = u1_1.employeegroup_id)
  • Heap Fetches: 0
20. 148.137 5,639.001 ↓ 22,115.0 22,115 3 / 3

Hash (cost=26,016.48..26,016.48 rows=1 width=8) (actual time=5,638.995..5,639.001 rows=22,115 loops=3)

  • Buckets: 32,768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1,120kB
21. 432.041 5,490.864 ↓ 22,115.0 22,115 3 / 3

Nested Loop (cost=25,928.39..26,016.48 rows=1 width=8) (actual time=4,013.842..5,490.864 rows=22,115 loops=3)

22. 433.434 4,815.558 ↓ 1,005.2 22,115 3 / 3

Nested Loop (cost=25,927.96..26,003.05 rows=22 width=4) (actual time=4,013.799..4,815.558 rows=22,115 loops=3)

23. 298.618 4,160.974 ↓ 1,005.2 22,115 3 / 3

HashAggregate (cost=25,927.54..25,927.76 rows=22 width=4) (actual time=4,013.679..4,160.974 rows=22,115 loops=3)

  • Group Key: v0.id
24. 541.195 3,862.356 ↓ 1,007.6 22,168 3 / 3

Nested Loop Left Join (cost=24,391.48..25,927.48 rows=22 width=4) (actual time=0.862..3,862.356 rows=22,168 loops=3)

  • Filter: ((v0.status = ANY ('{10,20,15}'::integer[])) OR ((v0.status = 30) AND (hashed SubPlan 3)))
  • Rows Removed by Filter: 7,942
25. 570.784 1,690.108 ↓ 534.4 29,928 3 / 3

Nested Loop (cost=98.06..1,602.10 rows=56 width=12) (actual time=0.820..1,690.108 rows=29,928 loops=3)

26. 380.185 730.260 ↓ 534.4 29,928 3 / 3

Nested Loop (cost=97.64..1,349.65 rows=56 width=12) (actual time=0.758..730.260 rows=29,928 loops=3)

27. 1.896 4.799 ↓ 7.7 46 3 / 3

Hash Join (cost=97.21..297.43 rows=6 width=8) (actual time=0.659..4.799 rows=46 loops=3)

  • Hash Cond: (v1.id = v3.employeegroup_id)
28. 2.284 2.284 ↑ 1.0 250 3 / 3

Seq Scan on accounts_employeegroup v1 (cost=0.00..199.56 rows=250 width=4) (actual time=0.013..2.284 rows=250 loops=3)

  • Filter: (company_id = 18,855)
  • Rows Removed by Filter: 1,635
29. 0.303 0.619 ↑ 1.0 46 3 / 3

Hash (cost=96.64..96.64 rows=46 width=4) (actual time=0.613..0.619 rows=46 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
30. 0.302 0.316 ↑ 1.0 46 3 / 3

Bitmap Heap Scan on accounts_employeegroup_employees v3 (cost=4.64..96.64 rows=46 width=4) (actual time=0.025..0.316 rows=46 loops=3)

  • Recheck Cond: (user_id = 29,532)
  • Heap Blocks: exact=1
31. 0.014 0.014 ↑ 1.0 46 3 / 3

Bitmap Index Scan on accounts_employeegroup_employees_e8701ad4 (cost=0.00..4.63 rows=46 width=0) (actual time=0.009..0.014 rows=46 loops=3)

  • Index Cond: (user_id = 29,532)
32. 345.276 345.276 ↓ 40.7 651 138 / 3

Index Scan using clients_groupassignment_group_id_692cd6dd on clients_groupassignment v0 (cost=0.42..175.21 rows=16 width=16) (actual time=0.028..7.506 rows=651 loops=138)

  • 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))
  • Rows Removed by Filter: 857
33. 389.064 389.064 ↑ 1.0 1 89,784 / 3

Index Only Scan using clients_client_pkey on clients_client v5 (cost=0.43..4.51 rows=1 width=4) (actual time=0.013..0.013 rows=1 loops=89,784)

  • Index Cond: (id = v0.client_id)
  • Heap Fetches: 29,928
34. 239.424 239.424 ↓ 0.0 0 89,784 / 3

Index Scan using units_tenant_client_id on units_tenant v6 (cost=0.42..0.50 rows=4 width=8) (actual time=0.007..0.008 rows=0 loops=89,784)

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

SubPlan (for Nested Loop Left Join)

36. 47.096 1,391.629 ↓ 3.7 44 3 / 3

Nested Loop (cost=1.71..24,292.96 rows=12 width=4) (actual time=50.633..1,391.629 rows=44 loops=3)

37. 401.372 1,315.549 ↓ 3,623.0 3,623 3 / 3

Nested Loop (cost=1.28..24,286.04 rows=1 width=4) (actual time=0.812..1,315.549 rows=3,623 loops=3)

38. 354.876 676.613 ↓ 69.1 26,396 3 / 3

Nested Loop (cost=0.86..23,239.86 rows=382 width=4) (actual time=0.085..676.613 rows=26,396 loops=3)

39. 2.065 2.065 ↓ 1.7 248 3 / 3

Index Scan using units_building_management_company_id on units_building u2 (cost=0.43..576.62 rows=143 width=4) (actual time=0.036..2.065 rows=248 loops=3)

  • Index Cond: (management_company_id = 18,855)
40. 319.672 319.672 ↓ 2.7 106 744 / 3

Index Scan using units_unit_4c63c6ae on units_unit u1_2 (cost=0.43..158.09 rows=39 width=8) (actual time=0.019..1.289 rows=106 loops=744)

  • Index Cond: (building_id = u2.id)
41. 237.564 237.564 ↓ 0.0 0 79,188 / 3

Index Scan using units_tenant_e8175980 on units_tenant u0_2 (cost=0.42..2.73 rows=1 width=8) (actual time=0.008..0.009 rows=0 loops=79,188)

  • Index Cond: (unit_id = u1_2.id)
  • Filter: (lease_start_date >= '2020-09-14'::date)
  • Rows Removed by Filter: 0
42. 28.984 28.984 ↓ 0.0 0 10,869 / 3

Index Scan using units_unit_current_tenant_id_key on units_unit u4 (cost=0.43..6.92 rows=1 width=4) (actual time=0.008..0.008 rows=0 loops=10,869)

  • Index Cond: (current_tenant_id = u0_2.id)
  • Filter: (id IS NOT NULL)
43. 221.150 221.150 ↑ 1.0 1 66,345 / 3

Index Scan using clients_groupassignment_pkey on clients_groupassignment w2 (cost=0.42..3.42 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=66,345)

  • Index Cond: (id = v0.id)
44. 243.265 243.265 ↑ 1.0 1 66,345 / 3

Index Scan using clients_client_pkey on clients_client w0 (cost=0.43..0.61 rows=1 width=4) (actual time=0.011..0.011 rows=1 loops=66,345)

  • Index Cond: (id = w2.client_id)
  • Filter: (brokerage_id = 18,855)
Planning time : 11.457 ms
Execution time : 10,981.921 ms