feat: add src_id and dst_id indexes to tailnet_tunnels (#12911)

spikecurtis · coadler · commit 353888a5d8fd · 2024-04-17T21:01:39.000Z
Fixes #12780 Adds indexes to the `tailnet_tunnels` table to speed up `GetTailnetTunnelPeerIDs` and `GetTailnetTunnelPeerBindings` queries, which match on `src_id` and `dst_id`. (cherry picked from commit a231b5a)
diff --git a/coderd/database/dump.sql b/coderd/database/dump.sql
diff --git a/coderd/database/migrations/000206_add_tailnet_tunnels_indexes.down.sql b/coderd/database/migrations/000206_add_tailnet_tunnels_indexes.down.sql
@@ -0,0 +1,2 @@
+DROP INDEX idx_tailnet_tunnels_src_id;
+DROP INDEX idx_tailnet_tunnels_dst_id;
diff --git a/coderd/database/migrations/000206_add_tailnet_tunnels_indexes.up.sql b/coderd/database/migrations/000206_add_tailnet_tunnels_indexes.up.sql
@@ -0,0 +1,3 @@
+-- Since src_id and dst_id are UUIDs, we only ever compare them with equality, so hash is better
+CREATE INDEX idx_tailnet_tunnels_src_id ON tailnet_tunnels USING hash (src_id);
+CREATE INDEX idx_tailnet_tunnels_dst_id ON tailnet_tunnels USING hash (dst_id);

Original file line number	Diff line number	Diff line change
`@@ -0,0 +1,2 @@`
	`1`	`+DROP INDEX idx_tailnet_tunnels_src_id;`
	`2`	`+DROP INDEX idx_tailnet_tunnels_dst_id;`
Original file line number	Diff line number	Diff line change
`@@ -0,0 +1,3 @@`
	`1`	`+-- Since src_id and dst_id are UUIDs, we only ever compare them with equality, so hash is better`
	`2`	`+CREATE INDEX idx_tailnet_tunnels_src_id ON tailnet_tunnels USING hash (src_id);`
	`3`	`+CREATE INDEX idx_tailnet_tunnels_dst_id ON tailnet_tunnels USING hash (dst_id);`