From: Christoph Haller <ch@rodos.fzk.de>
To: Tambet Matiisen <t.matiisen@aprote.ee>
Cc: pgsql-sql@postgresql.org
Subject: Re: How to force subquery scan?
Date: Tue, 15 Mar 2005 13:24:50 +0100
Message-ID: <4236D412.695425E6@rodos.fzk.de> (raw)
References: <A66A11DBF5525341AEF6B8DE39CDE770088053@black.aprote.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ch@rodos.fzk.de, t.matiisen@aprote.ee
Subject: Re: How to force subquery scan?
In-Reply-To: <4236D412.695425E6@rodos.fzk.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox