speeding up a join query that utilizes a view - Mailing list pgsql-general

I am looking for advice on a performance problem. I'm pretty sure that the culprit of my slow performance is a view
thatis several hundred million records in size. Because it is a view, I can only index the underlying table, but
becausethe view generates an "un-pivoted" version of the underlying table with un unnest function, I can't index the
importantcolumn 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.643rows=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.588rows=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