Forum OpenACS Q&A: Postgres syntax requires me to run a function repeatedly?

Collapse
1: Postgres syntax requires me to run a function repeatedly?
Posted by Cathy Sarisky on 11/02/01 03:59 AM
Hello,

If you are SELECTing a function and then also using it in a WHERE statement, is it possible to avoid having to calculate the result of the function twice?

I'm building off of the bboard search function in openACS 3.2.5 to do some searching of a hacked-together-module to archive experiments. (www.labarchive.net/labdb/search). I'm using the rank_for_search function defined in rank_for_search.sql. I want to only return hits with a rating greater than 50% (or whatever). The present code does a select with the rank_for_search function, and then also has a WHERE statement further down with the same rank_for_search function. It seems to me that this means the rank score is being calculated twice. I tried selecting rank_for_search(args) AS rank and then doing a WHERE rank > 50, but that gives an error message. (Attribute 'rank' not found.)

Can some postgres guru give me some advice? It seems inefficient to be calculating the score twice for each entry. Is there a better way to do this?

Thanks in advance,

Collapse
2: Response to Postgres syntax requires me to run a function repeatedly? (response to 1)
Posted by Don Baccus on 11/02/01 05:09 AM
Check out "iscacheable" ("iscachable"???) in the PG documentation.  This avoids the problem.  Ideally PL/pgSQL would recognize invariants when compiling and set this for you but it's not likely to happen for a long time ... meanwhile you're free to give PG hints.
Collapse
3: Response to Postgres syntax requires me to run a function repeatedly? (response to 1)
Posted by Jonathan Ellis on 11/02/01 05:26 PM
Cool... that's good to know.
Collapse
4: Response to Postgres syntax requires me to run a function repeatedly? (response to 1)
Posted by David Walker on 11/02/01 06:14 PM
The following syntax does work. I suspect it may be faster in some cases and slower in others depending on how expensive the function is.

select * from 
(select *, rank_for_search('david'::varchar,first_names) as rank 
from users) qry_alias 
where rank > 50
order by rank desc

IsCacheable (However it is spelled) is certainly the way to go with the rank_for_search function. The same arguments passed in will always produce the same result.
Collapse
5: Response to Postgres syntax requires me to run a function repeatedly? (response to 1)
Posted by Don Baccus on 11/02/01 06:28 PM
In fact the PG folks are thinking about providing three levels of granularity:  always invariant (i.e. "sqrt(2)"), invariant for a given transaction ("rank_for_search"), never invariant (the default case, of course).
Collapse
6: Thank you! (response to 1)
Posted by Cathy Sarisky on 11/02/01 08:07 PM
Thank you Don and David!  I'm looking forward to getting home today so that I can try your suggestions!
Collapse
7: Response to Postgres syntax requires me to run a function repeatedly? (response to 1)
Posted by Stephen . on 11/03/01 02:47 AM
You can also: 
select a, func(b)
from table
order by 1, 2
At least you can in Oracle...
Collapse
8: Response to Postgres syntax requires me to run a function repeatedly? (response to 1)
Posted by Don Baccus on 11/03/01 03:22 AM
Yes, you can in PG too, and in fact the query in question already does so:
<blockquote><pre>
        select rank_for_search('$final_query_string',
                              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
</pre></blockquote>
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.
<p>
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!