Skip to content

Fix member expiration not always working

Stan Hu requested to merge sh-add-index-members-expires into master

The Sidekiq job RemoveExpiredMembersWorker was failing to run in production because it was hitting statement timeouts because it was scanning all rows in order. On staging, where it used to scan 4 million rows, adding an index brought this down to only a few hundred rows.

Before

gitlabhq_production=# explain analyze SELECT  "members".* FROM "members" WHERE (expires_at <= '2019-09-11 14:19:59.404701') ORDER BY "members"."id" ASC LIMIT 1000;
                                                       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=105597.18..105597.45 rows=110 width=168) (actual time=723.583..723.587 rows=10 loops=1)
   ->  Sort  (cost=105597.18..105597.45 rows=110 width=168) (actual time=723.581..723.585 rows=10 loops=1)
         Sort Key: id
         Sort Method: quicksort  Memory: 26kB
         ->  Seq Scan on members  (cost=0.00..105593.45 rows=110 width=168) (actual time=0.387..723.527 rows=10 loops=1)
               Filter: (expires_at <= '2019-09-11'::date)
               Rows Removed by Filter: 3950025
 Planning time: 1.758 ms
 Execution time: 723.639 ms
(9 rows)

After

gitlabhq_production=# create index concurrently index_members_test on members(expires_at);
CREATE INDEX
gitlabhq_production=#  explain analyze SELECT  "members".* FROM "members" WHERE (expires_at <= '2019-09-11 14:19:59.404701') ORDER BY "members"."id" ASC LIMIT 1000;
                                                                  QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=171.23..171.50 rows=110 width=168) (actual time=0.051..0.053 rows=10 loops=1)
   ->  Sort  (cost=171.23..171.50 rows=110 width=168) (actual time=0.050..0.051 rows=10 loops=1)
         Sort Key: id
         Sort Method: quicksort  Memory: 26kB
         ->  Index Scan using index_members_test on members  (cost=0.43..167.50 rows=110 width=168) (actual time=0.015..0.041 rows=10 loops=1)
               Index Cond: (expires_at <= '2019-09-11'::date)
 Planning time: 0.420 ms
 Execution time: 0.085 ms
(8 rows)

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

Edited by Stan Hu

Merge request reports