• Toon Claes's avatar
    Enhance performance of counting local Uploads · 1c481b7a
    Toon Claes authored
    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
    
    ---
    
    ### Query plans
    
    Query:
    
    ```sql
    SELECT COUNT(*)
    FROM "uploads"
    WHERE ("uploads"."store" = ? OR "uploads"."store" IS NULL)
    ```
    
    #### 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)
    ```
    
    Closes https://gitlab.com/gitlab-org/gitlab-ee/issues/6070
    1c481b7a
tc-index-uploads-file-store.yml 104 Bytes