explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yDfu : STV count

Settings
# exclusive inclusive rows x rows loops node
1. 0.012 8.033 ↑ 3.4 11 1

Sort (cost=1,206.72..1,206.82 rows=37 width=48) (actual time=8.033..8.033 rows=11 loops=1)

  • Sort Key: w.vote_value DESC, w.round
  • Sort Method: quicksort Memory: 25kB
2.          

CTE cdata

3. 0.001 0.024 ↑ 1.0 1 1

Subquery Scan on s (cost=5.51..5.54 rows=1 width=20) (actual time=0.024..0.024 rows=1 loops=1)

4. 0.010 0.023 ↑ 1.0 1 1

Aggregate (cost=5.51..5.52 rows=1 width=12) (actual time=0.023..0.023 rows=1 loops=1)

5. 0.013 0.013 ↑ 1.0 201 1

Seq Scan on ballots (cost=0.00..5.01 rows=201 width=0) (actual time=0.002..0.013 rows=201 loops=1)

6.          

CTE work_ballots

7. 0.267 7.632 ↓ 1.5 1,998 1

Recursive Union (cost=12.67..1,169.51 rows=1,330 width=85) (actual time=0.130..7.632 rows=1,998 loops=1)

8. 0.152 0.273 ↓ 1.0 201 1

WindowAgg (cost=12.67..16.17 rows=200 width=85) (actual time=0.129..0.273 rows=201 loops=1)

9.          

Initplan (forWindowAgg)

10. 0.025 0.025 ↑ 1.0 1 1

CTE Scan on cdata (cost=0.00..0.02 rows=1 width=4) (actual time=0.025..0.025 rows=1 loops=1)

11. 0.043 0.096 ↓ 1.0 201 1

Sort (cost=12.65..13.15 rows=200 width=49) (actual time=0.088..0.096 rows=201 loops=1)

  • Sort Key: (ballots_1.prefs[1])
  • Sort Method: quicksort Memory: 47kB
12. 0.053 0.053 ↓ 1.0 201 1

Seq Scan on ballots ballots_1 (cost=0.00..5.01 rows=200 width=49) (actual time=0.009..0.053 rows=201 loops=1)

  • Filter: (prefs[1] IS NOT NULL)
13. 7.092 7.092 ↓ 1.3 150 12

CTE Scan on new_ballots (cost=110.41..112.67 rows=113 width=85) (actual time=0.389..0.591 rows=150 loops=12)

14.          

CTE w

15. 0.204 0.204 ↑ 2.0 166 12

WorkTable Scan on work_ballots (cost=0.00..45.00 rows=333 width=85) (actual time=0.000..0.017 rows=166 loops=12)

  • Filter: ((NOT final_result) AND (vacancies > 0))
  • Rows Removed by Filter: 1
16.          

CTE c_score

17. 0.527 1.032 ↑ 33.3 6 12

HashAggregate (cost=10.01..12.51 rows=200 width=56) (actual time=0.084..0.086 rows=6 loops=12)

  • Group Key: w_1.candidate
18.          

Initplan (forHashAggregate)

19. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on cdata cdata_1 (cost=0.00..0.02 rows=1 width=8) (actual time=0.000..0.001 rows=1 loops=1)

20. 0.504 0.504 ↑ 2.0 166 12

CTE Scan on w w_1 (cost=0.00..6.66 rows=333 width=48) (actual time=0.001..0.042 rows=166 loops=12)

21.          

CTE elect

22. 0.000 0.996 ↓ 0.0 0 12

Limit (cost=6.59..6.59 rows=1 width=96) (actual time=0.083..0.083 rows=0 loops=12)

23. 0.012 0.996 ↓ 0.0 0 12

Sort (cost=6.59..6.60 rows=4 width=96) (actual time=0.083..0.083 rows=0 loops=12)

  • Sort Key: cs.votes DESC, cs.priority DESC, (c_1.precast_lots[cs.round]) DESC
  • Sort Method: quicksort Memory: 25kB
24. 0.016 0.984 ↓ 0.0 0 12

Hash Join (cost=1.25..6.57 rows=4 width=96) (actual time=0.081..0.082 rows=0 loops=12)

  • Hash Cond: (cs.candidate = c_1.candidate_id)
25. 0.960 0.960 ↓ 0.0 0 12

CTE Scan on c_score cs (cost=0.00..5.00 rows=67 width=56) (actual time=0.080..0.080 rows=0 loops=12)

  • Filter: (votes >= (quota)::numeric)
  • Rows Removed by Filter: 5
26. 0.003 0.008 ↑ 1.0 11 1

Hash (cost=1.11..1.11 rows=11 width=121) (actual time=0.008..0.008 rows=11 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
27. 0.005 0.005 ↑ 1.0 11 1

Seq Scan on candidates c_1 (cost=0.00..1.11 rows=11 width=121) (actual time=0.004..0.005 rows=11 loops=1)

28.          

CTE exclude

29. 0.012 0.072 ↑ 1.0 1 12

Limit (cost=6.18..6.18 rows=1 width=64) (actual time=0.006..0.006 rows=1 loops=12)

30.          

Initplan (forLimit)

31. 0.000 0.000 ↓ 0.0 0 12

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

32. 0.024 0.060 ↑ 11.0 1 12

Sort (cost=6.16..6.19 rows=11 width=64) (actual time=0.005..0.005 rows=1 loops=12)

  • Sort Key: cs_1.votes, cs_1.priority, (c_2.precast_lots[cs_1.round])
  • Sort Method: quicksort Memory: 25kB
33. 0.012 0.036 ↑ 2.8 4 12

Result (cost=1.25..6.11 rows=11 width=64) (actual time=0.002..0.003 rows=4 loops=12)

  • One-Time Filter: (NOT $7)
34. 0.010 0.024 ↑ 1.8 6 8

Hash Join (cost=1.25..6.11 rows=11 width=173) (actual time=0.001..0.003 rows=6 loops=8)

  • Hash Cond: (cs_1.candidate = c_2.candidate_id)
35. 0.008 0.008 ↑ 33.3 6 8

CTE Scan on c_score cs_1 (cost=0.00..4.00 rows=200 width=56) (actual time=0.000..0.001 rows=6 loops=8)

36. 0.002 0.006 ↑ 1.0 11 1

Hash (cost=1.11..1.11 rows=11 width=121) (actual time=0.006..0.006 rows=11 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
37. 0.004 0.004 ↑ 1.0 11 1

Seq Scan on candidates c_2 (cost=0.00..1.11 rows=11 width=121) (actual time=0.002..0.004 rows=11 loops=1)

38.          

CTE retire

39. 0.000 1.092 ↑ 2.0 1 12

Append (cost=0.00..0.07 rows=2 width=52) (actual time=0.090..0.091 rows=1 loops=12)

40. 1.008 1.008 ↓ 0.0 0 12

CTE Scan on elect elect_1 (cost=0.00..0.02 rows=1 width=52) (actual time=0.084..0.084 rows=0 loops=12)

41. 0.000 0.084 ↑ 1.0 1 12

Subquery Scan on *SELECT* 2 (cost=0.00..0.03 rows=1 width=52) (actual time=0.007..0.007 rows=1 loops=12)

42. 0.084 0.084 ↑ 1.0 1 12

CTE Scan on exclude (cost=0.00..0.02 rows=1 width=48) (actual time=0.006..0.007 rows=1 loops=12)

43.          

CTE transfer

44. 0.293 1.716 ↓ 8.0 24 12

Hash Join (cost=0.07..8.01 rows=3 width=76) (actual time=0.101..0.143 rows=24 loops=12)

  • Hash Cond: (w_2.candidate = retire.candidate)
45. 0.312 0.312 ↑ 2.6 128 12

CTE Scan on w w_2 (cost=0.00..6.66 rows=331 width=76) (actual time=0.002..0.026 rows=128 loops=12)

  • Filter: (remainder[1] IS NOT NULL)
  • Rows Removed by Filter: 38
46. 0.011 1.111 ↑ 2.0 1 11

Hash (cost=0.04..0.04 rows=2 width=36) (actual time=0.101..0.101 rows=1 loops=11)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
47. 1.100 1.100 ↑ 2.0 1 11

CTE Scan on retire (cost=0.00..0.04 rows=2 width=36) (actual time=0.098..0.100 rows=1 loops=11)

48.          

CTE keep

49. 0.528 0.528 ↑ 2.6 125 12

CTE Scan on w w_3 (cost=0.08..8.40 rows=331 width=76) (actual time=0.003..0.044 rows=125 loops=12)

  • Filter: (candidate <> $12)
  • Rows Removed by Filter: 40
50.          

Initplan (forCTE Scan)

51. 0.000 0.000 ↑ 2.0 1 10

CTE Scan on retire retire_1 (cost=0.00..0.04 rows=2 width=4) (actual time=0.000..0.000 rows=1 loops=10)

52. 0.000 0.000 ↑ 2.0 1 11

CTE Scan on retire retire_2 (cost=0.00..0.04 rows=2 width=4) (actual time=0.000..0.000 rows=1 loops=11)

53.          

CTE new_ballots

54. 0.132 6.372 ↓ 1.3 150 12

Append (cost=19.13..23.64 rows=113 width=85) (actual time=0.388..0.531 rows=150 loops=12)

55. 0.156 6.228 ↓ 1.3 149 12

Subquery Scan on *SELECT* 1 (cost=19.13..23.01 rows=111 width=85) (actual time=0.388..0.519 rows=149 loops=12)

56. 1.488 6.072 ↓ 1.3 149 12

WindowAgg (cost=19.13..21.90 rows=111 width=125) (actual time=0.387..0.506 rows=149 loops=12)

57.          

Initplan (forWindowAgg)

58. 0.000 0.000 ↑ 2.0 1 10

CTE Scan on retire retire_3 (cost=0.00..0.04 rows=2 width=4) (actual time=0.000..0.000 rows=1 loops=10)

59. 0.864 4.584 ↓ 1.3 149 12

Sort (cost=19.09..19.37 rows=111 width=116) (actual time=0.376..0.382 rows=149 loops=12)

  • Sort Key: cs_2.votes, cs_2.priority
  • Sort Method: quicksort Memory: 25kB
60. 0.228 3.720 ↓ 1.3 149 12

Hash Join (cost=9.46..15.32 rows=111 width=116) (actual time=0.292..0.310 rows=149 loops=12)

  • Hash Cond: (cs_2.candidate = transfer.candidate)
61. 0.120 0.120 ↑ 33.3 6 10

CTE Scan on c_score cs_2 (cost=0.00..4.00 rows=200 width=44) (actual time=0.011..0.012 rows=6 loops=10)

62. 0.204 3.372 ↓ 1.3 149 12

Hash (cost=8.07..8.07 rows=111 width=76) (actual time=0.281..0.281 rows=149 loops=12)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
63. 0.132 3.168 ↓ 1.3 149 12

Append (cost=0.00..8.07 rows=111 width=76) (actual time=0.103..0.264 rows=149 loops=12)

64. 1.848 1.848 ↓ 24.0 24 12

CTE Scan on transfer (cost=0.00..0.07 rows=1 width=76) (actual time=0.102..0.154 rows=24 loops=12)

  • Filter: (vote_value > '0'::numeric)
65. 1.188 1.188 ↓ 1.1 125 12

CTE Scan on keep (cost=0.00..7.45 rows=110 width=76) (actual time=0.004..0.099 rows=125 loops=12)

  • Filter: (vote_value > '0'::numeric)
66. 0.000 0.012 ↑ 2.0 1 12

Subquery Scan on *SELECT* 2_1 (cost=0.00..0.07 rows=2 width=85) (actual time=0.001..0.001 rows=1 loops=12)

67. 0.012 0.012 ↑ 2.0 1 12

CTE Scan on retire retire_4 (cost=0.00..0.04 rows=2 width=61) (actual time=0.001..0.001 rows=1 loops=12)

68. 0.008 8.021 ↑ 3.4 11 1

Hash Join (cost=1.25..30.71 rows=37 width=48) (actual time=1.294..8.021 rows=11 loops=1)

  • Hash Cond: (w.candidate = c.candidate_id)
69. 8.007 8.007 ↑ 60.5 11 1

CTE Scan on work_ballots w (cost=0.00..26.60 rows=665 width=40) (actual time=1.285..8.007 rows=11 loops=1)

  • Filter: final_result
  • Rows Removed by Filter: 1987
70. 0.002 0.006 ↑ 1.0 11 1

Hash (cost=1.11..1.11 rows=11 width=12) (actual time=0.006..0.006 rows=11 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
71. 0.004 0.004 ↑ 1.0 11 1

Seq Scan on candidates c (cost=0.00..1.11 rows=11 width=12) (actual time=0.003..0.004 rows=11 loops=1)