> -----Original Message----- > From: Kirk Wythers [mailto:kwythers@xxxxxxx] > Sent: Thursday, January 17, 2013 12:16 AM > To: pgsql-general@xxxxxxxxxxxxxx > 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
