From: Jim Buttafuoco <jim@contactbda.com>
To: Jan Dittmer <jdi@l4x.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Better way to write aggregates?
Date: Fri, 21 Apr 2006 08:38:58 -0400
Message-ID: <20060421123759.M11447@contactbda.com> (raw)
In-Reply-To: <4448D195.4020801@l4x.org>
References: <444899B6.6090001@l4x.org>
<20060421122617.M81850@contactbda.com>
<4448D195.4020801@l4x.org>
I don't think an index will help you with this query.
---------- Original Message -----------
From: Jan Dittmer <jdi@l4x.org>
To: jim@contactbda.com
Cc: pgsql-performance@postgresql.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 -------
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jim@contactbda.com, jdi@l4x.org
Subject: Re: Better way to write aggregates?
In-Reply-To: <20060421123759.M11447@contactbda.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox