pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFreeBSD, Linux: select, select count(*) performance
6+ messages / 3 participants
[nested] [flat]
* FreeBSD, Linux: select, select count(*) performance
@ 2002-11-27 12:23 Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 6+ messages in thread
From: Achilleus Mantzios @ 2002-11-27 12:23 UTC (permalink / raw)
To: pgsql-general@postgresql.org, <pgsql-hackers@postgresql.org>; +Cc: 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@matrix.gatewaynet.com
mantzios@softlab.ece.ntua.gr
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: FreeBSD, Linux: select, select count(*) performance
@ 2002-11-27 16:15 Tom Lane <tgl@sss.pgh.pa.us>
parent: Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 2 replies; 6+ messages in thread
From: Tom Lane @ 2002-11-27 16:15 UTC (permalink / raw)
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-general@postgresql.org; pgsql-hackers@postgresql.org; pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [GENERAL] FreeBSD, Linux: select, select count(*) performance
@ 2002-11-27 16:29 Oleg Bartunov <oleg@sai.msu.su>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 6+ messages in thread
From: Oleg Bartunov @ 2002-11-27 16:29 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Achilleus Mantzios <achill@matrix.gatewaynet.com>; Pgsql Hackers <pgsql-hackers@postgresql.org>
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: FreeBSD, Linux: select, select count(*) performance
@ 2002-11-27 16:35 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 6+ messages in thread
From: Achilleus Mantzios @ 2002-11-27 16:35 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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.
278 columns of various types.
namely,
Table "noon"
Column | Type | Modifiers
------------------------+------------------------+-----------
v_code | character varying(4) |
log_no | bigint |
report_date | date |
report_time | time without time zone |
voyage_no | integer |
charterer | character varying(12) |
port | character varying(24) |
duration | character varying(4) |
rotation | character varying(9) |
me_do_cons | double precision |
reason | character varying(12) |
ancorage_date | date |
ancorage_time | time without time zone |
exp_berth_date | date |
exp_berth_time | time without time zone |
berth_date | date |
berth_time | time without time zone |
exp_sail_date | date |
exp_sail_time | time without time zone |
draft_fw | double precision |
draft_aft | double precision |
etc_date | date |
etc_time | time without time zone |
completion_date | date |
completion_time | time without time zone |
load_quantity | double precision |
discharging_quantity | double precision |
delivery_date | date |
delivery_place | character varying(12) |
redelivery_date | date |
redelivery_time | time without time zone |
redelivery_place | character varying(12) |
rob_ifo | double precision |
rob_mdo | double precision |
log_ifo | double precision |
log_mdo | double precision |
rcv_ifo | double precision |
rcv_mdo | double precision |
rcv_me | double precision |
rcv_cyl | double precision |
rcv_gen | double precision |
rob_me | double precision |
rob_cyl | double precision |
rob_gen | double precision |
voyage_sub_no | integer |
voyage_activity | character varying(3) |
remarks | character varying(60) |
latitude | character varying(6) |
longitude | character varying(6) |
speed | double precision |
wind_direction | character varying(1) |
rpm | double precision |
fuelconsumption | double precision |
me_bearing_oil_presure | double precision |
me_bearing_amber | double precision |
ambere | character varying(8) |
remarks2 | character varying(12) |
steam_hours | double precision |
ifoconsboilerheat | double precision |
ae_mdo_consumption | double precision |
cyl_me_exh_temp01 | double precision |
cyl_me_exh_temp02 | double precision |
cyl_me_exh_temp03 | double precision |
cyl_me_exh_temp04 | double precision |
cyl_me_exh_temp05 | double precision |
cyl_me_exh_temp06 | double precision |
cyl_me_exh_temp07 | double precision |
cyl_me_exh_temp08 | double precision |
cyl_me_exh_temp09 | double precision |
cyl_me_exh_temp10 | double precision |
cyl_me_exh_temp11 | double precision |
cyl_me_exh_temp12 | double precision |
cyl_me_exh_temp13 | double precision |
cyl_me_exh_temp14 | double precision |
gen1_ae_exh_temp01 | double precision |
gen1_ae_exh_temp02 | double precision |
gen1_ae_exh_temp03 | double precision |
gen1_ae_exh_temp04 | double precision |
gen1_ae_exh_temp05 | double precision |
gen1_ae_exh_temp06 | double precision |
gen1_ae_exh_temp07 | double precision |
gen1_ae_exh_temp08 | double precision |
gen2_ae_exh_temp01 | double precision |
gen2_ae_exh_temp02 | double precision |
gen2_ae_exh_temp03 | double precision |
gen2_ae_exh_temp04 | double precision |
gen2_ae_exh_temp05 | double precision |
gen2_ae_exh_temp06 | double precision |
gen2_ae_exh_temp07 | double precision |
gen2_ae_exh_temp08 | double precision |
gen3_ae_exh_temp01 | double precision |
gen3_ae_exh_temp02 | double precision |
gen3_ae_exh_temp03 | double precision |
gen3_ae_exh_temp04 | double precision |
gen3_ae_exh_temp05 | double precision |
gen3_ae_exh_temp06 | double precision |
gen3_ae_exh_temp07 | double precision |
gen3_ae_exh_temp08 | double precision |
dont_know | character varying(14) |
voyage_confirmation | character varying(1) |
ldin | double precision |
dist_to_go | integer |
dom_fw_rob | double precision |
fw_produced | double precision |
fw_salinity | double precision |
fw_cons_dom | double precision |
fw_cons_boil | double precision |
ifo_ballast | double precision |
ifo_deballast | double precision |
ifo_load | double precision |
ifo_disc | double precision |
ifo_blr_heat | double precision |
foofield | double precision |
sc_air_pr | double precision |
sc_air_temp | integer |
ae_oil_pr1 | double precision |
ae_oil_pr2 | double precision |
ae_oil_pr3 | double precision |
ae_oil_pr4 | double precision |
ae_oil_pr5 | double precision |
gen1_ex_9 | integer |
gen1_ex_10 | integer |
gen1_ex_11 | integer |
gen1_ex_12 | integer |
gen1_ex_13 | integer |
gen1_ex_14 | integer |
gen1_ex_15 | integer |
gen1_ex_16 | integer |
gen1_ex_17 | integer |
gen1_ex_18 | integer |
gen1_ex_19 | integer |
gen1_ex_20 | integer |
gen2_ex_9 | integer |
gen2_ex_10 | integer |
gen2_ex_11 | integer |
gen2_ex_12 | integer |
gen2_ex_13 | integer |
gen2_ex_14 | integer |
gen2_ex_15 | integer |
gen2_ex_16 | integer |
gen2_ex_17 | integer |
gen2_ex_18 | integer |
gen2_ex_19 | integer |
gen2_ex_20 | integer |
gen3_ex_9 | integer |
gen3_ex_10 | integer |
gen3_ex_11 | integer |
gen3_ex_12 | integer |
gen3_ex_13 | integer |
gen3_ex_14 | integer |
gen3_ex_15 | integer |
gen3_ex_16 | integer |
gen3_ex_17 | integer |
gen3_ex_18 | integer |
gen3_ex_19 | integer |
gen3_ex_20 | integer |
gen4_ex_1 | integer |
gen4_ex_2 | integer |
gen4_ex_3 | integer |
gen4_ex_4 | integer |
gen4_ex_5 | integer |
gen4_ex_6 | integer |
gen4_ex_7 | integer |
gen4_ex_8 | integer |
gen4_ex_9 | integer |
gen4_ex_10 | integer |
gen4_ex_11 | integer |
gen4_ex_12 | integer |
gen4_ex_13 | integer |
gen4_ex_14 | integer |
gen4_ex_15 | integer |
gen4_ex_16 | integer |
gen4_ex_17 | integer |
gen4_ex_18 | integer |
gen4_ex_19 | integer |
gen4_ex_20 | integer |
gen5_ex_1 | integer |
gen5_ex_2 | integer |
gen5_ex_3 | integer |
gen5_ex_4 | integer |
gen5_ex_5 | integer |
gen5_ex_6 | integer |
gen5_ex_7 | integer |
gen5_ex_8 | integer |
gen5_ex_9 | integer |
gen5_ex_10 | integer |
gen5_ex_11 | integer |
gen5_ex_12 | integer |
gen5_ex_13 | integer |
gen5_ex_14 | integer |
gen5_ex_15 | integer |
gen5_ex_16 | integer |
gen5_ex_17 | integer |
gen5_ex_18 | integer |
gen5_ex_19 | integer |
gen5_ex_20 | integer |
ae_kw1 | integer |
ae_kw2 | integer |
ae_kw3 | integer |
ae_kw4 | integer |
ae_kw5 | integer |
filler | integer |
me_tc_rpm1 | integer |
me_tc_rpm2 | integer |
me_tc_rpm3 | integer |
me_tc_rpm4 | integer |
me_tc_rpm5 | integer |
me_tc_ex1 | integer |
me_tc_ex2 | integer |
me_tc_ex3 | integer |
me_tc_ex4 | integer |
me_tc_ex5 | integer |
me_air_cool1 | integer |
me_air_cool2 | integer |
heat_c1 | double precision |
heat_c2 | double precision |
heat_c3 | double precision |
heat_c4 | double precision |
heat_c5 | double precision |
heat_c6 | double precision |
heat_p1 | double precision |
heat_p2 | double precision |
heat_p3 | double precision |
heat_p4 | double precision |
heat_p5 | double precision |
heat_p6 | double precision |
heat_s1 | double precision |
heat_s2 | double precision |
heat_s3 | double precision |
heat_s4 | double precision |
heat_s5 | double precision |
heat_s6 | double precision |
igs_c1 | double precision |
igs_c2 | double precision |
igs_c3 | double precision |
igs_c4 | double precision |
igs_c5 | double precision |
igs_c6 | double precision |
igs_p1 | double precision |
igs_p2 | double precision |
igs_p3 | double precision |
igs_p4 | double precision |
igs_p5 | double precision |
igs_p6 | double precision |
igs_s1 | double precision |
igs_s2 | double precision |
igs_s3 | double precision |
igs_s4 | double precision |
igs_s5 | double precision |
igs_s6 | double precision |
slip | double precision |
foofloat | double precision |
fohandle | double precision |
wind_dir | integer |
intensity | integer |
state_sea | character varying(12) |
soundings | character varying(12) |
ecyl15 | integer |
ecyl16 | integer |
ecyl17 | integer |
ecyl18 | integer |
ecyl19 | integer |
ecyl20 | integer |
rem7 | character varying(12) |
rem8 | character varying(12) |
rem9 | character varying(12) |
rem10 | character varying(12) |
rem11 | character varying(12) |
rem12 | character varying(12) |
rem13 | character varying(12) |
rem14 | character varying(12) |
rem15 | character varying(12) |
mesumplevel | double precision |
oilwat2 | double precision |
tot_steam_time | double precision |
sea_temp | integer |
air_temp | integer |
tg_kw | character varying(4) |
Indexes: noonf_date,
noonf_logno,
noonf_rotation,
noonf_vcode,
noonf_voyageno
The data as i told you are the same db dumped from the production system.
This same dump file was used to populate both (Linux,FBSD) databases.
How is it possible one to have toasted columns whereas the other not??
How can someone identify toasted columns??
Thanx,
Achilleus
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: FreeBSD, Linux: select, select count(*) performance
@ 2002-11-27 16:56 Tom Lane <tgl@sss.pgh.pa.us>
parent: Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2002-11-27 16:56 UTC (permalink / raw)
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-general@postgresql.org; pgsql-sql
Achilleus Mantzios <achill@matrix.gatewaynet.com> writes:
> On Wed, 27 Nov 2002, Tom Lane wrote:
>> 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.
> 278 columns of various types.
> namely,
> [snip]
Hmm, no particularly wide columns there --- but 278 columns is a lot.
I think the extra time might just be the time involved in fetching all
those column values out of the table row?
If you're interested in pursuing it, I'd suggest rebuilding the backend
with profiling enabled so you can see exactly where the time goes.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [GENERAL] FreeBSD, Linux: select, select count(*) performance
@ 2002-11-27 16:57 Tom Lane <tgl@sss.pgh.pa.us>
parent: Oleg Bartunov <oleg@sai.msu.su>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2002-11-27 16:57 UTC (permalink / raw)
To: Oleg Bartunov <oleg@sai.msu.su>; +Cc: Achilleus Mantzios <achill@matrix.gatewaynet.com>; Pgsql Hackers <pgsql-hackers@postgresql.org>
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2002-11-27 16:57 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-11-27 12:23 FreeBSD, Linux: select, select count(*) performance Achilleus Mantzios <achill@matrix.gatewaynet.com>
2002-11-27 16:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-11-27 16:29 ` Oleg Bartunov <oleg@sai.msu.su>
2002-11-27 16:57 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-11-27 16:35 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2002-11-27 16:56 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox