Forum OpenACS Q&A: Response to Postgres syntax requires me to run a function repeatedly?
email || first_names || last_name || one_line || message::text) as the_score,
case when bboard.refers_to is null then 1 else 0 end,
bboard.*, users.first_names || ' ' || users.last_name as name, users.email, bboard_topics.presentation_type, bboard_topics.topic
from bboard, users, bboard_topics
where rank_for_search('$final_query_string', email || first_names || last_name || one_line || message::text) > 0
and bboard.user_id = users.user_id
and (active_p = 't' or active_p is null)
and (bboard_topics.moderation_policy is null or bboard_topics.moderation_policy != 'private')
and bboard_topics.topic_id = bboard.topic_id
order by 1 desc, 2 desc
but it is called in the WHERE clause. As you can see this is a horrible query no matter how you look at it as it does a sequential scan on the entire table. Well, not in this case which is searching a single topic (forum), but it does in the "search all forums" variant.
But at least it only calls the rank_for_search function twice rather than three times per returned row, and of course only once for each rejected row!