Re: 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.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(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)