Postgres Pro
Downloads
Home   >   mailing lists

Thread: FreeBSD, Linux: select, select count(*) performance

FreeBSD, Linux: select, select count(*) performance

From
Achilleus Mantzios
Date:
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

Re: [GENERAL] FreeBSD, Linux: select, select count(*) performance

From
Tom Lane
Date:
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

Re: [GENERAL] FreeBSD, Linux: select, select count(*) performance

From
Oleg Bartunov
Date:
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

Re: [GENERAL] FreeBSD, Linux: select, select count(*) performance

From
Tom Lane
Date:
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