Re: speeding up a join query that utilizes a view

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

> -----Original Message-----
> From: Kirk Wythers [mailto:kwythers(at)umn(dot)edu]
> Sent: Thursday, January 17, 2013 5:05 PM
> To: Igor Neyman
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] speeding up a join query that utilizes a view
>
>
> On Jan 17, 2013, at 3:51 PM, Igor Neyman <ineyman(at)perceptron(dot)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(at)umn(dot)edu]
> >> Sent: Thursday, January 17, 2013 3:59 PM
> >> To: Igor Neyman
> >> Cc: Kirk Wythers; pgsql-general(at)postgresql(dot)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)