Re:PostgreSQL Full-Text Search: Fast When Done Right (Debunk...
by ahoka on Wednesday April 09, 2025 @08:42AM
(#43631413)
I had the same question when reading the article, why not just index the expression?
Re:PostgreSQL Full-Text Search: Fast When Done Right (Debunk...
by sgarland on Wednesday April 09, 2025 @09:30AM
(#43631853)
You are correct, I missed that. In MySQL, functional indices are implemented as invisible generated virtual columns (and there is no vector index type supported yet that I'm aware of), but Postgres has a more capable approach.
Re:PostgreSQL Full-Text Search: Fast When Done Right (Debunk...
by charettes on Wednesday April 09, 2025 @03:17PM
(#43636459)
TIL I wasn't aware MySQL functional indices were implemented using virtual columns [0]
[0] https://dev.mysql.com/doc/refman/8.4/en/create-index.html#cr...
I'm not sure why the author doesn't use them but it's clearly pointed out in the documentation (https://www.postgresql.org/docs/current/textsearch-tables.ht...).
In other words, I believe they didn't need a `message_tsvector` column and creating an index of the form
would have allowed queries of the form to use the `idx_gin_logs_message_tsvector` index without materializing `to_tsvector('english', message)` on disk outside of the index.Here's a fiddle supporting it https://dbfiddle.uk/aSFjXJWz