speeding up a join query that utilizes a view

Started by Kirk Wythersabout 13 years ago10 messagesgeneral

kwythers@umn.edu

about 13 years ago

I am looking for advice on a performance problem. I'm pretty sure that the culprit of my slow performance is a view that is several hundred million records in size. Because it is a view, I can only index the underlying table, but because the view generates an "un-pivoted" version of the underlying table with un unnest function, I can't index the important column in the underlying table, because it doesn't exist until after the un-pivot or stacking function of the view… I know… this is all very circular.

Here is the join query that uses the view. I have

SELECT
data_key.site, data_key.canopy, data_key.measurement_interval, data_key.treatment_code, data_key.treatment_abbr, data_key.plot, fifteen_min_stacked_view.*
FROM
data_key, fifteen_min_stacked_view WHERE data_key.variable_channel = fifteen_min_stacked_view.variable AND data_key.block_name = fifteen_min_stacked_view.block_name AND fifteen_min_stacked_view.variable ~ 'tsoil'

I have tried adding indexes where I can on the join colums in the data_key table
Here is the EXPLAIN.

QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=195.20..548004.70 rows=196 width=192) (actual time=3.295..443523.222 rows=28779376 loops=1)
Hash Cond: ((fifteen_min_stacked_propper.variable = (data_key.variable_channel)::text) AND ((fifteen_min_stacked_propper.block_name)::text = (data_key.block_name)::text))
-> Subquery Scan on fifteen_min_stacked_propper (cost=0.00..547620.47 rows=2878 width=156) (actual time=0.247..424911.643 rows=28779376 loops=1)
Filter: (fifteen_min_stacked_propper.variable ~ 'tsoil'::text)
-> Index Scan using fifteen_min_pkey on fifteen_min (cost=0.00..525136.58 rows=1798711 width=1072) (actual time=0.034..96077.588 rows=428093218 loops=1)
-> Hash (cost=124.28..124.28 rows=4728 width=55) (actual time=3.036..3.036 rows=4728 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 437kB
-> Seq Scan on data_key (cost=0.00..124.28 rows=4728 width=55) (actual time=0.007..1.277 rows=4728 loops=1)
Total runtime: 444912.792 ms
(9 rows)

Any ideas would be much appreciated

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

Igor Neyman

ineyman@perceptron.com

about 13 years ago

In reply to: Kirk Wythers (#1)

Re: speeding up a join query that utilizes a view

-----Original Message-----
From: Kirk Wythers [mailto:kwythers@umn.edu]
Sent: Thursday, January 17, 2013 12:16 AM
To: pgsql-general@postgresql.org
Subject: speeding up a join query that utilizes a view

I am looking for advice on a performance problem. I'm pretty sure that
the culprit of my slow performance is a view that is several hundred
million records in size. Because it is a view, I can only index the
underlying table, but because the view generates an "un-pivoted"
version of the underlying table with un unnest function, I can't index
the important column in the underlying table, because it doesn't exist
until after the un-pivot or stacking function of the view... I know... this
is all very circular.

Here is the join query that uses the view. I have

SELECT
data_key.site,
data_key.canopy,
data_key.measurement_interval,
data_key.treatment_code,
data_key.treatment_abbr,
data_key.plot,
fifteen_min_stacked_view.*
FROM
data_key,
fifteen_min_stacked_view
WHERE
data_key.variable_channel =
fifteen_min_stacked_view.variable AND data_key.block_name =
fifteen_min_stacked_view.block_name
AND fifteen_min_stacked_view.variable ~ 'tsoil'

I have tried adding indexes where I can on the join colums in the
data_key table Here is the EXPLAIN.

QUERY PLAN
-----------------------------------------------------------------------
-----------------------------------------------------------------------
--------------------------------
Hash Join (cost=195.20..548004.70 rows=196 width=192) (actual
time=3.295..443523.222 rows=28779376 loops=1)
Hash Cond: ((fifteen_min_stacked_propper.variable =
(data_key.variable_channel)::text) AND
((fifteen_min_stacked_propper.block_name)::text =
(data_key.block_name)::text))
-> Subquery Scan on fifteen_min_stacked_propper
(cost=0.00..547620.47 rows=2878 width=156) (actual
time=0.247..424911.643 rows=28779376 loops=1)
Filter: (fifteen_min_stacked_propper.variable ~ 'tsoil'::text)
-> Index Scan using fifteen_min_pkey on fifteen_min
(cost=0.00..525136.58 rows=1798711 width=1072) (actual
time=0.034..96077.588 rows=428093218 loops=1)
-> Hash (cost=124.28..124.28 rows=4728 width=55) (actual
time=3.036..3.036 rows=4728 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 437kB
-> Seq Scan on data_key (cost=0.00..124.28 rows=4728
width=55) (actual time=0.007..1.277 rows=4728 loops=1) Total runtime:
444912.792 ms
(9 rows)

Any ideas would be much appreciated

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?

Regards,
Igor Neyman

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

Kirk Wythers

kwythers@umn.edu

about 13 years ago

In reply to: Igor Neyman (#2)

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=# SELECT COUNT(*) FROM data_key;
count
-------
4728
(1 row)

b4warmed3=# SELECT COUNT(*) FROM fifteen_min;
count
---------
1798711
(1 row)

b4warmed3=# SELECT COUNT(*) FROM fifteen_min_stacked_propper;
count
-----------
428093218
(1 row)

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

Igor Neyman

ineyman@perceptron.com

about 13 years ago

In reply to: Kirk Wythers (#3)

Re: speeding up a join query that utilizes a view

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

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

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

Kirk Wythers

kwythers@umn.edu

about 13 years ago

In reply to: Igor Neyman (#4)

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

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

Igor Neyman

ineyman@perceptron.com

about 13 years ago

In reply to: Kirk Wythers (#5)

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

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

Kirk Wythers

kwythers@umn.edu

about 13 years ago

In reply to: Igor Neyman (#6)

Re: speeding up a join query that utilizes a view

On Jan 18, 2013, at 8:10 AM, Igor Neyman <ineyman@perceptron.com> wrote:

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

Here some extra bits form the postgresql.conf file. As you can see, I have not changed much from the default settings.

#------------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------------

# - Memory -

shared_buffers = 3GB # 7GB # min 128kB
# (change requires restart)
temp_buffers = 80MB # 8MB # min 800kB
#max_prepared_transactions = 0 # zero disables the feature
# (change requires restart)
# Note: Increasing max_prepared_transactions costs ~600 bytes of shared memory
# per transaction slot, plus lock space (see max_locks_per_transaction).
# It is not advisable to set max_prepared_transactions nonzero unless you
# actively intend to use prepared transactions.
work_mem = 64MB #8MB # min 64kB
maintenance_work_mem = 128MB # min 1MB
#max_stack_depth = 2MB # min 100kB

# - Kernel Resource Usage -

#max_files_per_process = 1000 # min 25
# (change requires restart)
#shared_preload_libraries = '' # (change requires restart)

# - Cost-Based Vacuum Delay -

#vacuum_cost_delay = 0ms # 0-100 milliseconds
#vacuum_cost_page_hit = 1 # 0-10000 credits
#vacuum_cost_page_miss = 10 # 0-10000 credits
#vacuum_cost_page_dirty = 20 # 0-10000 credits
#vacuum_cost_limit = 200 # 1-10000 credits

# - Background Writer -

#bgwriter_delay = 200ms # 10-10000ms between rounds
#bgwriter_lru_maxpages = 100 # 0-1000 max buffers written/round
#bgwriter_lru_multiplier = 2.0 # 0-10.0 multipler on buffers scanned/round

# - Asynchronous Behavior -

#effective_io_concurrency = 1 # 1-1000. 0 disables prefetching

#------------------------------------------------------------------------------
# QUERY TUNING
#------------------------------------------------------------------------------

# - Planner Method Configuration -

#enable_bitmapscan = on
#enable_hashagg = on
#enable_hashjoin = on
#enable_indexscan = on
#enable_material = on
#enable_mergejoin = on
#enable_nestloop = on
#enable_seqscan = on
#enable_sort = on
#enable_tidscan = on

# - Planner Cost Constants -

#seq_page_cost = 1.0 # measured on an arbitrary scale
#random_page_cost = 4.0 # same scale as above
#cpu_tuple_cost = 0.01 # same scale as above
#cpu_index_tuple_cost = 0.005 # same scale as above
#cpu_operator_cost = 0.0025 # same scale as above
effective_cache_size = 6GB #13GB

# - Genetic Query Optimizer -

#geqo = on
#geqo_threshold = 12
#geqo_effort = 5 # range 1-10
#geqo_pool_size = 0 # selects default based on effort
#geqo_generations = 0 # selects default based on effort
#geqo_selection_bias = 2.0 # range 1.5-2.0
#geqo_seed = 0.0 # range 0.0-1.0

# - Other Planner Options -

#default_statistics_target = 100 # range 1-10000
#constraint_exclusion = partition # on, off, or partition
#cursor_tuple_fraction = 0.1 # range 0.0-1.0
#from_collapse_limit = 8
#join_collapse_limit = 8 # 1 disables collapsing of explicit
# JOIN clauses

Here is a snip from earlier that includes info about both the table that is used to build the view and the view. In short, I use the UNNEST function to un-pivot all the variables of interest in the "fifteen_min" table into the columns "variable" and "value" in the "fifteen_min_stacked_proper" view.

Thanks again.

Kirk

b4warmed3=# SELECT COUNT(*) FROM fifteen_min; count
---------
1798711
(1 row)

b4warmed3=# SELECT COUNT(*) FROM fifteen_min_stacked_propper;
count
-----------
428093218
(1 row)

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

Igor Neyman

ineyman@perceptron.com

about 13 years ago

In reply to: Kirk Wythers (#7)

Re: speeding up a join query that utilizes a view

Kirk,

Are you doing un-pivoting in most of your queries?
Did you try normalized design for fifteen_minute table?
Is there specific reason for de-normalization?

Regards,
Igor Neyman

-----Original Message-----
From: Kirk Wythers [mailto:kwythers@umn.edu]
Sent: Friday, January 18, 2013 10:50 AM
To: Igor Neyman
Cc: Kirk Wythers; pgsql-general@postgresql.org
Subject: Re: [GENERAL] speeding up a join query that utilizes a view

On Jan 18, 2013, at 8:10 AM, Igor Neyman <ineyman@perceptron.com>
wrote:

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

Here some extra bits form the postgresql.conf file. As you can see, I
have not changed much from the default settings.

#----------------------------------------------------------------------
--------
# RESOURCE USAGE (except WAL)
#----------------------------------------------------------------------
--------

# - Memory -

shared_buffers = 3GB # 7GB # min 128kB
# (change requires restart)
temp_buffers = 80MB # 8MB # min 800kB
#max_prepared_transactions = 0 # zero disables the feature
# (change requires restart) #
Note: Increasing max_prepared_transactions costs ~600 bytes of shared
memory # per transaction slot, plus lock space (see
max_locks_per_transaction).
# It is not advisable to set max_prepared_transactions nonzero unless
you # actively intend to use prepared transactions.
work_mem = 64MB #8MB # min 64kB
maintenance_work_mem = 128MB # min 1MB
#max_stack_depth = 2MB # min 100kB

# - Kernel Resource Usage -

#max_files_per_process = 1000 # min 25
# (change requires restart)
#shared_preload_libraries = '' # (change requires restart)

# - Cost-Based Vacuum Delay -

#vacuum_cost_delay = 0ms # 0-100 milliseconds
#vacuum_cost_page_hit = 1 # 0-10000 credits
#vacuum_cost_page_miss = 10 # 0-10000 credits
#vacuum_cost_page_dirty = 20 # 0-10000 credits
#vacuum_cost_limit = 200 # 1-10000 credits

# - Background Writer -

#bgwriter_delay = 200ms # 10-10000ms between rounds
#bgwriter_lru_maxpages = 100 # 0-1000 max buffers
written/round
#bgwriter_lru_multiplier = 2.0 # 0-10.0 multipler on buffers
scanned/round

# - Asynchronous Behavior -

#effective_io_concurrency = 1 # 1-1000. 0 disables
prefetching

#----------------------------------------------------------------------
--------
# QUERY TUNING
#----------------------------------------------------------------------
--------

# - Planner Method Configuration -

#enable_bitmapscan = on
#enable_hashagg = on
#enable_hashjoin = on
#enable_indexscan = on
#enable_material = on
#enable_mergejoin = on
#enable_nestloop = on
#enable_seqscan = on
#enable_sort = on
#enable_tidscan = on

# - Planner Cost Constants -

#seq_page_cost = 1.0 # measured on an arbitrary
scale
#random_page_cost = 4.0 # same scale as above
#cpu_tuple_cost = 0.01 # same scale as above
#cpu_index_tuple_cost = 0.005 # same scale as above
#cpu_operator_cost = 0.0025 # same scale as above
effective_cache_size = 6GB #13GB

# - Genetic Query Optimizer -

#geqo = on
#geqo_threshold = 12
#geqo_effort = 5 # range 1-10
#geqo_pool_size = 0 # selects default based on
effort
#geqo_generations = 0 # selects default based on
effort
#geqo_selection_bias = 2.0 # range 1.5-2.0
#geqo_seed = 0.0 # range 0.0-1.0

# - Other Planner Options -

#default_statistics_target = 100 # range 1-10000
#constraint_exclusion = partition # on, off, or partition
#cursor_tuple_fraction = 0.1 # range 0.0-1.0
#from_collapse_limit = 8
#join_collapse_limit = 8 # 1 disables collapsing of
explicit
# JOIN clauses

Here is a snip from earlier that includes info about both the table
that is used to build the view and the view. In short, I use the UNNEST
function to un-pivot all the variables of interest in the "fifteen_min"
table into the columns "variable" and "value" in the
"fifteen_min_stacked_proper" view.

Thanks again.

Kirk

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Kirk Wythers

kwythers@umn.edu

about 13 years ago

In reply to: Igor Neyman (#8)

Re: speeding up a join query that utilizes a view

On Jan 18, 2013, at 10:05 AM, Igor Neyman <ineyman@perceptron.com> wrote:

Kirk,

Are you doing un-pivoting in most of your queries?
Did you try normalized design for fifteen_minute table?
Is there specific reason for de-normalization?

Regards,
Igor Neyman

Thanks Igor. The only reason I'm de-normalizing with unnest, is so I can perform a join on variable_name with the table "data_key". I't kind of a crazy design, but it is what I was given to work with. Here is the join that takes so dang long to perform:

SELECT
data_key.site,
data_key.canopy,
data_key.measurement_interval,
data_key.treatment_code,
data_key.treatment_abbr,
data_key.plot,
fifteen_min_stacked_propper.*
FROM
data_key,
fifteen_min_stacked_propper
WHERE
data_key.variable_channel = fifteen_min_stacked_propper.variable AND data_key.block_name = fifteen_min_stacked_propper.block_name
--AND 2012 = EXTRACT(YEAR FROM time2)
--AND fifteen_min_stacked_propper.block_name ~ 'b4warm_[ace]'
--AND fifteen_min_stacked_propper.value IS NOT NULL
AND fifteen_min_stacked_propper.variable ~ 'tsoil'

The whole point of the de-normalized table "fifteen_min_stacked_propper" is so that variable names in fifteen_min_stacked_propper.variable can be used to join on data_key.variable_channel.

Does that make sense?

Kirk

-----Original Message-----
From: Kirk Wythers [mailto:kwythers@umn.edu]
Sent: Friday, January 18, 2013 10:50 AM
To: Igor Neyman
Cc: Kirk Wythers; pgsql-general@postgresql.org
Subject: Re: [GENERAL] speeding up a join query that utilizes a view

On Jan 18, 2013, at 8:10 AM, Igor Neyman <ineyman@perceptron.com>
wrote:

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

Here some extra bits form the postgresql.conf file. As you can see, I
have not changed much from the default settings.

#----------------------------------------------------------------------
--------
# RESOURCE USAGE (except WAL)
#----------------------------------------------------------------------
--------

# - Memory -

shared_buffers = 3GB # 7GB # min 128kB
# (change requires restart)
temp_buffers = 80MB # 8MB # min 800kB
#max_prepared_transactions = 0 # zero disables the feature
# (change requires restart) #
Note: Increasing max_prepared_transactions costs ~600 bytes of shared
memory # per transaction slot, plus lock space (see
max_locks_per_transaction).
# It is not advisable to set max_prepared_transactions nonzero unless
you # actively intend to use prepared transactions.
work_mem = 64MB #8MB # min 64kB
maintenance_work_mem = 128MB # min 1MB
#max_stack_depth = 2MB # min 100kB

# - Kernel Resource Usage -

#max_files_per_process = 1000 # min 25
# (change requires restart)
#shared_preload_libraries = '' # (change requires restart)

# - Cost-Based Vacuum Delay -

#vacuum_cost_delay = 0ms # 0-100 milliseconds
#vacuum_cost_page_hit = 1 # 0-10000 credits
#vacuum_cost_page_miss = 10 # 0-10000 credits
#vacuum_cost_page_dirty = 20 # 0-10000 credits
#vacuum_cost_limit = 200 # 1-10000 credits

# - Background Writer -

#bgwriter_delay = 200ms # 10-10000ms between rounds
#bgwriter_lru_maxpages = 100 # 0-1000 max buffers
written/round
#bgwriter_lru_multiplier = 2.0 # 0-10.0 multipler on buffers
scanned/round

# - Asynchronous Behavior -

#effective_io_concurrency = 1 # 1-1000. 0 disables
prefetching

#----------------------------------------------------------------------
--------
# QUERY TUNING
#----------------------------------------------------------------------
--------

# - Planner Method Configuration -

#enable_bitmapscan = on
#enable_hashagg = on
#enable_hashjoin = on
#enable_indexscan = on
#enable_material = on
#enable_mergejoin = on
#enable_nestloop = on
#enable_seqscan = on
#enable_sort = on
#enable_tidscan = on

# - Planner Cost Constants -

#seq_page_cost = 1.0 # measured on an arbitrary
scale
#random_page_cost = 4.0 # same scale as above
#cpu_tuple_cost = 0.01 # same scale as above
#cpu_index_tuple_cost = 0.005 # same scale as above
#cpu_operator_cost = 0.0025 # same scale as above
effective_cache_size = 6GB #13GB

# - Genetic Query Optimizer -

#geqo = on
#geqo_threshold = 12
#geqo_effort = 5 # range 1-10
#geqo_pool_size = 0 # selects default based on
effort
#geqo_generations = 0 # selects default based on
effort
#geqo_selection_bias = 2.0 # range 1.5-2.0
#geqo_seed = 0.0 # range 0.0-1.0

# - Other Planner Options -

#default_statistics_target = 100 # range 1-10000
#constraint_exclusion = partition # on, off, or partition
#cursor_tuple_fraction = 0.1 # range 0.0-1.0
#from_collapse_limit = 8
#join_collapse_limit = 8 # 1 disables collapsing of
explicit
# JOIN clauses

Here is a snip from earlier that includes info about both the table
that is used to build the view and the view. In short, I use the UNNEST
function to un-pivot all the variables of interest in the "fifteen_min"
table into the columns "variable" and "value" in the
"fifteen_min_stacked_proper" view.

Thanks again.

Kirk

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

#10

Igor Neyman

ineyman@perceptron.com

about 13 years ago

In reply to: Kirk Wythers (#9)

Re: speeding up a join query that utilizes a view

Kirk,

Are you limited to "pure" SQL or procedural language (PgPlSQL) allowed?
If PgPlSQL is allowed, you could normalize fifteen_min table, break it into several tables (one for a_dc, another for a_dif, another for a_targettemp, and so on...) and use dynamic sql inside PlPgSQL function to join with the proper table.
In that case you could index normalized tables properly, also not having table rows as wide as they are now helps.
Thus you'll avoid sequencial scan on a big and wide table.

Also increasing default_statistics_target may help, this:

Index Scan using fifteen_min_pkey on fifteen_min (cost=0.00..525136.58 rows=1798711 width=1072) (actual time=0.034..96077.588 rows=428093218 loops=1)

Shows to big of a difference between estimated and actual row counts. Are these tables analyzed often enough?

Regards,
Igor Neyman

-----Original Message-----
From: Kirk Wythers [mailto:kwythers@umn.edu]
Sent: Friday, January 18, 2013 11:15 AM
To: Igor Neyman
Cc: Kirk Wythers; pgsql-general@postgresql.org
Subject: Re: [GENERAL] speeding up a join query that utilizes a view

On Jan 18, 2013, at 10:05 AM, Igor Neyman <ineyman@perceptron.com>
wrote:

Kirk,

Are you doing un-pivoting in most of your queries?
Did you try normalized design for fifteen_minute table?
Is there specific reason for de-normalization?

Regards,
Igor Neyman

Thanks Igor. The only reason I'm de-normalizing with unnest, is so I
can perform a join on variable_name with the table "data_key". I't kind
of a crazy design, but it is what I was given to work with. Here is the
join that takes so dang long to perform:

SELECT
data_key.site,
data_key.canopy,
data_key.measurement_interval,
data_key.treatment_code,
data_key.treatment_abbr,
data_key.plot,
fifteen_min_stacked_propper.*
FROM
data_key,
fifteen_min_stacked_propper
WHERE
data_key.variable_channel = fifteen_min_stacked_propper.variable
AND data_key.block_name = fifteen_min_stacked_propper.block_name
--AND 2012 = EXTRACT(YEAR FROM time2)
--AND fifteen_min_stacked_propper.block_name ~ 'b4warm_[ace]'
--AND fifteen_min_stacked_propper.value IS NOT NULL AND
fifteen_min_stacked_propper.variable ~ 'tsoil'

The whole point of the de-normalized table
"fifteen_min_stacked_propper" is so that variable names in
fifteen_min_stacked_propper.variable can be used to join on
data_key.variable_channel.

Does that make sense?

Kirk

-----Original Message-----
From: Kirk Wythers [mailto:kwythers@umn.edu]
Sent: Friday, January 18, 2013 10:50 AM
To: Igor Neyman
Cc: Kirk Wythers; pgsql-general@postgresql.org
Subject: Re: [GENERAL] speeding up a join query that utilizes a view

On Jan 18, 2013, at 8:10 AM, Igor Neyman <ineyman@perceptron.com>
wrote:

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

Here some extra bits form the postgresql.conf file. As you can see,
I have not changed much from the default settings.

#-------------------------------------------------------------------

-

--
--------
# RESOURCE USAGE (except WAL)
#-------------------------------------------------------------------

-

--
--------

# - Memory -

shared_buffers = 3GB # 7GB # min 128kB
# (change requires restart)
temp_buffers = 80MB # 8MB # min 800kB
#max_prepared_transactions = 0 # zero disables the feature
# (change requires restart) #
Note: Increasing max_prepared_transactions costs ~600 bytes of
shared memory # per transaction slot, plus lock space (see
max_locks_per_transaction).
# It is not advisable to set max_prepared_transactions nonzero

unless

you # actively intend to use prepared transactions.
work_mem = 64MB #8MB # min 64kB
maintenance_work_mem = 128MB # min 1MB
#max_stack_depth = 2MB # min 100kB

# - Kernel Resource Usage -

#max_files_per_process = 1000 # min 25
# (change requires restart)
#shared_preload_libraries = '' # (change requires restart)

# - Cost-Based Vacuum Delay -

#vacuum_cost_delay = 0ms # 0-100 milliseconds
#vacuum_cost_page_hit = 1 # 0-10000 credits
#vacuum_cost_page_miss = 10 # 0-10000 credits
#vacuum_cost_page_dirty = 20 # 0-10000 credits
#vacuum_cost_limit = 200 # 1-10000 credits

# - Background Writer -

#bgwriter_delay = 200ms # 10-10000ms between rounds
#bgwriter_lru_maxpages = 100 # 0-1000 max buffers
written/round
#bgwriter_lru_multiplier = 2.0 # 0-10.0 multipler on

buffers

scanned/round

# - Asynchronous Behavior -

#effective_io_concurrency = 1 # 1-1000. 0 disables
prefetching

#-------------------------------------------------------------------

-

--
--------
# QUERY TUNING
#-------------------------------------------------------------------

-

--
--------

# - Planner Method Configuration -

#enable_bitmapscan = on
#enable_hashagg = on
#enable_hashjoin = on
#enable_indexscan = on
#enable_material = on
#enable_mergejoin = on
#enable_nestloop = on
#enable_seqscan = on
#enable_sort = on
#enable_tidscan = on

# - Planner Cost Constants -

#seq_page_cost = 1.0 # measured on an arbitrary
scale
#random_page_cost = 4.0 # same scale as above
#cpu_tuple_cost = 0.01 # same scale as above
#cpu_index_tuple_cost = 0.005 # same scale as above
#cpu_operator_cost = 0.0025 # same scale as above
effective_cache_size = 6GB #13GB

# - Genetic Query Optimizer -

#geqo = on
#geqo_threshold = 12
#geqo_effort = 5 # range 1-10
#geqo_pool_size = 0 # selects default based on
effort
#geqo_generations = 0 # selects default based on
effort
#geqo_selection_bias = 2.0 # range 1.5-2.0
#geqo_seed = 0.0 # range 0.0-1.0

# - Other Planner Options -

#default_statistics_target = 100 # range 1-10000
#constraint_exclusion = partition # on, off, or partition
#cursor_tuple_fraction = 0.1 # range 0.0-1.0
#from_collapse_limit = 8
#join_collapse_limit = 8 # 1 disables collapsing of
explicit
# JOIN clauses

Here is a snip from earlier that includes info about both the table
that is used to build the view and the view. In short, I use the
UNNEST function to un-pivot all the variables of interest in the

"fifteen_min"

table into the columns "variable" and "value" in the
"fifteen_min_stacked_proper" view.

Thanks again.

Kirk

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@postgresql.org) To
make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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