Add project_id, user_id, status, ref index to ci_pipelines

What does this MR do?

Previous attempt: !32687 (closed)

Related to #217748 (closed)

We are implementing filters for pipelines. Right now, we have author and branch name filter. Next, we will implement status and tag name filter. However, before that, we realized that this endpoint has not good ~performance when user filter involves.

This MR aims to improve ~performance of pipeline queries.

Later, we'll cover other points in the issue description.

We've realized that this single index covers our use cases:

CREATE INDEX index_ci_pipelines_on_project_id_and_user_id_and_status_and_ref ON public.ci_pipelines USING btree (project_id, user_id, status, ref) WHERE (source <> 12);

database-lab:

The query has been executed. Duration: 27.424 min

UP:

== 20200528125905 AddProjectIdUserIdStatusRefIndexToCiPipelines: migrating ====
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:ci_pipelines, [:project_id, :user_id, :status, :ref], {:where=>"source != 12", :algorithm=>:concurrently})
   -> 0.0064s
-- add_index(:ci_pipelines, [:project_id, :user_id, :status, :ref], {:where=>"source != 12", :algorithm=>:concurrently})
   -> 0.0038s
== 20200528125905 AddProjectIdUserIdStatusRefIndexToCiPipelines: migrated (0.0106s)

DOWN:

== 20200528125905 AddProjectIdUserIdStatusRefIndexToCiPipelines: reverting ====
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:ci_pipelines, [:project_id, :user_id, :status, :ref], {:where=>"source != 12", :algorithm=>:concurrently})
   -> 0.0069s
-- remove_index(:ci_pipelines, {:where=>"source != 12", :algorithm=>:concurrently, :column=>[:project_id, :user_id, :status, :ref]})
   -> 0.0065s
== 20200528125905 AddProjectIdUserIdStatusRefIndexToCiPipelines: reverted (0.0138s)

SQL Queries

Endpoint-1: pipelines.json?page=1&scope=all&username=stanhu

EXPLAIN
SELECT "ci_pipelines".*
FROM "ci_pipelines"
INNER JOIN "users" ON "users"."id" = "ci_pipelines"."user_id"
WHERE "ci_pipelines"."project_id" = 278964
  AND "ci_pipelines"."source" != 12
  AND "users"."username" = 'stanhu'
ORDER BY "ci_pipelines"."id" DESC LIMIT 20
OFFSET 0;

• Before index: https://explain.depesz.com/s/3kIQ

Time: 29.264 ms
  - planning: 0.668 ms
  - execution: 28.596 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 17770 (~138.80 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

• After index: https://explain.depesz.com/s/fkpH

Time: 15.099 ms
  - planning: 1.089 ms
  - execution: 14.010 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 4330 (~33.80 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Endpoint-2: pipelines.json?page=1&scope=all&username=stanhu&ref=master

EXPLAIN
SELECT "ci_pipelines".*
FROM "ci_pipelines"
INNER JOIN "users" ON "users"."id" = "ci_pipelines"."user_id"
WHERE "ci_pipelines"."project_id" = 278964
  AND "ci_pipelines"."source" != 12
  AND "ci_pipelines"."ref" = 'master'
  AND "users"."username" = 'stanhu'
ORDER BY "ci_pipelines"."id" DESC LIMIT 20
OFFSET 0;

• Before index: https://explain.depesz.com/s/3wAK

Time: 26.355 ms
  - planning: 0.831 ms
  - execution: 25.524 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 17770 (~138.80 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

• After index: https://explain.depesz.com/s/BJIP

Time: 5.511 ms
  - planning: 0.932 ms
  - execution: 4.579 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 1223 (~9.60 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Endpoint-3: pipelines.json?page=1&scope=all&username=stanhu&status=success

EXPLAIN
SELECT "ci_pipelines".*
FROM "ci_pipelines"
INNER JOIN "users" ON "users"."id" = "ci_pipelines"."user_id"
WHERE "ci_pipelines"."project_id" = 278964
  AND "ci_pipelines"."source" != 12
  AND "ci_pipelines"."status" = 'success'
  AND "users"."username" = 'stanhu'
ORDER BY "ci_pipelines"."id" DESC LIMIT 20
OFFSET 0;

• Before index: https://explain.depesz.com/s/7xmI

Time: 26.537 ms
  - planning: 0.874 ms
  - execution: 25.663 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 17770 (~138.80 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

• After index: https://explain.depesz.com/s/TzhN

Time: 9.397 ms
  - planning: 0.954 ms
  - execution: 8.443 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 2039 (~15.90 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Endpoint-4: pipelines.json?page=1&scope=all&username=stanhu&status=success&ref=master

EXPLAIN
SELECT "ci_pipelines".*
FROM "ci_pipelines"
INNER JOIN "users" ON "users"."id" = "ci_pipelines"."user_id"
WHERE "ci_pipelines"."project_id" = 278964
  AND "ci_pipelines"."source" != 12
  AND "ci_pipelines"."status" = 'success'
  AND "ci_pipelines"."ref" = 'master'
  AND "users"."username" = 'stanhu'
ORDER BY "ci_pipelines"."id" DESC LIMIT 20
OFFSET 0;

• Before index: https://explain.depesz.com/s/GZzU

Time: 24.733 ms
  - planning: 0.951 ms
  - execution: 23.782 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 17770 (~138.80 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

• After index: https://explain.depesz.com/s/KZEb

Time: 3.918 ms
  - planning: 1.033 ms
  - execution: 2.885 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 872 (~6.80 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Does this MR meet the acceptance criteria?

Conformity

• Changelog entry
• [-] 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