Re: Unnecessary function calls

On Tue, May 02, 2006 at 01:37:54PM +0200, 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

How about:

SELECT id, get_category_text_path(id)
FROM (SELECT id FROM category
ORDER BY rank
LIMIT 5) as x;

Evidently you don't have an index on rank, otherwise it would've used
the index to cut down on the number of rows that needed to be examined.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.