Inefficient SQL for class Identity
This is a corrective action for incident gitlab-com/gl-infra/production#4645 (closed)
- Problem summary: gitlab-com/gl-infra/production#4645 (comment 580338887)
- Solution proposal: gitlab-com/gl-infra/production#4645 (comment 580339696)
Briefly, the identities
table (backing ruby class Identity
) has an index on:
lower(extern_uid), provider
But at least some of the queries we generate and run are omitting the lower()
function, such that the query's WHERE clause filters are not compatible with the index:
...
WHERE
"identities"."provider" = 'github'
AND "identities"."extern_uid" = '14' /* Note: Not calling lower() function on field extern_uid */
Due to this incompatibility, the index scan has become roughly 200x less efficient. The query executor has to effectively do an index skip-scan: walk the whole index skipping the first indexed field (lower(extern_uid)
) to fetch all rows that match the second field's value, and then as a post-processing step filter the intermediate results (roughly 200K - 400K) by the value of the first field. This costs a few hundred milliseconds instead of 1-2 ms.
To fix this inefficiency, we can either:
- Change the query to match the index spec:
lower(identities.extern_uid) = ...
- Change the index to match the query spec:
CREATE INDEX on identities ( provider, extern_uid )