Optimize the query on Environment Detail page

What does this MR do and why?

This MR fixes the issue Can't render the Environment Detail page when there are many deployments. As you can see in the problem statement, currently the query takes 1.220 min to finish the query, which results in statement timeout (500).

This MR adds an index on the visible scope to efficiently find the deployments of a specific environment.

Closes #356632 (closed)

Query comparison

Old

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

New

Plan

 Limit  (cost=0.57..27.38 rows=20 width=144) (actual time=2.717..13.200 rows=20 loops=1)
   Buffers: shared read=24 dirtied=7
   I/O Timings: read=12.883 write=0.000
   ->  Index Scan using index_deployments_for_environment_detail_page on public.deployments  (cost=0.57..527192.03 rows=393260 width=144) (actual time=2.715..13.187 rows=20 loops=1)
         Index Cond: (deployments.environment_id = 137)
         Buffers: shared read=24 dirtied=7
         I/O Timings: read=12.883 write=0.000

Timing

Time: 18.548 ms
  - planning: 5.265 ms
  - execution: 13.283 ms
    - I/O read: 12.883 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 0 from the buffer pool
  - reads: 24 (~192.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 7 (~56.00 KiB)
  - writes: 0

Migration

Screenshots or screen recordings

N/A

How to set up and validate locally

N/A

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.