Tambet Matiisen wrote:
>
> Another (possibly design?) problem of mine. I have a function
> product_cost(product_id, date), which does simple SELECT call. I
> declared it as STABLE, hoping that multiple invocations of the same
> function are replaced with one. Query is something like this:
>
> SELECT
> p.product_id,
> avg(product_cost(s.product_id, s.date)) as average_cost,
> sum(product_cost(s.product_id, s.date) * s.amount) as cost_total
> FROM products p
> LEFT JOIN sales s ON s.date between '2004-01-01' and '2005-01-01'
> GROUP BY p.product_id;
>
> (For those interested in the intent of the query - imagine there is
> internal cost associated with a product, which is different in different
> periods. There is no cost column in sales table, because it might
> change, for previous periods too).
>
> When I ran the query for long periods I observed that my assumption
> about STABLE was wrong. It did not help to reduce function invocations,
> as one could think after reading the documentation. It was also
> confirmed in mailing lists, that STABLE only allows function to be used
> in index scan, there is no function result cacheing.
>
> I was able to reduce function calls to just one per row by using
> subquery:
>
> SELECT
> p.product_id,
> avg(s.cost) as average_cost,
> sum(s.cost * s.amount) as cost_total
> FROM products p
> LEFT JOIN (SELECT *, product_cost(product_id, date) as cost FROM sales)
> s ON s.date between '2004-01-01' and '2005-01-01'
> GROUP BY p.product_id;
>
> But it did work only as long I used LEFT JOIN. When I used regular JOIN,
> the optimizer happily optimized subquery scan to just table scan and
> elevated the function call to next query level, where it was executed
> twice. My question is, is there a trick that would force subquery scan
> when I want it?
>
> Tambet
>
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;
Regards, Christoph
