... > > 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 ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match