[performance] Optimize local timeline + local status count queries (#3892)

* [performance] Optimize local timeline + local status count queries

* remove if not exists from create view

1 files changed, 33 insertions, 25 deletions

diff --git a/internal/db/bundb/instance.go b/internal/db/bundb/instance.go
index 7852ae52e..154c1d34e 100644
--- a/internal/db/bundb/instance.go
+++ b/internal/db/bundb/instance.go
@@ -77,44 +77,52 @@ func (i *instanceDB) CountInstanceUsers(ctx context.Context, domain string) (int
 }
 
 func (i *instanceDB) CountInstanceStatuses(ctx context.Context, domain string) (int, error) {
-	localhost := (domain == config.GetHost() || domain == config.GetAccountDomain())
+	local := (domain == config.GetHost() || domain == config.GetAccountDomain())
 
-	if localhost {
-		// Check for a cached instance statuses count, if so return this.
-		if n := i.state.Caches.DB.LocalInstance.Statuses.Load(); n != nil {
-			return *n, nil
-		}
+	if local {
+		return i.countLocalStatuses(ctx)
 	}
 
 	q := i.db.
 		NewSelect().
-		TableExpr("? AS ?", bun.Ident("statuses"), bun.Ident("status"))
+		TableExpr("? AS ?", bun.Ident("statuses"), bun.Ident("status")).
+		// Join on the domain of the account.
+		Join(
+			"JOIN ? AS ? ON ? = ?",
+			bun.Ident("accounts"), bun.Ident("account"),
+			bun.Ident("account.id"), bun.Ident("status.account_id"),
+		).
+		Where("? = ?", bun.Ident("account.domain"), domain).
+		// Ignore pending approval.
+		Where("? = ?", bun.Ident("status.pending_approval"), false).
+		// Ignore direct messages.
+		Where("NOT ? = ?", bun.Ident("status.visibility"), gtsmodel.VisibilityDirect)
 
-	if localhost {
-		// if the domain is *this* domain, just count where local is true
-		q = q.Where("? = ?", bun.Ident("status.local"), true)
-	} else {
-		// join on the domain of the account
-		q = q.
-			Join("JOIN ? AS ? ON ? = ?", bun.Ident("accounts"), bun.Ident("account"), bun.Ident("account.id"), bun.Ident("status.account_id")).
-			Where("? = ?", bun.Ident("account.domain"), domain)
+	count, err := q.Count(ctx)
+	if err != nil {
+		return 0, err
 	}
 
-	// Ignore statuses that are currently pending approval.
-	q = q.Where("NOT ? = ?", bun.Ident("status.pending_approval"), true)
+	return count, nil
+}
 
-	// Ignore statuses that are direct messages.
-	q = q.Where("NOT ? = ?", bun.Ident("status.visibility"), gtsmodel.VisibilityDirect)
+func (i *instanceDB) countLocalStatuses(ctx context.Context) (int, error) {
+	// Check for a cached instance statuses count, if so return this.
+	if n := i.state.Caches.DB.LocalInstance.Statuses.Load(); n != nil {
+		return *n, nil
+	}
 
-	count, err := q.Count(ctx)
-	if err != nil {
+	// Select from local count view.
+	var count int
+	if err := i.db.
+		NewSelect().
+		Table("statuses_local_count_view").
+		Scan(ctx, &count); err != nil {
 		return 0, err
 	}
 
-	if localhost {
-		// Update cached instance statuses account value.
-		i.state.Caches.DB.LocalInstance.Statuses.Store(&count)
-	}
+	// Update cached instance statuses account value.
+	i.state.Caches.DB.LocalInstance.Statuses.Store(&count)
 
 	return count, nil
 }