explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4kOr

Settings
# exclusive inclusive rows x rows loops node
1. 0.738 7,399.199 ↑ 7.3 437 1

Sort (cost=2,405,868.09..2,405,876.03 rows=3,175 width=58) (actual time=7,399.181..7,399.199 rows=437 loops=1)

  • Sort Key: c.name
  • Sort Method: quicksort Memory: 84kB
2.          

CTE w

3. 1.660 24.567 ↑ 2.1 4,053 1

Hash Join (cost=1,128.06..2,716.31 rows=8,389 width=8) (actual time=15.516..24.567 rows=4,053 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.007..0.008 rows=1 loops=1)

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

Index Scan using login_attempt_created_at_idx on login_attempt l (cost=0.43..1,566.66 rows=8,389 width=28) (actual time=0.069..7.484 rows=4,584 loops=1)

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

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1053kB
8. 9.338 9.338 ↑ 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.338 rows=26,782 loops=1)

9.          

CTE m

10. 5.156 21.132 ↓ 2.3 19,013 1

Hash Join (cost=1,128.06..2,716.31 rows=8,389 width=8) (actual time=8.379..21.132 rows=19,013 loops=1)

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

Initplan (for Hash Join)

12. 0.011 0.011 ↑ 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.010..0.011 rows=1 loops=1)

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

Index Scan using login_attempt_created_at_idx on login_attempt l_1 (cost=0.43..1,566.66 rows=8,389 width=28) (actual time=0.069..7.686 rows=19,295 loops=1)

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

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1053kB
15. 5.205 5.205 ↑ 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.009..5.205 rows=26,782 loops=1)

16. 7.627 7,398.461 ↑ 7.3 437 1

Group (cost=0.28..2,400,250.80 rows=3,175 width=58) (actual time=55.544..7,398.461 rows=437 loops=1)

  • Group Key: c.id
  • Filter: (((SubPlan 9) <> 0) OR ((SubPlan 10) <> 0) OR ((SubPlan 11) <> 0) OR ((SubPlan 12) <> 0))
  • Rows Removed by Filter: 2738
17. 4.684 4.684 ↑ 1.0 3,175 1

Index Scan using client_pkey on client c (cost=0.28..292.11 rows=3,175 width=26) (actual time=0.015..4.684 rows=3,175 loops=1)

18.          

SubPlan (for Group)

19. 0.000 663.575 ↑ 1.0 1 3,175

Aggregate (cost=188.96..188.97 rows=1 width=8) (actual time=0.209..0.209 rows=1 loops=3,175)

20. 663.575 663.575 ↑ 42.0 1 3,175

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

  • Filter: (client_id = c.id)
  • Rows Removed by Filter: 4052
21. 2.784 545.664 ↑ 1.0 1 2,784

Aggregate (cost=188.96..188.97 rows=1 width=8) (actual time=0.196..0.196 rows=1 loops=2,784)

22. 542.880 542.880 ↓ 0.0 0 2,784

CTE Scan on w w_3 (cost=0.00..188.75 rows=42 width=32) (actual time=0.195..0.195 rows=0 loops=2,784)

  • Filter: (client_id = c.id)
  • Rows Removed by Filter: 4053
23. 2.781 2,600.235 ↑ 1.0 1 2,781

Aggregate (cost=188.96..188.97 rows=1 width=8) (actual time=0.935..0.935 rows=1 loops=2,781)

24. 2,597.454 2,597.454 ↑ 42.0 1 2,781

CTE Scan on m m_2 (cost=0.00..188.75 rows=42 width=32) (actual time=0.923..0.934 rows=1 loops=2,781)

  • Filter: (client_id = c.id)
  • Rows Removed by Filter: 19012
25. 2.743 2,561.962 ↑ 1.0 1 2,743

Aggregate (cost=188.96..188.97 rows=1 width=8) (actual time=0.934..0.934 rows=1 loops=2,743)

26. 2,559.219 2,559.219 ↓ 0.0 0 2,743

CTE Scan on m m_3 (cost=0.00..188.75 rows=42 width=32) (actual time=0.932..0.933 rows=0 loops=2,743)

  • Filter: (client_id = c.id)
  • Rows Removed by Filter: 19013
27. 0.874 86.963 ↑ 1.0 1 437

Aggregate (cost=188.96..188.97 rows=1 width=8) (actual time=0.199..0.199 rows=1 loops=437)

28. 86.089 86.089 ↑ 4.7 9 437

CTE Scan on w (cost=0.00..188.75 rows=42 width=32) (actual time=0.078..0.197 rows=9 loops=437)

  • Filter: (client_id = c.id)
  • Rows Removed by Filter: 4044
29. 0.874 87.400 ↑ 1.0 1 437

Aggregate (cost=188.96..188.97 rows=1 width=8) (actual time=0.200..0.200 rows=1 loops=437)

30. 86.526 86.526 ↑ 4.7 9 437

CTE Scan on w w_1 (cost=0.00..188.75 rows=42 width=32) (actual time=0.077..0.198 rows=9 loops=437)

  • Filter: (client_id = c.id)
  • Rows Removed by Filter: 4044
31. 1.748 437.000 ↑ 1.0 1 437

Aggregate (cost=188.96..188.97 rows=1 width=8) (actual time=1.000..1.000 rows=1 loops=437)

32. 435.252 435.252 ↓ 1.0 44 437

CTE Scan on m (cost=0.00..188.75 rows=42 width=32) (actual time=0.711..0.996 rows=44 loops=437)

  • Filter: (client_id = c.id)
  • Rows Removed by Filter: 18969
33. 1.311 403.351 ↑ 1.0 1 437

Aggregate (cost=188.96..188.97 rows=1 width=8) (actual time=0.923..0.923 rows=1 loops=437)

34. 402.040 402.040 ↓ 1.0 44 437

CTE Scan on m m_1 (cost=0.00..188.75 rows=42 width=32) (actual time=0.677..0.920 rows=44 loops=437)

  • Filter: (client_id = c.id)
  • Rows Removed by Filter: 18969
Planning time : 1.817 ms
Execution time : 7,399.519 ms