Remove feature flag optimize_issue_filter_assigned_to_self [RUN ALL RSPEC] [RUN AS-IF-FOSS]

What does this MR do?

This was added in !57073 (merged)

This has already been enabled on production for 4 days without issues so we can probably remove it now.

#325470 (comment 538325532)

Queries

There doesn't appear to be any noticeable time improvement in the queries but the total cost is reduced from ~1400 to ~700 . The query is also quite a bit simpler so it seems logical that this would improve performance but it's hard to tell for sure with the explains I ran.

It's also worth noting I had all these warnings in the Before and After query analysis that maybe could provide clues to improving further:

Recommendations:

• ❗ Query processes too much data to return a relatively small number of rows. – Reduce data cardinality as early as possible during the execution, using one or several of the following techniques: new indexes, partitioning, query rewriting, denormalization. See the visualization of the plan to understand which plan nodes are the main bottlenecks. Show details
• ❗ Specialized index needed – The index(es) currently used does not serve quite well for the needs of this query (notice Rows Removed by Filter: ..., meaning that the index fetched many non-target rows). Consider adding more specialized index(es). Show details
• ❗ VACUUM ANALYZE needed – Visibility map(s) for the table(s) involved in the query are outdated. For better performance: 1) run VACUUM ANALYZE on them as a one-time solution, 2) perform autovacuum tuning as a long-term permanent solution (tuning "vacuum" part of autovacuum). Show details

Before

SELECT Count(*)
FROM   "issues"
       INNER JOIN "projects"
               ON "projects"."id" = "issues"."project_id"
       LEFT JOIN project_features
              ON projects.id = project_features.project_id
WHERE  ( issues.confidential IS NOT TRUE
          OR ( issues.confidential = true
               AND ( issues.author_id = 120073
                      OR EXISTS (SELECT true
                                 FROM   issue_assignees
                                 WHERE  user_id = 120073
                                        AND issue_id = issues.id)
                      OR EXISTS (SELECT 1
                                 FROM   "project_authorizations"
                                 WHERE  "project_authorizations"."user_id" =
                                        120073
                                        AND ( project_authorizations.project_id
                                              =
                                            issues.project_id )
                                        AND (
                                project_authorizations.access_level >= 20
                                            )
                                ) ) ) )
       AND ( EXISTS (SELECT 1
                     FROM   "project_authorizations"
                     WHERE  "project_authorizations"."user_id" = 120073
                            AND ( project_authorizations.project_id =
                                  projects.id )
                            AND ( project_authorizations.access_level >= 10 ))
              OR projects.visibility_level IN ( 10, 20 ) )
       AND ( "project_features"."issues_access_level" IS NULL
              OR "project_features"."issues_access_level" IN ( 20, 30 )
              OR ( "project_features"."issues_access_level" = 10
                   AND EXISTS (SELECT 1
                               FROM   "project_authorizations"
                               WHERE  "project_authorizations"."user_id" =
                                      120073
                                      AND ( project_authorizations.project_id =
                                          projects.id )
                                      AND ( project_authorizations.access_level
                                            >= 10 )
                              ) ) )
       AND ( "issues"."state_id" IN ( 1 ) )
       AND ( EXISTS (SELECT true
                     FROM   "issue_assignees"
                     WHERE  "issue_assignees"."user_id" IN ( 120073 )
                            AND issue_id = issues.id) )
       AND "projects"."archived" = false 

Plan: https://explain.depesz.com/s/20C6

After

SELECT
    COUNT(*)
FROM
    "issues"
    INNER JOIN "projects" ON "projects"."id" = "issues"."project_id"
    LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE (EXISTS (
        SELECT
            1
        FROM
            "project_authorizations"
        WHERE
            "project_authorizations"."user_id" = 120073
            AND (project_authorizations.project_id = projects.id)
            AND (project_authorizations.access_level >= 10))
        OR projects.visibility_level IN (10, 20))
AND ("project_features"."issues_access_level" IS NULL
    OR "project_features"."issues_access_level" IN (20, 30)
    OR ("project_features"."issues_access_level" = 10
        AND EXISTS (
            SELECT
                1
            FROM
                "project_authorizations"
            WHERE
                "project_authorizations"."user_id" = 120073
                AND (project_authorizations.project_id = projects.id)
                AND (project_authorizations.access_level >= 10))))
AND ("issues"."state_id" IN (1))
AND (EXISTS (
        SELECT
            TRUE
        FROM
            "issue_assignees"
        WHERE
            "issue_assignees"."user_id" IN (120073)
            AND issue_id = issues.id))
AND "projects"."archived" = FALSE

Plan: https://explain.depesz.com/s/qcgb

Postgres AI: https://console.postgres.ai/gitlab/gitlab-production-tunnel/sessions/3163/commands/10295

Does this MR meet the acceptance criteria?

Conformity

• 📋 Does this MR need a changelog?
  • I have included a changelog entry.
  • I have not included a changelog entry because _____.
• [-] Documentation (if required)
• Code review guidelines
• Merge request performance guidelines
• Style guides
• Database guides
• Separation of EE specific content

Availability and Testing

• Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process.
• Tested in all supported browsers
• Informed Infrastructure department of a default or new setting change, if applicable per definition of done

Security

If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:

• Label as security and @ mention @gitlab-com/gl-security/appsec
• The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
• Security reports checked/validated by a reviewer from the AppSec team