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