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]

 



On Jan 17, 2013, at 3:51 PM, Igor Neyman <ineyman@xxxxxxxxxxxxxx> wrote:

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

Sorry

pg 9.1

OS X 10.8 server. 
32 G ram 8 cores

I thought what you meant by index definition is at the bottom of the \d table-name. For example: 
>> 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))

on data_key. 

I'm not sure what you mean by config parameters? Output from pg_config?

~$ pg_config 
BINDIR = /usr/bin
DOCDIR = /usr/share/doc/postgresql
HTMLDIR = /usr/share/postgresql
INCLUDEDIR = /usr/include
PKGINCLUDEDIR = /usr/include/postgresql
INCLUDEDIR-SERVER = /usr/include/postgresql/server
LIBDIR = /usr/lib
PKGLIBDIR = /usr/lib/postgresql
LOCALEDIR = /usr/share/locale
MANDIR = /usr/share/man
SHAREDIR = /usr/share/postgresql
SYSCONFDIR = /private/etc/postgresql
PGXS = /usr/lib/postgresql/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--infodir=/usr/share/info' '--disable-dependency-tracking' '--prefix=/usr' '--sbindir=/usr/libexec' '--sysconfdir=/private/etc' '--mandir=/usr/share/man' '--localstatedir=/private/var/pgsql' '--htmldir=/usr/share/postgresql' '--enable-thread-safety' '--enable-dtrace' '--with-tcl' '--with-perl' '--with-python' '--with-gssapi' '--with-krb5' '--with-pam' '--with-ldap' '--with-bonjour' '--with-openssl' '--with-libxml' '--with-libxslt' '--with-system-tzdata=/usr/share/zoneinfo' 'CC=/Applications/Xcode.app/Contents/Developer/Toolchains/OSX10.8.xctoolchain/usr/bin/cc' 'CFLAGS=-arch x86_64 -pipe -Os -g -Wall -Wno-deprecated-declarations' 'LDFLAGS=-arch x86_64 -pipe -Os -g -Wall -Wno-deprecated-declarations' 'LDFLAGS_EX=-mdynamic-no-pic'
CC = /Applications/Xcode.app/Contents/Developer/Toolchains/OSX10.8.xctoolchain/usr/bin/cc
CPPFLAGS = -I/usr/include/libxml2
CFLAGS = -arch x86_64 -pipe -Os -g -Wall -Wno-deprecated-declarations -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv
CFLAGS_SL = 
LDFLAGS = -arch x86_64 -pipe -Os -g -Wall -Wno-deprecated-declarations -Wl,-dead_strip_dylibs
LDFLAGS_EX = -mdynamic-no-pic
LDFLAGS_SL = 
LIBS = -lpgport -lxslt -lxml2 -lpam -lssl -lcrypto -lgssapi_krb5 -lz -lreadline -lm 
VERSION = PostgreSQL 9.1.4

Does that help? 

> 
>> -----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