Hi,
I have a small problem. I have one view which sum's a another tables field and
uses that sum for several things including filtering. Every time it uses the
that summarised field in other queries or views, the planner seems to duplicate
the SUM. Isn't it possible for the planner only to do the SUM once and reuse it?
I've done a small example illustrating my problem. I'm using 8.3.3. Hope
somebody can tell my what I'm doing wrong or why this is happening. The problem
isn't great and I think I can work around it, using a stable function for doing
the SUM, but I'm still wondering.
Best regards
Martin
----------------------
EXAMPLE:
CREATE TABLE test (
id INTEGER,
name TEXT
);
INSERT INTO test (id, name) VALUES(1, 'ddd');
CREATE TABLE test_use (
test_id INTEGER,
number INTEGER
);
INSERT INTO test_use (test_id, number) VALUES(1, 3);
INSERT INTO test_use (test_id, number) VALUES(1, 4);
INSERT INTO test_use (test_id, number) VALUES(1, 1);
INSERT INTO test_use (test_id, number) VALUES(1, 27);
CREATE OR REPLACE VIEW v_test_with_number AS
SELECT
t.*,
(SELECT SUM(number) FROM test_use WHERE test_id = t.id) as numbers
FROM test t;
CREATE OR REPLACE VIEW v_test_with_number_filtered AS
SELECT * FROM v_test_with_number WHERE numbers > 0;
EXPLAIN SELECT * FROM v_test_with_number;
QUERY PLAN
------------------------------------------------------------------------
Seq Scan on test t (cost=0.00..45274.00 rows=1230 width=36)
SubPlan
-> Aggregate (cost=36.78..36.79 rows=1 width=4)
-> Seq Scan on test_use (cost=0.00..36.75 rows=11 width=4)
Filter: (test_id = $0)
(5 rows)
EXPLAIN SELECT * FROM v_test_with_number_filtered;
QUERY PLAN
------------------------------------------------------------------------
Seq Scan on test t (cost=0.00..60360.97 rows=410 width=36)
Filter: ((subplan) > 0)
SubPlan
-> Aggregate (cost=36.78..36.79 rows=1 width=4)
-> Seq Scan on test_use (cost=0.00..36.75 rows=11 width=4)
Filter: (test_id = $0)
-> Aggregate (cost=36.78..36.79 rows=1 width=4)
-> Seq Scan on test_use (cost=0.00..36.75 rows=11 width=4)
Filter: (test_id = $0)
(9 rows)
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
