From: Tom Lane <tgl@sss.pgh.pa.us>
To: Martin Kjeldsen <martin@martinkjeldsen.dk>
Cc: pgsql-performance@postgresql.org
Subject: Re: Aggregate weirdness
Date: Tue, 04 Nov 2008 08:39:32 -0500
Message-ID: <16439.1225805972@sss.pgh.pa.us> (raw)
In-Reply-To: <20081104105625.GB1383@martinkjeldsen.dk>
References: <20081104105625.GB1383@martinkjeldsen.dk>
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
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-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, martin@martinkjeldsen.dk
Subject: Re: Aggregate weirdness
In-Reply-To: <16439.1225805972@sss.pgh.pa.us>
* 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