Fix statement timeouts in RemoveRestrictedTodos migration
On GitLab.com, the RemoveRestrictedTodos background migration encountered about 700+ failures a day due to statement timeouts.
PostgreSQL might perform badly with a LIMIT 1 because the planner is
guessing that scanning the index in ID order will come across the
desired row in less time it will take the planner than using another
index. The order_hint does not affect the search results. For example,
ORDER BY id ASC, updated_at ASC
means the same thing as ORDER BY id ASC
.
Before:
gitlabhq_production=# EXPLAIN ANALYZE SELECT "issues"."id" FROM "issues" WHERE "issues"."confidential" = 't' AND "issues"."project_id" = 359093 ORDER BY "issues"."id" ASC LIMIT 1;
ERROR: canceling statement due to statement timeout
After
gitlabhq_production=# explain analyze select id FROM issues where confidential = 't' AND project_id = 359093 order by confidential, id DESC LIMIT 1;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------
Limit (cost=9380.89..9380.89 rows=1 width=5) (actual time=1725.754..1725.754 rows=0 loops=1)
-> Sort (cost=9380.89..9381.90 rows=404 width=5) (actual time=1725.754..1725.754 rows=0 loops=1)
Sort Key: confidential, id DESC
Sort Method: quicksort Memory: 25kB
-> Bitmap Heap Scan on issues (cost=8747.42..9378.87 rows=404 width=5) (actual time=1725.725..1725.725 rows=0 loops=1)
Recheck Cond: (project_id = 359093)
Filter: confidential
-> BitmapAnd (cost=8747.42..8747.42 rows=404 width=0) (actual time=1725.676..1725.676 rows=0 loops=1)
-> Bitmap Index Scan on index_issues_on_project_id_and_iid (cost=0.00..403.72 rows=26371 width=0) (actual time=8.256..
8.256 rows=23601 loops=1)
Index Cond: (project_id = 359093)
-> Bitmap Index Scan on index_issues_on_confidential (cost=0.00..8343.25 rows=183559 width=0) (actual time=1708.000..1
708.000 rows=178499 loops=1)
Index Cond: (confidential = true)
Planning time: 2.154 ms
Execution time: 1725.926 ms
(14 rows)
Subsequent calls are faster:
gitlabhq_production=# explain analyze select id FROM issues where confidential = 't' AND project_id = 359093 order by confidential, id DESC LIMIT 1;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
--------------------------
Limit (cost=9380.89..9380.89 rows=1 width=5) (actual time=56.988..56.988 rows=0 loops=1)
-> Sort (cost=9380.89..9381.90 rows=404 width=5) (actual time=56.988..56.988 rows=0 loops=1)
Sort Key: confidential, id DESC
Sort Method: quicksort Memory: 25kB
-> Bitmap Heap Scan on issues (cost=8747.42..9378.87 rows=404 width=5) (actual time=56.943..56.943 rows=0 loops=1)
Recheck Cond: (project_id = 359093)
Filter: confidential
-> BitmapAnd (cost=8747.42..8747.42 rows=404 width=0) (actual time=56.899..56.899 rows=0 loops=1)
-> Bitmap Index Scan on index_issues_on_project_id_and_iid (cost=0.00..403.72 rows=26371 width=0) (actual time=3.723..
3.723 rows=23601 loops=1)
Index Cond: (project_id = 359093)
-> Bitmap Index Scan on index_issues_on_confidential (cost=0.00..8343.25 rows=183559 width=0) (actual time=47.250..47.
250 rows=178501 loops=1)
Index Cond: (confidential = true)
Planning time: 0.142 ms
Execution time: 57.019 ms
(14 rows)
Edited by Stan Hu