Re: How to force subquery scan?

...
>
> Does this do better:
>
> SELECT
> prodid,
> avg(prodcost) as average_cost,
> sum(prodcost * salesamount) as cost_total
> FROM (
> SELECT
> p.product_id as prodid,
> product_cost(s.product_id, s.date) as prodcost,
> s.amount as salesamount
> FROM products p
> LEFT JOIN sales s ON s.date between '2004-01-01' and '2005-01-01'
> ) q
> GROUP BY prodid;
>

Sorry, couldn't test it as my query wasn't actual query. I just made up
something for the list. But I made some tests with similar queries and
your solution doesn't help. Subquery scan is not forced, as your query
can be easily translated into single flat query returning the same
results.

It seems that subquery scan is only used, when the query can not be
translated into single flat query. Which is mostly good, I assume. The
problem is, that if SELECT list of subquery contains function call, and
the result of this function is used in multiple places in outer query,
then the function is invoked multiple times (per row). This can get
expensive with slow function and big queries.

Tambet