Re: [GENERAL] FreeBSD, Linux: select, select count(*) performance
| От | Achilleus Mantzios |
|---|---|
| Тема | Re: [GENERAL] FreeBSD, Linux: select, select count(*) performance |
| Дата | |
| Msg-id | Pine.LNX.4.44.0211271826120.27228-100000@matrix.gatewaynet.com обсуждение исходный текст |
| Ответ на | Re: [GENERAL] FreeBSD, Linux: select, select count(*) performance (Tom Lane <tgl@sss.pgh.pa.us>) |
| Ответы |
Re: [GENERAL] FreeBSD, Linux: select, select count(*) performance
(Tom Lane <tgl@sss.pgh.pa.us>)
|
| Список | 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
В списке pgsql-sql по дате отправления:
Предыдущее
От: Tom LaneДата:
Сообщение: Re: [GENERAL] FreeBSD, Linux: select, select count(*) performance
Следующее
От: Tom LaneДата:
Сообщение: Re: [GENERAL] FreeBSD, Linux: select, select count(*) performance