FreeBSD, Linux: select, select count(*) performance - Mailing list pgsql-sql

Hi,

i run 2 queries on 2 similar boxes (one running Linux 2.4.7, redhat 7.1
and the other running FreeBSD 4.7-RELEASE-p2)

The 2 boxes run postgresql 7.2.3.

I get some performance results that are not obvious (at least to me)

i have one table named "noon" with 108095 rows.

The 2 queries are:
q1: SELECT count(*) from noon;
q2: SELECT * from noon;

Linux q1
========
dynacom=# EXPLAIN ANALYZE SELECT count(*) from noon;
NOTICE:  QUERY PLAN:

Aggregate  (cost=20508.19..20508.19 rows=1 width=0) (actual
time=338.17..338.17
rows=1 loops=1)
  ->  Seq Scan on noon  (cost=0.00..20237.95 rows=108095 width=0) (actual
time=0.01..225.73 rows=108095 loops=1)
Total runtime: 338.25 msec

Linux q2
========
dynacom=# EXPLAIN ANALYZE SELECT * from noon;
NOTICE:  QUERY PLAN:

Seq Scan on noon  (cost=0.00..20237.95 rows=108095 width=1960) (actual
time=1.22..67909.31 rows=108095 loops=1)
Total runtime: 68005.96 msec

FreeBSD q1
==========
dynacom=# EXPLAIN ANALYZE SELECT count(*) from noon;
NOTICE:  QUERY PLAN:

Aggregate  (cost=20508.19..20508.19 rows=1 width=0) (actual
time=888.93..888.94
rows=1 loops=1)
  ->  Seq Scan on noon  (cost=0.00..20237.95 rows=108095 width=0) (actual
time=0.02..501.09 rows=108095 loops=1)
Total runtime: 889.06 msec

FreeBSD q2
==========
dynacom=# EXPLAIN ANALYZE SELECT * from noon;
NOTICE:  QUERY PLAN:

Seq Scan on noon  (cost=0.00..20237.95 rows=108095 width=1975) (actual
time=1.08..53470.93 rows=108095 loops=1)
Total runtime: 53827.37 msec

The pgsql configuration for both systems is identical
(the FreeBSD system has less memory but vmstat dont show
any paging activity so i assume this is not an issue here).

The interesting part is that FreeBSD does better in select *,
whereas Linux seem to do much better in select count(*).

Paging and disk IO activity for both systems is near 0.

When i run the select count(*) in Linux i notice a small
increase (15%) in Context Switches per sec, whereas in FreeBSD
i notice a big increase in Context Switches (300%) and
a huge increase in system calls per second (from normally
9-10 to 110,000).
(Linux vmstat gives no syscall info).

The same results come out for every count(*) i try.
Is it just the reporting from explain analyze??

Has any hacker some light to shed??

Thanx.

==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel:    +30-10-8981112
fax:    +30-10-8981877
email:  achill@matrix.gatewaynet.com
        mantzios@softlab.ece.ntua.gr