pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedBetter way to write aggregates?
5+ messages / 3 participants
[nested] [flat]
* Better way to write aggregates?
@ 2006-04-21 08:37 Jan Dittmer <jdi@l4x.org>
0 siblings, 1 reply; 5+ messages in thread
From: Jan Dittmer @ 2006-04-21 08:37 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Better way to write aggregates?
@ 2006-04-21 12:27 Jim Buttafuoco <jim@contactbda.com>
parent: Jan Dittmer <jdi@l4x.org>
0 siblings, 1 reply; 5+ messages in thread
From: Jim Buttafuoco @ 2006-04-21 12:27 UTC (permalink / raw)
To: Jan Dittmer <jdi@l4x.org>; pgsql-performance
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 -------
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Better way to write aggregates?
@ 2006-04-21 12:35 Jan Dittmer <jdi@l4x.org>
parent: Jim Buttafuoco <jim@contactbda.com>
0 siblings, 2 replies; 5+ messages in thread
From: Jan Dittmer @ 2006-04-21 12:35 UTC (permalink / raw)
To: jim@contactbda.com; +Cc: pgsql-performance
Jim Buttafuoco wrote:
> 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;
It would help if booleans could be casted to integer 1/0 :-) But
performance wise it should be about the same? I think I'll
run some tests later today with real data.
Would an index on NULLIF(state,1) help count(NULLIF(state,1)) ?
Can one build an index on (case when c.state = 3 then 1 else 0 end)?
Thanks,
Jan
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Better way to write aggregates?
@ 2006-04-21 12:38 Jim Buttafuoco <jim@contactbda.com>
parent: Jan Dittmer <jdi@l4x.org>
1 sibling, 0 replies; 5+ messages in thread
From: Jim Buttafuoco @ 2006-04-21 12:38 UTC (permalink / raw)
To: Jan Dittmer <jdi@l4x.org>; +Cc: pgsql-performance
I don't think an index will help you with this query.
---------- Original Message -----------
From: Jan Dittmer <jdi@l4x.org>
To: jim@contactbda.com
Cc: pgsql-performance@postgresql.org
Sent: Fri, 21 Apr 2006 14:35:33 +0200
Subject: Re: [PERFORM] Better way to write aggregates?
> Jim Buttafuoco wrote:
> > 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;
>
> It would help if booleans could be casted to integer 1/0 :-) But
> performance wise it should be about the same? I think I'll
> run some tests later today with real data.
> Would an index on NULLIF(state,1) help count(NULLIF(state,1)) ?
> Can one build an index on (case when c.state = 3 then 1 else 0 end)?
>
> Thanks,
>
> Jan
------- End of Original Message -------
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Better way to write aggregates?
@ 2006-04-21 14:26 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jan Dittmer <jdi@l4x.org>
1 sibling, 0 replies; 5+ messages in thread
From: Tom Lane @ 2006-04-21 14:26 UTC (permalink / raw)
To: Jan Dittmer <jdi@l4x.org>; +Cc: jim@contactbda.com; pgsql-performance
Jan Dittmer <jdi@l4x.org> writes:
> It would help if booleans could be casted to integer 1/0 :-)
As of 8.1 there is such a cast in the system:
regression=# select 't'::bool::int;
int4
------
1
(1 row)
In earlier releases you can make your own.
As for the original question, though: have you looked at the crosstab
functions in contrib/tablefunc?
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2006-04-21 14:26 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-04-21 08:37 Better way to write aggregates? Jan Dittmer <jdi@l4x.org>
2006-04-21 12:27 ` Jim Buttafuoco <jim@contactbda.com>
2006-04-21 12:35 ` Jan Dittmer <jdi@l4x.org>
2006-04-21 12:38 ` Jim Buttafuoco <jim@contactbda.com>
2006-04-21 14:26 ` Tom Lane <tgl@sss.pgh.pa.us>
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