Skip site navigation (1) Skip section navigation (2)

Re: Better way to write aggregates?

From: Jan Dittmer <jdi(at)l4x(dot)org>
To: jim(at)contactbda(dot)com
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Better way to write aggregates?
Date: 2006-04-21 12:35:33
Message-ID: 4448D195.4020801@l4x.org (view raw or flat)
Thread:
Lists: 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


In response to

Responses

pgsql-performance by date

Next:From: Jim ButtafuocoDate: 2006-04-21 12:38:58
Subject: Re: Better way to write aggregates?
Previous:From: Jim ButtafuocoDate: 2006-04-21 12:27:32
Subject: Re: Better way to write aggregates?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group