Enhance performance of counting local Uploads
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?
-
Changelog entry added, if necessary -
Documentation created/updated -
Tests added for this feature/bug -
Conforms to the code review guidelines -
Conforms to the merge request performance guidelines -
Conforms to the style guides -
Conforms to the database guides
Edited by Toon Claes