explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8G0w

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 0.106 ↓ 0.0 0 1

Limit (cost=25.71..25.72 rows=1 width=148) (actual time=0.106..0.106 rows=0 loops=1)

2.          

CTE u

3. 0.022 0.059 ↑ 1.0 1 1

Bitmap Heap Scan on "user" (cost=4.28..8.29 rows=1 width=12) (actual time=0.059..0.059 rows=1 loops=1)

  • Recheck Cond: (id = 14)
  • Filter: (sys = 1)
  • Heap Blocks: exact=1
4. 0.037 0.037 ↑ 1.0 1 1

Bitmap Index Scan on user_pkey (cost=0.00..4.28 rows=1 width=0) (actual time=0.037..0.037 rows=1 loops=1)

  • Index Cond: (id = 14)
5.          

CTE a

6. 0.001 0.008 ↓ 0.0 0 1

Nested Loop (cost=0.43..8.48 rows=1 width=8) (actual time=0.008..0.008 rows=0 loops=1)

  • Join Filter: (u_1.user_id = answer.user_id)
7. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on u u_1 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.001 rows=1 loops=1)

8. 0.006 0.006 ↓ 0.0 0 1

Index Scan using answer_created_utc_idx on answer (cost=0.43..8.45 rows=1 width=8) (actual time=0.006..0.006 rows=0 loops=1)

  • Index Cond: ((created_utc >= 0) AND (created_utc <= 199999999))
9.          

CTE total_a

10. 0.001 0.009 ↓ 0.0 0 1

HashAggregate (cost=0.02..0.03 rows=1 width=12) (actual time=0.009..0.009 rows=0 loops=1)

  • Group Key: a.user_id
11. 0.008 0.008 ↓ 0.0 0 1

CTE Scan on a (cost=0.00..0.02 rows=1 width=4) (actual time=0.008..0.008 rows=0 loops=1)

12.          

CTE todo_a

13. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=0.03..0.04 rows=1 width=12) (never executed)

  • Group Key: a_1.user_id
14. 0.000 0.000 ↓ 0.0 0

CTE Scan on a a_1 (cost=0.00..0.02 rows=1 width=4) (never executed)

  • Filter: (status = 1)
15.          

CTE correct_a

16. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=0.03..0.04 rows=1 width=12) (never executed)

  • Group Key: a_2.user_id
17. 0.000 0.000 ↓ 0.0 0

CTE Scan on a a_2 (cost=0.00..0.03 rows=1 width=4) (never executed)

  • Filter: ((status = 2) OR (status = 3))
18.          

CTE conflict_a

19. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=0.03..0.04 rows=1 width=12) (never executed)

  • Group Key: a_3.user_id
20. 0.000 0.000 ↓ 0.0 0

CTE Scan on a a_3 (cost=0.00..0.02 rows=1 width=4) (never executed)

  • Filter: (status = 4)
21.          

CTE wrong_a

22. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=0.03..0.04 rows=1 width=12) (never executed)

  • Group Key: a_4.user_id
23. 0.000 0.000 ↓ 0.0 0

CTE Scan on a a_4 (cost=0.00..0.02 rows=1 width=4) (never executed)

  • Filter: (status = 5)
24.          

CTE feedback_a

25. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=8.49..8.51 rows=1 width=12) (never executed)

  • Group Key: u_2.user_id
26. 0.000 0.000 ↓ 0.0 0

Sort (cost=8.49..8.50 rows=1 width=4) (never executed)

  • Sort Key: u_2.user_id
27. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.43..8.48 rows=1 width=4) (never executed)

  • Join Filter: (u_2.user_id = answer_1.user_id)
28. 0.000 0.000 ↓ 0.0 0

CTE Scan on u u_2 (cost=0.00..0.02 rows=1 width=4) (never executed)

29. 0.000 0.000 ↓ 0.0 0

Index Scan using answer_created_utc_idx on answer answer_1 (cost=0.43..8.45 rows=1 width=4) (never executed)

  • Index Cond: ((created_utc > 0) AND (created_utc < 159999999))
  • Filter: (status = 6)
30. 0.032 0.105 ↓ 0.0 0 1

Sort (cost=0.23..0.24 rows=1 width=148) (actual time=0.105..0.105 rows=0 loops=1)

  • Sort Key: ((COALESCE(total_a.count, '0'::bigint) - COALESCE(feedback_a.count, '0'::bigint))) DESC, u.user_id
  • Sort Method: quicksort Memory: 25kB
31. 0.001 0.073 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.00..0.22 rows=1 width=148) (actual time=0.073..0.073 rows=0 loops=1)

  • Join Filter: (u.user_id = feedback_a.user_id)
32. 0.000 0.072 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.00..0.18 rows=1 width=76) (actual time=0.072..0.072 rows=0 loops=1)

  • Join Filter: (u.user_id = wrong_a.user_id)
33. 0.000 0.072 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.00..0.15 rows=1 width=68) (actual time=0.072..0.072 rows=0 loops=1)

  • Join Filter: (u.user_id = conflict_a.user_id)
34. 0.000 0.072 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.00..0.12 rows=1 width=60) (actual time=0.072..0.072 rows=0 loops=1)

  • Join Filter: (u.user_id = correct_a.user_id)
35. 0.000 0.072 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.00..0.09 rows=1 width=52) (actual time=0.072..0.072 rows=0 loops=1)

  • Join Filter: (u.user_id = todo_a.user_id)
36. 0.001 0.072 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.00..0.06 rows=1 width=44) (actual time=0.072..0.072 rows=0 loops=1)

  • Join Filter: (u.user_id = total_a.user_id)
  • Filter: (COALESCE(total_a.count, '0'::bigint) > 0)
  • Rows Removed by Filter: 1
37. 0.061 0.061 ↑ 1.0 1 1

CTE Scan on u (cost=0.00..0.02 rows=1 width=36) (actual time=0.061..0.061 rows=1 loops=1)

38. 0.010 0.010 ↓ 0.0 0 1

CTE Scan on total_a (cost=0.00..0.02 rows=1 width=12) (actual time=0.010..0.010 rows=0 loops=1)

39. 0.000 0.000 ↓ 0.0 0

CTE Scan on todo_a (cost=0.00..0.02 rows=1 width=12) (never executed)

40. 0.000 0.000 ↓ 0.0 0

CTE Scan on correct_a (cost=0.00..0.02 rows=1 width=12) (never executed)

41. 0.000 0.000 ↓ 0.0 0

CTE Scan on conflict_a (cost=0.00..0.02 rows=1 width=12) (never executed)

42. 0.000 0.000 ↓ 0.0 0

CTE Scan on wrong_a (cost=0.00..0.02 rows=1 width=12) (never executed)

43. 0.000 0.000 ↓ 0.0 0

CTE Scan on feedback_a (cost=0.00..0.02 rows=1 width=12) (never executed)