HiÐΞをご覧ください。（投げ銭もお待ちしております……） https://hide.ac/articles/DjGNSvW4G
ノート全文検索、ユーザー検索をPGroongaでインデックスするようにします。
ILIKEの代わりに&@~を使用するため、AND/OR検索といったクエリー構文を利用できます。
Last active
December 12, 2023 09:34
-
-
Save tamaina/29e88e02d0dc5acfa2f50b3347d3d20d to your computer and use it in GitHub Desktop.
Misske PGroonga Patch (for a29ff7b1fa45e01bc7c097e8d770a4c4eedf2a8f)
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| diff --git a/packages/backend/migration/1652210810723-PGroonga.js b/packages/backend/migration/1652210810723-PGroonga.js | |
| new file mode 100644 | |
| index 000000000..bd3fee34e | |
| --- /dev/null | |
| +++ b/packages/backend/migration/1652210810723-PGroonga.js | |
| @@ -0,0 +1,11 @@ | |
| +export class PGroonga1652210810723 { | |
| + name = 'PGroonga1652210810723' | |
| + | |
| + async up(queryRunner) { | |
| + await queryRunner.query(`CREATE INDEX "IDX_f27f5d88941e57442be75ba9c8" ON "note" USING "pgroonga" ("text")`); | |
| + } | |
| + | |
| + async down(queryRunner) { | |
| + await queryRunner.query(`DROP INDEX "public"."IDX_f27f5d88941e57442be75ba9c8"`); | |
| + } | |
| +} | |
| diff --git a/packages/backend/migration/1652213168020-PGroongaUserName.js b/packages/backend/migration/1652213168020-PGroongaUserName.js | |
| new file mode 100644 | |
| index 000000000..9e1e75ece | |
| --- /dev/null | |
| +++ b/packages/backend/migration/1652213168020-PGroongaUserName.js | |
| @@ -0,0 +1,11 @@ | |
| +export class PGroongaUserName1652213168020 { | |
| + name = 'PGroongaUserName1652213168020' | |
| + | |
| + async up(queryRunner) { | |
| + await queryRunner.query(`CREATE INDEX "IDX_065d4d8f3b5adb4a08841eae3c" ON "user" USING "pgroonga" ("name" pgroonga_varchar_full_text_search_ops_v2)`); | |
| + } | |
| + | |
| + async down(queryRunner) { | |
| + await queryRunner.query(`DROP INDEX "public"."IDX_065d4d8f3b5adb4a08841eae3c"`); | |
| + } | |
| +} | |
| diff --git a/packages/backend/migration/1652213556290-PGroongaUserDescription.js b/packages/backend/migration/1652213556290-PGroongaUserDescription.js | |
| new file mode 100644 | |
| index 000000000..7216438ab | |
| --- /dev/null | |
| +++ b/packages/backend/migration/1652213556290-PGroongaUserDescription.js | |
| @@ -0,0 +1,11 @@ | |
| +export class PGroongaUserDescription1652213556290 { | |
| + name = 'PGroongaUserDescription1652213556290' | |
| + | |
| + async up(queryRunner) { | |
| + await queryRunner.query(`CREATE INDEX "IDX_fcb770976ff8240af5799e3ffc" ON "user_profile" USING "pgroonga" ("description" pgroonga_varchar_full_text_search_ops_v2) `); | |
| + } | |
| + | |
| + async down(queryRunner) { | |
| + await queryRunner.query(`DROP INDEX "public"."IDX_fcb770976ff8240af5799e3ffc"`); | |
| + } | |
| +} | |
| diff --git a/packages/backend/src/models/entities/note.ts b/packages/backend/src/models/entities/note.ts | |
| index 0ffeb85f6..4c28e5936 100644 | |
| --- a/packages/backend/src/models/entities/note.ts | |
| +++ b/packages/backend/src/models/entities/note.ts | |
| @@ -53,6 +53,7 @@ export class Note { | |
| }) | |
| public threadId: string | null; | |
| + @Index() // USING pgroonga | |
| @Column('text', { | |
| nullable: true, | |
| }) | |
| diff --git a/packages/backend/src/models/entities/user-profile.ts b/packages/backend/src/models/entities/user-profile.ts | |
| index 1778742ea..748ab38b4 100644 | |
| --- a/packages/backend/src/models/entities/user-profile.ts | |
| +++ b/packages/backend/src/models/entities/user-profile.ts | |
| @@ -29,6 +29,7 @@ export class UserProfile { | |
| }) | |
| public birthday: string | null; | |
| + @Index() // USING pgroonga pgroonga_varchar_full_text_search_ops_v2 | |
| @Column('varchar', { | |
| length: 2048, nullable: true, | |
| comment: 'The description (bio) of the User.', | |
| diff --git a/packages/backend/src/models/entities/user.ts b/packages/backend/src/models/entities/user.ts | |
| index df92fb825..ce0d6c9ed 100644 | |
| --- a/packages/backend/src/models/entities/user.ts | |
| +++ b/packages/backend/src/models/entities/user.ts | |
| @@ -50,6 +50,7 @@ export class User { | |
| }) | |
| public usernameLower: string; | |
| + @Index() // USING pgroonga pgroonga_varchar_full_text_search_ops_v2 | |
| @Column('varchar', { | |
| length: 128, nullable: true, | |
| comment: 'The name of the User.', | |
| diff --git a/packages/backend/src/server/api/endpoints/notes/search.ts b/packages/backend/src/server/api/endpoints/notes/search.ts | |
| index af9b5f0a1..8910b9946 100644 | |
| --- a/packages/backend/src/server/api/endpoints/notes/search.ts | |
| +++ b/packages/backend/src/server/api/endpoints/notes/search.ts | |
| @@ -7,6 +7,7 @@ import { makePaginationQuery } from '../../common/make-pagination-query.js'; | |
| import { generateVisibilityQuery } from '../../common/generate-visibility-query.js'; | |
| import { generateMutedUserQuery } from '../../common/generate-muted-user-query.js'; | |
| import { generateBlockedUserQuery } from '../../common/generate-block-query.js'; | |
| +import { ApiError } from '../../error.js'; | |
| export const meta = { | |
| tags: ['notes'], | |
| @@ -24,6 +25,11 @@ export const meta = { | |
| }, | |
| errors: { | |
| + noSuchNote: { | |
| + message: 'Query is empty.', | |
| + code: 'QUERY_IS_EMPTY', | |
| + id: 'd0410b51-f409-4667-8118-cfe999e453c3', | |
| + }, | |
| }, | |
| } as const; | |
| @@ -48,6 +54,8 @@ export const paramDef = { | |
| // eslint-disable-next-line import/no-default-export | |
| export default define(meta, paramDef, async (ps, me) => { | |
| + if (ps.query.trim().length === 0) throw new ApiError(meta.errors.noSuchNote); | |
| + | |
| if (es == null) { | |
| const query = makePaginationQuery(Notes.createQueryBuilder('note'), ps.sinceId, ps.untilId); | |
| @@ -58,7 +66,7 @@ export default define(meta, paramDef, async (ps, me) => { | |
| } | |
| query | |
| - .andWhere('note.text ILIKE :q', { q: `%${ps.query}%` }) | |
| + .andWhere('note.text &@~ :q', { q: ps.query }) | |
| .innerJoinAndSelect('note.user', 'user') | |
| .leftJoinAndSelect('user.avatar', 'avatar') | |
| .leftJoinAndSelect('user.banner', 'banner') | |
| diff --git a/packages/backend/src/server/api/endpoints/users/search.ts b/packages/backend/src/server/api/endpoints/users/search.ts | |
| index a72a58a84..bdaa16ef4 100644 | |
| --- a/packages/backend/src/server/api/endpoints/users/search.ts | |
| +++ b/packages/backend/src/server/api/endpoints/users/search.ts | |
| @@ -61,7 +61,14 @@ export default define(meta, paramDef, async (ps, me) => { | |
| .getMany(); | |
| } else { | |
| const nameQuery = Users.createQueryBuilder('user') | |
| - .where('user.name ILIKE :query', { query: '%' + ps.query + '%' }) | |
| + .where(new Brackets(qb => { | |
| + qb.where('user.name &@~ :query', { query: ps.query }); | |
| + | |
| + // Also search username if it qualifies as username | |
| + if (Users.validateLocalUsername(ps.query)) { | |
| + qb.orWhere('user.usernameLower LIKE :username', { username: ps.query.toLowerCase() + '%' }); | |
| + } | |
| + })) | |
| .andWhere(new Brackets(qb => { qb | |
| .where('user.updatedAt IS NULL') | |
| .orWhere('user.updatedAt > :activeThreshold', { activeThreshold: activeThreshold }); | |
| @@ -83,7 +90,7 @@ export default define(meta, paramDef, async (ps, me) => { | |
| if (users.length < ps.limit) { | |
| const profQuery = UserProfiles.createQueryBuilder('prof') | |
| .select('prof.userId') | |
| - .where('prof.description ILIKE :query', { query: '%' + ps.query + '%' }); | |
| + .where('prof.description &@~ :query', { query: ps.query }); | |
| if (ps.origin === 'local') { | |
| profQuery.andWhere('prof.userHost IS NULL'); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment