Skip to content

Fix statement timeouts in RemoveRestrictedTodos migration

Stan Hu requested to merge sh-fix-issue-52649 into master

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)

Closes https://gitlab.com/gitlab-org/gitlab-ce/issues/52649

Edited by Stan Hu

Merge request reports