Skip to content

Commit 229d051

Browse files
spikecurtisspikecurtis
authored
fix: speed up GetTailnetTunnelPeerBindings query (#19444)
relates to: coder/internal#718 Optimizes the GetTailnetTunnelPeerBindings query to reduce its execution time. Before: https://explain.dalibo.com/plan/c2fd53f913aah21c After: https://explain.dalibo.com/plan/6bc67d323g7afh61 At a high level, we first assemble the total list of peer IDs needed by the query, and only then go into the `tailnet_peers` table to extract their info. This saves us some time instead of hashing the entire `tailnet_peers` table.
1 parent 51d8a05 commit 229d051

File tree

2 files changed

+24
-20
lines changed

2 files changed

+24
-20
lines changed

‎coderd/database/queries.sql.go

Lines changed: 12 additions & 10 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/queries/tailnet.sql

Lines changed: 12 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -150,7 +150,7 @@ DO UPDATE SET
150150
RETURNING *;
151151

152152
-- name: UpdateTailnetPeerStatusByCoordinator :exec
153-
UPDATE
153+
UPDATE
154154
tailnet_peers
155155
SET
156156
status = $2
@@ -205,15 +205,17 @@ FROM tailnet_tunnels
205205
WHERE tailnet_tunnels.dst_id = $1;
206206

207207
-- name: GetTailnetTunnelPeerBindings :many
208-
SELECT tailnet_tunnels.dst_id as peer_id, tailnet_peers.coordinator_id, tailnet_peers.updated_at, tailnet_peers.node, tailnet_peers.status
209-
FROM tailnet_tunnels
210-
INNER JOIN tailnet_peers ON tailnet_tunnels.dst_id = tailnet_peers.id
211-
WHERE tailnet_tunnels.src_id = $1
212-
UNION
213-
SELECT tailnet_tunnels.src_id as peer_id, tailnet_peers.coordinator_id, tailnet_peers.updated_at, tailnet_peers.node, tailnet_peers.status
214-
FROM tailnet_tunnels
215-
INNER JOIN tailnet_peers ON tailnet_tunnels.src_id = tailnet_peers.id
216-
WHERE tailnet_tunnels.dst_id = $1;
208+
SELECT id AS peer_id, coordinator_id, updated_at, node, status
209+
FROM tailnet_peers
210+
WHERE id IN (
211+
SELECT dst_id as peer_id
212+
FROM tailnet_tunnels
213+
WHERE tailnet_tunnels.src_id = $1
214+
UNION
215+
SELECT src_id as peer_id
216+
FROM tailnet_tunnels
217+
WHERE tailnet_tunnels.dst_id = $1
218+
);
217219

218220
-- For PG Coordinator HTMLDebug
219221

0 commit comments

Comments
 (0)