Hacker News new | ask | show | jobs
by YorickPeterse 3752 days ago
> I'm not sure that "full text search works best when the text vectors are stored in physical columns with an index" is true

It's mostly based on my past experiences. At my previous gig we indexed quite a bit of data using PostgreSQL's full text search system and we noticed significantly improved performance when using physical columns containing text vectors over just using GIN indexes. It's been a while so the details are a bit fuzzy.
1 comments
alexpeattie 3752 days ago
Ah ok, interesting :). We were weighing up exactly these kind of tradeoffs a few weeks ago, and the benchmarks we did put the 2 approaches more or less neck and neck, so maybe PG has managed to optimize things a bit? Or, our search is over hundreds of thousands of rows, so maybe things only slow down in the millions and above...
link
YorickPeterse 3752 days ago
If I remember correctly the tables we ran full text search on didn't have millions of rows, and we used PostgreSQL's simple/basic language (whichever one of the two it was). We did also run a similarity filter (using the pg_trgm similarity() function), maybe we also had trigram indexes on top. I can't really remember.
link