Commit229d051

authored

fix: speed up GetTailnetTunnelPeerBindings query (#19444)

relates to:coder/internal#718Optimizes the GetTailnetTunnelPeerBindings query to reduce its execution time.Before:https://explain.dalibo.com/plan/c2fd53f913aah21cAfter:https://explain.dalibo.com/plan/6bc67d323g7afh61At 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 parent51d8a05 commit229d051Copy full SHA for 229d051

File tree

+24

-20

lines changed

+24

-20

lines changed

Lines changed: 12 additions & 10 deletions

Some generated files are not rendered by default. Learn more aboutcustomizing how changed files appear on GitHub.

Lines changed: 12 additions & 10 deletions

Original file line number	Diff line number	Diff line change
`@@ -150,7 +150,7 @@ DO UPDATE SET`
`150`	`150`	`RETURNING*;`
`151`	`151`
`152`	`152`	`-- name: UpdateTailnetPeerStatusByCoordinator :exec`
`153`		`-UPDATE`
	`153`	`+UPDATE`
`154`	`154`	`tailnet_peers`
`155`	`155`	`SET`
`156`	`156`	`status= $2`
`@@ -205,15 +205,17 @@ FROM tailnet_tunnels`
`205`	`205`	`WHEREtailnet_tunnels.dst_id= $1;`
`206`	`206`
`207`	`207`	`-- name: GetTailnetTunnelPeerBindings :many`
`208`		`-SELECTtailnet_tunnels.dst_idas peer_id,tailnet_peers.coordinator_id,tailnet_peers.updated_at,tailnet_peers.node,tailnet_peers.status`
`209`		`-FROM tailnet_tunnels`
`210`		`-INNER JOIN tailnet_peersONtailnet_tunnels.dst_id=tailnet_peers.id`
`211`		`-WHEREtailnet_tunnels.src_id= $1`
`212`		`-UNION`
`213`		`-SELECTtailnet_tunnels.src_idas peer_id,tailnet_peers.coordinator_id,tailnet_peers.updated_at,tailnet_peers.node,tailnet_peers.status`
`214`		`-FROM tailnet_tunnels`
`215`		`-INNER JOIN tailnet_peersONtailnet_tunnels.src_id=tailnet_peers.id`
`216`		`-WHEREtailnet_tunnels.dst_id= $1;`
	`208`	`+SELECT idAS peer_id, coordinator_id, updated_at, node, status`
	`209`	`+FROM tailnet_peers`
	`210`	`+WHERE idIN (`
	`211`	`+SELECT dst_idas peer_id`
	`212`	`+FROM tailnet_tunnels`
	`213`	`+WHEREtailnet_tunnels.src_id= $1`
	`214`	`+UNION`
	`215`	`+SELECT src_idas peer_id`
	`216`	`+FROM tailnet_tunnels`
	`217`	`+WHEREtailnet_tunnels.dst_id= $1`
	`218`	`+);`
`217`	`219`
`218`	`220`	`-- For PG Coordinator HTMLDebug`
`219`	`221`

Comments

(0)