Improve migration 110

[kwk]

Use Postgres CTE to update iteration numbers

When explaining the updae before it looks like this:

postgres@172:postgres> explain UPDATE iterations iter SET number = seq.row_number
                       FROM (
                           SELECT id, space_id, created_at, row_number() OVER (PARTITION BY space_id ORDER BY created_at ASC)
                           FROM iterations
                       ) AS seq
                       WHERE iter.space_id = seq.space_id AND iter.id = seq.id;
+-----------------------------------------------------------------------------------------+
| QUERY PLAN                                                                              |
|-----------------------------------------------------------------------------------------|
| Update on iterations iter  (cost=2.06..2.16 rows=1 width=263)                           |
|   ->  Merge Join  (cost=2.06..2.16 rows=1 width=263)                                    |
|         Merge Cond: (seq.space_id = iter.space_id)                                      |
|         Join Filter: (iter.id = seq.id)                                                 |
|         ->  Subquery Scan on seq  (cost=1.03..1.09 rows=2 width=112)                    |
|               ->  WindowAgg  (cost=1.03..1.07 rows=2 width=48)                          |
|                     ->  Sort  (cost=1.03..1.03 rows=2 width=40)                         |
|                           Sort Key: iterations.space_id, iterations.created_at          |
|                           ->  Seq Scan on iterations  (cost=0.00..1.02 rows=2 width=40) |
|         ->  Sort  (cost=1.03..1.03 rows=2 width=187)                                    |
|               Sort Key: iter.space_id                                                   |
|               ->  Seq Scan on iterations iter  (cost=0.00..1.02 rows=2 width=187)       |
+-----------------------------------------------------------------------------------------+

Now the update looks better (notice absence of nested seq scans):

postgres@172:postgres> explain WITH iteration_numbers AS (
                           SELECT *, ROW_NUMBER() OVER(PARTITION BY space_id ORDER BY created_at ASC) AS num
                           FROM iterations
                       )
                       UPDATE iterations SET number = (SELECT num FROM iteration_numbers WHERE iteration_numbers.id = iterations.id);
+---------------------------------------------------------------------------------------------+
| QUERY PLAN                                                                                  |
|---------------------------------------------------------------------------------------------|
| Update on iterations  (cost=1.07..2.18 rows=2 width=191)                                    |
|   CTE iteration_numbers                                                                     |
|     ->  WindowAgg  (cost=1.03..1.07 rows=2 width=193)                                       |
|           ->  Sort  (cost=1.03..1.03 rows=2 width=185)                                      |
|                 Sort Key: iterations_1.space_id, iterations_1.created_at                    |
|                 ->  Seq Scan on iterations iterations_1  (cost=0.00..1.02 rows=2 width=185) |
|   ->  Seq Scan on iterations  (cost=0.00..1.11 rows=2 width=191)                            |
|         SubPlan 2                                                                           |
|           ->  CTE Scan on iteration_numbers  (cost=0.00..0.04 rows=1 width=8)               |
|                 Filter: (id = iterations.id)                                                |
+---------------------------------------------------------------------------------------------+

[alien-ike]

Ike Plugins (test-keeper)

Thank you @kwk for this contribution!

It appears that no tests have been added or updated in this PR.

Automated tests give us confidence in shipping reliable software. Please add some as part of this change.

If you are an admin or the reviewer of this PR and you are sure that no test is needed then you can use the command /ok-without-tests as a comment to make the status green.

For more information please head over to official documentation. You can find there how to configure the plugin.

[codecov[bot]]

Codecov Report

Merging #2319 into master will increase coverage by 0.03%. The diff coverage is n/a.

@@            Coverage Diff             @@
##           master    #2319      +/-   ##
==========================================
+ Coverage   69.99%   70.03%   +0.03%     
==========================================
  Files         171      171              
  Lines       16610    16610              
==========================================
+ Hits        11626    11632       +6     
+ Misses       3861     3854       -7     
- Partials     1123     1124       +1

Impacted Files	Coverage Δ
workitem/workitem_repository.go	67.79% <0%> (-0.23%)	:arrow_down:
remoteworkitem/scheduler.go	60.97% <0%> (+7.31%)	:arrow_up:
remoteworkitem/jira.go	100% <0%> (+25%)	:arrow_up:

---

Continue to review full report at Codecov.

Legend - Click here to learn more Δ = absolute <relative> (impact), ø = not affected, ? = missing data Powered by Codecov. Last update 37a913a...19d48f5. Read the comment docs.

[jarifibrahim]

/ok-without-tests