explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uVZS : Optimization for: Optimization for: Optimization for: plan #H4AZ; plan #qeuJ; plan #ObhK

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 600.854 12,159.774 ↓ 41.8 3,766,418 1

Subquery Scan on a (cost=61,066.41..62,192.24 rows=90,066 width=645) (actual time=10,408.359..12,159.774 rows=3,766,418 loops=1)

2. 10,304.290 11,558.920 ↓ 41.8 3,766,418 1

Sort (cost=61,066.41..61,291.58 rows=90,066 width=649) (actual time=10,408.357..11,558.920 rows=3,766,418 loops=1)

  • Sort Key: (((usr."FName")::text || (usr."LName")::text)), rol."Name", perm."Name", res."ControlName", res."Type
  • Sort Method: external merge Disk: 457640kB
3. 1,045.058 1,254.630 ↓ 41.8 3,766,418 1

Hash Join (cost=118.95..27,485.07 rows=90,066 width=649) (actual time=3.106..1,254.630 rows=3,766,418 loops=1)

  • Hash Cond: (asnusr."RoleId" = rol."Id")
4. 155.337 207.267 ↓ 19.8 57,141 1

Nested Loop (cost=52.68..25,497.80 rows=2,887 width=72) (actual time=0.793..207.267 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
5. 1.254 2.520 ↑ 1.0 2,745 1

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

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

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

7. 0.317 0.661 ↓ 1.0 572 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 67kB
8. 0.344 0.344 ↓ 1.0 572 1

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

9. 48.858 49.410 ↑ 1.0 406 2,745

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

10. 0.339 0.552 ↑ 1.0 406 1

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

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

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

12. 0.049 0.093 ↓ 1.0 127 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
13. 0.044 0.044 ↓ 1.0 127 1

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

14. 0.721 2.305 ↓ 1.5 1,055 1

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

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 196kB
15. 0.688 1.584 ↓ 1.5 1,055 1

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

  • Hash Cond: ((prol."PermissionId")::text = (perm."Id")::text)
16. 0.396 0.532 ↑ 1.0 585 1

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

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

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

18. 0.012 0.024 ↑ 1.0 23 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
19. 0.012 0.012 ↑ 1.0 23 1

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

20. 0.098 0.364 ↓ 1.0 164 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 33kB
21. 0.133 0.266 ↓ 1.0 164 1

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

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

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

23. 0.046 0.096 ↑ 1.3 102 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
24. 0.050 0.050 ↑ 1.3 102 1

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

Planning time : 10.014 ms