explain.depesz.com

PostgreSQL's explain analyze made readable

Result: H4AZ

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 1,034.466 1,240.445 ↓ 41.8 3,766,418 1

Hash Join (cost=118.95..27,485.07 rows=90,066 width=645) (actual time=1.435..1,240.445 rows=3,766,418 loops=1)

  • Hash Cond: (asnusr."RoleId" = rol."Id")
2. 156.400 204.934 ↓ 19.8 57,141 1

Nested Loop (cost=52.68..25,497.80 rows=2,887 width=72) (actual time=0.385..204.934 rows=57,141 loops=1)

  • Join Filter: ((CASE WHEN (asnusr."SourceType" = 0) THEN asnusr."SourceId" WHEN (asnusr."SourceType" = 1) THEN cli."Id" WHEN (asnusr."SourceType" = 2) THEN pro."Id" ELSE NULL::character varying END)::text = (asnusr."SourceId")::text)
  • Rows Removed by Join Filter: 1057329
3. 1.121 1.869 ↑ 1.0 2,745 1

Hash Join (cost=45.85..182.38 rows=2,766 width=113) (actual time=0.331..1.869 rows=2,745 loops=1)

  • Hash Cond: (asnusr."UserId" = usr."Id")
4. 0.432 0.432 ↑ 1.0 2,745 1

Seq Scan on "AspNetUserRoles" asnusr (cost=0.00..101.66 rows=2,766 width=115) (actual time=0.008..0.432 rows=2,745 loops=1)

5. 0.136 0.316 ↓ 1.0 572 1

Hash (cost=38.71..38.71 rows=571 width=72) (actual time=0.316..0.316 rows=572 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 67kB
6. 0.180 0.180 ↓ 1.0 572 1

Seq Scan on "AspNetUsers" usr (cost=0.00..38.71 rows=571 width=72) (actual time=0.005..0.180 rows=572 loops=1)

7. 46.396 46.665 ↑ 1.0 406 2,745

Materialize (cost=6.83..49.02 rows=406 width=74) (actual time=0.000..0.017 rows=406 loops=2,745)

8. 0.158 0.269 ↑ 1.0 406 1

Hash Join (cost=6.83..46.99 rows=406 width=74) (actual time=0.051..0.269 rows=406 loops=1)

  • Hash Cond: ((pro."ClientId")::text = (cli."Id")::text)
9. 0.067 0.067 ↑ 1.0 406 1

Seq Scan on "Project" pro (cost=0.00..35.06 rows=406 width=74) (actual time=0.004..0.067 rows=406 loops=1)

10. 0.022 0.044 ↓ 1.0 127 1

Hash (cost=5.26..5.26 rows=126 width=37) (actual time=0.044..0.044 rows=127 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
11. 0.022 0.022 ↓ 1.0 127 1

Seq Scan on "Client" cli (cost=0.00..5.26 rows=126 width=37) (actual time=0.004..0.022 rows=127 loops=1)

12. 0.326 1.045 ↓ 1.5 1,055 1

Hash (cost=57.29..57.29 rows=718 width=632) (actual time=1.045..1.045 rows=1,055 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 196kB
13. 0.305 0.719 ↓ 1.5 1,055 1

Hash Join (cost=18.90..57.29 rows=718 width=632) (actual time=0.188..0.719 rows=1,055 loops=1)

  • Hash Cond: ((prol."PermissionId")::text = (perm."Id")::text)
14. 0.177 0.245 ↑ 1.0 585 1

Hash Join (cost=1.52..30.53 rows=585 width=623) (actual time=0.016..0.245 rows=585 loops=1)

  • Hash Cond: ((prol."RoleId")::text = rol."Id")
15. 0.057 0.057 ↑ 1.0 585 1

Seq Scan on "PermissionRole" prol (cost=0.00..21.85 rows=585 width=74) (actual time=0.003..0.057 rows=585 loops=1)

16. 0.005 0.011 ↑ 1.0 23 1

Hash (cost=1.23..1.23 rows=23 width=549) (actual time=0.011..0.011 rows=23 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
17. 0.006 0.006 ↑ 1.0 23 1

Seq Scan on "AspNetRoles" rol (cost=0.00..1.23 rows=23 width=549) (actual time=0.003..0.006 rows=23 loops=1)

18. 0.043 0.169 ↓ 1.0 164 1

Hash (cost=15.42..15.42 rows=157 width=120) (actual time=0.169..0.169 rows=164 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 33kB
19. 0.060 0.126 ↓ 1.0 164 1

Hash Join (cost=6.88..15.42 rows=157 width=120) (actual time=0.050..0.126 rows=164 loops=1)

  • Hash Cond: ((res."PermissionId")::text = (perm."Id")::text)
20. 0.021 0.021 ↓ 1.0 164 1

Seq Scan on "Resource" res (cost=0.00..6.57 rows=157 width=63) (actual time=0.003..0.021 rows=164 loops=1)

21. 0.021 0.045 ↑ 1.3 102 1

Hash (cost=5.28..5.28 rows=128 width=57) (actual time=0.045..0.045 rows=102 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
22. 0.024 0.024 ↑ 1.3 102 1

Seq Scan on "Permission" perm (cost=0.00..5.28 rows=128 width=57) (actual time=0.003..0.024 rows=102 loops=1)

Planning time : 4.359 ms