Enhance performance of counting local Uploads

Toon Claesrequested to merge
tc-index-uploads-file-store into master

What does this MR do?

Add an index to the store column on uploads. This makes counting local uploads faster.

Also, there is no longer need to check for objects with store = NULL. See https://gitlab.com/gitlab-org/gitlab-ce/merge_requests/18557

EE port: https://gitlab.com/gitlab-org/gitlab-ee/merge_requests/8144

Query plans

Query BEFORE:

SELECT COUNT(*)
FROM "uploads"
WHERE ("uploads"."store" = ? OR "uploads"."store" IS NULL)

Query AFTER:

SELECT COUNT(*)
FROM "uploads"
WHERE ("uploads"."store" = ?)

Without index

gitlabhq_production=# EXPLAIN ANALYZE SELECT uploads.* FROM uploads WHERE (uploads.store = 1 OR uploads.store IS NULL);
                                                  QUERY PLAN
---------------------------------------------------------------------------------------------------------------
 Seq Scan on uploads  (cost=0.00..601729.54 rows=578 width=272) (actual time=6.170..2308.256 rows=545 loops=1)
   Filter: ((store = 1) OR (store IS NULL))
   Rows Removed by Filter: 4411957
 Planning time: 38.652 ms
 Execution time: 2308.454 ms
(5 rows)

Add index

gitlabhq_production=# create index uploads_tmp1 on uploads (store);
CREATE INDEX

With index

gitlabhq_production=# EXPLAIN ANALYZE SELECT uploads.* FROM uploads WHERE (uploads.store = 1 OR uploads.store IS NULL);
                                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on uploads  (cost=11.46..1238.88 rows=574 width=272) (actual time=0.155..0.577 rows=545 loops=1)
   Recheck Cond: ((store = 1) OR (store IS NULL))
   Heap Blocks: exact=217
   ->  BitmapOr  (cost=11.46..11.46 rows=574 width=0) (actual time=0.116..0.116 rows=0 loops=1)
         ->  Bitmap Index Scan on uploads_tmp1  (cost=0.00..8.74 rows=574 width=0) (actual time=0.095..0.095 rows=545 loops=1)
               Index Cond: (store = 1)
         ->  Bitmap Index Scan on uploads_tmp1  (cost=0.00..2.44 rows=1 width=0) (actual time=0.020..0.020 rows=0 loops=1)
               Index Cond: (store IS NULL)
 Planning time: 0.274 ms
 Execution time: 0.637 ms
(10 rows)

What are the relevant issue numbers?

Closes https://gitlab.com/gitlab-org/gitlab-ee/issues/6070

Does this MR meet the acceptance criteria?

Edited by Toon Claes

Merge request reports