Message-ID: <4448D195.4020801@l4x.org>
Date: Fri, 21 Apr 2006 14:35:33 +0200
From: Jan Dittmer <jdi@l4x.org>
User-Agent: Debian Thunderbird 1.0.7 (X11/20051017)
MIME-Version: 1.0
To: jim@contactbda.com
CC: pgsql-performance@postgresql.org
Subject: Re: Better way to write aggregates?
References: <444899B6.6090001@l4x.org> <20060421122617.M81850@contactbda.com>
In-Reply-To: <20060421122617.M81850@contactbda.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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