Skip to content

batches: Faster workspace lookup

Administrator requested to merge es/faster-workspace-list into main

Created by: eseliger

This index makes it faster to lookup workspaces in a batch spec.

sg=# explain analyze select * from batch_spec_workspaces join repo on repo.id = repo_id where batch_spec_id = 2023 and repo.deleted_at is null order by batch_spec_workspaces.id asc limit 50;
                                                                                 QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.71..85.03 rows=50 width=851) (actual time=154.062..154.261 rows=50 loops=1)
   ->  Nested Loop  (cost=0.71..81022.71 rows=48050 width=851) (actual time=154.061..154.256 rows=50 loops=1)
         ->  Index Scan using batch_spec_workspaces_pkey on batch_spec_workspaces  (cost=0.42..61220.80 rows=48124 width=257) (actual time=154.036..154.067 rows=50 loops=1)
               Filter: (batch_spec_id = 2023)
               Rows Removed by Filter: 592596
         ->  Index Scan using repo_pkey on repo  (cost=0.29..0.41 rows=1 width=586) (actual time=0.003..0.003 rows=1 loops=50)
               Index Cond: (id = batch_spec_workspaces.repo_id)
               Filter: (deleted_at IS NULL)
 Planning Time: 0.352 ms
 Execution Time: 154.354 ms
(10 rows)

sg=# create index on batch_spec_workspaces(id, batch_spec_id);
CREATE INDEX
sg=# explain analyze select * from batch_spec_workspaces join repo on repo.id = repo_id where batch_spec_id = 2023 and repo.deleted_at is null order by batch_spec_workspaces.id asc limit 50;
                                                                                        QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.71..50.10 rows=50 width=851) (actual time=17.969..18.174 rows=50 loops=1)
   ->  Nested Loop  (cost=0.71..47462.30 rows=48050 width=851) (actual time=17.967..18.169 rows=50 loops=1)
         ->  Index Scan using batch_spec_workspaces_id_batch_spec_id_idx on batch_spec_workspaces  (cost=0.42..27660.39 rows=48124 width=257) (actual time=17.949..17.982 rows=50 loops=1)
               Index Cond: (batch_spec_id = 2023)
         ->  Index Scan using repo_pkey on repo  (cost=0.29..0.41 rows=1 width=586) (actual time=0.003..0.003 rows=1 loops=50)
               Index Cond: (id = batch_spec_workspaces.repo_id)
               Filter: (deleted_at IS NULL)
 Planning Time: 0.413 ms
 Execution Time: 18.287 ms
(9 rows)

Test plan

Index change, won't break anything and manually verified its impact.

Merge request reports