Thread

1. group by

   Kathy Zhu <kathy.zhu@sun.com> — 2003-10-03T22:30:14Z

   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
   
   
   
   

2. Re: group by

   Nigel J. Andrews <nandrews@investsystems.co.uk> — 2003-10-03T22:52:31Z

   On Fri, 3 Oct 2003, 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 ??
   
   
   First thought was caching: the disk blocks are cached in memory after the first
   qery so all the others just fetch from there.
   
   Second thought: well I haven't really had it but indexes and sorting is sort of
   sloshing around in my mind. Not sure how that applies to here since I can't
   remember if the first would require the sort step having retrieved all the
   tuples and the others would just use the index pages.
   
   It is Friday though.
   
   
   -- 
   Nigel J. Andrews
   
   
   

3. Re: group by

   Dennis Gearon <gearond@fireserve.net> — 2003-10-03T22:53:02Z

   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
   >
   >  
   >
   
   
   

4. Re: group by

   Kris Jurka <books@ejurka.com> — 2003-10-03T23:11:35Z

   
   On Fri, 3 Oct 2003, 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
   >
   
   I believe Solaris's qsort implementation (which is used for the group by)
   has problems dealing with large numbers of similar values.
   
   I think in later versions of pg our own qsort is used.
   
   Kris Jurka