Search Postgresql Archives

Re: speeding up a join query that utilizes a view

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



What about index definition, Postgres version, config parameters?
Hardware configuration would be helpful too.


> -----Original Message-----
> From: Kirk Wythers [mailto:kwythers@xxxxxxx]
> Sent: Thursday, January 17, 2013 3:59 PM
> To: Igor Neyman
> Cc: Kirk Wythers; pgsql-general@xxxxxxxxxxxxxx
> Subject: Re:  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)


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux