Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

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.

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

Already on GitHub?Sign in to your account

Merged
dannykopping merged 2 commits intocoder:mainfrommykyta-protsenko:add-missing-index
Nov 26, 2025

Conversation

@mykyta-protsenko
Copy link
Contributor

@mykyta-protsenkomykyta-protsenko commentedNov 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_agentswhere 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-botcdr-botbot added the communityPull Requests and issues created by the community. labelNov 25, 2025
@github-actions
Copy link

github-actionsbot commentedNov 25, 2025•
edited
Loading

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

@mykyta-protsenko
Copy link
ContributorAuthor

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

@mykyta-protsenko
Copy link
ContributorAuthor

recheck

cdrci2 added a commit to coder/cla that referenced this pull requestNov 25, 2025
Signed-off-by: Danny Kopping <danny@coder.com>
Copy link
Contributor

@dannykoppingdannykopping left a comment

Choose a reason for hiding this comment

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

Thank youvery much for this@mykyta-protsenko!

@dannykoppingdannykopping merged commitc87c33f intocoder:mainNov 26, 2025
25 checks passed
@github-actionsgithub-actionsbot locked and limited conversation to collaboratorsNov 26, 2025
Sign up for freeto subscribe to this conversation on GitHub. Already have an account?Sign in.

Reviewers

@dannykoppingdannykoppingdannykopping approved these changes

Labels

communityPull 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
[8]ページ先頭
©2009-2025 Movatter.jp