• Kamil Trzciński's avatar
    Make `User#ci_owned_runners` to use unnest index instead of GIN · 7304cf06
    Kamil Trzciński authored
    The GIN type of search (using `&&` operator) is very slow. It appears
    that we might have a queries in range of 1-10ms if we use a dedicated
    index that decomposes traversal_ids and perform prefix search in form of:
    
    ```sql
    SELECT * FROM ci_namespace_mirrors
    WHERE ((traversal_ids[1])) IN ((1)) OR (((traversal_ids[1]), ((traversal_ids[2])) IN ((1,2))
    ```
    
    The 4-levels are used since this appears to be enough to cover 99% cases queried
    by the index. Beyond 4th level it will be more efficient to do seq scan of remaining
    entries.
    
    Changelog: performance
    7304cf06
ci_owned_runners_unnest_index.yml 274 Bytes