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
