From: | "Jim Buttafuoco" <jim(at)contactbda(dot)com> |
---|---|
To: | Jan Dittmer <jdi(at)l4x(dot)org> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Better way to write aggregates? |
Date: | 2006-04-21 12:38:58 |
Message-ID: | 20060421123759.M11447@contactbda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
I don't think an index will help you with this query.
---------- Original Message -----------
From: Jan Dittmer <jdi(at)l4x(dot)org>
To: jim(at)contactbda(dot)com
Cc: pgsql-performance(at)postgresql(dot)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 -------
From | Date | Subject | |
---|---|---|---|
Next Message | Dave Dutcher | 2006-04-21 13:26:33 | Re: Takes too long to fetch the data from database |
Previous Message | Jan Dittmer | 2006-04-21 12:35:33 | Re: Better way to write aggregates? |