Open haleymt opened 6 years ago
I’d love to figure out a way to only apply the ts_headline
to the results returned instead of to the whole table.
I’m guessing that the problem is that we want the ts_headline
call to only exist directly in the SELECT
expression, outside of the subquery, correct?
Yep! That's the way my SQL ended up looking. I followed the basic structure of the suggestion in some of the older docs, which was:
SELECT id, ts_headline(body, q), rank
FROM (SELECT id, body, q, ts_rank_cd(ti, q) AS rank
FROM apod, to_tsquery('stars') q
WHERE ti @@ q
ORDER BY rank DESC LIMIT 10) AS foo;
Although in practice it looked more like:
SELECT ts_headline(body, q) AS pg_search_highlight, table.*
FROM table
INNER JOIN (
SELECT id AS pg_search_id, ts_rank(body, q) AS rank
FROM table
WHERE ti @@ q
ORDER BY rank DESC
LIMIT 20
OFFSET 0
) AS pg_search
(That may not be the best way to do it though--I'm a bit rusty on my SQL.)
I think I fixed it with PR #375
Sorry in advance if this is already on your radar! I looked through the open issues but didn't see anything.
I was trying to use
with_pg_search_highlight
recently and was confused by how slow it made my queries. I expectedts_headline
to make the search slower, so I paginated my results, but it didn't seem to make a difference one way or another. Eventually I realized thatts_headline
was being applied to every result, rather than just the page of them. I ended up writing the SQL myself instead of usingpg_search
and the performance improved when usingts_headline
by almost an order of magnitude.My
pg_search
queries looked something like this:Where
search_text_for
was apg_search_scope
(I wasn't usingmultisearch
if it makes a difference).Is there a reason that you apply the highlighting to every row? Or is there a way to paginate that I just wasn't aware of?
Thanks!