Skip to content

Convert project_id column to bigint to increase planning time performance

Marius Bobin requested to merge 340583-improve-query-planning-time into master

What does this MR do and why?

Before: https://explain.depesz.com/s/ZWmP

| Planning time   | :  | 14.435 ms |
|-----------------|----|-----------|
| Execution time  | :  | 0.174 ms  |

After: https://explain.depesz.com/s/Tm4I

| Planning time   | :  | 1.673 ms |
|-----------------|----|----------|
| Execution time  | :  | 0.198 ms |

On the pending builds table the project_id column is bigint and on ci_runner_projects is integer:

gitlabhq_development=# \d ci_pending_builds
                                              Table "public.ci_pending_builds"
          Column          |           Type           | Collation | Nullable |                    Default
--------------------------+--------------------------+-----------+----------+-----------------------------------------------
 id                       | bigint                   |           | not null | nextval('ci_pending_builds_id_seq'::regclass)
 build_id                 | bigint                   |           | not null |
 project_id               | bigint                   |           | not null |
 created_at               | timestamp with time zone |           | not null | now()
 protected                | boolean                  |           | not null | false
 instance_runners_enabled | boolean                  |           | not null | false
 namespace_id             | bigint                   |           |          |
 minutes_exceeded         | boolean                  |           | not null | false
 tag_ids                  | integer[]                |           |          | '{}'::integer[]
 namespace_traversal_ids  | integer[]                |           |          | '{}'::integer[]

gitlabhq_development=# \d ci_runner_projects
                                        Table "public.ci_runner_projects"
   Column   |            Type             | Collation | Nullable |                    Default
------------+-----------------------------+-----------+----------+------------------------------------------------
 id         | integer                     |           | not null | nextval('ci_runner_projects_id_seq'::regclass)
 runner_id  | integer                     |           | not null |
 created_at | timestamp without time zone |           |          |
 updated_at | timestamp without time zone |           |          |
 project_id | integer                     |           |          |

Screenshots or screen recordings

These are strongly recommended to assist reviewers and reduce the time to merge your change.

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.

MR acceptance checklist

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

Related to #340583 (closed)

Edited by Marius Bobin

Merge request reports