Re: speeding up a join query that utilizes a view

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(at)umn(dot)edu]
> Sent: Friday, January 18, 2013 10:50 AM
> To: Igor Neyman
> Cc: Kirk Wythers; pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] speeding up a join query that utilizes a view
>
>
> On Jan 18, 2013, at 8:10 AM, Igor Neyman <ineyman(at)perceptron(dot)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)
>