Re: How to save the results of replace(split_part(trim(both ' ' from "vx6000__12channel"), '__', 2), 'channel', 'myChannel') in my query into a temp variable?

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