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 <[EMAIL PROTECTED]> > 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 <[EMAIL PROTECTED]> > CC: [EMAIL PROTECTED] > 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 > > > > > > > ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
