diff options
| -rw-r--r-- | internal/db/bundb/migrations/20220830014732_regenerate_indexes_for_follows.go | 114 | ||||
| -rw-r--r-- | internal/db/bundb/relationship.go | 9 |
2 files changed, 120 insertions, 3 deletions
diff --git a/internal/db/bundb/migrations/20220830014732_regenerate_indexes_for_follows.go b/internal/db/bundb/migrations/20220830014732_regenerate_indexes_for_follows.go new file mode 100644 index 000000000..259d757f0 --- /dev/null +++ b/internal/db/bundb/migrations/20220830014732_regenerate_indexes_for_follows.go @@ -0,0 +1,114 @@ +/* + GoToSocial + Copyright (C) 2021-2022 GoToSocial Authors admin@gotosocial.org + + This program is free software: you can redistribute it and/or modify + it under the terms of the GNU Affero General Public License as published by + the Free Software Foundation, either version 3 of the License, or + (at your option) any later version. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + GNU Affero General Public License for more details. + + You should have received a copy of the GNU Affero General Public License + along with this program. If not, see <http://www.gnu.org/licenses/>. +*/ + +package migrations + +import ( + "context" + + "github.com/superseriousbusiness/gotosocial/internal/gtsmodel" + "github.com/uptrace/bun" +) + +func init() { + up := func(ctx context.Context, db *bun.DB) error { + return db.RunInTx(ctx, nil, func(ctx context.Context, tx bun.Tx) error { + // drop indexes for these tables + if _, err := tx. + NewDropIndex(). + Index("follow_requests_account_id_idx"). + Exec(ctx); err != nil { + return err + } + + if _, err := tx. + NewDropIndex(). + Index("follow_requests_target_account_id_idx"). + Exec(ctx); err != nil { + return err + } + + if _, err := tx. + NewDropIndex(). + Index("follows_account_id_idx"). + Exec(ctx); err != nil { + return err + } + + if _, err := tx. + NewDropIndex(). + Index("follows_target_account_id_idx"). + Exec(ctx); err != nil { + return err + } + + // now re-create them with an ordering + if _, err := tx. + NewCreateIndex(). + Model(>smodel.FollowRequest{}). + Index("follow_requests_account_id_idx"). + Column("account_id"). + ColumnExpr("updated_at DESC"). + Exec(ctx); err != nil { + return err + } + + if _, err := tx. + NewCreateIndex(). + Model(>smodel.FollowRequest{}). + Index("follow_requests_target_account_id_idx"). + Column("target_account_id"). + ColumnExpr("updated_at DESC"). + Exec(ctx); err != nil { + return err + } + + if _, err := tx. + NewCreateIndex(). + Model(>smodel.Follow{}). + Index("follows_account_id_idx"). + Column("account_id"). + ColumnExpr("updated_at DESC"). + Exec(ctx); err != nil { + return err + } + + if _, err := tx. + NewCreateIndex(). + Model(>smodel.Follow{}). + Index("follows_target_account_id_idx"). + Column("target_account_id"). + ColumnExpr("updated_at DESC"). + Exec(ctx); err != nil { + return err + } + + return nil + }) + } + + down := func(ctx context.Context, db *bun.DB) error { + return db.RunInTx(ctx, nil, func(ctx context.Context, tx bun.Tx) error { + return nil + }) + } + + if err := Migrations.Register(up, down); err != nil { + panic(err) + } +} diff --git a/internal/db/bundb/relationship.go b/internal/db/bundb/relationship.go index 60dd72663..ba72a053a 100644 --- a/internal/db/bundb/relationship.go +++ b/internal/db/bundb/relationship.go @@ -292,7 +292,8 @@ func (r *relationshipDB) GetAccountFollowRequests(ctx context.Context, accountID followRequests := []*gtsmodel.FollowRequest{} q := r.newFollowQ(&followRequests). - Where("target_account_id = ?", accountID) + Where("target_account_id = ?", accountID). + Order("follow_request.updated_at DESC") if err := q.Scan(ctx); err != nil { return nil, r.conn.ProcessError(err) @@ -304,7 +305,8 @@ func (r *relationshipDB) GetAccountFollows(ctx context.Context, accountID string follows := []*gtsmodel.Follow{} q := r.newFollowQ(&follows). - Where("account_id = ?", accountID) + Where("account_id = ?", accountID). + Order("follow.updated_at DESC") if err := q.Scan(ctx); err != nil { return nil, r.conn.ProcessError(err) @@ -325,7 +327,8 @@ func (r *relationshipDB) GetAccountFollowedBy(ctx context.Context, accountID str q := r.conn. NewSelect(). - Model(&follows) + Model(&follows). + Order("follow.updated_at DESC") if localOnly { q = q.ColumnExpr("follow.*"). |