I did a vacuum and got the same result.
I think the problem lies in there is swapping going for groupby when there is a
large number of rows in the table, 5000 in this case.
I guess I have to use group by with caution.
thanks for the all the replies though,
kathy
> Date: Fri, 03 Oct 2003 15:53:02 -0700
> From: Dennis Gearon <gearond@fireserve.net>
> User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.4)
Gecko/20030624
> X-Accept-Language: en-us, ru, es-mx
> To: Kathy Zhu <Kathy.Zhu@sun.com>
> CC: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] group by
> Content-Transfer-Encoding: 7bit
>
> When was the last time you vacuumed full?
>
> Kathy Zhu wrote:
>
> >Hi,
> >
> >I notices a weird thing here.
> >
> >version 7.2.1
> >on Solaris
> >
> >table "test", has a field "state".
> >There are 4 "state" values, 1, 2, 3, 4.
> >
> >select count(*) from test group by state;
> >took 11500 msec
> >
> >but
> >
> >select count(*) from test where state = 1;
> >select count(*) from test where state = 2;
> >select count(*) from test where state = 3;
> >select count(*) from test where state = 4;
> >total took 626 msec
> >
> >Why ??
> >
> >thanks,
> >kathy
> >
> >
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/docs/faqs/FAQ.html
> >
> >
> >
>
