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

On Wed, 27 Nov 2002, Tom Lane wrote:

> Achilleus Mantzios <achill(at)matrix(dot)gatewaynet(dot)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