From: Tambet Matiisen <t.matiisen@aprote.ee>
To: pgsql-sql@postgresql.org
Subject: How to force subquery scan?
Date: Tue, 15 Mar 2005 12:08:52 +0200
Message-ID: <A66A11DBF5525341AEF6B8DE39CDE770088053@black.aprote.com> (raw)
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
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: t.matiisen@aprote.ee
Subject: Re: How to force subquery scan?
In-Reply-To: <A66A11DBF5525341AEF6B8DE39CDE770088053@black.aprote.com>
* 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