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@xxxxxxxxxxxxxxxxxx
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
