Fix member expiration not always working
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)
Edited by Stan Hu