pg.ddx.io pgsql-sql@postgresql.org mailing list archive help / color / mirror / Atom feed From: Achilleus Mantzios <achill@matrix.gatewaynet.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-general@postgresql.org, <pgsql-sql@postgresql.org>
Subject: Re: FreeBSD, Linux: select, select count(*) performance
Date: Wed, 27 Nov 2002 18:35:20 +0200 (EET)
Message-ID: <Pine.LNX.4.44.0211271826120.27228-100000@matrix.gatewaynet.com> (raw )
In-Reply-To: <17919.1038413758@sss.pgh.pa.us >
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
view thread (6+ messages) latest in thread
Message-ID: <Pine.LNX.4.44.0211271826120.27228-100000@matrix.gatewaynet.com>
Permalink: ../Pine.LNX.4.44.0211271826120.27228-100000@matrix.gatewaynet.com/
Also on: postgresql.org/message-id/Pine.LNX.4.44.0211271826120.27228-100000@matrix.gatewaynet.com
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: achill@matrix.gatewaynet.com, tgl@sss.pgh.pa.us
Subject: Re: FreeBSD, Linux: select, select count(*) performance
In-Reply-To: <Pine.LNX.4.44.0211271826120.27228-100000@matrix.gatewaynet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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