Re: speeding up a join query that utilizes a view
| От | Igor Neyman |
|---|---|
| Тема | Re: speeding up a join query that utilizes a view |
| Дата | |
| Msg-id | A76B25F2823E954C9E45E32FA49D70EC08F72AED@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 |
Yes, my mistake, I meant to ask about fifteen_min_stacked_view definition, and Postgres parameters from postgresql.conf configurationfile, at least those - modified from default setting and related to "resource consumption" and "query tuning". Regards, Igor Neyman > -----Original Message----- > From: Kirk Wythers [mailto:kwythers@umn.edu] > Sent: Thursday, January 17, 2013 5:05 PM > To: Igor Neyman > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] speeding up a join query that utilizes a view > > > On Jan 17, 2013, at 3:51 PM, Igor Neyman <ineyman@perceptron.com> > 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.xctoo > lchain/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.xctoolcha > in/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@umn.edu] > >> Sent: Thursday, January 17, 2013 3:59 PM > >> To: Igor Neyman > >> Cc: Kirk Wythers; pgsql-general@postgresql.org > >> Subject: Re: [GENERAL] 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)
В списке pgsql-general по дате отправления: