gtsocial-umbx

Unnamed repository; edit this file 'description' to name the repository.
Log | Files | Refs | README | LICENSE

commit 429bb770e22cda6e6decdaa68e4b42bee7b6b192
parent c4a08292ee44bc731ff90bad18a3f37e5ee8ef22
Author: tobi <31960611+tsmethurst@users.noreply.github.com>
Date:   Mon, 26 Sep 2022 14:43:19 +0200

[performance] Update indexes that were causing slow db queries (#855)

* add status.url index, use id desc in some indexes

* test account last posted
Diffstat:
Minternal/db/bundb/account_test.go | 6++++++
Ainternal/db/bundb/migrations/20220926115233_indexes.go | 88+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2 files changed, 94 insertions(+), 0 deletions(-)

diff --git a/internal/db/bundb/account_test.go b/internal/db/bundb/account_test.go @@ -139,6 +139,12 @@ func (suite *AccountTestSuite) TestUpdateAccount() { suite.WithinDuration(time.Now(), noCache.UpdatedAt, 5*time.Second) } +func (suite *AccountTestSuite) TestGetAccountLastPosted() { + lastPosted, err := suite.db.GetAccountLastPosted(context.Background(), suite.testAccounts["local_account_1"].ID) + suite.NoError(err) + suite.EqualValues(1653046675, lastPosted.Unix()) +} + func (suite *AccountTestSuite) TestInsertAccountWithDefaults() { key, err := rsa.GenerateKey(rand.Reader, 2048) suite.NoError(err) diff --git a/internal/db/bundb/migrations/20220926115233_indexes.go b/internal/db/bundb/migrations/20220926115233_indexes.go @@ -0,0 +1,88 @@ +/* + 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 { + // index statuses on url to make searching them faster + if _, err := tx. + NewCreateIndex(). + Model(&gtsmodel.Status{}). + Index("statuses_url_idx"). + Column("url"). + Exec(ctx); err != nil { + return err + } + + // recreate account status index to allow id desc + if _, err := tx. + NewDropIndex(). + Index("statuses_account_id_id_idx"). + Exec(ctx); err != nil { + return err + } + if _, err := tx. + NewCreateIndex(). + Model(&gtsmodel.Status{}). + Index("statuses_account_id_id_idx"). + Column("account_id"). + ColumnExpr("id DESC"). + Exec(ctx); err != nil { + return err + } + + // recreate statuses public timeline index to allow id desc + if _, err := tx. + NewDropIndex(). + Index("statuses_public_timeline_idx"). + Exec(ctx); err != nil { + return err + } + if _, err := tx. + NewCreateIndex(). + Model(&gtsmodel.Status{}). + Index("statuses_public_timeline_idx"). + Column("visibility"). + ColumnExpr("id 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) + } +}