Restrict inefficient filter combinations on EnvironmentsFinder for search operation
As noted in !102227 (comment 1168889576)
If we allow additional query parameters in future from Graphql and Public API the EnvironmentsFinder
then it will be possible to query using inefficient indexes and this will affect performance and if there is abusive usage it can also disrupt system health. Validate and raise error for such cases as we have done in DeploymentsFinder
.
What we want to make sure here is that the index for
search
always takes a higher precedence over to the other low-selectivity indexes. For example, specifying bothtype
andsearch
would generate a query like the following one:
SELECT
COUNT(*) AS count_all,
"environments"."state" AS environments_state
FROM
(
(
SELECT
"environments".*
FROM
"environments"
WHERE
"environments"."project_id" = 278964
AND "environments"."environment_type" = 'review'
AND (
LOWER(environments.name) LIKE LOWER('rev') || '%'
)
)
UNION
(
SELECT
"environments".*
FROM
"environments"
WHERE
"environments"."project_id" = 278964
AND "environments"."environment_type" = 'review'
AND (
LOWER(
ltrim(
environments.name,
environments.environment_type || '/'
)
) LIKE LOWER('rev') || '%'
)
)
) environments
GROUP BY
"environments"."state"
You can see a query plan in https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/13216/commands/46358 that
index_environments_on_project_id_state_environment_type
is instead ofindex_environments_on_project_name_varchar_pattern_ops_state
. Therefore, PostgreSQL needs to sequentially filter byFilter: (lower((environments.name)::text) ~~ 'rev%'::text)
, which is slower than Index scanning. I think the same thing could happen inindex_environments_for_name_search_within_folder
as well.