Thread: Aggregate weirdness

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)

Martin Kjeldsen <martin@martinkjeldsen.dk> writes:
> 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;

This is a bad way to do it --- the sub-select isn't readily optimizable.
Try something like

 SELECT t.id, t.name, sum(test_use.number) AS numbers
   FROM test_use
   JOIN test t ON test_use.test_id = t.id
  GROUP BY t.id, t.name;


            regards, tom lane