Search Postgresql Archives

Re: speeding up a join query that utilizes a view

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux