May 18, 2016 19:44
Sometimes, beeing too specific can be risky, since the search could return zero or little results.
Better search for a wide query and use ranking to sort the results.
Example:
SELECT title,ts_rank_cd(fts, to_tsquery('english','x-ray & russian')) AS rank
FROM apod WHERE fts @@ to_tsquery('english','x-ray&russian') ORDER BY rank
DESC LIMIT 5;
title | rank
----------------------------------------+-----------
The High Energy Heart Of The Milky Way | 0.0240938
(1 row)
SELECT title,ts_rank_cd(fts, to_tsquery('english','x-ray & russian')) AS rank
FROM apod WHERE fts @@ to_tsquery('english','x-ray') ORDER BY rank DESC LIMIT 5;
title | rank
----------------------------------------+-----------
The High Energy Heart Of The Milky Way | 0.0240938
X-Ray Jet From Centaurus A | 0
Barnacle Bill And Sojourner | 0
The Crab Nebula in X-Rays | 0
M27: The Dumbbell Nebula | 0
(5 rows)
fts,
pg,
pgen