gtsocial-umbx

commit d7d660374a45157ac41920bc18077bbcd2b0ee9b
parent 45773a0bf441b6e8a9aa4f5abdef16a7085677e6
Author: tobi <31960611+tsmethurst@users.noreply.github.com>
Date:   Sun,  4 Jun 2023 21:17:28 +0200

[bugfix] Fix slow joined home timeline query (#1867)


Diffstat:
M
internal/db/bundb/timeline.go
 | 
32
+++++++++++++++-----------------

1 file changed, 15 insertions(+), 17 deletions(-)
diff --git a/internal/db/bundb/timeline.go b/internal/db/bundb/timeline.go
@@ -53,15 +53,7 @@ func (t *timelineDB) GetHomeTimeline(ctx context.Context, accountID string, maxI
 		NewSelect().
 		TableExpr("? AS ?", bun.Ident("statuses"), bun.Ident("status")).
 		// Select only IDs from table
-		Column("status.id").
-		// Find out who accountID follows.
-		Join("LEFT JOIN ? AS ? ON ? = ? AND ? = ?",
-			bun.Ident("follows"),
-			bun.Ident("follow"),
-			bun.Ident("follow.target_account_id"),
-			bun.Ident("status.account_id"),
-			bun.Ident("follow.account_id"),
-			accountID)
+		Column("status.id")
 
 	if maxID == "" || maxID >= id.Highest {
 		const future = 24 * time.Hour
@@ -109,15 +101,21 @@ func (t *timelineDB) GetHomeTimeline(ctx context.Context, accountID string, maxI
 		q = q.Order("status.id ASC")
 	}
 
-	// Use a WhereGroup here to specify that we want EITHER statuses posted by accounts that accountID follows,
-	// OR statuses posted by accountID itself (since a user should be able to see their own statuses).
-	//
-	// This is equivalent to something like WHERE ... AND (... OR ...)
-	// See: https://bun.uptrace.dev/guide/queries.html#select
-	q = q.WhereGroup(" AND ", func(*bun.SelectQuery) *bun.SelectQuery {
+	// Subquery to select target (followed) account
+	// IDs from follows owned by given accountID.
+	subQ := t.conn.
+		NewSelect().
+		TableExpr("? AS ?", bun.Ident("follows"), bun.Ident("follow")).
+		Column("follow.target_account_id").
+		Where("? = ?", bun.Ident("follow.account_id"), accountID)
+
+	// Use the subquery in a WhereGroup here to specify that we want EITHER
+	// - statuses posted by accountID itself OR
+	// - statuses posted by accounts that accountID follows
+	q = q.WhereGroup(" AND ", func(q *bun.SelectQuery) *bun.SelectQuery {
 		return q.
-			WhereOr("? = ?", bun.Ident("follow.account_id"), accountID).
-			WhereOr("? = ?", bun.Ident("status.account_id"), accountID)
+			Where("? = ?", bun.Ident("status.account_id"), accountID).
+			WhereOr("? IN (?)", bun.Ident("status.account_id"), subQ)
 	})
 
 	if err := q.Scan(ctx, &statusIDs); err != nil {