From db8e26b60e1d3a81580d21f90ea46d63e3c50fb8 Mon Sep 17 00:00:00 2001
From: tobi <tobi.smethurst@protonmail.com>
Date: Wed, 15 Oct 2025 10:52:21 +0200
Subject: [chore/performance] Use CTE for list select statuses query (#4501)

# Description

> If this is a code change, please include a summary of what you've coded, and link to the issue(s) it closes/implements.
>
> If this is a documentation change, please briefly describe what you've changed and why.

This PR changes the list select query to use (potentially cached) account IDs provided in a CTE, rather than using a subquery that joins on the follows table. This should be a little faster!

## Checklist

Please put an x inside each checkbox to indicate that you've read and followed it: `[ ]` -> `[x]`

If this is a documentation change, only the first checkbox must be filled (you can delete the others if you want).

- [x] I/we have read the [GoToSocial contribution guidelines](https://codeberg.org/superseriousbusiness/gotosocial/src/branch/main/CONTRIBUTING.md).
- [x] I/we have discussed the proposed changes already, either in an issue on the repository, or in the Matrix chat.
- [x] I/we have not leveraged AI to create the proposed changes.
- [x] I/we have performed a self-review of added code.
- [x] I/we have written code that is legible and maintainable by others.
- [x] I/we have commented the added code, particularly in hard-to-understand areas.
- [x] I/we have made any necessary changes to documentation.
- [ ] I/we have added tests that cover new code.
- [x] I/we have run tests and they pass locally with the changes.
- [x] I/we have run `go fmt ./...` and `golangci-lint run`.

Reviewed-on: https://codeberg.org/superseriousbusiness/gotosocial/pulls/4501
Co-authored-by: tobi <tobi.smethurst@protonmail.com>
Co-committed-by: tobi <tobi.smethurst@protonmail.com>
---
 internal/db/bundb/timeline.go | 28 ++++++++++++++++------------
 internal/db/bundb/util.go     |  6 ++++++
 2 files changed, 22 insertions(+), 12 deletions(-)

(limited to 'internal/db')

diff --git a/internal/db/bundb/timeline.go b/internal/db/bundb/timeline.go
index 37144429e..3b217cc5c 100644
--- a/internal/db/bundb/timeline.go
+++ b/internal/db/bundb/timeline.go
@@ -252,24 +252,28 @@ func (t *timelineDB) GetListTimeline(ctx context.Context, listID string, page *p
 		// of any paging parameters, it selects by list entries.
 		func(q *bun.SelectQuery) (*bun.SelectQuery, error) {
 
-			// Fetch all follow IDs contained in list from DB.
-			followIDs, err := t.state.DB.GetFollowIDsInList(
+			// Get IDs of all accounts in the list.
+			accountIDs, err := t.state.DB.GetAccountIDsInList(
 				ctx, listID, nil,
 			)
 			if err != nil {
-				return nil, gtserror.Newf("error getting follows in list: %w", err)
+				return nil, gtserror.Newf("error getting account IDs in list: %w", err)
 			}
 
-			// Select target account
-			// IDs from list follows.
-			subQ := t.db.NewSelect().
-				Table("follows").
-				Column("follows.target_account_id").
-				Where("? IN (?)", bun.Ident("follows.id"), bun.In(followIDs))
-			q = q.Where("? IN (?)", bun.Ident("statuses.account_id"), subQ)
+			// Provide account IDs as common table expression values.
+			values := make([]accountIDValue, 0, len(accountIDs))
+			for _, accountID := range accountIDs {
+				values = append(values, accountIDValue{accountID})
+			}
 
-			// Only include statuses that aren't pending approval.
-			q = q.Where("NOT ? = ?", bun.Ident("pending_approval"), true)
+			// "Join" on the CTE values to select only
+			// statuses belonging to those account IDs.
+			q = q.
+				With("_data", t.db.NewValues(&values)).
+				Table("_data").
+				Where("? = ?", bun.Ident("statuses.account_id"), bun.Ident("_data.account_id")).
+				// Only include statuses that aren't pending approval.
+				Where("NOT ? = ?", bun.Ident("pending_approval"), true)
 
 			return q, nil
 		},
diff --git a/internal/db/bundb/util.go b/internal/db/bundb/util.go
index d0d25a236..39849ba73 100644
--- a/internal/db/bundb/util.go
+++ b/internal/db/bundb/util.go
@@ -240,3 +240,9 @@ func whereArrayIsNullOrEmpty(query *bun.SelectQuery, subject interface{}) *bun.S
 			WhereOr(arrayEmptySQL, subject)
 	})
 }
+
+// accountIDValue is a convenience struct for using
+// CTE's to provide accountIDs to select statuses of.
+type accountIDValue struct {
+	AccountID string `bun:"type:CHAR(26)"`
+}
-- 
cgit v1.2.3