explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9cvq

Settings
# exclusive inclusive rows x rows loops node
1. 13.476 7,214.376 ↑ 1.0 3,175 1

Index Scan using client_name_key on client c (cost=5,432.47..2,404,861.38 rows=3,175 width=54) (actual time=46.700..7,214.376 rows=3,175 loops=1)

2.          

CTE w

3. 1.697 24.546 ↑ 2.1 4,050 1

Hash Join (cost=1,128.06..2,716.10 rows=8,386 width=8) (actual time=15.097..24.546 rows=4,050 loops=1)

  • Hash Cond: (l.username = u.username)
4.          

Initplan (for Hash Join)

5. 0.008 0.008 ↑ 1.0 1 1

Seq Scan on login_app_type (cost=0.00..1.04 rows=1 width=4) (actual time=0.006..0.008 rows=1 loops=1)

  • Filter: (app_type = 'web'::text)
  • Rows Removed by Filter: 2
6. 7.853 7.853 ↑ 1.8 4,581 1

Index Scan using login_attempt_created_at_idx on login_attempt l (cost=0.43..1,566.45 rows=8,386 width=28) (actual time=0.071..7.853 rows=4,581 loops=1)

  • Index Cond: (created_at > (now() - '7 days'::interval))
  • Filter: (login_app_type_id = $0)
  • Rows Removed by Filter: 19,707
7. 5.759 14.988 ↑ 2.0 13,657 1

Hash (cost=791.82..791.82 rows=26,782 width=27) (actual time=14.988..14.988 rows=13,657 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,053kB
8. 9.229 9.229 ↑ 1.0 26,782 1

Seq Scan on user_account u (cost=0.00..791.82 rows=26,782 width=27) (actual time=0.007..9.229 rows=26,782 loops=1)

9.          

CTE m

10. 3.958 17.030 ↓ 2.3 19,011 1

Hash Join (cost=1,128.06..2,716.10 rows=8,386 width=8) (actual time=7.145..17.030 rows=19,011 loops=1)

  • Hash Cond: (l_1.username = u_1.username)
11.          

Initplan (for Hash Join)

12. 0.009 0.009 ↑ 1.0 1 1

Seq Scan on login_app_type login_app_type_1 (cost=0.00..1.04 rows=1 width=4) (actual time=0.008..0.009 rows=1 loops=1)

  • Filter: (app_type = 'mobile'::text)
  • Rows Removed by Filter: 2
13. 6.000 6.000 ↓ 2.3 19,293 1

Index Scan using login_attempt_created_at_idx on login_attempt l_1 (cost=0.43..1,566.45 rows=8,386 width=28) (actual time=0.051..6.000 rows=19,293 loops=1)

  • Index Cond: (created_at > (now() - '7 days'::interval))
  • Filter: (login_app_type_id = $2)
  • Rows Removed by Filter: 4,995
14. 2.608 7.063 ↑ 2.0 13,657 1

Hash (cost=791.82..791.82 rows=26,782 width=27) (actual time=7.063..7.063 rows=13,657 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,053kB
15. 4.455 4.455 ↑ 1.0 26,782 1

Seq Scan on user_account u_1 (cost=0.00..791.82 rows=26,782 width=27) (actual time=0.006..4.455 rows=26,782 loops=1)

16.          

SubPlan (for Index Scan)

17. 3.175 657.225 ↑ 1.0 1 3,175

Aggregate (cost=188.90..188.91 rows=1 width=8) (actual time=0.206..0.207 rows=1 loops=3,175)

18. 654.050 654.050 ↑ 42.0 1 3,175

CTE Scan on w (cost=0.00..188.69 rows=42 width=32) (actual time=0.189..0.206 rows=1 loops=3,175)

  • Filter: (client_id = c.id)
  • Rows Removed by Filter: 4,049
19. 3.175 619.125 ↑ 1.0 1 3,175

Aggregate (cost=188.90..188.91 rows=1 width=8) (actual time=0.195..0.195 rows=1 loops=3,175)

20. 615.950 615.950 ↑ 42.0 1 3,175

CTE Scan on w w_1 (cost=0.00..188.69 rows=42 width=32) (actual time=0.178..0.194 rows=1 loops=3,175)

  • Filter: (client_id = c.id)
  • Rows Removed by Filter: 4,049
21. 3.175 3,025.775 ↑ 1.0 1 3,175

Aggregate (cost=188.90..188.91 rows=1 width=8) (actual time=0.953..0.953 rows=1 loops=3,175)

22. 3,022.600 3,022.600 ↑ 7.0 6 3,175

CTE Scan on m (cost=0.00..188.69 rows=42 width=32) (actual time=0.918..0.952 rows=6 loops=3,175)

  • Filter: (client_id = c.id)
  • Rows Removed by Filter: 19,005
23. 0.000 2,898.775 ↑ 1.0 1 3,175

Aggregate (cost=188.90..188.91 rows=1 width=8) (actual time=0.913..0.913 rows=1 loops=3,175)

24. 2,898.775 2,898.775 ↑ 7.0 6 3,175

CTE Scan on m m_1 (cost=0.00..188.69 rows=42 width=32) (actual time=0.880..0.913 rows=6 loops=3,175)

  • Filter: (client_id = c.id)
  • Rows Removed by Filter: 19,005
Planning time : 1.673 ms
Execution time : 7,214.913 ms