Re: speeding up a join query that utilizes a view

> -----Original Message-----
> From: Kirk Wythers [mailto:kwythers(at)umn(dot)edu]
> Sent: Thursday, January 17, 2013 12:16 AM
> To: pgsql-general(at)postgresql(dot)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