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

Commitc87c33f

Browse files
perf: add index to improve the GetWorkspaceAgentByInstanceID query performance (#20936)
## ContextGetWorkspaceAgentByInstanceID has a suboptimal plan. Even though it isdesigned to fetch a small subset of records, there are no correspondingindexes 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 PostgreSQLinstance, 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)```---------Signed-off-by: Danny Kopping <danny@coder.com>Co-authored-by: Danny Kopping <danny@coder.com>
1 parenta926157 commitc87c33f

File tree

3 files changed

+4
-0
lines changed

3 files changed

+4
-0
lines changed

‎coderd/database/dump.sql‎

Lines changed: 2 additions & 0 deletions
Some generated files are not rendered by default. Learn more aboutcustomizing how changed files appear on GitHub.
Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1 @@
1+
DROPINDEX IF EXISTSpublic.workspace_agents_auth_instance_id_deleted_idx;
Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1 @@
1+
CREATEINDEXIF NOT EXISTS workspace_agents_auth_instance_id_deleted_idxONpublic.workspace_agents (auth_instance_id, deleted);

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp