Re: Unnecessary function calls

SELECT id, get_category_text_path(id)
FROM category
WHERE id IN (
   SELECT c.id
   FROM category AS c
   ORDER BY c.rank
   LIMIT 5
)

Terry Fielder
terry@greatgulfhomes.com
Associate Director Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
Fax: (416) 441-9085



Markus Schiltknecht wrote:
> Hi,
>
> when using LIMIT, how do I tell the planner to only call a function for
> rows it returns?
>
> An example: I want to fetch the top five categories. A function
> get_category_text_path(cat_id int) returns the textual representation of
> the category. For that I do something like:
>
> SELECT id, get_category_text_path(id)
>    FROM category
>    ORDER BY rank
>    LIMIT 5
>
> Unfortunately this takes very long because it calls
> get_category_text_path() for all of the 450'000 categories in the table.
> But I only need the full text path of the top five rows.
>
> It does not matter if I declare the function to be IMMUTABLE, STABLE or
> VOLATILE - it gets called for every row in category (which normally is
> what you want I guess).
>
> How can I rewrite the query to call get_category_text_path() only for
> the top five rows?
>
> Thanks for hints.
>
> Markus
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
>
>