Add trigram index for usable note search

ignaloidas commented

2026-01-02 19:16:34 +00:00

Contributor

Searching notes kinda sucks, I almost never get it to succeed on rarely used terms that would require going through a lot of note history. Adding a trigram index like CREATE INDEX test_trgm_idx ON note USING GIN (text gin_trgm_ops); has massively improved the speed without needing to rewrite the search query for full-text search, but I'm not sure if/how to add it to the ORM, and what to do about the pg_trgm extension requirement.

Searching notes kinda sucks, I almost never get it to succeed on rarely used terms that would require going through a lot of note history. Adding a trigram index like `CREATE INDEX test_trgm_idx ON note USING GIN (text gin_trgm_ops);` has massively improved the speed without needing to rewrite the search query for full-text search, but I'm not sure if/how to add it to the ORM, and what to do about the `pg_trgm` extension requirement.

Johann150 commented

2026-01-13 14:12:49 +00:00

Owner

Improving the search speed was the intention behind adding the ability to restrict which users' notes are searched. And personally I haven't had any trouble finding notes with the search, even though I checked that there is no index on the note.text column in my database. So in my opinion this is not necessary.

As a general suggestion in case you haven't tried that, I would recommend vacuuming (full + analyze) and while you're at it using pg_repack (even if the latter probably doesn't have anything to do with note text search).

Since as you said, no change to the code is needed, I think it can easily be optional. It could be added as an optional thingy in docs/install.md, which also resolves the question about what to do with the extension requirement you mentioned.

Improving the search speed was the intention behind adding the ability to restrict which users' notes are searched. And personally I haven't had any trouble finding notes with the search, even though I checked that there is no index on the `note.text` column in my database. So in my opinion this is not necessary. As a general suggestion in case you haven't tried that, I would recommend vacuuming (full + analyze) and while you're at it using `pg_repack` (even if the latter probably doesn't have anything to do with note text search). Since as you said, no change to the code is needed, I think it can easily be optional. It could be added as an optional thingy in `docs/install.md`, which also resolves the question about what to do with the extension requirement you mentioned.

ignaloidas commented

2026-01-30 09:05:59 +00:00

Author

Contributor

FWIW I often don't know/remember what user's notes I want to search, and without setting a user it times out basically every time (unless the search is so generic that it fills the limit fast enough). Full vacuum + analyze doesn't help that much either.

Will look into adding it into some docs though.

FWIW I often don't know/remember what user's notes I want to search, and without setting a user it times out basically every time (unless the search is so generic that it fills the limit fast enough). Full vacuum + analyze doesn't help that much either. Will look into adding it into some docs though.

Rows
Columns

Add trigram index for usable note search #409