From: | Jan Dittmer <jdi(at)l4x(dot)org> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Better way to write aggregates? |
Date: | 2006-04-21 08:37:10 |
Message-ID: | 444899B6.6090001@l4x.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | luchot | 2006-04-21 09:33:15 | Little use of CPU ( < 5%) |
Previous Message | Markus Schaber | 2006-04-21 07:53:34 | Re: Introducing a new linux readahead framework |