Fix query timeout issues on `Security::Scans::PurgeWorker`

What does this MR do and why?

Ordering the query by created_at, and id fixes the timeout issue with the help of the partial index introduced by this MR.

Related to Query timeout issues on `Security::Scans::Purge... (#383202 - closed).

Database review

The following query is currently timing out

SELECT 
  "security_scans"."id" 
FROM 
  "security_scans" 
WHERE 
  (created_at < '2022-08-22 22:20:36.817840') AND 
  "security_scans"."status" != 6 AND 
  "security_scans"."id" > 29731563 
ORDER BY 
  "security_scans"."id" ASC 
LIMIT 100

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/13355/commands/46877

Ordering the records by `created_at`, and `id` with the help of the index introduced by this MR performs way better

SELECT
    "security_scans"."id"
FROM
    "security_scans"
WHERE (created_at < '2022-08-22 22:44:22.172750')
    AND "security_scans"."status" != 6
    AND (("security_scans"."created_at",
            "security_scans"."id") > ('2022-08-22 23:30:59.017464',
            29732491))
ORDER BY
    "security_scans"."created_at" ASC,
    "security_scans"."id" ASC
LIMIT 100

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/13355/commands/46923

Rake command outputs

VERSION=20221121000127

migrate up

main: == 20221121000127 IndexSecurityScansOnCreatedAtAndIdForNonPurgedRecords: migrating
main: -- transaction_open?()
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0041s
main: -- index_exists?(:security_scans, ["created_at", "id"], {:where=>"status != 6", :name=>:index_security_scans_for_non_purged_records, :algorithm=>:concurrently})
main:    -> 0.0059s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0005s
main: -- add_index(:security_scans, ["created_at", "id"], {:where=>"status != 6", :name=>:index_security_scans_for_non_purged_records, :algorithm=>:concurrently})
main:    -> 0.0039s
main: -- execute("RESET statement_timeout")
main:    -> 0.0004s
main: == 20221121000127 IndexSecurityScansOnCreatedAtAndIdForNonPurgedRecords: migrated (0.0299s)

migrate down

main: == 20221121000127 IndexSecurityScansOnCreatedAtAndIdForNonPurgedRecords: reverting
main: -- transaction_open?()
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0037s
main: -- indexes(:security_scans)
main:    -> 0.0046s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0005s
main: -- remove_index(:security_scans, {:algorithm=>:concurrently, :name=>:index_security_scans_for_non_purged_records})
main:    -> 0.0065s
main: -- execute("RESET statement_timeout")
main:    -> 0.0006s
main: == 20221121000127 IndexSecurityScansOnCreatedAtAndIdForNonPurgedRecords: reverted (0.0266s)

VERSION=20221121000451

migrate up

main: == 20221121000451 DropIndexSecurityScansOnIdForNonPurgedRecords: migrating ====
main: -- transaction_open?()
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0112s
main: -- indexes(:security_scans)
main:    -> 0.0084s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- remove_index(:security_scans, {:algorithm=>:concurrently, :name=>:index_security_scans_on_id_for_non_purged_records})
main:    -> 0.0144s
main: -- execute("RESET statement_timeout")
main:    -> 0.0004s
main: == 20221121000451 DropIndexSecurityScansOnIdForNonPurgedRecords: migrated (0.0492s)

migrate down

main: == 20221121000451 DropIndexSecurityScansOnIdForNonPurgedRecords: reverting ====
main: -- transaction_open?()
main:    -> 0.0001s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0009s
main: -- index_exists?(:security_scans, :id, {:where=>"status != 6", :name=>:index_security_scans_on_id_for_non_purged_records, :algorithm=>:concurrently})
main:    -> 0.0040s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0002s
main: -- add_index(:security_scans, :id, {:where=>"status != 6", :name=>:index_security_scans_on_id_for_non_purged_records, :algorithm=>:concurrently})
main:    -> 0.0090s
main: -- execute("RESET statement_timeout")
main:    -> 0.0004s
main: == 20221121000451 DropIndexSecurityScansOnIdForNonPurgedRecords: reverted (0.0248s)

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

• I have evaluated the MR acceptance checklist for this MR.