X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 5595D5656B for ; Tue, 15 Mar 2005 10:09:04 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 77736-04 for ; Tue, 15 Mar 2005 10:08:55 +0000 (GMT) Received: from ns.aprote.ee (ns.aprote.ee [80.235.78.106]) by svr1.postgresql.org (Postfix) with ESMTP id 5CCAC54EE6 for ; Tue, 15 Mar 2005 10:08:52 +0000 (GMT) Received: Message by Barricade ns.aprote.ee with ESMTP id j2FA9ejC004891 for ; Tue, 15 Mar 2005 12:09:40 +0200 content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable X-MimeOLE: Produced By Microsoft Exchange V6.0.6556.0 Subject: How to force subquery scan? Date: Tue, 15 Mar 2005 12:08:52 +0200 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: How to force subquery scan? Thread-Index: AcUnUexNULfSyXwVQHCGdXdz6D7KMwB69jag From: "Tambet Matiisen" To: X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/216 X-Sequence-Number: 20695 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