News for hackers. Stuff that matters.
Re:PostgreSQL Full-Text Search: Fast When Done Right (Debunk... by charettes on Wednesday April 09, 2025 @01:19AM (#43629106)
Virtual generated columns are not required to allow an index to be used in this case without incurring the cost of materializing `to_tsvector('english', message)`. Postgres supports indexing expressions and the query planner is smart enough to identify candidate on exact matches.

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

  CREATE INDEX idx_gin_logs_message_tsvector
  ON benchmark_logs USING GIN (to_tsvector('english', message))
  WITH (fastupdate = off);
would have allowed queries of the form

  WHERE to_tsvector('english', message) @@ to_tsquery('english', 'research')
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

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...