explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 979.245 1,174.394 ↓ 41.8 3,766,418 1

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

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

Nested Loop (cost=52.68..25,497.80 rows=2,887 width=72) (actual time=0.775..192.648 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. 0.917 1.894 ↑ 1.0 2,745 1

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

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

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

5. 0.301 0.645 ↓ 1.0 572 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 67kB
6. 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)

7. 40.634 41.175 ↑ 1.0 406 2,745

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

8. 0.336 0.541 ↑ 1.0 406 1

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

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

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

10. 0.048 0.090 ↓ 1.0 127 1

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

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

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

12. 0.885 2.501 ↓ 1.5 1,055 1

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

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

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

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

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

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

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

16. 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
17. 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)

18. 0.098 0.367 ↓ 1.0 164 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 33kB
19. 0.134 0.269 ↓ 1.0 164 1

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

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

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

21. 0.047 0.097 ↑ 1.3 102 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
22. 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 : 9.311 ms