Re: Aggregate weirdness

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Martin Kjeldsen <martin(at)martinkjeldsen(dot)dk>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Aggregate weirdness
Date: 2008-11-04 13:39:32
Message-ID: 16439.1225805972@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Martin Kjeldsen <martin(at)martinkjeldsen(dot)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

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message praveen 2008-11-04 14:20:49 Installation Error of postgresql-8.1.5 with perl.
Previous Message Sathish Duraiswamy 2008-11-04 13:08:39 Re: [GENERAL] epqa; postgres performance optimizer support tool; opensource.