I don't think an index will help you with this query. ---------- Original Message ----------- From: Jan Dittmer <jdi@xxxxxxx> To: jim@xxxxxxxxxxxxxx Cc: pgsql-performance@xxxxxxxxxxxxxx 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 -------
