HiÐΞをご覧ください。(投げ銭もお待ちしております……) https://hide.ac/articles/DjGNSvW4G
ノート全文検索、ユーザー検索をPGroongaでインデックスするようにします。
ILIKEの代わりに&@~
を使用するため、AND/OR検索といったクエリー構文を利用できます。
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'); |
HiÐΞをご覧ください。(投げ銭もお待ちしております……) https://hide.ac/articles/DjGNSvW4G
ノート全文検索、ユーザー検索をPGroongaでインデックスするようにします。
ILIKEの代わりに&@~
を使用するため、AND/OR検索といったクエリー構文を利用できます。