Thread: speeding up a join query that utilizes a view
I am looking for advice on a performance problem. I'm pretty sure that the culprit of my slow performance is a view
thatis several hundred million records in size. Because it is a view, I can only index the underlying table, but
becausethe view generates an "un-pivoted" version of the underlying table with un unnest function, I can't index the
importantcolumn in the underlying table, because it doesn't exist until after the un-pivot or stacking function of the
view…I know… this is all very circular.
Here is the join query that uses the view. I have
SELECT
data_key.site,
data_key.canopy,
data_key.measurement_interval,
data_key.treatment_code,
data_key.treatment_abbr,
data_key.plot,
fifteen_min_stacked_view.*
FROM
data_key,
fifteen_min_stacked_view
WHERE
data_key.variable_channel= fifteen_min_stacked_view.variable AND data_key.block_name =
fifteen_min_stacked_view.block_name
AND fifteen_min_stacked_view.variable ~ 'tsoil'
I have tried adding indexes where I can on the join colums in the data_key table
Here is the EXPLAIN.
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=195.20..548004.70 rows=196 width=192) (actual time=3.295..443523.222 rows=28779376 loops=1)
Hash Cond: ((fifteen_min_stacked_propper.variable = (data_key.variable_channel)::text) AND
((fifteen_min_stacked_propper.block_name)::text= (data_key.block_name)::text))
-> Subquery Scan on fifteen_min_stacked_propper (cost=0.00..547620.47 rows=2878 width=156) (actual
time=0.247..424911.643rows=28779376 loops=1)
Filter: (fifteen_min_stacked_propper.variable ~ 'tsoil'::text)
-> Index Scan using fifteen_min_pkey on fifteen_min (cost=0.00..525136.58 rows=1798711 width=1072) (actual
time=0.034..96077.588rows=428093218 loops=1)
-> Hash (cost=124.28..124.28 rows=4728 width=55) (actual time=3.036..3.036 rows=4728 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 437kB
-> Seq Scan on data_key (cost=0.00..124.28 rows=4728 width=55) (actual time=0.007..1.277 rows=4728 loops=1)
Total runtime: 444912.792 ms
(9 rows)
Any ideas would be much appreciated
> -----Original Message----- > From: Kirk Wythers [mailto:kwythers@umn.edu] > Sent: Thursday, January 17, 2013 12:16 AM > To: pgsql-general@postgresql.org > Subject: speeding up a join query that utilizes a view > > I am looking for advice on a performance problem. I'm pretty sure that > the culprit of my slow performance is a view that is several hundred > million records in size. Because it is a view, I can only index the > underlying table, but because the view generates an "un-pivoted" > version of the underlying table with un unnest function, I can't index > the important column in the underlying table, because it doesn't exist > until after the un-pivot or stacking function of the view... I know... this > is all very circular. > > Here is the join query that uses the view. I have > > SELECT > data_key.site, > data_key.canopy, > data_key.measurement_interval, > data_key.treatment_code, > data_key.treatment_abbr, > data_key.plot, > fifteen_min_stacked_view.* > FROM > data_key, > fifteen_min_stacked_view > WHERE > data_key.variable_channel = > fifteen_min_stacked_view.variable AND data_key.block_name = > fifteen_min_stacked_view.block_name > AND fifteen_min_stacked_view.variable ~ 'tsoil' > > I have tried adding indexes where I can on the join colums in the > data_key table Here is the EXPLAIN. > > > QUERY PLAN > ----------------------------------------------------------------------- > ----------------------------------------------------------------------- > -------------------------------- > Hash Join (cost=195.20..548004.70 rows=196 width=192) (actual > time=3.295..443523.222 rows=28779376 loops=1) > Hash Cond: ((fifteen_min_stacked_propper.variable = > (data_key.variable_channel)::text) AND > ((fifteen_min_stacked_propper.block_name)::text = > (data_key.block_name)::text)) > -> Subquery Scan on fifteen_min_stacked_propper > (cost=0.00..547620.47 rows=2878 width=156) (actual > time=0.247..424911.643 rows=28779376 loops=1) > Filter: (fifteen_min_stacked_propper.variable ~ 'tsoil'::text) > -> Index Scan using fifteen_min_pkey on fifteen_min > (cost=0.00..525136.58 rows=1798711 width=1072) (actual > time=0.034..96077.588 rows=428093218 loops=1) > -> Hash (cost=124.28..124.28 rows=4728 width=55) (actual > time=3.036..3.036 rows=4728 loops=1) > Buckets: 1024 Batches: 1 Memory Usage: 437kB > -> Seq Scan on data_key (cost=0.00..124.28 rows=4728 > width=55) (actual time=0.007..1.277 rows=4728 loops=1) Total runtime: > 444912.792 ms > (9 rows) > > > Any ideas would be much appreciated Not enough information: Postgres version? OS? Some Postgres configuration parameters, specifically related to "RESOURCE USAGE" and " QUERY TUNING"? Table structures (including indexes) for: fifteen_min_stacked_propper, fifteen_min, and data_key? View definition for fifteen_min_stacked_view? Regards, Igor Neyman
>
> Not enough information:
>
> Postgres version?
> OS?
> Some Postgres configuration parameters, specifically related to "RESOURCE USAGE" and " QUERY TUNING"?
> Table structures (including indexes) for: fifteen_min_stacked_propper, fifteen_min, and data_key?
> View definition for fifteen_min_stacked_view?
>
Here is some additional information:
b4warmed3=# \d data_key
Table "public.data_key"
Column | Type | Modifiers
----------------------+-----------------------+------------------------------------------
site | character varying(6) |
canopy | character varying(24) |
block | character(2) |
plot | character(2) |
measurement_interval | interval |
warming_treatment | character varying(24) |
treatment_code | character varying(24) |
treatment_abbr | character varying(24) |
water_treatment | character varying(24) |
block_name | character varying(24) |
variable_name | character varying(24) |
variable_channel | character varying(24) |
variable_id | character varying(24) | not null default NULL::character varying
Indexes:
"data_key_pkey" PRIMARY KEY, btree (variable_id)
"data_key_lower_idx" btree (lower(block_name::text))
"data_key_lower_idx1" btree (lower(variable_channel::text))
b4warmed3=# SELECT COUNT(*) FROM data_key;
count
-------
4728
(1 row)
b4warmed3=# \d fifteen_min
Table "public.fifteen_min"
Column | Type | Modifiers
---------------------+-----------------------------+-----------
rowid | character varying(48) | not null
time2 | timestamp without time zone |
timestamp | timestamp without time zone |
block_name | character varying(8) |
stat_name | character varying(8) |
table_name | character varying(10) |
program | character varying(48) |
a_dc_avg1 | real |
a_dc_avg2 | real |
a_dc_avg3 | real |
a_dc_avg4 | real |
a_dif_avg1 | real |
a_dif_avg2 | real |
a_dif_avg3 | real |
a_dif_avg4 | real |
a_targettemp_avg1 | real |
a_targettemp_avg2 | real |
a_targettemp_avg3 | real |
a_targettemp_avg4 | real |
a_targettemp_avg5 | real |
a_targettemp_avg6 | real |
a_targettemp_avg7 | real |
a_targettemp_avg8 | real |
a_tc_avg1 | real |
a_tc_avg10 | real |
a_tc_avg11 | real |
a_tc_avg12 | real |
a_tc_avg2 | real |
a_tc_avg3 | real |
a_tc_avg4 | real |
a_tc_avg5 | real |
a_tc_avg6 | real |
a_tc_avg7 | real |
a_tc_avg8 | real |
a_tc_avg9 | real |
a_tc_std1 | real |
a_tc_std10 | real |
a_tc_std11 | real |
a_tc_std12 | real |
a_tc_std2 | real |
a_tc_std3 | real |
a_tc_std4 | real |
a_tc_std5 | real |
a_tc_std6 | real |
a_tc_std7 | real |
a_tc_std8 | real |
a_tc_std9 | real |
airtc_avg | real |
airtemp_avg | real |
airtemp_max | real |
airtemp_min | real |
all_avgt | real |
am25tref1 | real |
amb_a_avg | real |
amb_avg1 | real |
amb_avg2 | real |
amb_closed_avg | real |
b_dc_avg1 | real |
b_dc_avg2 | real |
b_dc_avg3 | real |
b_dc_avg4 | real |
batt_volt | real |
etcref_avg | real |
flag1 | integer |
flag10 | integer |
flag11 | integer |
flag12 | integer |
flag2 | integer |
flag3 | integer |
flag4 | integer |
flag5 | integer |
flag6 | integer |
flag7 | integer |
flag8 | integer |
flag9 | integer |
heat_a_avg1 | real |
heat_a_avg2 | real |
heat_a_avg3 | real |
heat_a_avg4 | real |
pid_lmt_avg1 | real |
pid_lmt_avg2 | real |
pid_lmt_avg3 | real |
pid_lmt_avg4 | real |
pid_out_avg1 | real |
pid_out_avg2 | real |
pid_out_avg3 | real |
pid_out_avg4 | real |
ptemp_avg | real |
rh | real |
runavga1 | real |
runavga2 | real |
runavga21 | real |
runavga22 | real |
runavga23 | real |
runavga24 | real |
runavga25 | real |
runavga26 | real |
runavga27 | real |
runavga28 | real |
runavga3 | real |
runavga4 | real |
runavga5 | real |
runavga6 | real |
runavga7 | real |
runavga8 | real |
runavgs_avg1 | real |
runavgs_avg10 | real |
runavgs_avg11 | real |
runavgs_avg12 | real |
runavgs_avg13 | real |
runavgs_avg14 | real |
runavgs_avg15 | real |
runavgs_avg16 | real |
runavgs_avg2 | real |
runavgs_avg3 | real |
runavgs_avg4 | real |
runavgs_avg5 | real |
runavgs_avg6 | real |
runavgs_avg7 | real |
runavgs_avg8 | real |
runavgs_avg9 | real |
s_all_avgt_avg | real |
s_dif1 | real |
s_dif2 | real |
s_dif3 | real |
s_dif4 | real |
s_pid_lmt_avg1 | real |
s_pid_lmt_avg2 | real |
s_pid_lmt_avg3 | real |
s_pid_lmt_avg4 | real |
s_pid_out_avg1 | real |
s_pid_out_avg2 | real |
s_pid_out_avg3 | real |
s_pid_out_avg4 | real |
s_scldout_avg1 | real |
s_scldout_avg2 | real |
s_scldout_avg3 | real |
s_scldout_avg4 | real |
s_sdm_out_avg1 | real |
s_sdm_out_avg2 | real |
s_sdm_out_avg3 | real |
s_sdm_out_avg4 | real |
s_tc_avg1 | real |
s_tc_avg10 | real |
s_tc_avg11 | real |
s_tc_avg12 | real |
s_tc_avg2 | real |
s_tc_avg3 | real |
s_tc_avg4 | real |
s_tc_avg5 | real |
s_tc_avg6 | real |
s_tc_avg7 | real |
s_tc_avg8 | real |
s_tc_avg9 | real |
s_tc_std1 | real |
s_tc_std10 | real |
s_tc_std11 | real |
s_tc_std12 | real |
s_tc_std2 | real |
s_tc_std3 | real |
s_tc_std4 | real |
s_tc_std5 | real |
s_tc_std6 | real |
s_tc_std7 | real |
s_tc_std8 | real |
s_tc_std9 | real |
sbtemp_avg1 | real |
sbtemp_avg2 | real |
sbtemp_avg3 | real |
sbtemp_avg4 | real |
sbtemp_avg5 | real |
sbtemp_avg6 | real |
sbtemp_avg7 | real |
sbtemp_avg8 | real |
scldout_avg1 | real |
scldout_avg2 | real |
scldout_avg3 | real |
scldout_avg4 | real |
sctemp_avg1 | real |
sctemp_avg10 | real |
sctemp_avg11 | real |
sctemp_avg12 | real |
sctemp_avg13 | real |
sctemp_avg14 | real |
sctemp_avg15 | real |
sctemp_avg16 | real |
sctemp_avg17 | real |
sctemp_avg18 | real |
sctemp_avg19 | real |
sctemp_avg2 | real |
sctemp_avg20 | real |
sctemp_avg21 | real |
sctemp_avg22 | real |
sctemp_avg23 | real |
sctemp_avg24 | real |
sctemp_avg3 | real |
sctemp_avg4 | real |
sctemp_avg5 | real |
sctemp_avg6 | real |
sctemp_avg7 | real |
sctemp_avg8 | real |
sctemp_avg9 | real |
sdm_out_avg1 | real |
sdm_out_avg2 | real |
sdm_out_avg3 | real |
sdm_out_avg4 | real |
stemp_avg1 | real |
stemp_avg10 | real |
stemp_avg11 | real |
stemp_avg12 | real |
stemp_avg13 | real |
stemp_avg14 | real |
stemp_avg15 | real |
stemp_avg16 | real |
stemp_avg2 | real |
stemp_avg3 | real |
stemp_avg4 | real |
stemp_avg5 | real |
stemp_avg6 | real |
stemp_avg7 | real |
stemp_avg8 | real |
stemp_avg9 | real |
tabove_avg1 | real |
tabove_avg2 | real |
tabove_avg3 | real |
tabove_avg4 | real |
tabove_avg5 | real |
tabove_avg6 | real |
tabove_avg7 | real |
tabove_avg8 | real |
targettemp_adj_avg1 | real |
targettemp_adj_avg2 | real |
targettemp_adj_avg3 | real |
targettemp_adj_avg4 | real |
targettemp_avg1 | real |
targettemp_avg2 | real |
targettemp_avg3 | real |
targettemp_avg4 | real |
targettemp_avg5 | real |
targettemp_avg6 | real |
targettemp_avg7 | real |
targettemp_avg8 | real |
tmv_avg1 | real |
tmv_avg2 | real |
tmv_avg3 | real |
tmv_avg4 | real |
tmv_avg5 | real |
tmv_avg6 | real |
tmv_avg7 | real |
tmv_avg8 | real |
tsoil_avg1 | real |
tsoil_avg2 | real |
tsoil_avg3 | real |
tsoil_avg4 | real |
tsoil_avg5 | real |
tsoil_avg6 | real |
tsoil_avg7 | real |
tsoil_avg8 | real |
tsoilr1 | real |
tsoilr2 | real |
tsoilr3 | real |
tsoilr4 | real |
tsoilr5 | real |
tsoilr6 | real |
tsoilr7 | real |
tsoilr8 | real |
vp_avg | real |
winddir_d1_wvt | real |
ws_ms_avg | real |
wtcref_avg | real |
Indexes:
"fifteen_min_pkey" PRIMARY KEY, btree (rowid)
"fifteen_min_lower_idx" btree (lower(block_name::text))
b4warmed3=# SELECT COUNT(*) FROM fifteen_min;
count
---------
1798711
(1 row)
b4warmed3=# \d fifteen_min_stacked_propper
View "public.fifteen_min_stacked_propper"
Column | Type | Modifiers
----------------+-----------------------------+-----------
rowid | character varying(48) |
time2 | timestamp without time zone |
block_name | character varying(8) |
table_name | character varying(10) |
batt_volt | real |
flag1 | integer |
flag2 | integer |
flag3 | integer |
airtc_avg | real |
airtemp_avg | real |
airtemp_max | real |
airtemp_min | real |
all_avgt | real |
am25tref1 | real |
ptemp_avg | real |
rh | real |
s_all_avgt_avg | real |
vp_avg | real |
winddir_d1_wvt | real |
ws_ms_avg | real |
variable | text |
value | real |
b4warmed3=# SELECT COUNT(*) FROM fifteen_min_stacked_propper;
count
-----------
428093218
(1 row)
What about index definition, Postgres version, config parameters? Hardware configuration would be helpful too. > -----Original Message----- > From: Kirk Wythers [mailto:kwythers@umn.edu] > Sent: Thursday, January 17, 2013 3:59 PM > To: Igor Neyman > Cc: Kirk Wythers; pgsql-general@postgresql.org > Subject: Re: [GENERAL] speeding up a join query that utilizes a view > > > > > Not enough information: > > > > Postgres version? > > OS? > > Some Postgres configuration parameters, specifically related to > "RESOURCE USAGE" and " QUERY TUNING"? > > Table structures (including indexes) for: > fifteen_min_stacked_propper, fifteen_min, and data_key? > > View definition for fifteen_min_stacked_view? > > > > > Here is some additional information: > > b4warmed3=# \d data_key > Table "public.data_key" > Column | Type | > Modifiers > ----------------------+-----------------------+------------------------ > - > ----------------------+-----------------------+----------------- > site | character varying(6) | > canopy | character varying(24) | > block | character(2) | > plot | character(2) | > measurement_interval | interval | > warming_treatment | character varying(24) | > treatment_code | character varying(24) | > treatment_abbr | character varying(24) | > water_treatment | character varying(24) | > block_name | character varying(24) | > variable_name | character varying(24) | > variable_channel | character varying(24) | > variable_id | character varying(24) | not null default > NULL::character varying > Indexes: > "data_key_pkey" PRIMARY KEY, btree (variable_id) > "data_key_lower_idx" btree (lower(block_name::text)) > "data_key_lower_idx1" btree (lower(variable_channel::text)) > > b4warmed3=# SELECT COUNT(*) FROM data_key; count > ------- > 4728 > (1 row) > > b4warmed3=# \d fifteen_min > Table "public.fifteen_min" > Column | Type | Modifiers > ---------------------+-----------------------------+----------- > rowid | character varying(48) | not null > time2 | timestamp without time zone | > timestamp | timestamp without time zone | > block_name | character varying(8) | > stat_name | character varying(8) | > table_name | character varying(10) | > program | character varying(48) | > a_dc_avg1 | real | > a_dc_avg2 | real | > a_dc_avg3 | real | > a_dc_avg4 | real | > a_dif_avg1 | real | > a_dif_avg2 | real | > a_dif_avg3 | real | > a_dif_avg4 | real | > a_targettemp_avg1 | real | > a_targettemp_avg2 | real | > a_targettemp_avg3 | real | > a_targettemp_avg4 | real | > a_targettemp_avg5 | real | > a_targettemp_avg6 | real | > a_targettemp_avg7 | real | > a_targettemp_avg8 | real | > a_tc_avg1 | real | > a_tc_avg10 | real | > a_tc_avg11 | real | > a_tc_avg12 | real | > a_tc_avg2 | real | > a_tc_avg3 | real | > a_tc_avg4 | real | > a_tc_avg5 | real | > a_tc_avg6 | real | > a_tc_avg7 | real | > a_tc_avg8 | real | > a_tc_avg9 | real | > a_tc_std1 | real | > a_tc_std10 | real | > a_tc_std11 | real | > a_tc_std12 | real | > a_tc_std2 | real | > a_tc_std3 | real | > a_tc_std4 | real | > a_tc_std5 | real | > a_tc_std6 | real | > a_tc_std7 | real | > a_tc_std8 | real | > a_tc_std9 | real | > airtc_avg | real | > airtemp_avg | real | > airtemp_max | real | > airtemp_min | real | > all_avgt | real | > am25tref1 | real | > amb_a_avg | real | > amb_avg1 | real | > amb_avg2 | real | > amb_closed_avg | real | > b_dc_avg1 | real | > b_dc_avg2 | real | > b_dc_avg3 | real | > b_dc_avg4 | real | > batt_volt | real | > etcref_avg | real | > flag1 | integer | > flag10 | integer | > flag11 | integer | > flag12 | integer | > flag2 | integer | > flag3 | integer | > flag4 | integer | > flag5 | integer | > flag6 | integer | > flag7 | integer | > flag8 | integer | > flag9 | integer | > heat_a_avg1 | real | > heat_a_avg2 | real | > heat_a_avg3 | real | > heat_a_avg4 | real | > pid_lmt_avg1 | real | > pid_lmt_avg2 | real | > pid_lmt_avg3 | real | > pid_lmt_avg4 | real | > pid_out_avg1 | real | > pid_out_avg2 | real | > pid_out_avg3 | real | > pid_out_avg4 | real | > ptemp_avg | real | > rh | real | > runavga1 | real | > runavga2 | real | > runavga21 | real | > runavga22 | real | > runavga23 | real | > runavga24 | real | > runavga25 | real | > runavga26 | real | > runavga27 | real | > runavga28 | real | > runavga3 | real | > runavga4 | real | > runavga5 | real | > runavga6 | real | > runavga7 | real | > runavga8 | real | > runavgs_avg1 | real | > runavgs_avg10 | real | > runavgs_avg11 | real | > runavgs_avg12 | real | > runavgs_avg13 | real | > runavgs_avg14 | real | > runavgs_avg15 | real | > runavgs_avg16 | real | > runavgs_avg2 | real | > runavgs_avg3 | real | > runavgs_avg4 | real | > runavgs_avg5 | real | > runavgs_avg6 | real | > runavgs_avg7 | real | > runavgs_avg8 | real | > runavgs_avg9 | real | > s_all_avgt_avg | real | > s_dif1 | real | > s_dif2 | real | > s_dif3 | real | > s_dif4 | real | > s_pid_lmt_avg1 | real | > s_pid_lmt_avg2 | real | > s_pid_lmt_avg3 | real | > s_pid_lmt_avg4 | real | > s_pid_out_avg1 | real | > s_pid_out_avg2 | real | > s_pid_out_avg3 | real | > s_pid_out_avg4 | real | > s_scldout_avg1 | real | > s_scldout_avg2 | real | > s_scldout_avg3 | real | > s_scldout_avg4 | real | > s_sdm_out_avg1 | real | > s_sdm_out_avg2 | real | > s_sdm_out_avg3 | real | > s_sdm_out_avg4 | real | > s_tc_avg1 | real | > s_tc_avg10 | real | > s_tc_avg11 | real | > s_tc_avg12 | real | > s_tc_avg2 | real | > s_tc_avg3 | real | > s_tc_avg4 | real | > s_tc_avg5 | real | > s_tc_avg6 | real | > s_tc_avg7 | real | > s_tc_avg8 | real | > s_tc_avg9 | real | > s_tc_std1 | real | > s_tc_std10 | real | > s_tc_std11 | real | > s_tc_std12 | real | > s_tc_std2 | real | > s_tc_std3 | real | > s_tc_std4 | real | > s_tc_std5 | real | > s_tc_std6 | real | > s_tc_std7 | real | > s_tc_std8 | real | > s_tc_std9 | real | > sbtemp_avg1 | real | > sbtemp_avg2 | real | > sbtemp_avg3 | real | > sbtemp_avg4 | real | > sbtemp_avg5 | real | > sbtemp_avg6 | real | > sbtemp_avg7 | real | > sbtemp_avg8 | real | > scldout_avg1 | real | > scldout_avg2 | real | > scldout_avg3 | real | > scldout_avg4 | real | > sctemp_avg1 | real | > sctemp_avg10 | real | > sctemp_avg11 | real | > sctemp_avg12 | real | > sctemp_avg13 | real | > sctemp_avg14 | real | > sctemp_avg15 | real | > sctemp_avg16 | real | > sctemp_avg17 | real | > sctemp_avg18 | real | > sctemp_avg19 | real | > sctemp_avg2 | real | > sctemp_avg20 | real | > sctemp_avg21 | real | > sctemp_avg22 | real | > sctemp_avg23 | real | > sctemp_avg24 | real | > sctemp_avg3 | real | > sctemp_avg4 | real | > sctemp_avg5 | real | > sctemp_avg6 | real | > sctemp_avg7 | real | > sctemp_avg8 | real | > sctemp_avg9 | real | > sdm_out_avg1 | real | > sdm_out_avg2 | real | > sdm_out_avg3 | real | > sdm_out_avg4 | real | > stemp_avg1 | real | > stemp_avg10 | real | > stemp_avg11 | real | > stemp_avg12 | real | > stemp_avg13 | real | > stemp_avg14 | real | > stemp_avg15 | real | > stemp_avg16 | real | > stemp_avg2 | real | > stemp_avg3 | real | > stemp_avg4 | real | > stemp_avg5 | real | > stemp_avg6 | real | > stemp_avg7 | real | > stemp_avg8 | real | > stemp_avg9 | real | > tabove_avg1 | real | > tabove_avg2 | real | > tabove_avg3 | real | > tabove_avg4 | real | > tabove_avg5 | real | > tabove_avg6 | real | > tabove_avg7 | real | > tabove_avg8 | real | > targettemp_adj_avg1 | real | > targettemp_adj_avg2 | real | > targettemp_adj_avg3 | real | > targettemp_adj_avg4 | real | > targettemp_avg1 | real | > targettemp_avg2 | real | > targettemp_avg3 | real | > targettemp_avg4 | real | > targettemp_avg5 | real | > targettemp_avg6 | real | > targettemp_avg7 | real | > targettemp_avg8 | real | > tmv_avg1 | real | > tmv_avg2 | real | > tmv_avg3 | real | > tmv_avg4 | real | > tmv_avg5 | real | > tmv_avg6 | real | > tmv_avg7 | real | > tmv_avg8 | real | > tsoil_avg1 | real | > tsoil_avg2 | real | > tsoil_avg3 | real | > tsoil_avg4 | real | > tsoil_avg5 | real | > tsoil_avg6 | real | > tsoil_avg7 | real | > tsoil_avg8 | real | > tsoilr1 | real | > tsoilr2 | real | > tsoilr3 | real | > tsoilr4 | real | > tsoilr5 | real | > tsoilr6 | real | > tsoilr7 | real | > tsoilr8 | real | > vp_avg | real | > winddir_d1_wvt | real | > ws_ms_avg | real | > wtcref_avg | real | > Indexes: > "fifteen_min_pkey" PRIMARY KEY, btree (rowid) > "fifteen_min_lower_idx" btree (lower(block_name::text)) > > b4warmed3=# SELECT COUNT(*) FROM fifteen_min; > count > --------- > 1798711 > (1 row) > > > b4warmed3=# \d fifteen_min_stacked_propper > View "public.fifteen_min_stacked_propper" > Column | Type | Modifiers > ----------------+-----------------------------+----------- > rowid | character varying(48) | > time2 | timestamp without time zone | > block_name | character varying(8) | > table_name | character varying(10) | > batt_volt | real | > flag1 | integer | > flag2 | integer | > flag3 | integer | > airtc_avg | real | > airtemp_avg | real | > airtemp_max | real | > airtemp_min | real | > all_avgt | real | > am25tref1 | real | > ptemp_avg | real | > rh | real | > s_all_avgt_avg | real | > vp_avg | real | > winddir_d1_wvt | real | > ws_ms_avg | real | > variable | text | > value | real | > > b4warmed3=# SELECT COUNT(*) FROM fifteen_min_stacked_propper; > count > ----------- > 428093218 > (1 row)
On Jan 17, 2013, at 3:51 PM, Igor Neyman <ineyman@perceptron.com> wrote: > What about index definition, Postgres version, config parameters? > Hardware configuration would be helpful too. > Sorry pg 9.1 OS X 10.8 server. 32 G ram 8 cores I thought what you meant by index definition is at the bottom of the \d table-name. For example: >> Indexes: >> "data_key_pkey" PRIMARY KEY, btree (variable_id) >> "data_key_lower_idx" btree (lower(block_name::text)) >> "data_key_lower_idx1" btree (lower(variable_channel::text)) on data_key. I'm not sure what you mean by config parameters? Output from pg_config? ~$ pg_config BINDIR = /usr/bin DOCDIR = /usr/share/doc/postgresql HTMLDIR = /usr/share/postgresql INCLUDEDIR = /usr/include PKGINCLUDEDIR = /usr/include/postgresql INCLUDEDIR-SERVER = /usr/include/postgresql/server LIBDIR = /usr/lib PKGLIBDIR = /usr/lib/postgresql LOCALEDIR = /usr/share/locale MANDIR = /usr/share/man SHAREDIR = /usr/share/postgresql SYSCONFDIR = /private/etc/postgresql PGXS = /usr/lib/postgresql/pgxs/src/makefiles/pgxs.mk CONFIGURE = '--infodir=/usr/share/info' '--disable-dependency-tracking' '--prefix=/usr' '--sbindir=/usr/libexec' '--sysconfdir=/private/etc''--mandir=/usr/share/man' '--localstatedir=/private/var/pgsql' '--htmldir=/usr/share/postgresql''--enable-thread-safety' '--enable-dtrace' '--with-tcl' '--with-perl' '--with-python' '--with-gssapi''--with-krb5' '--with-pam' '--with-ldap' '--with-bonjour' '--with-openssl' '--with-libxml' '--with-libxslt''--with-system-tzdata=/usr/share/zoneinfo' 'CC=/Applications/Xcode.app/Contents/Developer/Toolchains/OSX10.8.xctoolchain/usr/bin/cc''CFLAGS=-arch x86_64 -pipe -Os -g-Wall -Wno-deprecated-declarations' 'LDFLAGS=-arch x86_64 -pipe -Os -g -Wall -Wno-deprecated-declarations' 'LDFLAGS_EX=-mdynamic-no-pic' CC = /Applications/Xcode.app/Contents/Developer/Toolchains/OSX10.8.xctoolchain/usr/bin/cc CPPFLAGS = -I/usr/include/libxml2 CFLAGS = -arch x86_64 -pipe -Os -g -Wall -Wno-deprecated-declarations -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement-Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv CFLAGS_SL = LDFLAGS = -arch x86_64 -pipe -Os -g -Wall -Wno-deprecated-declarations -Wl,-dead_strip_dylibs LDFLAGS_EX = -mdynamic-no-pic LDFLAGS_SL = LIBS = -lpgport -lxslt -lxml2 -lpam -lssl -lcrypto -lgssapi_krb5 -lz -lreadline -lm VERSION = PostgreSQL 9.1.4 Does that help? > >> -----Original Message----- >> From: Kirk Wythers [mailto:kwythers@umn.edu] >> Sent: Thursday, January 17, 2013 3:59 PM >> To: Igor Neyman >> Cc: Kirk Wythers; pgsql-general@postgresql.org >> Subject: Re: [GENERAL] speeding up a join query that utilizes a view >> >>> >>> Not enough information: >>> >>> Postgres version? >>> OS? >>> Some Postgres configuration parameters, specifically related to >> "RESOURCE USAGE" and " QUERY TUNING"? >>> Table structures (including indexes) for: >> fifteen_min_stacked_propper, fifteen_min, and data_key? >>> View definition for fifteen_min_stacked_view? >>> >> >> >> Here is some additional information: >> >> b4warmed3=# \d data_key >> Table "public.data_key" >> Column | Type | >> Modifiers >> ----------------------+-----------------------+------------------------ >> - >> ----------------------+-----------------------+----------------- >> site | character varying(6) | >> canopy | character varying(24) | >> block | character(2) | >> plot | character(2) | >> measurement_interval | interval | >> warming_treatment | character varying(24) | >> treatment_code | character varying(24) | >> treatment_abbr | character varying(24) | >> water_treatment | character varying(24) | >> block_name | character varying(24) | >> variable_name | character varying(24) | >> variable_channel | character varying(24) | >> variable_id | character varying(24) | not null default >> NULL::character varying >> Indexes: >> "data_key_pkey" PRIMARY KEY, btree (variable_id) >> "data_key_lower_idx" btree (lower(block_name::text)) >> "data_key_lower_idx1" btree (lower(variable_channel::text)) >> >> b4warmed3=# SELECT COUNT(*) FROM data_key; count >> ------- >> 4728 >> (1 row) >> >> b4warmed3=# \d fifteen_min >> Table "public.fifteen_min" >> Column | Type | Modifiers >> ---------------------+-----------------------------+----------- >> rowid | character varying(48) | not null >> time2 | timestamp without time zone | >> timestamp | timestamp without time zone | >> block_name | character varying(8) | >> stat_name | character varying(8) | >> table_name | character varying(10) | >> program | character varying(48) | >> a_dc_avg1 | real | >> a_dc_avg2 | real | >> a_dc_avg3 | real | >> a_dc_avg4 | real | >> a_dif_avg1 | real | >> a_dif_avg2 | real | >> a_dif_avg3 | real | >> a_dif_avg4 | real | >> a_targettemp_avg1 | real | >> a_targettemp_avg2 | real | >> a_targettemp_avg3 | real | >> a_targettemp_avg4 | real | >> a_targettemp_avg5 | real | >> a_targettemp_avg6 | real | >> a_targettemp_avg7 | real | >> a_targettemp_avg8 | real | >> a_tc_avg1 | real | >> a_tc_avg10 | real | >> a_tc_avg11 | real | >> a_tc_avg12 | real | >> a_tc_avg2 | real | >> a_tc_avg3 | real | >> a_tc_avg4 | real | >> a_tc_avg5 | real | >> a_tc_avg6 | real | >> a_tc_avg7 | real | >> a_tc_avg8 | real | >> a_tc_avg9 | real | >> a_tc_std1 | real | >> a_tc_std10 | real | >> a_tc_std11 | real | >> a_tc_std12 | real | >> a_tc_std2 | real | >> a_tc_std3 | real | >> a_tc_std4 | real | >> a_tc_std5 | real | >> a_tc_std6 | real | >> a_tc_std7 | real | >> a_tc_std8 | real | >> a_tc_std9 | real | >> airtc_avg | real | >> airtemp_avg | real | >> airtemp_max | real | >> airtemp_min | real | >> all_avgt | real | >> am25tref1 | real | >> amb_a_avg | real | >> amb_avg1 | real | >> amb_avg2 | real | >> amb_closed_avg | real | >> b_dc_avg1 | real | >> b_dc_avg2 | real | >> b_dc_avg3 | real | >> b_dc_avg4 | real | >> batt_volt | real | >> etcref_avg | real | >> flag1 | integer | >> flag10 | integer | >> flag11 | integer | >> flag12 | integer | >> flag2 | integer | >> flag3 | integer | >> flag4 | integer | >> flag5 | integer | >> flag6 | integer | >> flag7 | integer | >> flag8 | integer | >> flag9 | integer | >> heat_a_avg1 | real | >> heat_a_avg2 | real | >> heat_a_avg3 | real | >> heat_a_avg4 | real | >> pid_lmt_avg1 | real | >> pid_lmt_avg2 | real | >> pid_lmt_avg3 | real | >> pid_lmt_avg4 | real | >> pid_out_avg1 | real | >> pid_out_avg2 | real | >> pid_out_avg3 | real | >> pid_out_avg4 | real | >> ptemp_avg | real | >> rh | real | >> runavga1 | real | >> runavga2 | real | >> runavga21 | real | >> runavga22 | real | >> runavga23 | real | >> runavga24 | real | >> runavga25 | real | >> runavga26 | real | >> runavga27 | real | >> runavga28 | real | >> runavga3 | real | >> runavga4 | real | >> runavga5 | real | >> runavga6 | real | >> runavga7 | real | >> runavga8 | real | >> runavgs_avg1 | real | >> runavgs_avg10 | real | >> runavgs_avg11 | real | >> runavgs_avg12 | real | >> runavgs_avg13 | real | >> runavgs_avg14 | real | >> runavgs_avg15 | real | >> runavgs_avg16 | real | >> runavgs_avg2 | real | >> runavgs_avg3 | real | >> runavgs_avg4 | real | >> runavgs_avg5 | real | >> runavgs_avg6 | real | >> runavgs_avg7 | real | >> runavgs_avg8 | real | >> runavgs_avg9 | real | >> s_all_avgt_avg | real | >> s_dif1 | real | >> s_dif2 | real | >> s_dif3 | real | >> s_dif4 | real | >> s_pid_lmt_avg1 | real | >> s_pid_lmt_avg2 | real | >> s_pid_lmt_avg3 | real | >> s_pid_lmt_avg4 | real | >> s_pid_out_avg1 | real | >> s_pid_out_avg2 | real | >> s_pid_out_avg3 | real | >> s_pid_out_avg4 | real | >> s_scldout_avg1 | real | >> s_scldout_avg2 | real | >> s_scldout_avg3 | real | >> s_scldout_avg4 | real | >> s_sdm_out_avg1 | real | >> s_sdm_out_avg2 | real | >> s_sdm_out_avg3 | real | >> s_sdm_out_avg4 | real | >> s_tc_avg1 | real | >> s_tc_avg10 | real | >> s_tc_avg11 | real | >> s_tc_avg12 | real | >> s_tc_avg2 | real | >> s_tc_avg3 | real | >> s_tc_avg4 | real | >> s_tc_avg5 | real | >> s_tc_avg6 | real | >> s_tc_avg7 | real | >> s_tc_avg8 | real | >> s_tc_avg9 | real | >> s_tc_std1 | real | >> s_tc_std10 | real | >> s_tc_std11 | real | >> s_tc_std12 | real | >> s_tc_std2 | real | >> s_tc_std3 | real | >> s_tc_std4 | real | >> s_tc_std5 | real | >> s_tc_std6 | real | >> s_tc_std7 | real | >> s_tc_std8 | real | >> s_tc_std9 | real | >> sbtemp_avg1 | real | >> sbtemp_avg2 | real | >> sbtemp_avg3 | real | >> sbtemp_avg4 | real | >> sbtemp_avg5 | real | >> sbtemp_avg6 | real | >> sbtemp_avg7 | real | >> sbtemp_avg8 | real | >> scldout_avg1 | real | >> scldout_avg2 | real | >> scldout_avg3 | real | >> scldout_avg4 | real | >> sctemp_avg1 | real | >> sctemp_avg10 | real | >> sctemp_avg11 | real | >> sctemp_avg12 | real | >> sctemp_avg13 | real | >> sctemp_avg14 | real | >> sctemp_avg15 | real | >> sctemp_avg16 | real | >> sctemp_avg17 | real | >> sctemp_avg18 | real | >> sctemp_avg19 | real | >> sctemp_avg2 | real | >> sctemp_avg20 | real | >> sctemp_avg21 | real | >> sctemp_avg22 | real | >> sctemp_avg23 | real | >> sctemp_avg24 | real | >> sctemp_avg3 | real | >> sctemp_avg4 | real | >> sctemp_avg5 | real | >> sctemp_avg6 | real | >> sctemp_avg7 | real | >> sctemp_avg8 | real | >> sctemp_avg9 | real | >> sdm_out_avg1 | real | >> sdm_out_avg2 | real | >> sdm_out_avg3 | real | >> sdm_out_avg4 | real | >> stemp_avg1 | real | >> stemp_avg10 | real | >> stemp_avg11 | real | >> stemp_avg12 | real | >> stemp_avg13 | real | >> stemp_avg14 | real | >> stemp_avg15 | real | >> stemp_avg16 | real | >> stemp_avg2 | real | >> stemp_avg3 | real | >> stemp_avg4 | real | >> stemp_avg5 | real | >> stemp_avg6 | real | >> stemp_avg7 | real | >> stemp_avg8 | real | >> stemp_avg9 | real | >> tabove_avg1 | real | >> tabove_avg2 | real | >> tabove_avg3 | real | >> tabove_avg4 | real | >> tabove_avg5 | real | >> tabove_avg6 | real | >> tabove_avg7 | real | >> tabove_avg8 | real | >> targettemp_adj_avg1 | real | >> targettemp_adj_avg2 | real | >> targettemp_adj_avg3 | real | >> targettemp_adj_avg4 | real | >> targettemp_avg1 | real | >> targettemp_avg2 | real | >> targettemp_avg3 | real | >> targettemp_avg4 | real | >> targettemp_avg5 | real | >> targettemp_avg6 | real | >> targettemp_avg7 | real | >> targettemp_avg8 | real | >> tmv_avg1 | real | >> tmv_avg2 | real | >> tmv_avg3 | real | >> tmv_avg4 | real | >> tmv_avg5 | real | >> tmv_avg6 | real | >> tmv_avg7 | real | >> tmv_avg8 | real | >> tsoil_avg1 | real | >> tsoil_avg2 | real | >> tsoil_avg3 | real | >> tsoil_avg4 | real | >> tsoil_avg5 | real | >> tsoil_avg6 | real | >> tsoil_avg7 | real | >> tsoil_avg8 | real | >> tsoilr1 | real | >> tsoilr2 | real | >> tsoilr3 | real | >> tsoilr4 | real | >> tsoilr5 | real | >> tsoilr6 | real | >> tsoilr7 | real | >> tsoilr8 | real | >> vp_avg | real | >> winddir_d1_wvt | real | >> ws_ms_avg | real | >> wtcref_avg | real | >> Indexes: >> "fifteen_min_pkey" PRIMARY KEY, btree (rowid) >> "fifteen_min_lower_idx" btree (lower(block_name::text)) >> >> b4warmed3=# SELECT COUNT(*) FROM fifteen_min; >> count >> --------- >> 1798711 >> (1 row) >> >> >> b4warmed3=# \d fifteen_min_stacked_propper >> View "public.fifteen_min_stacked_propper" >> Column | Type | Modifiers >> ----------------+-----------------------------+----------- >> rowid | character varying(48) | >> time2 | timestamp without time zone | >> block_name | character varying(8) | >> table_name | character varying(10) | >> batt_volt | real | >> flag1 | integer | >> flag2 | integer | >> flag3 | integer | >> airtc_avg | real | >> airtemp_avg | real | >> airtemp_max | real | >> airtemp_min | real | >> all_avgt | real | >> am25tref1 | real | >> ptemp_avg | real | >> rh | real | >> s_all_avgt_avg | real | >> vp_avg | real | >> winddir_d1_wvt | real | >> ws_ms_avg | real | >> variable | text | >> value | real | >> >> b4warmed3=# SELECT COUNT(*) FROM fifteen_min_stacked_propper; >> count >> ----------- >> 428093218 >> (1 row)
Yes, my mistake, I meant to ask about fifteen_min_stacked_view definition, and Postgres parameters from postgresql.conf configurationfile, at least those - modified from default setting and related to "resource consumption" and "query tuning". Regards, Igor Neyman > -----Original Message----- > From: Kirk Wythers [mailto:kwythers@umn.edu] > Sent: Thursday, January 17, 2013 5:05 PM > To: Igor Neyman > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] speeding up a join query that utilizes a view > > > On Jan 17, 2013, at 3:51 PM, Igor Neyman <ineyman@perceptron.com> > wrote: > > > What about index definition, Postgres version, config parameters? > > Hardware configuration would be helpful too. > > > > Sorry > > pg 9.1 > > OS X 10.8 server. > 32 G ram 8 cores > > I thought what you meant by index definition is at the bottom of the \d > table-name. For example: > >> Indexes: > >> "data_key_pkey" PRIMARY KEY, btree (variable_id) > >> "data_key_lower_idx" btree (lower(block_name::text)) > >> "data_key_lower_idx1" btree (lower(variable_channel::text)) > > on data_key. > > I'm not sure what you mean by config parameters? Output from pg_config? > > ~$ pg_config > BINDIR = /usr/bin > DOCDIR = /usr/share/doc/postgresql > HTMLDIR = /usr/share/postgresql > INCLUDEDIR = /usr/include > PKGINCLUDEDIR = /usr/include/postgresql > INCLUDEDIR-SERVER = /usr/include/postgresql/server LIBDIR = /usr/lib > PKGLIBDIR = /usr/lib/postgresql LOCALEDIR = /usr/share/locale MANDIR = > /usr/share/man SHAREDIR = /usr/share/postgresql SYSCONFDIR = > /private/etc/postgresql PGXS = > /usr/lib/postgresql/pgxs/src/makefiles/pgxs.mk > CONFIGURE = '--infodir=/usr/share/info' '--disable-dependency-tracking' > '--prefix=/usr' '--sbindir=/usr/libexec' '--sysconfdir=/private/etc' '- > -mandir=/usr/share/man' '--localstatedir=/private/var/pgsql' '-- > htmldir=/usr/share/postgresql' '--enable-thread-safety' '--enable- > dtrace' '--with-tcl' '--with-perl' '--with-python' '--with-gssapi' '-- > with-krb5' '--with-pam' '--with-ldap' '--with-bonjour' '--with-openssl' > '--with-libxml' '--with-libxslt' '--with-system- > tzdata=/usr/share/zoneinfo' > 'CC=/Applications/Xcode.app/Contents/Developer/Toolchains/OSX10.8.xctoo > lchain/usr/bin/cc' 'CFLAGS=-arch x86_64 -pipe -Os -g -Wall -Wno- > deprecated-declarations' 'LDFLAGS=-arch x86_64 -pipe -Os -g -Wall -Wno- > deprecated-declarations' 'LDFLAGS_EX=-mdynamic-no-pic' > CC = > /Applications/Xcode.app/Contents/Developer/Toolchains/OSX10.8.xctoolcha > in/usr/bin/cc > CPPFLAGS = -I/usr/include/libxml2 > CFLAGS = -arch x86_64 -pipe -Os -g -Wall -Wno-deprecated-declarations - > Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement > -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv CFLAGS_SL > = LDFLAGS = -arch x86_64 -pipe -Os -g -Wall -Wno-deprecated- > declarations -Wl,-dead_strip_dylibs LDFLAGS_EX = -mdynamic-no-pic > LDFLAGS_SL = LIBS = -lpgport -lxslt -lxml2 -lpam -lssl -lcrypto - > lgssapi_krb5 -lz -lreadline -lm VERSION = PostgreSQL 9.1.4 > > Does that help? > > > > >> -----Original Message----- > >> From: Kirk Wythers [mailto:kwythers@umn.edu] > >> Sent: Thursday, January 17, 2013 3:59 PM > >> To: Igor Neyman > >> Cc: Kirk Wythers; pgsql-general@postgresql.org > >> Subject: Re: [GENERAL] speeding up a join query that utilizes a view > >> > >>> > >>> Not enough information: > >>> > >>> Postgres version? > >>> OS? > >>> Some Postgres configuration parameters, specifically related to > >> "RESOURCE USAGE" and " QUERY TUNING"? > >>> Table structures (including indexes) for: > >> fifteen_min_stacked_propper, fifteen_min, and data_key? > >>> View definition for fifteen_min_stacked_view? > >>> > >> > >> > >> Here is some additional information: > >> > >> b4warmed3=# \d data_key > >> Table "public.data_key" > >> Column | Type | > >> Modifiers > >> ----------------------+-----------------------+--------------------- > - > >> ----------------------+-----------------------+-- > >> - > >> ----------------------+-----------------------+----------------- > >> site | character varying(6) | > >> canopy | character varying(24) | > >> block | character(2) | > >> plot | character(2) | > >> measurement_interval | interval | > >> warming_treatment | character varying(24) | > >> treatment_code | character varying(24) | > >> treatment_abbr | character varying(24) | > >> water_treatment | character varying(24) | > >> block_name | character varying(24) | > >> variable_name | character varying(24) | > >> variable_channel | character varying(24) | > >> variable_id | character varying(24) | not null default > >> NULL::character varying > >> Indexes: > >> "data_key_pkey" PRIMARY KEY, btree (variable_id) > >> "data_key_lower_idx" btree (lower(block_name::text)) > >> "data_key_lower_idx1" btree (lower(variable_channel::text)) > >> > >> b4warmed3=# SELECT COUNT(*) FROM data_key; count > >> ------- > >> 4728 > >> (1 row) > >> > >> b4warmed3=# \d fifteen_min > >> Table "public.fifteen_min" > >> Column | Type | Modifiers > >> ---------------------+-----------------------------+----------- > >> rowid | character varying(48) | not null > >> time2 | timestamp without time zone | > >> timestamp | timestamp without time zone | > >> block_name | character varying(8) | > >> stat_name | character varying(8) | > >> table_name | character varying(10) | > >> program | character varying(48) | > >> a_dc_avg1 | real | > >> a_dc_avg2 | real | > >> a_dc_avg3 | real | > >> a_dc_avg4 | real | > >> a_dif_avg1 | real | > >> a_dif_avg2 | real | > >> a_dif_avg3 | real | > >> a_dif_avg4 | real | > >> a_targettemp_avg1 | real | > >> a_targettemp_avg2 | real | > >> a_targettemp_avg3 | real | > >> a_targettemp_avg4 | real | > >> a_targettemp_avg5 | real | > >> a_targettemp_avg6 | real | > >> a_targettemp_avg7 | real | > >> a_targettemp_avg8 | real | > >> a_tc_avg1 | real | > >> a_tc_avg10 | real | > >> a_tc_avg11 | real | > >> a_tc_avg12 | real | > >> a_tc_avg2 | real | > >> a_tc_avg3 | real | > >> a_tc_avg4 | real | > >> a_tc_avg5 | real | > >> a_tc_avg6 | real | > >> a_tc_avg7 | real | > >> a_tc_avg8 | real | > >> a_tc_avg9 | real | > >> a_tc_std1 | real | > >> a_tc_std10 | real | > >> a_tc_std11 | real | > >> a_tc_std12 | real | > >> a_tc_std2 | real | > >> a_tc_std3 | real | > >> a_tc_std4 | real | > >> a_tc_std5 | real | > >> a_tc_std6 | real | > >> a_tc_std7 | real | > >> a_tc_std8 | real | > >> a_tc_std9 | real | > >> airtc_avg | real | > >> airtemp_avg | real | > >> airtemp_max | real | > >> airtemp_min | real | > >> all_avgt | real | > >> am25tref1 | real | > >> amb_a_avg | real | > >> amb_avg1 | real | > >> amb_avg2 | real | > >> amb_closed_avg | real | > >> b_dc_avg1 | real | > >> b_dc_avg2 | real | > >> b_dc_avg3 | real | > >> b_dc_avg4 | real | > >> batt_volt | real | > >> etcref_avg | real | > >> flag1 | integer | > >> flag10 | integer | > >> flag11 | integer | > >> flag12 | integer | > >> flag2 | integer | > >> flag3 | integer | > >> flag4 | integer | > >> flag5 | integer | > >> flag6 | integer | > >> flag7 | integer | > >> flag8 | integer | > >> flag9 | integer | > >> heat_a_avg1 | real | > >> heat_a_avg2 | real | > >> heat_a_avg3 | real | > >> heat_a_avg4 | real | > >> pid_lmt_avg1 | real | > >> pid_lmt_avg2 | real | > >> pid_lmt_avg3 | real | > >> pid_lmt_avg4 | real | > >> pid_out_avg1 | real | > >> pid_out_avg2 | real | > >> pid_out_avg3 | real | > >> pid_out_avg4 | real | > >> ptemp_avg | real | > >> rh | real | > >> runavga1 | real | > >> runavga2 | real | > >> runavga21 | real | > >> runavga22 | real | > >> runavga23 | real | > >> runavga24 | real | > >> runavga25 | real | > >> runavga26 | real | > >> runavga27 | real | > >> runavga28 | real | > >> runavga3 | real | > >> runavga4 | real | > >> runavga5 | real | > >> runavga6 | real | > >> runavga7 | real | > >> runavga8 | real | > >> runavgs_avg1 | real | > >> runavgs_avg10 | real | > >> runavgs_avg11 | real | > >> runavgs_avg12 | real | > >> runavgs_avg13 | real | > >> runavgs_avg14 | real | > >> runavgs_avg15 | real | > >> runavgs_avg16 | real | > >> runavgs_avg2 | real | > >> runavgs_avg3 | real | > >> runavgs_avg4 | real | > >> runavgs_avg5 | real | > >> runavgs_avg6 | real | > >> runavgs_avg7 | real | > >> runavgs_avg8 | real | > >> runavgs_avg9 | real | > >> s_all_avgt_avg | real | > >> s_dif1 | real | > >> s_dif2 | real | > >> s_dif3 | real | > >> s_dif4 | real | > >> s_pid_lmt_avg1 | real | > >> s_pid_lmt_avg2 | real | > >> s_pid_lmt_avg3 | real | > >> s_pid_lmt_avg4 | real | > >> s_pid_out_avg1 | real | > >> s_pid_out_avg2 | real | > >> s_pid_out_avg3 | real | > >> s_pid_out_avg4 | real | > >> s_scldout_avg1 | real | > >> s_scldout_avg2 | real | > >> s_scldout_avg3 | real | > >> s_scldout_avg4 | real | > >> s_sdm_out_avg1 | real | > >> s_sdm_out_avg2 | real | > >> s_sdm_out_avg3 | real | > >> s_sdm_out_avg4 | real | > >> s_tc_avg1 | real | > >> s_tc_avg10 | real | > >> s_tc_avg11 | real | > >> s_tc_avg12 | real | > >> s_tc_avg2 | real | > >> s_tc_avg3 | real | > >> s_tc_avg4 | real | > >> s_tc_avg5 | real | > >> s_tc_avg6 | real | > >> s_tc_avg7 | real | > >> s_tc_avg8 | real | > >> s_tc_avg9 | real | > >> s_tc_std1 | real | > >> s_tc_std10 | real | > >> s_tc_std11 | real | > >> s_tc_std12 | real | > >> s_tc_std2 | real | > >> s_tc_std3 | real | > >> s_tc_std4 | real | > >> s_tc_std5 | real | > >> s_tc_std6 | real | > >> s_tc_std7 | real | > >> s_tc_std8 | real | > >> s_tc_std9 | real | > >> sbtemp_avg1 | real | > >> sbtemp_avg2 | real | > >> sbtemp_avg3 | real | > >> sbtemp_avg4 | real | > >> sbtemp_avg5 | real | > >> sbtemp_avg6 | real | > >> sbtemp_avg7 | real | > >> sbtemp_avg8 | real | > >> scldout_avg1 | real | > >> scldout_avg2 | real | > >> scldout_avg3 | real | > >> scldout_avg4 | real | > >> sctemp_avg1 | real | > >> sctemp_avg10 | real | > >> sctemp_avg11 | real | > >> sctemp_avg12 | real | > >> sctemp_avg13 | real | > >> sctemp_avg14 | real | > >> sctemp_avg15 | real | > >> sctemp_avg16 | real | > >> sctemp_avg17 | real | > >> sctemp_avg18 | real | > >> sctemp_avg19 | real | > >> sctemp_avg2 | real | > >> sctemp_avg20 | real | > >> sctemp_avg21 | real | > >> sctemp_avg22 | real | > >> sctemp_avg23 | real | > >> sctemp_avg24 | real | > >> sctemp_avg3 | real | > >> sctemp_avg4 | real | > >> sctemp_avg5 | real | > >> sctemp_avg6 | real | > >> sctemp_avg7 | real | > >> sctemp_avg8 | real | > >> sctemp_avg9 | real | > >> sdm_out_avg1 | real | > >> sdm_out_avg2 | real | > >> sdm_out_avg3 | real | > >> sdm_out_avg4 | real | > >> stemp_avg1 | real | > >> stemp_avg10 | real | > >> stemp_avg11 | real | > >> stemp_avg12 | real | > >> stemp_avg13 | real | > >> stemp_avg14 | real | > >> stemp_avg15 | real | > >> stemp_avg16 | real | > >> stemp_avg2 | real | > >> stemp_avg3 | real | > >> stemp_avg4 | real | > >> stemp_avg5 | real | > >> stemp_avg6 | real | > >> stemp_avg7 | real | > >> stemp_avg8 | real | > >> stemp_avg9 | real | > >> tabove_avg1 | real | > >> tabove_avg2 | real | > >> tabove_avg3 | real | > >> tabove_avg4 | real | > >> tabove_avg5 | real | > >> tabove_avg6 | real | > >> tabove_avg7 | real | > >> tabove_avg8 | real | > >> targettemp_adj_avg1 | real | > >> targettemp_adj_avg2 | real | > >> targettemp_adj_avg3 | real | > >> targettemp_adj_avg4 | real | > >> targettemp_avg1 | real | > >> targettemp_avg2 | real | > >> targettemp_avg3 | real | > >> targettemp_avg4 | real | > >> targettemp_avg5 | real | > >> targettemp_avg6 | real | > >> targettemp_avg7 | real | > >> targettemp_avg8 | real | > >> tmv_avg1 | real | > >> tmv_avg2 | real | > >> tmv_avg3 | real | > >> tmv_avg4 | real | > >> tmv_avg5 | real | > >> tmv_avg6 | real | > >> tmv_avg7 | real | > >> tmv_avg8 | real | > >> tsoil_avg1 | real | > >> tsoil_avg2 | real | > >> tsoil_avg3 | real | > >> tsoil_avg4 | real | > >> tsoil_avg5 | real | > >> tsoil_avg6 | real | > >> tsoil_avg7 | real | > >> tsoil_avg8 | real | > >> tsoilr1 | real | > >> tsoilr2 | real | > >> tsoilr3 | real | > >> tsoilr4 | real | > >> tsoilr5 | real | > >> tsoilr6 | real | > >> tsoilr7 | real | > >> tsoilr8 | real | > >> vp_avg | real | > >> winddir_d1_wvt | real | > >> ws_ms_avg | real | > >> wtcref_avg | real | > >> Indexes: > >> "fifteen_min_pkey" PRIMARY KEY, btree (rowid) > >> "fifteen_min_lower_idx" btree (lower(block_name::text)) > >> > >> b4warmed3=# SELECT COUNT(*) FROM fifteen_min; count > >> --------- > >> 1798711 > >> (1 row) > >> > >> > >> b4warmed3=# \d fifteen_min_stacked_propper > >> View "public.fifteen_min_stacked_propper" > >> Column | Type | Modifiers > >> ----------------+-----------------------------+----------- > >> rowid | character varying(48) | > >> time2 | timestamp without time zone | > >> block_name | character varying(8) | > >> table_name | character varying(10) | > >> batt_volt | real | > >> flag1 | integer | > >> flag2 | integer | > >> flag3 | integer | > >> airtc_avg | real | > >> airtemp_avg | real | > >> airtemp_max | real | > >> airtemp_min | real | > >> all_avgt | real | > >> am25tref1 | real | > >> ptemp_avg | real | > >> rh | real | > >> s_all_avgt_avg | real | > >> vp_avg | real | > >> winddir_d1_wvt | real | > >> ws_ms_avg | real | > >> variable | text | > >> value | real | > >> > >> b4warmed3=# SELECT COUNT(*) FROM fifteen_min_stacked_propper; > >> count > >> ----------- > >> 428093218 > >> (1 row)
On Jan 18, 2013, at 8:10 AM, Igor Neyman <ineyman@perceptron.com> wrote:
> Yes, my mistake, I meant to ask about fifteen_min_stacked_view definition, and Postgres parameters from
postgresql.confconfiguration file, at least those - modified from default setting and related to "resource consumption"
and"query tuning".
>
> Regards,
> Igor Neyman
Here some extra bits form the postgresql.conf file. As you can see, I have not changed much from the default settings.
#------------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------------
# - Memory -
shared_buffers = 3GB # 7GB # min 128kB
# (change requires restart)
temp_buffers = 80MB # 8MB # min 800kB
#max_prepared_transactions = 0 # zero disables the feature
# (change requires restart)
# Note: Increasing max_prepared_transactions costs ~600 bytes of shared memory
# per transaction slot, plus lock space (see max_locks_per_transaction).
# It is not advisable to set max_prepared_transactions nonzero unless you
# actively intend to use prepared transactions.
work_mem = 64MB #8MB # min 64kB
maintenance_work_mem = 128MB # min 1MB
#max_stack_depth = 2MB # min 100kB
# - Kernel Resource Usage -
#max_files_per_process = 1000 # min 25
# (change requires restart)
#shared_preload_libraries = '' # (change requires restart)
# - Cost-Based Vacuum Delay -
#vacuum_cost_delay = 0ms # 0-100 milliseconds
#vacuum_cost_page_hit = 1 # 0-10000 credits
#vacuum_cost_page_miss = 10 # 0-10000 credits
#vacuum_cost_page_dirty = 20 # 0-10000 credits
#vacuum_cost_limit = 200 # 1-10000 credits
# - Background Writer -
#bgwriter_delay = 200ms # 10-10000ms between rounds
#bgwriter_lru_maxpages = 100 # 0-1000 max buffers written/round
#bgwriter_lru_multiplier = 2.0 # 0-10.0 multipler on buffers scanned/round
# - Asynchronous Behavior -
#effective_io_concurrency = 1 # 1-1000. 0 disables prefetching
#------------------------------------------------------------------------------
# QUERY TUNING
#------------------------------------------------------------------------------
# - Planner Method Configuration -
#enable_bitmapscan = on
#enable_hashagg = on
#enable_hashjoin = on
#enable_indexscan = on
#enable_material = on
#enable_mergejoin = on
#enable_nestloop = on
#enable_seqscan = on
#enable_sort = on
#enable_tidscan = on
# - Planner Cost Constants -
#seq_page_cost = 1.0 # measured on an arbitrary scale
#random_page_cost = 4.0 # same scale as above
#cpu_tuple_cost = 0.01 # same scale as above
#cpu_index_tuple_cost = 0.005 # same scale as above
#cpu_operator_cost = 0.0025 # same scale as above
effective_cache_size = 6GB #13GB
# - Genetic Query Optimizer -
#geqo = on
#geqo_threshold = 12
#geqo_effort = 5 # range 1-10
#geqo_pool_size = 0 # selects default based on effort
#geqo_generations = 0 # selects default based on effort
#geqo_selection_bias = 2.0 # range 1.5-2.0
#geqo_seed = 0.0 # range 0.0-1.0
# - Other Planner Options -
#default_statistics_target = 100 # range 1-10000
#constraint_exclusion = partition # on, off, or partition
#cursor_tuple_fraction = 0.1 # range 0.0-1.0
#from_collapse_limit = 8
#join_collapse_limit = 8 # 1 disables collapsing of explicit
# JOIN clauses
Here is a snip from earlier that includes info about both the table that is used to build the view and the view. In
short,I use the UNNEST function to un-pivot all the variables of interest in the "fifteen_min" table into the columns
"variable"and "value" in the "fifteen_min_stacked_proper" view.
Thanks again.
Kirk
b4warmed3=# \d fifteen_min
Table "public.fifteen_min"
Column | Type | Modifiers
---------------------+-----------------------------+-----------
rowid | character varying(48) | not null
time2 | timestamp without time zone |
timestamp | timestamp without time zone |
block_name | character varying(8) |
stat_name | character varying(8) |
table_name | character varying(10) |
program | character varying(48) |
a_dc_avg1 | real |
a_dc_avg2 | real |
a_dc_avg3 | real |
a_dc_avg4 | real |
a_dif_avg1 | real |
a_dif_avg2 | real |
a_dif_avg3 | real |
a_dif_avg4 | real |
a_targettemp_avg1 | real |
a_targettemp_avg2 | real |
a_targettemp_avg3 | real |
a_targettemp_avg4 | real |
a_targettemp_avg5 | real |
a_targettemp_avg6 | real |
a_targettemp_avg7 | real |
a_targettemp_avg8 | real |
a_tc_avg1 | real |
a_tc_avg10 | real |
a_tc_avg11 | real |
a_tc_avg12 | real |
a_tc_avg2 | real |
a_tc_avg3 | real |
a_tc_avg4 | real |
a_tc_avg5 | real |
a_tc_avg6 | real |
a_tc_avg7 | real |
a_tc_avg8 | real |
a_tc_avg9 | real |
a_tc_std1 | real |
a_tc_std10 | real |
a_tc_std11 | real |
a_tc_std12 | real |
a_tc_std2 | real |
a_tc_std3 | real |
a_tc_std4 | real |
a_tc_std5 | real |
a_tc_std6 | real |
a_tc_std7 | real |
a_tc_std8 | real |
a_tc_std9 | real |
airtc_avg | real |
airtemp_avg | real |
airtemp_max | real |
airtemp_min | real |
all_avgt | real |
am25tref1 | real |
amb_a_avg | real |
amb_avg1 | real |
amb_avg2 | real |
amb_closed_avg | real |
b_dc_avg1 | real |
b_dc_avg2 | real |
b_dc_avg3 | real |
b_dc_avg4 | real |
batt_volt | real |
etcref_avg | real |
flag1 | integer |
flag10 | integer |
flag11 | integer |
flag12 | integer |
flag2 | integer |
flag3 | integer |
flag4 | integer |
flag5 | integer |
flag6 | integer |
flag7 | integer |
flag8 | integer |
flag9 | integer |
heat_a_avg1 | real |
heat_a_avg2 | real |
heat_a_avg3 | real |
heat_a_avg4 | real |
pid_lmt_avg1 | real |
pid_lmt_avg2 | real |
pid_lmt_avg3 | real |
pid_lmt_avg4 | real |
pid_out_avg1 | real |
pid_out_avg2 | real |
pid_out_avg3 | real |
pid_out_avg4 | real |
ptemp_avg | real |
rh | real |
runavga1 | real |
runavga2 | real |
runavga21 | real |
runavga22 | real |
runavga23 | real |
runavga24 | real |
runavga25 | real |
runavga26 | real |
runavga27 | real |
runavga28 | real |
runavga3 | real |
runavga4 | real |
runavga5 | real |
runavga6 | real |
runavga7 | real |
runavga8 | real |
runavgs_avg1 | real |
runavgs_avg10 | real |
runavgs_avg11 | real |
runavgs_avg12 | real |
runavgs_avg13 | real |
runavgs_avg14 | real |
runavgs_avg15 | real |
runavgs_avg16 | real |
runavgs_avg2 | real |
runavgs_avg3 | real |
runavgs_avg4 | real |
runavgs_avg5 | real |
runavgs_avg6 | real |
runavgs_avg7 | real |
runavgs_avg8 | real |
runavgs_avg9 | real |
s_all_avgt_avg | real |
s_dif1 | real |
s_dif2 | real |
s_dif3 | real |
s_dif4 | real |
s_pid_lmt_avg1 | real |
s_pid_lmt_avg2 | real |
s_pid_lmt_avg3 | real |
s_pid_lmt_avg4 | real |
s_pid_out_avg1 | real |
s_pid_out_avg2 | real |
s_pid_out_avg3 | real |
s_pid_out_avg4 | real |
s_scldout_avg1 | real |
s_scldout_avg2 | real |
s_scldout_avg3 | real |
s_scldout_avg4 | real |
s_sdm_out_avg1 | real |
s_sdm_out_avg2 | real |
s_sdm_out_avg3 | real |
s_sdm_out_avg4 | real |
s_tc_avg1 | real |
s_tc_avg10 | real |
s_tc_avg11 | real |
s_tc_avg12 | real |
s_tc_avg2 | real |
s_tc_avg3 | real |
s_tc_avg4 | real |
s_tc_avg5 | real |
s_tc_avg6 | real |
s_tc_avg7 | real |
s_tc_avg8 | real |
s_tc_avg9 | real |
s_tc_std1 | real |
s_tc_std10 | real |
s_tc_std11 | real |
s_tc_std12 | real |
s_tc_std2 | real |
s_tc_std3 | real |
s_tc_std4 | real |
s_tc_std5 | real |
s_tc_std6 | real |
s_tc_std7 | real |
s_tc_std8 | real |
s_tc_std9 | real |
sbtemp_avg1 | real |
sbtemp_avg2 | real |
sbtemp_avg3 | real |
sbtemp_avg4 | real |
sbtemp_avg5 | real |
sbtemp_avg6 | real |
sbtemp_avg7 | real |
sbtemp_avg8 | real |
scldout_avg1 | real |
scldout_avg2 | real |
scldout_avg3 | real |
scldout_avg4 | real |
sctemp_avg1 | real |
sctemp_avg10 | real |
sctemp_avg11 | real |
sctemp_avg12 | real |
sctemp_avg13 | real |
sctemp_avg14 | real |
sctemp_avg15 | real |
sctemp_avg16 | real |
sctemp_avg17 | real |
sctemp_avg18 | real |
sctemp_avg19 | real |
sctemp_avg2 | real |
sctemp_avg20 | real |
sctemp_avg21 | real |
sctemp_avg22 | real |
sctemp_avg23 | real |
sctemp_avg24 | real |
sctemp_avg3 | real |
sctemp_avg4 | real |
sctemp_avg5 | real |
sctemp_avg6 | real |
sctemp_avg7 | real |
sctemp_avg8 | real |
sctemp_avg9 | real |
sdm_out_avg1 | real |
sdm_out_avg2 | real |
sdm_out_avg3 | real |
sdm_out_avg4 | real |
stemp_avg1 | real |
stemp_avg10 | real |
stemp_avg11 | real |
stemp_avg12 | real |
stemp_avg13 | real |
stemp_avg14 | real |
stemp_avg15 | real |
stemp_avg16 | real |
stemp_avg2 | real |
stemp_avg3 | real |
stemp_avg4 | real |
stemp_avg5 | real |
stemp_avg6 | real |
stemp_avg7 | real |
stemp_avg8 | real |
stemp_avg9 | real |
tabove_avg1 | real |
tabove_avg2 | real |
tabove_avg3 | real |
tabove_avg4 | real |
tabove_avg5 | real |
tabove_avg6 | real |
tabove_avg7 | real |
tabove_avg8 | real |
targettemp_adj_avg1 | real |
targettemp_adj_avg2 | real |
targettemp_adj_avg3 | real |
targettemp_adj_avg4 | real |
targettemp_avg1 | real |
targettemp_avg2 | real |
targettemp_avg3 | real |
targettemp_avg4 | real |
targettemp_avg5 | real |
targettemp_avg6 | real |
targettemp_avg7 | real |
targettemp_avg8 | real |
tmv_avg1 | real |
tmv_avg2 | real |
tmv_avg3 | real |
tmv_avg4 | real |
tmv_avg5 | real |
tmv_avg6 | real |
tmv_avg7 | real |
tmv_avg8 | real |
tsoil_avg1 | real |
tsoil_avg2 | real |
tsoil_avg3 | real |
tsoil_avg4 | real |
tsoil_avg5 | real |
tsoil_avg6 | real |
tsoil_avg7 | real |
tsoil_avg8 | real |
tsoilr1 | real |
tsoilr2 | real |
tsoilr3 | real |
tsoilr4 | real |
tsoilr5 | real |
tsoilr6 | real |
tsoilr7 | real |
tsoilr8 | real |
vp_avg | real |
winddir_d1_wvt | real |
ws_ms_avg | real |
wtcref_avg | real |
Indexes:
"fifteen_min_pkey" PRIMARY KEY, btree (rowid)
"fifteen_min_lower_idx" btree (lower(block_name::text))
b4warmed3=# SELECT COUNT(*) FROM fifteen_min; count
---------
1798711
(1 row)
b4warmed3=# \d fifteen_min_stacked_propper
View "public.fifteen_min_stacked_propper"
Column | Type | Modifiers
----------------+-----------------------------+-----------
rowid | character varying(48) |
time2 | timestamp without time zone |
block_name | character varying(8) |
table_name | character varying(10) |
batt_volt | real |
flag1 | integer |
flag2 | integer |
flag3 | integer |
airtc_avg | real |
airtemp_avg | real |
airtemp_max | real |
airtemp_min | real |
all_avgt | real |
am25tref1 | real |
ptemp_avg | real |
rh | real |
s_all_avgt_avg | real |
vp_avg | real |
winddir_d1_wvt | real |
ws_ms_avg | real |
variable | text |
value | real |
b4warmed3=# SELECT COUNT(*) FROM fifteen_min_stacked_propper;
count
-----------
428093218
(1 row)
Kirk, Are you doing un-pivoting in most of your queries? Did you try normalized design for fifteen_minute table? Is there specific reason for de-normalization? Regards, Igor Neyman > -----Original Message----- > From: Kirk Wythers [mailto:kwythers@umn.edu] > Sent: Friday, January 18, 2013 10:50 AM > To: Igor Neyman > Cc: Kirk Wythers; pgsql-general@postgresql.org > Subject: Re: [GENERAL] speeding up a join query that utilizes a view > > > On Jan 18, 2013, at 8:10 AM, Igor Neyman <ineyman@perceptron.com> > wrote: > > > Yes, my mistake, I meant to ask about fifteen_min_stacked_view > definition, and Postgres parameters from postgresql.conf configuration > file, at least those - modified from default setting and related to > "resource consumption" and "query tuning". > > > > Regards, > > Igor Neyman > > Here some extra bits form the postgresql.conf file. As you can see, I > have not changed much from the default settings. > > #---------------------------------------------------------------------- > -------- > # RESOURCE USAGE (except WAL) > #---------------------------------------------------------------------- > -------- > > # - Memory - > > shared_buffers = 3GB # 7GB # min 128kB > # (change requires restart) > temp_buffers = 80MB # 8MB # min 800kB > #max_prepared_transactions = 0 # zero disables the feature > # (change requires restart) # > Note: Increasing max_prepared_transactions costs ~600 bytes of shared > memory # per transaction slot, plus lock space (see > max_locks_per_transaction). > # It is not advisable to set max_prepared_transactions nonzero unless > you # actively intend to use prepared transactions. > work_mem = 64MB #8MB # min 64kB > maintenance_work_mem = 128MB # min 1MB > #max_stack_depth = 2MB # min 100kB > > # - Kernel Resource Usage - > > #max_files_per_process = 1000 # min 25 > # (change requires restart) > #shared_preload_libraries = '' # (change requires restart) > > # - Cost-Based Vacuum Delay - > > #vacuum_cost_delay = 0ms # 0-100 milliseconds > #vacuum_cost_page_hit = 1 # 0-10000 credits > #vacuum_cost_page_miss = 10 # 0-10000 credits > #vacuum_cost_page_dirty = 20 # 0-10000 credits > #vacuum_cost_limit = 200 # 1-10000 credits > > # - Background Writer - > > #bgwriter_delay = 200ms # 10-10000ms between rounds > #bgwriter_lru_maxpages = 100 # 0-1000 max buffers > written/round > #bgwriter_lru_multiplier = 2.0 # 0-10.0 multipler on buffers > scanned/round > > # - Asynchronous Behavior - > > #effective_io_concurrency = 1 # 1-1000. 0 disables > prefetching > > #---------------------------------------------------------------------- > -------- > # QUERY TUNING > #---------------------------------------------------------------------- > -------- > > # - Planner Method Configuration - > > #enable_bitmapscan = on > #enable_hashagg = on > #enable_hashjoin = on > #enable_indexscan = on > #enable_material = on > #enable_mergejoin = on > #enable_nestloop = on > #enable_seqscan = on > #enable_sort = on > #enable_tidscan = on > > # - Planner Cost Constants - > > #seq_page_cost = 1.0 # measured on an arbitrary > scale > #random_page_cost = 4.0 # same scale as above > #cpu_tuple_cost = 0.01 # same scale as above > #cpu_index_tuple_cost = 0.005 # same scale as above > #cpu_operator_cost = 0.0025 # same scale as above > effective_cache_size = 6GB #13GB > > # - Genetic Query Optimizer - > > #geqo = on > #geqo_threshold = 12 > #geqo_effort = 5 # range 1-10 > #geqo_pool_size = 0 # selects default based on > effort > #geqo_generations = 0 # selects default based on > effort > #geqo_selection_bias = 2.0 # range 1.5-2.0 > #geqo_seed = 0.0 # range 0.0-1.0 > > # - Other Planner Options - > > #default_statistics_target = 100 # range 1-10000 > #constraint_exclusion = partition # on, off, or partition > #cursor_tuple_fraction = 0.1 # range 0.0-1.0 > #from_collapse_limit = 8 > #join_collapse_limit = 8 # 1 disables collapsing of > explicit > # JOIN clauses > > > Here is a snip from earlier that includes info about both the table > that is used to build the view and the view. In short, I use the UNNEST > function to un-pivot all the variables of interest in the "fifteen_min" > table into the columns "variable" and "value" in the > "fifteen_min_stacked_proper" view. > > Thanks again. > > Kirk > > > b4warmed3=# \d fifteen_min > Table "public.fifteen_min" > Column | Type | Modifiers > ---------------------+-----------------------------+----------- > rowid | character varying(48) | not null > time2 | timestamp without time zone | > timestamp | timestamp without time zone | > block_name | character varying(8) | > stat_name | character varying(8) | > table_name | character varying(10) | > program | character varying(48) | > a_dc_avg1 | real | > a_dc_avg2 | real | > a_dc_avg3 | real | > a_dc_avg4 | real | > a_dif_avg1 | real | > a_dif_avg2 | real | > a_dif_avg3 | real | > a_dif_avg4 | real | > a_targettemp_avg1 | real | > a_targettemp_avg2 | real | > a_targettemp_avg3 | real | > a_targettemp_avg4 | real | > a_targettemp_avg5 | real | > a_targettemp_avg6 | real | > a_targettemp_avg7 | real | > a_targettemp_avg8 | real | > a_tc_avg1 | real | > a_tc_avg10 | real | > a_tc_avg11 | real | > a_tc_avg12 | real | > a_tc_avg2 | real | > a_tc_avg3 | real | > a_tc_avg4 | real | > a_tc_avg5 | real | > a_tc_avg6 | real | > a_tc_avg7 | real | > a_tc_avg8 | real | > a_tc_avg9 | real | > a_tc_std1 | real | > a_tc_std10 | real | > a_tc_std11 | real | > a_tc_std12 | real | > a_tc_std2 | real | > a_tc_std3 | real | > a_tc_std4 | real | > a_tc_std5 | real | > a_tc_std6 | real | > a_tc_std7 | real | > a_tc_std8 | real | > a_tc_std9 | real | > airtc_avg | real | > airtemp_avg | real | > airtemp_max | real | > airtemp_min | real | > all_avgt | real | > am25tref1 | real | > amb_a_avg | real | > amb_avg1 | real | > amb_avg2 | real | > amb_closed_avg | real | > b_dc_avg1 | real | > b_dc_avg2 | real | > b_dc_avg3 | real | > b_dc_avg4 | real | > batt_volt | real | > etcref_avg | real | > flag1 | integer | > flag10 | integer | > flag11 | integer | > flag12 | integer | > flag2 | integer | > flag3 | integer | > flag4 | integer | > flag5 | integer | > flag6 | integer | > flag7 | integer | > flag8 | integer | > flag9 | integer | > heat_a_avg1 | real | > heat_a_avg2 | real | > heat_a_avg3 | real | > heat_a_avg4 | real | > pid_lmt_avg1 | real | > pid_lmt_avg2 | real | > pid_lmt_avg3 | real | > pid_lmt_avg4 | real | > pid_out_avg1 | real | > pid_out_avg2 | real | > pid_out_avg3 | real | > pid_out_avg4 | real | > ptemp_avg | real | > rh | real | > runavga1 | real | > runavga2 | real | > runavga21 | real | > runavga22 | real | > runavga23 | real | > runavga24 | real | > runavga25 | real | > runavga26 | real | > runavga27 | real | > runavga28 | real | > runavga3 | real | > runavga4 | real | > runavga5 | real | > runavga6 | real | > runavga7 | real | > runavga8 | real | > runavgs_avg1 | real | > runavgs_avg10 | real | > runavgs_avg11 | real | > runavgs_avg12 | real | > runavgs_avg13 | real | > runavgs_avg14 | real | > runavgs_avg15 | real | > runavgs_avg16 | real | > runavgs_avg2 | real | > runavgs_avg3 | real | > runavgs_avg4 | real | > runavgs_avg5 | real | > runavgs_avg6 | real | > runavgs_avg7 | real | > runavgs_avg8 | real | > runavgs_avg9 | real | > s_all_avgt_avg | real | > s_dif1 | real | > s_dif2 | real | > s_dif3 | real | > s_dif4 | real | > s_pid_lmt_avg1 | real | > s_pid_lmt_avg2 | real | > s_pid_lmt_avg3 | real | > s_pid_lmt_avg4 | real | > s_pid_out_avg1 | real | > s_pid_out_avg2 | real | > s_pid_out_avg3 | real | > s_pid_out_avg4 | real | > s_scldout_avg1 | real | > s_scldout_avg2 | real | > s_scldout_avg3 | real | > s_scldout_avg4 | real | > s_sdm_out_avg1 | real | > s_sdm_out_avg2 | real | > s_sdm_out_avg3 | real | > s_sdm_out_avg4 | real | > s_tc_avg1 | real | > s_tc_avg10 | real | > s_tc_avg11 | real | > s_tc_avg12 | real | > s_tc_avg2 | real | > s_tc_avg3 | real | > s_tc_avg4 | real | > s_tc_avg5 | real | > s_tc_avg6 | real | > s_tc_avg7 | real | > s_tc_avg8 | real | > s_tc_avg9 | real | > s_tc_std1 | real | > s_tc_std10 | real | > s_tc_std11 | real | > s_tc_std12 | real | > s_tc_std2 | real | > s_tc_std3 | real | > s_tc_std4 | real | > s_tc_std5 | real | > s_tc_std6 | real | > s_tc_std7 | real | > s_tc_std8 | real | > s_tc_std9 | real | > sbtemp_avg1 | real | > sbtemp_avg2 | real | > sbtemp_avg3 | real | > sbtemp_avg4 | real | > sbtemp_avg5 | real | > sbtemp_avg6 | real | > sbtemp_avg7 | real | > sbtemp_avg8 | real | > scldout_avg1 | real | > scldout_avg2 | real | > scldout_avg3 | real | > scldout_avg4 | real | > sctemp_avg1 | real | > sctemp_avg10 | real | > sctemp_avg11 | real | > sctemp_avg12 | real | > sctemp_avg13 | real | > sctemp_avg14 | real | > sctemp_avg15 | real | > sctemp_avg16 | real | > sctemp_avg17 | real | > sctemp_avg18 | real | > sctemp_avg19 | real | > sctemp_avg2 | real | > sctemp_avg20 | real | > sctemp_avg21 | real | > sctemp_avg22 | real | > sctemp_avg23 | real | > sctemp_avg24 | real | > sctemp_avg3 | real | > sctemp_avg4 | real | > sctemp_avg5 | real | > sctemp_avg6 | real | > sctemp_avg7 | real | > sctemp_avg8 | real | > sctemp_avg9 | real | > sdm_out_avg1 | real | > sdm_out_avg2 | real | > sdm_out_avg3 | real | > sdm_out_avg4 | real | > stemp_avg1 | real | > stemp_avg10 | real | > stemp_avg11 | real | > stemp_avg12 | real | > stemp_avg13 | real | > stemp_avg14 | real | > stemp_avg15 | real | > stemp_avg16 | real | > stemp_avg2 | real | > stemp_avg3 | real | > stemp_avg4 | real | > stemp_avg5 | real | > stemp_avg6 | real | > stemp_avg7 | real | > stemp_avg8 | real | > stemp_avg9 | real | > tabove_avg1 | real | > tabove_avg2 | real | > tabove_avg3 | real | > tabove_avg4 | real | > tabove_avg5 | real | > tabove_avg6 | real | > tabove_avg7 | real | > tabove_avg8 | real | > targettemp_adj_avg1 | real | > targettemp_adj_avg2 | real | > targettemp_adj_avg3 | real | > targettemp_adj_avg4 | real | > targettemp_avg1 | real | > targettemp_avg2 | real | > targettemp_avg3 | real | > targettemp_avg4 | real | > targettemp_avg5 | real | > targettemp_avg6 | real | > targettemp_avg7 | real | > targettemp_avg8 | real | > tmv_avg1 | real | > tmv_avg2 | real | > tmv_avg3 | real | > tmv_avg4 | real | > tmv_avg5 | real | > tmv_avg6 | real | > tmv_avg7 | real | > tmv_avg8 | real | > tsoil_avg1 | real | > tsoil_avg2 | real | > tsoil_avg3 | real | > tsoil_avg4 | real | > tsoil_avg5 | real | > tsoil_avg6 | real | > tsoil_avg7 | real | > tsoil_avg8 | real | > tsoilr1 | real | > tsoilr2 | real | > tsoilr3 | real | > tsoilr4 | real | > tsoilr5 | real | > tsoilr6 | real | > tsoilr7 | real | > tsoilr8 | real | > vp_avg | real | > winddir_d1_wvt | real | > ws_ms_avg | real | > wtcref_avg | real | > Indexes: > "fifteen_min_pkey" PRIMARY KEY, btree (rowid) > "fifteen_min_lower_idx" btree (lower(block_name::text)) > > b4warmed3=# SELECT COUNT(*) FROM fifteen_min; count > --------- > 1798711 > (1 row) > > > b4warmed3=# \d fifteen_min_stacked_propper > View "public.fifteen_min_stacked_propper" > Column | Type | Modifiers > ----------------+-----------------------------+----------- > rowid | character varying(48) | > time2 | timestamp without time zone | > block_name | character varying(8) | > table_name | character varying(10) | > batt_volt | real | > flag1 | integer | > flag2 | integer | > flag3 | integer | > airtc_avg | real | > airtemp_avg | real | > airtemp_max | real | > airtemp_min | real | > all_avgt | real | > am25tref1 | real | > ptemp_avg | real | > rh | real | > s_all_avgt_avg | real | > vp_avg | real | > winddir_d1_wvt | real | > ws_ms_avg | real | > variable | text | > value | real | > > b4warmed3=# SELECT COUNT(*) FROM fifteen_min_stacked_propper; count > ----------- > 428093218 > (1 row) >
On Jan 18, 2013, at 10:05 AM, Igor Neyman <ineyman@perceptron.com> wrote:
> Kirk,
>
> Are you doing un-pivoting in most of your queries?
> Did you try normalized design for fifteen_minute table?
> Is there specific reason for de-normalization?
>
> Regards,
> Igor Neyman
Thanks Igor. The only reason I'm de-normalizing with unnest, is so I can perform a join on variable_name with the table
"data_key".I't kind of a crazy design, but it is what I was given to work with. Here is the join that takes so dang
longto perform:
SELECT
data_key.site,
data_key.canopy,
data_key.measurement_interval,
data_key.treatment_code,
data_key.treatment_abbr,
data_key.plot,
fifteen_min_stacked_propper.*
FROM
data_key,
fifteen_min_stacked_propper
WHERE
data_key.variable_channel = fifteen_min_stacked_propper.variable AND data_key.block_name =
fifteen_min_stacked_propper.block_name
--AND 2012 = EXTRACT(YEAR FROM time2)
--AND fifteen_min_stacked_propper.block_name ~ 'b4warm_[ace]'
--AND fifteen_min_stacked_propper.value IS NOT NULL
AND fifteen_min_stacked_propper.variable ~ 'tsoil'
The whole point of the de-normalized table "fifteen_min_stacked_propper" is so that variable names in
fifteen_min_stacked_propper.variablecan be used to join on data_key.variable_channel.
Does that make sense?
Kirk
>
>> -----Original Message-----
>> From: Kirk Wythers [mailto:kwythers@umn.edu]
>> Sent: Friday, January 18, 2013 10:50 AM
>> To: Igor Neyman
>> Cc: Kirk Wythers; pgsql-general@postgresql.org
>> Subject: Re: [GENERAL] speeding up a join query that utilizes a view
>>
>>
>> On Jan 18, 2013, at 8:10 AM, Igor Neyman <ineyman@perceptron.com>
>> wrote:
>>
>>> Yes, my mistake, I meant to ask about fifteen_min_stacked_view
>> definition, and Postgres parameters from postgresql.conf configuration
>> file, at least those - modified from default setting and related to
>> "resource consumption" and "query tuning".
>>>
>>> Regards,
>>> Igor Neyman
>>
>> Here some extra bits form the postgresql.conf file. As you can see, I
>> have not changed much from the default settings.
>>
>> #----------------------------------------------------------------------
>> --------
>> # RESOURCE USAGE (except WAL)
>> #----------------------------------------------------------------------
>> --------
>>
>> # - Memory -
>>
>> shared_buffers = 3GB # 7GB # min 128kB
>> # (change requires restart)
>> temp_buffers = 80MB # 8MB # min 800kB
>> #max_prepared_transactions = 0 # zero disables the feature
>> # (change requires restart) #
>> Note: Increasing max_prepared_transactions costs ~600 bytes of shared
>> memory # per transaction slot, plus lock space (see
>> max_locks_per_transaction).
>> # It is not advisable to set max_prepared_transactions nonzero unless
>> you # actively intend to use prepared transactions.
>> work_mem = 64MB #8MB # min 64kB
>> maintenance_work_mem = 128MB # min 1MB
>> #max_stack_depth = 2MB # min 100kB
>>
>> # - Kernel Resource Usage -
>>
>> #max_files_per_process = 1000 # min 25
>> # (change requires restart)
>> #shared_preload_libraries = '' # (change requires restart)
>>
>> # - Cost-Based Vacuum Delay -
>>
>> #vacuum_cost_delay = 0ms # 0-100 milliseconds
>> #vacuum_cost_page_hit = 1 # 0-10000 credits
>> #vacuum_cost_page_miss = 10 # 0-10000 credits
>> #vacuum_cost_page_dirty = 20 # 0-10000 credits
>> #vacuum_cost_limit = 200 # 1-10000 credits
>>
>> # - Background Writer -
>>
>> #bgwriter_delay = 200ms # 10-10000ms between rounds
>> #bgwriter_lru_maxpages = 100 # 0-1000 max buffers
>> written/round
>> #bgwriter_lru_multiplier = 2.0 # 0-10.0 multipler on buffers
>> scanned/round
>>
>> # - Asynchronous Behavior -
>>
>> #effective_io_concurrency = 1 # 1-1000. 0 disables
>> prefetching
>>
>> #----------------------------------------------------------------------
>> --------
>> # QUERY TUNING
>> #----------------------------------------------------------------------
>> --------
>>
>> # - Planner Method Configuration -
>>
>> #enable_bitmapscan = on
>> #enable_hashagg = on
>> #enable_hashjoin = on
>> #enable_indexscan = on
>> #enable_material = on
>> #enable_mergejoin = on
>> #enable_nestloop = on
>> #enable_seqscan = on
>> #enable_sort = on
>> #enable_tidscan = on
>>
>> # - Planner Cost Constants -
>>
>> #seq_page_cost = 1.0 # measured on an arbitrary
>> scale
>> #random_page_cost = 4.0 # same scale as above
>> #cpu_tuple_cost = 0.01 # same scale as above
>> #cpu_index_tuple_cost = 0.005 # same scale as above
>> #cpu_operator_cost = 0.0025 # same scale as above
>> effective_cache_size = 6GB #13GB
>>
>> # - Genetic Query Optimizer -
>>
>> #geqo = on
>> #geqo_threshold = 12
>> #geqo_effort = 5 # range 1-10
>> #geqo_pool_size = 0 # selects default based on
>> effort
>> #geqo_generations = 0 # selects default based on
>> effort
>> #geqo_selection_bias = 2.0 # range 1.5-2.0
>> #geqo_seed = 0.0 # range 0.0-1.0
>>
>> # - Other Planner Options -
>>
>> #default_statistics_target = 100 # range 1-10000
>> #constraint_exclusion = partition # on, off, or partition
>> #cursor_tuple_fraction = 0.1 # range 0.0-1.0
>> #from_collapse_limit = 8
>> #join_collapse_limit = 8 # 1 disables collapsing of
>> explicit
>> # JOIN clauses
>>
>>
>> Here is a snip from earlier that includes info about both the table
>> that is used to build the view and the view. In short, I use the UNNEST
>> function to un-pivot all the variables of interest in the "fifteen_min"
>> table into the columns "variable" and "value" in the
>> "fifteen_min_stacked_proper" view.
>>
>> Thanks again.
>>
>> Kirk
>>
>>
>> b4warmed3=# \d fifteen_min
>> Table "public.fifteen_min"
>> Column | Type | Modifiers
>> ---------------------+-----------------------------+-----------
>> rowid | character varying(48) | not null
>> time2 | timestamp without time zone |
>> timestamp | timestamp without time zone |
>> block_name | character varying(8) |
>> stat_name | character varying(8) |
>> table_name | character varying(10) |
>> program | character varying(48) |
>> a_dc_avg1 | real |
>> a_dc_avg2 | real |
>> a_dc_avg3 | real |
>> a_dc_avg4 | real |
>> a_dif_avg1 | real |
>> a_dif_avg2 | real |
>> a_dif_avg3 | real |
>> a_dif_avg4 | real |
>> a_targettemp_avg1 | real |
>> a_targettemp_avg2 | real |
>> a_targettemp_avg3 | real |
>> a_targettemp_avg4 | real |
>> a_targettemp_avg5 | real |
>> a_targettemp_avg6 | real |
>> a_targettemp_avg7 | real |
>> a_targettemp_avg8 | real |
>> a_tc_avg1 | real |
>> a_tc_avg10 | real |
>> a_tc_avg11 | real |
>> a_tc_avg12 | real |
>> a_tc_avg2 | real |
>> a_tc_avg3 | real |
>> a_tc_avg4 | real |
>> a_tc_avg5 | real |
>> a_tc_avg6 | real |
>> a_tc_avg7 | real |
>> a_tc_avg8 | real |
>> a_tc_avg9 | real |
>> a_tc_std1 | real |
>> a_tc_std10 | real |
>> a_tc_std11 | real |
>> a_tc_std12 | real |
>> a_tc_std2 | real |
>> a_tc_std3 | real |
>> a_tc_std4 | real |
>> a_tc_std5 | real |
>> a_tc_std6 | real |
>> a_tc_std7 | real |
>> a_tc_std8 | real |
>> a_tc_std9 | real |
>> airtc_avg | real |
>> airtemp_avg | real |
>> airtemp_max | real |
>> airtemp_min | real |
>> all_avgt | real |
>> am25tref1 | real |
>> amb_a_avg | real |
>> amb_avg1 | real |
>> amb_avg2 | real |
>> amb_closed_avg | real |
>> b_dc_avg1 | real |
>> b_dc_avg2 | real |
>> b_dc_avg3 | real |
>> b_dc_avg4 | real |
>> batt_volt | real |
>> etcref_avg | real |
>> flag1 | integer |
>> flag10 | integer |
>> flag11 | integer |
>> flag12 | integer |
>> flag2 | integer |
>> flag3 | integer |
>> flag4 | integer |
>> flag5 | integer |
>> flag6 | integer |
>> flag7 | integer |
>> flag8 | integer |
>> flag9 | integer |
>> heat_a_avg1 | real |
>> heat_a_avg2 | real |
>> heat_a_avg3 | real |
>> heat_a_avg4 | real |
>> pid_lmt_avg1 | real |
>> pid_lmt_avg2 | real |
>> pid_lmt_avg3 | real |
>> pid_lmt_avg4 | real |
>> pid_out_avg1 | real |
>> pid_out_avg2 | real |
>> pid_out_avg3 | real |
>> pid_out_avg4 | real |
>> ptemp_avg | real |
>> rh | real |
>> runavga1 | real |
>> runavga2 | real |
>> runavga21 | real |
>> runavga22 | real |
>> runavga23 | real |
>> runavga24 | real |
>> runavga25 | real |
>> runavga26 | real |
>> runavga27 | real |
>> runavga28 | real |
>> runavga3 | real |
>> runavga4 | real |
>> runavga5 | real |
>> runavga6 | real |
>> runavga7 | real |
>> runavga8 | real |
>> runavgs_avg1 | real |
>> runavgs_avg10 | real |
>> runavgs_avg11 | real |
>> runavgs_avg12 | real |
>> runavgs_avg13 | real |
>> runavgs_avg14 | real |
>> runavgs_avg15 | real |
>> runavgs_avg16 | real |
>> runavgs_avg2 | real |
>> runavgs_avg3 | real |
>> runavgs_avg4 | real |
>> runavgs_avg5 | real |
>> runavgs_avg6 | real |
>> runavgs_avg7 | real |
>> runavgs_avg8 | real |
>> runavgs_avg9 | real |
>> s_all_avgt_avg | real |
>> s_dif1 | real |
>> s_dif2 | real |
>> s_dif3 | real |
>> s_dif4 | real |
>> s_pid_lmt_avg1 | real |
>> s_pid_lmt_avg2 | real |
>> s_pid_lmt_avg3 | real |
>> s_pid_lmt_avg4 | real |
>> s_pid_out_avg1 | real |
>> s_pid_out_avg2 | real |
>> s_pid_out_avg3 | real |
>> s_pid_out_avg4 | real |
>> s_scldout_avg1 | real |
>> s_scldout_avg2 | real |
>> s_scldout_avg3 | real |
>> s_scldout_avg4 | real |
>> s_sdm_out_avg1 | real |
>> s_sdm_out_avg2 | real |
>> s_sdm_out_avg3 | real |
>> s_sdm_out_avg4 | real |
>> s_tc_avg1 | real |
>> s_tc_avg10 | real |
>> s_tc_avg11 | real |
>> s_tc_avg12 | real |
>> s_tc_avg2 | real |
>> s_tc_avg3 | real |
>> s_tc_avg4 | real |
>> s_tc_avg5 | real |
>> s_tc_avg6 | real |
>> s_tc_avg7 | real |
>> s_tc_avg8 | real |
>> s_tc_avg9 | real |
>> s_tc_std1 | real |
>> s_tc_std10 | real |
>> s_tc_std11 | real |
>> s_tc_std12 | real |
>> s_tc_std2 | real |
>> s_tc_std3 | real |
>> s_tc_std4 | real |
>> s_tc_std5 | real |
>> s_tc_std6 | real |
>> s_tc_std7 | real |
>> s_tc_std8 | real |
>> s_tc_std9 | real |
>> sbtemp_avg1 | real |
>> sbtemp_avg2 | real |
>> sbtemp_avg3 | real |
>> sbtemp_avg4 | real |
>> sbtemp_avg5 | real |
>> sbtemp_avg6 | real |
>> sbtemp_avg7 | real |
>> sbtemp_avg8 | real |
>> scldout_avg1 | real |
>> scldout_avg2 | real |
>> scldout_avg3 | real |
>> scldout_avg4 | real |
>> sctemp_avg1 | real |
>> sctemp_avg10 | real |
>> sctemp_avg11 | real |
>> sctemp_avg12 | real |
>> sctemp_avg13 | real |
>> sctemp_avg14 | real |
>> sctemp_avg15 | real |
>> sctemp_avg16 | real |
>> sctemp_avg17 | real |
>> sctemp_avg18 | real |
>> sctemp_avg19 | real |
>> sctemp_avg2 | real |
>> sctemp_avg20 | real |
>> sctemp_avg21 | real |
>> sctemp_avg22 | real |
>> sctemp_avg23 | real |
>> sctemp_avg24 | real |
>> sctemp_avg3 | real |
>> sctemp_avg4 | real |
>> sctemp_avg5 | real |
>> sctemp_avg6 | real |
>> sctemp_avg7 | real |
>> sctemp_avg8 | real |
>> sctemp_avg9 | real |
>> sdm_out_avg1 | real |
>> sdm_out_avg2 | real |
>> sdm_out_avg3 | real |
>> sdm_out_avg4 | real |
>> stemp_avg1 | real |
>> stemp_avg10 | real |
>> stemp_avg11 | real |
>> stemp_avg12 | real |
>> stemp_avg13 | real |
>> stemp_avg14 | real |
>> stemp_avg15 | real |
>> stemp_avg16 | real |
>> stemp_avg2 | real |
>> stemp_avg3 | real |
>> stemp_avg4 | real |
>> stemp_avg5 | real |
>> stemp_avg6 | real |
>> stemp_avg7 | real |
>> stemp_avg8 | real |
>> stemp_avg9 | real |
>> tabove_avg1 | real |
>> tabove_avg2 | real |
>> tabove_avg3 | real |
>> tabove_avg4 | real |
>> tabove_avg5 | real |
>> tabove_avg6 | real |
>> tabove_avg7 | real |
>> tabove_avg8 | real |
>> targettemp_adj_avg1 | real |
>> targettemp_adj_avg2 | real |
>> targettemp_adj_avg3 | real |
>> targettemp_adj_avg4 | real |
>> targettemp_avg1 | real |
>> targettemp_avg2 | real |
>> targettemp_avg3 | real |
>> targettemp_avg4 | real |
>> targettemp_avg5 | real |
>> targettemp_avg6 | real |
>> targettemp_avg7 | real |
>> targettemp_avg8 | real |
>> tmv_avg1 | real |
>> tmv_avg2 | real |
>> tmv_avg3 | real |
>> tmv_avg4 | real |
>> tmv_avg5 | real |
>> tmv_avg6 | real |
>> tmv_avg7 | real |
>> tmv_avg8 | real |
>> tsoil_avg1 | real |
>> tsoil_avg2 | real |
>> tsoil_avg3 | real |
>> tsoil_avg4 | real |
>> tsoil_avg5 | real |
>> tsoil_avg6 | real |
>> tsoil_avg7 | real |
>> tsoil_avg8 | real |
>> tsoilr1 | real |
>> tsoilr2 | real |
>> tsoilr3 | real |
>> tsoilr4 | real |
>> tsoilr5 | real |
>> tsoilr6 | real |
>> tsoilr7 | real |
>> tsoilr8 | real |
>> vp_avg | real |
>> winddir_d1_wvt | real |
>> ws_ms_avg | real |
>> wtcref_avg | real |
>> Indexes:
>> "fifteen_min_pkey" PRIMARY KEY, btree (rowid)
>> "fifteen_min_lower_idx" btree (lower(block_name::text))
>>
>> b4warmed3=# SELECT COUNT(*) FROM fifteen_min; count
>> ---------
>> 1798711
>> (1 row)
>>
>>
>> b4warmed3=# \d fifteen_min_stacked_propper
>> View "public.fifteen_min_stacked_propper"
>> Column | Type | Modifiers
>> ----------------+-----------------------------+-----------
>> rowid | character varying(48) |
>> time2 | timestamp without time zone |
>> block_name | character varying(8) |
>> table_name | character varying(10) |
>> batt_volt | real |
>> flag1 | integer |
>> flag2 | integer |
>> flag3 | integer |
>> airtc_avg | real |
>> airtemp_avg | real |
>> airtemp_max | real |
>> airtemp_min | real |
>> all_avgt | real |
>> am25tref1 | real |
>> ptemp_avg | real |
>> rh | real |
>> s_all_avgt_avg | real |
>> vp_avg | real |
>> winddir_d1_wvt | real |
>> ws_ms_avg | real |
>> variable | text |
>> value | real |
>>
>> b4warmed3=# SELECT COUNT(*) FROM fifteen_min_stacked_propper; count
>> -----------
>> 428093218
>> (1 row)
>>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
Kirk, Are you limited to "pure" SQL or procedural language (PgPlSQL) allowed? If PgPlSQL is allowed, you could normalize fifteen_min table, break it into several tables (one for a_dc, another for a_dif,another for a_targettemp, and so on...) and use dynamic sql inside PlPgSQL function to join with the proper table. In that case you could index normalized tables properly, also not having table rows as wide as they are now helps. Thus you'll avoid sequencial scan on a big and wide table. Also increasing default_statistics_target may help, this: Index Scan using fifteen_min_pkey on fifteen_min (cost=0.00..525136.58 rows=1798711 width=1072) (actual time=0.034..96077.588rows=428093218 loops=1) Shows to big of a difference between estimated and actual row counts. Are these tables analyzed often enough? Regards, Igor Neyman > -----Original Message----- > From: Kirk Wythers [mailto:kwythers@umn.edu] > Sent: Friday, January 18, 2013 11:15 AM > To: Igor Neyman > Cc: Kirk Wythers; pgsql-general@postgresql.org > Subject: Re: [GENERAL] speeding up a join query that utilizes a view > > > On Jan 18, 2013, at 10:05 AM, Igor Neyman <ineyman@perceptron.com> > wrote: > > > Kirk, > > > > Are you doing un-pivoting in most of your queries? > > Did you try normalized design for fifteen_minute table? > > Is there specific reason for de-normalization? > > > > Regards, > > Igor Neyman > > Thanks Igor. The only reason I'm de-normalizing with unnest, is so I > can perform a join on variable_name with the table "data_key". I't kind > of a crazy design, but it is what I was given to work with. Here is the > join that takes so dang long to perform: > > SELECT > data_key.site, > data_key.canopy, > data_key.measurement_interval, > data_key.treatment_code, > data_key.treatment_abbr, > data_key.plot, > fifteen_min_stacked_propper.* > FROM > data_key, > fifteen_min_stacked_propper > WHERE > data_key.variable_channel = fifteen_min_stacked_propper.variable > AND data_key.block_name = fifteen_min_stacked_propper.block_name > --AND 2012 = EXTRACT(YEAR FROM time2) > --AND fifteen_min_stacked_propper.block_name ~ 'b4warm_[ace]' > --AND fifteen_min_stacked_propper.value IS NOT NULL AND > fifteen_min_stacked_propper.variable ~ 'tsoil' > > The whole point of the de-normalized table > "fifteen_min_stacked_propper" is so that variable names in > fifteen_min_stacked_propper.variable can be used to join on > data_key.variable_channel. > > Does that make sense? > > Kirk > > > > > >> -----Original Message----- > >> From: Kirk Wythers [mailto:kwythers@umn.edu] > >> Sent: Friday, January 18, 2013 10:50 AM > >> To: Igor Neyman > >> Cc: Kirk Wythers; pgsql-general@postgresql.org > >> Subject: Re: [GENERAL] speeding up a join query that utilizes a view > >> > >> > >> On Jan 18, 2013, at 8:10 AM, Igor Neyman <ineyman@perceptron.com> > >> wrote: > >> > >>> Yes, my mistake, I meant to ask about fifteen_min_stacked_view > >> definition, and Postgres parameters from postgresql.conf > >> configuration file, at least those - modified from default setting > >> and related to "resource consumption" and "query tuning". > >>> > >>> Regards, > >>> Igor Neyman > >> > >> Here some extra bits form the postgresql.conf file. As you can see, > >> I have not changed much from the default settings. > >> > >> #------------------------------------------------------------------- > - > >> -- > >> -------- > >> # RESOURCE USAGE (except WAL) > >> #------------------------------------------------------------------- > - > >> -- > >> -------- > >> > >> # - Memory - > >> > >> shared_buffers = 3GB # 7GB # min 128kB > >> # (change requires restart) > >> temp_buffers = 80MB # 8MB # min 800kB > >> #max_prepared_transactions = 0 # zero disables the feature > >> # (change requires restart) # > >> Note: Increasing max_prepared_transactions costs ~600 bytes of > >> shared memory # per transaction slot, plus lock space (see > >> max_locks_per_transaction). > >> # It is not advisable to set max_prepared_transactions nonzero > unless > >> you # actively intend to use prepared transactions. > >> work_mem = 64MB #8MB # min 64kB > >> maintenance_work_mem = 128MB # min 1MB > >> #max_stack_depth = 2MB # min 100kB > >> > >> # - Kernel Resource Usage - > >> > >> #max_files_per_process = 1000 # min 25 > >> # (change requires restart) > >> #shared_preload_libraries = '' # (change requires restart) > >> > >> # - Cost-Based Vacuum Delay - > >> > >> #vacuum_cost_delay = 0ms # 0-100 milliseconds > >> #vacuum_cost_page_hit = 1 # 0-10000 credits > >> #vacuum_cost_page_miss = 10 # 0-10000 credits > >> #vacuum_cost_page_dirty = 20 # 0-10000 credits > >> #vacuum_cost_limit = 200 # 1-10000 credits > >> > >> # - Background Writer - > >> > >> #bgwriter_delay = 200ms # 10-10000ms between rounds > >> #bgwriter_lru_maxpages = 100 # 0-1000 max buffers > >> written/round > >> #bgwriter_lru_multiplier = 2.0 # 0-10.0 multipler on > buffers > >> scanned/round > >> > >> # - Asynchronous Behavior - > >> > >> #effective_io_concurrency = 1 # 1-1000. 0 disables > >> prefetching > >> > >> #------------------------------------------------------------------- > - > >> -- > >> -------- > >> # QUERY TUNING > >> #------------------------------------------------------------------- > - > >> -- > >> -------- > >> > >> # - Planner Method Configuration - > >> > >> #enable_bitmapscan = on > >> #enable_hashagg = on > >> #enable_hashjoin = on > >> #enable_indexscan = on > >> #enable_material = on > >> #enable_mergejoin = on > >> #enable_nestloop = on > >> #enable_seqscan = on > >> #enable_sort = on > >> #enable_tidscan = on > >> > >> # - Planner Cost Constants - > >> > >> #seq_page_cost = 1.0 # measured on an arbitrary > >> scale > >> #random_page_cost = 4.0 # same scale as above > >> #cpu_tuple_cost = 0.01 # same scale as above > >> #cpu_index_tuple_cost = 0.005 # same scale as above > >> #cpu_operator_cost = 0.0025 # same scale as above > >> effective_cache_size = 6GB #13GB > >> > >> # - Genetic Query Optimizer - > >> > >> #geqo = on > >> #geqo_threshold = 12 > >> #geqo_effort = 5 # range 1-10 > >> #geqo_pool_size = 0 # selects default based on > >> effort > >> #geqo_generations = 0 # selects default based on > >> effort > >> #geqo_selection_bias = 2.0 # range 1.5-2.0 > >> #geqo_seed = 0.0 # range 0.0-1.0 > >> > >> # - Other Planner Options - > >> > >> #default_statistics_target = 100 # range 1-10000 > >> #constraint_exclusion = partition # on, off, or partition > >> #cursor_tuple_fraction = 0.1 # range 0.0-1.0 > >> #from_collapse_limit = 8 > >> #join_collapse_limit = 8 # 1 disables collapsing of > >> explicit > >> # JOIN clauses > >> > >> > >> Here is a snip from earlier that includes info about both the table > >> that is used to build the view and the view. In short, I use the > >> UNNEST function to un-pivot all the variables of interest in the > "fifteen_min" > >> table into the columns "variable" and "value" in the > >> "fifteen_min_stacked_proper" view. > >> > >> Thanks again. > >> > >> Kirk > >> > >> > >> b4warmed3=# \d fifteen_min > >> Table "public.fifteen_min" > >> Column | Type | Modifiers > >> ---------------------+-----------------------------+----------- > >> rowid | character varying(48) | not null > >> time2 | timestamp without time zone | > >> timestamp | timestamp without time zone | > >> block_name | character varying(8) | > >> stat_name | character varying(8) | > >> table_name | character varying(10) | > >> program | character varying(48) | > >> a_dc_avg1 | real | > >> a_dc_avg2 | real | > >> a_dc_avg3 | real | > >> a_dc_avg4 | real | > >> a_dif_avg1 | real | > >> a_dif_avg2 | real | > >> a_dif_avg3 | real | > >> a_dif_avg4 | real | > >> a_targettemp_avg1 | real | > >> a_targettemp_avg2 | real | > >> a_targettemp_avg3 | real | > >> a_targettemp_avg4 | real | > >> a_targettemp_avg5 | real | > >> a_targettemp_avg6 | real | > >> a_targettemp_avg7 | real | > >> a_targettemp_avg8 | real | > >> a_tc_avg1 | real | > >> a_tc_avg10 | real | > >> a_tc_avg11 | real | > >> a_tc_avg12 | real | > >> a_tc_avg2 | real | > >> a_tc_avg3 | real | > >> a_tc_avg4 | real | > >> a_tc_avg5 | real | > >> a_tc_avg6 | real | > >> a_tc_avg7 | real | > >> a_tc_avg8 | real | > >> a_tc_avg9 | real | > >> a_tc_std1 | real | > >> a_tc_std10 | real | > >> a_tc_std11 | real | > >> a_tc_std12 | real | > >> a_tc_std2 | real | > >> a_tc_std3 | real | > >> a_tc_std4 | real | > >> a_tc_std5 | real | > >> a_tc_std6 | real | > >> a_tc_std7 | real | > >> a_tc_std8 | real | > >> a_tc_std9 | real | > >> airtc_avg | real | > >> airtemp_avg | real | > >> airtemp_max | real | > >> airtemp_min | real | > >> all_avgt | real | > >> am25tref1 | real | > >> amb_a_avg | real | > >> amb_avg1 | real | > >> amb_avg2 | real | > >> amb_closed_avg | real | > >> b_dc_avg1 | real | > >> b_dc_avg2 | real | > >> b_dc_avg3 | real | > >> b_dc_avg4 | real | > >> batt_volt | real | > >> etcref_avg | real | > >> flag1 | integer | > >> flag10 | integer | > >> flag11 | integer | > >> flag12 | integer | > >> flag2 | integer | > >> flag3 | integer | > >> flag4 | integer | > >> flag5 | integer | > >> flag6 | integer | > >> flag7 | integer | > >> flag8 | integer | > >> flag9 | integer | > >> heat_a_avg1 | real | > >> heat_a_avg2 | real | > >> heat_a_avg3 | real | > >> heat_a_avg4 | real | > >> pid_lmt_avg1 | real | > >> pid_lmt_avg2 | real | > >> pid_lmt_avg3 | real | > >> pid_lmt_avg4 | real | > >> pid_out_avg1 | real | > >> pid_out_avg2 | real | > >> pid_out_avg3 | real | > >> pid_out_avg4 | real | > >> ptemp_avg | real | > >> rh | real | > >> runavga1 | real | > >> runavga2 | real | > >> runavga21 | real | > >> runavga22 | real | > >> runavga23 | real | > >> runavga24 | real | > >> runavga25 | real | > >> runavga26 | real | > >> runavga27 | real | > >> runavga28 | real | > >> runavga3 | real | > >> runavga4 | real | > >> runavga5 | real | > >> runavga6 | real | > >> runavga7 | real | > >> runavga8 | real | > >> runavgs_avg1 | real | > >> runavgs_avg10 | real | > >> runavgs_avg11 | real | > >> runavgs_avg12 | real | > >> runavgs_avg13 | real | > >> runavgs_avg14 | real | > >> runavgs_avg15 | real | > >> runavgs_avg16 | real | > >> runavgs_avg2 | real | > >> runavgs_avg3 | real | > >> runavgs_avg4 | real | > >> runavgs_avg5 | real | > >> runavgs_avg6 | real | > >> runavgs_avg7 | real | > >> runavgs_avg8 | real | > >> runavgs_avg9 | real | > >> s_all_avgt_avg | real | > >> s_dif1 | real | > >> s_dif2 | real | > >> s_dif3 | real | > >> s_dif4 | real | > >> s_pid_lmt_avg1 | real | > >> s_pid_lmt_avg2 | real | > >> s_pid_lmt_avg3 | real | > >> s_pid_lmt_avg4 | real | > >> s_pid_out_avg1 | real | > >> s_pid_out_avg2 | real | > >> s_pid_out_avg3 | real | > >> s_pid_out_avg4 | real | > >> s_scldout_avg1 | real | > >> s_scldout_avg2 | real | > >> s_scldout_avg3 | real | > >> s_scldout_avg4 | real | > >> s_sdm_out_avg1 | real | > >> s_sdm_out_avg2 | real | > >> s_sdm_out_avg3 | real | > >> s_sdm_out_avg4 | real | > >> s_tc_avg1 | real | > >> s_tc_avg10 | real | > >> s_tc_avg11 | real | > >> s_tc_avg12 | real | > >> s_tc_avg2 | real | > >> s_tc_avg3 | real | > >> s_tc_avg4 | real | > >> s_tc_avg5 | real | > >> s_tc_avg6 | real | > >> s_tc_avg7 | real | > >> s_tc_avg8 | real | > >> s_tc_avg9 | real | > >> s_tc_std1 | real | > >> s_tc_std10 | real | > >> s_tc_std11 | real | > >> s_tc_std12 | real | > >> s_tc_std2 | real | > >> s_tc_std3 | real | > >> s_tc_std4 | real | > >> s_tc_std5 | real | > >> s_tc_std6 | real | > >> s_tc_std7 | real | > >> s_tc_std8 | real | > >> s_tc_std9 | real | > >> sbtemp_avg1 | real | > >> sbtemp_avg2 | real | > >> sbtemp_avg3 | real | > >> sbtemp_avg4 | real | > >> sbtemp_avg5 | real | > >> sbtemp_avg6 | real | > >> sbtemp_avg7 | real | > >> sbtemp_avg8 | real | > >> scldout_avg1 | real | > >> scldout_avg2 | real | > >> scldout_avg3 | real | > >> scldout_avg4 | real | > >> sctemp_avg1 | real | > >> sctemp_avg10 | real | > >> sctemp_avg11 | real | > >> sctemp_avg12 | real | > >> sctemp_avg13 | real | > >> sctemp_avg14 | real | > >> sctemp_avg15 | real | > >> sctemp_avg16 | real | > >> sctemp_avg17 | real | > >> sctemp_avg18 | real | > >> sctemp_avg19 | real | > >> sctemp_avg2 | real | > >> sctemp_avg20 | real | > >> sctemp_avg21 | real | > >> sctemp_avg22 | real | > >> sctemp_avg23 | real | > >> sctemp_avg24 | real | > >> sctemp_avg3 | real | > >> sctemp_avg4 | real | > >> sctemp_avg5 | real | > >> sctemp_avg6 | real | > >> sctemp_avg7 | real | > >> sctemp_avg8 | real | > >> sctemp_avg9 | real | > >> sdm_out_avg1 | real | > >> sdm_out_avg2 | real | > >> sdm_out_avg3 | real | > >> sdm_out_avg4 | real | > >> stemp_avg1 | real | > >> stemp_avg10 | real | > >> stemp_avg11 | real | > >> stemp_avg12 | real | > >> stemp_avg13 | real | > >> stemp_avg14 | real | > >> stemp_avg15 | real | > >> stemp_avg16 | real | > >> stemp_avg2 | real | > >> stemp_avg3 | real | > >> stemp_avg4 | real | > >> stemp_avg5 | real | > >> stemp_avg6 | real | > >> stemp_avg7 | real | > >> stemp_avg8 | real | > >> stemp_avg9 | real | > >> tabove_avg1 | real | > >> tabove_avg2 | real | > >> tabove_avg3 | real | > >> tabove_avg4 | real | > >> tabove_avg5 | real | > >> tabove_avg6 | real | > >> tabove_avg7 | real | > >> tabove_avg8 | real | > >> targettemp_adj_avg1 | real | > >> targettemp_adj_avg2 | real | > >> targettemp_adj_avg3 | real | > >> targettemp_adj_avg4 | real | > >> targettemp_avg1 | real | > >> targettemp_avg2 | real | > >> targettemp_avg3 | real | > >> targettemp_avg4 | real | > >> targettemp_avg5 | real | > >> targettemp_avg6 | real | > >> targettemp_avg7 | real | > >> targettemp_avg8 | real | > >> tmv_avg1 | real | > >> tmv_avg2 | real | > >> tmv_avg3 | real | > >> tmv_avg4 | real | > >> tmv_avg5 | real | > >> tmv_avg6 | real | > >> tmv_avg7 | real | > >> tmv_avg8 | real | > >> tsoil_avg1 | real | > >> tsoil_avg2 | real | > >> tsoil_avg3 | real | > >> tsoil_avg4 | real | > >> tsoil_avg5 | real | > >> tsoil_avg6 | real | > >> tsoil_avg7 | real | > >> tsoil_avg8 | real | > >> tsoilr1 | real | > >> tsoilr2 | real | > >> tsoilr3 | real | > >> tsoilr4 | real | > >> tsoilr5 | real | > >> tsoilr6 | real | > >> tsoilr7 | real | > >> tsoilr8 | real | > >> vp_avg | real | > >> winddir_d1_wvt | real | > >> ws_ms_avg | real | > >> wtcref_avg | real | > >> Indexes: > >> "fifteen_min_pkey" PRIMARY KEY, btree (rowid) > >> "fifteen_min_lower_idx" btree (lower(block_name::text)) > >> > >> b4warmed3=# SELECT COUNT(*) FROM fifteen_min; count > >> --------- > >> 1798711 > >> (1 row) > >> > >> > >> b4warmed3=# \d fifteen_min_stacked_propper > >> View "public.fifteen_min_stacked_propper" > >> Column | Type | Modifiers > >> ----------------+-----------------------------+----------- > >> rowid | character varying(48) | > >> time2 | timestamp without time zone | > >> block_name | character varying(8) | > >> table_name | character varying(10) | > >> batt_volt | real | > >> flag1 | integer | > >> flag2 | integer | > >> flag3 | integer | > >> airtc_avg | real | > >> airtemp_avg | real | > >> airtemp_max | real | > >> airtemp_min | real | > >> all_avgt | real | > >> am25tref1 | real | > >> ptemp_avg | real | > >> rh | real | > >> s_all_avgt_avg | real | > >> vp_avg | real | > >> winddir_d1_wvt | real | > >> ws_ms_avg | real | > >> variable | text | > >> value | real | > >> > >> b4warmed3=# SELECT COUNT(*) FROM fifteen_min_stacked_propper; count > >> ----------- > >> 428093218 > >> (1 row) > >> > > > > > > > > -- > > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To > > make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-general