Can't render the Environment Detail page when there are many deployments

Problem

Visiting https://gitlab.com/gitlab-com/www-gitlab-com/-/environments/137, it gives you 500 error due to a statement timeout. This is because the environment has many deployments.

For example,

Query

SELECT "deployments".* FROM "deployments" WHERE "deployments"."environment_id" = 137 AND "deployments"."status" IN (1, 2, 3, 4, 6) ORDER BY "deployments"."finished_at" DESC LIMIT 20 OFFSET 0

Plan

 Limit  (cost=513873.44..513875.78 rows=20 width=145) (actual time=73166.620..73225.642 rows=20 loops=1)
   Buffers: shared hit=179611 read=169491 dirtied=8312
   I/O Timings: read=215486.535 write=0.000
   ->  Gather Merge  (cost=513873.44..550829.03 rows=316740 width=145) (actual time=73166.617..73225.634 rows=20 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=179611 read=169491 dirtied=8312
         I/O Timings: read=215486.535 write=0.000
         ->  Sort  (cost=512873.42..513269.35 rows=158370 width=145) (actual time=73153.596..73153.600 rows=17 loops=3)
               Sort Key: deployments.finished_at DESC
               Sort Method: top-N heapsort  Memory: 35kB
               Buffers: shared hit=179608 read=169491 dirtied=8312
               I/O Timings: read=215486.535 write=0.000
               ->  Parallel Index Scan using index_deployments_on_environment_id_status_and_finished_at on public.deployments  (cost=0.57..508659.25 rows=158370 width=145) (actual time=367.500..72762.317 rows=227223 loops=3)
                     Index Cond: ((deployments.environment_id = 137) AND (deployments.status = ANY ('{1,2,3,4,6}'::integer[])))
                     Buffers: shared hit=179590 read=169491 dirtied=8312
                     I/O Timings: read=215486.535 write=0.000

Timing

Time: 1.220 min
  - planning: 0.590 ms
  - execution: 1.220 min
    - I/O read: 3.591 min
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 179611 (~1.40 GiB) from the buffer pool
  - reads: 169491 (~1.30 GiB) from the OS file cache, including disk I/O
  - dirtied: 8312 (~64.90 MiB)
  - writes: 0

The main problem is that the query fetches 227223 rows and manually sorting them by finished_at. We want to make this one-go by creating a dedicated index.

Proposal

exec CREATE INDEX index_deployments_on_environment_id_status_and_finished_at_new ON deployments USING btree (environment_id, status, finished_at DESC);

https://gitlab.slack.com/archives/CLJMDRD8C/p1648004772757979

Sentry

ActionView::Template::Error: PG::QueryCanceled: ERROR: canceling statement due to statement timeout

https://sentry.gitlab.net/gitlab/gitlabcom/issues/3190848/?referrer=gitlab_plugin

PG::QueryCanceled: ERROR:  canceling statement due to statement timeout

  lib/gitlab/database/load_balancing/connection_proxy.rb:110:in `block in read_using_load_balancer'
    connection.send(...)
  lib/gitlab/database/load_balancing/load_balancer.rb:55:in `read'
    return yield connection
  lib/gitlab/database/load_balancing/connection_proxy.rb:109:in `read_using_load_balancer'
    @load_balancer.read do |connection|
  lib/gitlab/database/load_balancing/connection_proxy.rb:54:in `select_all'
    read_using_load_balancer(:select_all, arel, name, binds)
  app/views/projects/environments/show.html.haml:12:
    - if @deployments.blank?
...
(231 additional frame(s) were not displayed)

ActiveRecord::QueryCanceled: PG::QueryCanceled: ERROR:  canceling statement due to statement timeout

ActionView::Template::Error: PG::QueryCanceled: ERROR:  canceling statement due to statement timeout
Edited by Shinya Maeda