Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 989EC476245; Wed, 27 Nov 2002 07:15:02 -0500 (EST) Received: from matrix.gatewaynet.com (unknown [217.19.69.50]) by postgresql.org (Postfix) with ESMTP id D9CB247612E; Wed, 27 Nov 2002 07:14:48 -0500 (EST) Received: from localhost (achill@localhost) by matrix.gatewaynet.com (8.11.6/8.11.6) with ESMTP id gARCNCw25947; Wed, 27 Nov 2002 14:23:12 +0200 Date: Wed, 27 Nov 2002 14:23:12 +0200 (EET) From: Achilleus Mantzios To: pgsql-general@postgresql.org, Cc: pgsql-sql@postgresql.org Subject: FreeBSD, Linux: select, select count(*) performance In-Reply-To: <3DE498E4.2050002@ualg.pt> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200211/1214 X-Sequence-Number: 33634 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