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: [EMAIL PROTECTED]
[EMAIL PROTECTED]
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
