Re: Better way to write aggregates? - Mailing list 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