On Jan 18, 2013, at 10:05 AM, Igor Neyman <ineyman@xxxxxxxxxxxxxx> 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@xxxxxxx] >> Sent: Friday, January 18, 2013 10:50 AM >> To: Igor Neyman >> Cc: Kirk Wythers; pgsql-general@xxxxxxxxxxxxxx >> Subject: Re: speeding up a join query that utilizes a view >> >> >> On Jan 18, 2013, at 8:10 AM, Igor Neyman <ineyman@xxxxxxxxxxxxxx> >> 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
