Skip to content

perf: add index to improve the GetWorkspaceAgentByInstanceID query performance #20936

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Merged
dannykopping merged 2 commits into from
Nov 26, 2025
Merged

perf: add index to improve the GetWorkspaceAgentByInstanceID query performance #20936

dannykopping merged 2 commits into from
Nov 26, 2025

Conversation

@mykyta-protsenko
Copy link
Contributor

@mykyta-protsenko mykyta-protsenko commented Nov 25, 2025 •
edited
Loading

Context

GetWorkspaceAgentByInstanceID has a suboptimal plan. Even though it is designed to fetch a small subset of records, there are no corresponding indexes and that query results in full table scan:

Query:

SELECT id, auth_instance_id FROM workspace_agents
where auth_instance_id='i-013c2b96b6441648a' and deleted=FALSE;

Plan:

------------------------------------------------------------------------------------------------------------------
 Seq Scan on workspace_agents  (cost=0.00..222325.48 rows=2 width=36) (actual time=0.012..234.152 rows=4 loops=1)
   Filter: ((NOT deleted) AND ((auth_instance_id)::text = 'i-013c2b96b6441648a'::text))
   Rows Removed by Filter: 302276
 Planning Time: 0.173 ms
 Execution Time: 234.169 ms

After adding the index, the plan improves drastically.

Updated plan:

 Bitmap Heap Scan on workspace_agents  (cost=4.44..12.32 rows=2 width=36) (actual time=0.019..0.019 rows=0 loops=1)
   Recheck Cond: (((auth_instance_id)::text = 'i-013c2b96b6441648a'::text) AND (NOT deleted))
   ->  Bitmap Index Scan on workspace_agents_auth_instance_id_deleted_idx  (cost=0.00..4.44 rows=2 width=0) (actual time=0.013..0.014 rows=0 loops=1)
         Index Cond: (((auth_instance_id)::text = 'i-013c2b96b6441648a'::text) AND (deleted = false))
 Planning Time: 0.388 ms
 Execution Time: 0.044 ms

Changes

  • add an index to optimize this query

Testing

  • ran the queries manually against prod and test DBs
  • ran ./scripts/develop.sh, connected to the local PostgreSQL instance, inspected the indexes to make sure new index is there:
Indexes:
    "workspace_agents_pkey" PRIMARY KEY, btree (id)
    // NEW INDEX CREATED SUCCESSFULLY  [comment is mine]
    "workspace_agents_auth_instance_id_deleted_idx" btree (auth_instance_id, deleted)
    "workspace_agents_auth_token_idx" btree (auth_token)
    "workspace_agents_resource_id_idx" btree (resource_id)

@cdr-bot cdr-bot bot added the community Pull Requests and issues created by the community. label Nov 25, 2025
@github-actions
Copy link

github-actions bot commented Nov 25, 2025 •
edited
Loading

All contributors have signed the CLA ✍️ ✅
Posted by the CLA Assistant Lite bot.

@mykyta-protsenko
Copy link
Contributor Author

mykyta-protsenko commented Nov 25, 2025

I have read the CLA Document and I hereby sign the CLA

@mykyta-protsenko
Copy link
Contributor Author

mykyta-protsenko commented Nov 25, 2025

recheck

cdrci2 added a commit to coder/cla that referenced this pull request Nov 25, 2025
@cdrci2
@mykyta-protsenko has signed the CLA in coder/coder#20936
6525e79
@dannykopping
chore: make gen
ba551fd 
Signed-off-by: Danny Kopping <danny@coder.com>
dannykopping
dannykopping approved these changes Nov 26, 2025
View reviewed changes
Copy link
Contributor

@dannykopping dannykopping left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Thank you very much for this @mykyta-protsenko!

@dannykopping dannykopping merged commit c87c33f into coder:main Nov 26, 2025
25 checks passed
@github-actions github-actions bot locked and limited conversation to collaborators Nov 26, 2025
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.

Reviewers

@dannykopping dannykopping dannykopping approved these changes

Assignees

@mykyta-protsenko mykyta-protsenko

Labels

community Pull Requests and issues created by the community.

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

2 participants

@mykyta-protsenko @dannykopping