From: | Achilleus Mantzios <achill(at)matrix(dot)gatewaynet(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org, <pgsql-hackers(at)postgresql(dot)org> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | FreeBSD, Linux: select, select count(*) performance |
Date: | 2002-11-27 12:23:12 |
Message-ID: | Pine.LNX.4.44.0211271320530.24849-100000@matrix.gatewaynet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers 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(at)matrix(dot)gatewaynet(dot)com
mantzios(at)softlab(dot)ece(dot)ntua(dot)gr
From | Date | Subject | |
---|---|---|---|
Next Message | Bruno Wolff III | 2002-11-27 12:24:48 | Re: Rép |
Previous Message | Aurangzeb M. Agha | 2002-11-27 11:37:41 | default value -- best practice question |
From | Date | Subject | |
---|---|---|---|
Next Message | Nicolai Tufar | 2002-11-27 14:02:22 | Re: [HACKERS] Realtime VACUUM, was: performance of insert/delete/update |
Previous Message | Andrew John | 2002-11-27 12:15:47 | Re: How can i import database from MSSQL to Postgres?(NULL BODY) |
From | Date | Subject | |
---|---|---|---|
Next Message | Tomas Berndtsson | 2002-11-27 12:46:49 | Big query problem |
Previous Message | Laurent Patureau | 2002-11-27 11:05:11 | Function and insert |