Обсуждение: FreeBSD, Linux: select, select count(*) performance

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

Achilleus Mantzios <achill@matrix.gatewaynet.com> writes:
> 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

You didn't say what was *in* the table, exactly ... but I'm betting
there are a lot of toasted columns, and that the extra runtime
represents the time to fetch (and perhaps decompress) the TOAST entries.

            regards, tom lane

On Wed, 27 Nov 2002, Tom Lane wrote:

> Achilleus Mantzios <achill@matrix.gatewaynet.com> writes:
> > 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
>
> You didn't say what was *in* the table, exactly ... but I'm betting
> there are a lot of toasted columns, and that the extra runtime
> represents the time to fetch (and perhaps decompress) the TOAST entries.

Are there any reason to "fetch (and perhaps decompress) the TOAST entries"
just to count(*) without any WHERE clause ?


>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
Regards,    Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

Oleg Bartunov <oleg@sai.msu.su> writes:
> Are there any reason to "fetch (and perhaps decompress) the TOAST entries"
> just to count(*) without any WHERE clause ?

It doesn't.  That was my point...
        regards, tom lane