Message-ID: <4236D412.695425E6@rodos.fzk.de>
Date: Tue, 15 Mar 2005 13:24:50 +0100
From: Christoph Haller <ch@rodos.fzk.de>
MIME-Version: 1.0
To: Tambet Matiisen <t.matiisen@aprote.ee>
Cc: pgsql-sql@postgresql.org
Subject: Re: How to force subquery scan?
References: <A66A11DBF5525341AEF6B8DE39CDE770088053@black.aprote.com>
Content-Type: text/plain; charset=iso-8859-2
Content-Transfer-Encoding: 7bit

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