Re: speeding up a join query that utilizes a view
| От | Kirk Wythers |
|---|---|
| Тема | Re: speeding up a join query that utilizes a view |
| Дата | |
| Msg-id | E0E1E6A6-9A18-41C8-B579-013D278DCB82@umn.edu обсуждение исходный текст |
| Ответ на | Re: speeding up a join query that utilizes a view (Igor Neyman <ineyman@perceptron.com>) |
| Ответы |
Re: speeding up a join query that utilizes a view
(Igor Neyman <ineyman@perceptron.com>)
|
| Список | pgsql-general |
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)
В списке pgsql-general по дате отправления: