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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
