From: Jim Buttafuoco <jim@contactbda.com>
To: Jan Dittmer <jdi@l4x.org>
To: pgsql-performance@postgresql.org
Subject: Re: Better way to write aggregates?
Date: Fri, 21 Apr 2006 08:27:32 -0400
Message-ID: <20060421122617.M81850@contactbda.com> (raw)
In-Reply-To: <444899B6.6090001@l4x.org>
References: <444899B6.6090001@l4x.org>
Jan,
I write queries like this
CREATE VIEW parent_childs AS
SELECT
c.parent,
count(c.state) as childtotal,
sum(case when c.state = 1 then 1 else 0 end) as childstate1,
sum(case when c.state = 2 then 1 else 0 end) as childstate2,
sum(case when c.state = 3 then 1 else 0 end) as childstate3
FROM child c
GROUP BY parent;
---------- Original Message -----------
From: Jan Dittmer <jdi@l4x.org>
To: pgsql-performance@postgresql.org
Sent: Fri, 21 Apr 2006 10:37:10 +0200
Subject: [PERFORM] Better way to write aggregates?
> Hi,
>
> I more or less often come about the problem of aggregating a
> child table counting it's different states. The cleanest solution
> I've come up with so far is:
>
> BEGIN;
> CREATE TABLE parent (
> id int not null,
> name text not null,
> UNIQUE(id)
> );
>
> CREATE TABLE child (
> name text not null,
> state int not null,
> parent int not null references parent(id)
> );
>
> CREATE VIEW parent_childs AS
> SELECT
> c.parent,
> count(c.state) as childtotal,
> count(c.state) - count(nullif(c.state,1)) as childstate1,
> count(c.state) - count(nullif(c.state,2)) as childstate2,
> count(c.state) - count(nullif(c.state,3)) as childstate3
> FROM child c
> GROUP BY parent;
>
> CREATE VIEW parent_view AS
> SELECT p.*,
> pc.*
> FROM parent p
> LEFT JOIN parent_childs pc ON (p.id = pc.parent);
> COMMIT;
>
> Is this the fastest way to build these aggregates (not considering
> tricks with triggers, etc)? The count(state) - count(nullif(...)) looks
> a bit clumsy.
> I also experimented with a pgsql function to sum these up, but considered
> it as not-so-nice and it also always forces a sequential scan on the
> data.
>
> Thanks for any advice,
>
> Jan
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
------- End of Original Message -------
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: jim@contactbda.com, jdi@l4x.org
Subject: Re: Better way to write aggregates?
In-Reply-To: <20060421122617.M81850@contactbda.com>
* 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