On 12/12/2009 09:59 AM, zxo102 ouyang wrote:
Hi everyone,
I have a big query (see below attached) in which all where clauses
have sc.channel = replace(split_part(trim(both ' ' from
ec.instantance_flux),'__',2),'channel','myChannel').
The value of ec.instantance_flux is like the format:
"vx6000__12channel". I want to replace "channel" with "myChannel":
"vx6000__12myChannel".
Since the query take all most 40 minutes, I want to "optimize" it to see
if i can get it done in shorter time. My question is: are there any way
just do once:
replace(split_part(trim(both ' ' from
ec.instantance_flux),'__',2),'channel','myChannel')
and save its result into "temp". The other where clause just use
sc.channel = temp
instead of doing
sc.channel
= replace(split_part(trim(both ' ' from
ec.instantance_flux),'__',2),'channel','myChannel')
for 12 times in my query.
Thanks for your help in advance.
OUyang
Are you sure that is what is taking so much time? I'd bet its not. You should find the thing that is taking the most time and optimize that.
Have you run your query with explain analyze? If you post the output here (or use http://explain.depesz.com/) someone might be able to point you in a helpful direction.
I'm not saying the replace(split_part... isn't worth optimizing, it probably is, I'm just saying, fix the slowest thing first.
-Andy
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
