By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,712 Members | 1,958 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,712 IT Pros & Developers. It's quick & easy.

group by

P: n/a
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

Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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 4: Don't 'kill -9' the postmaster

Nov 12 '05 #2

P: n/a
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
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #3

P: n/a


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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.