|
|
 | | From: | Merlin Moncure | | Subject: | Re: query optimization help | | Date: | Fri, 14 Jan 2005 10:04:49 -0500 |
|
|
 | Please post in plaintext, not html where possible.
Your group by clause was 'myst'...was that supposed to be mystate?
Her is something you might try...use the original query form and create a function which resolves the state code from the input data...you are already doing that with upper.
So,
create function get_state_code(text) returns char(2) as
$$
select case when len($1) = 2
then upper($1)
else lookup_state_code($1)
end;
$$
language sql stable;
lookup_state_code is a similar function which is boils down to a select from a lookup table. Or, you could make a giant cast statement (when GEORGIA then GA, etc). and now your function becomes IMMUTABLE and should execute very fast. Just make sure all the states are spelled correct in the original table via domain constraint.
Merlin
-----Original Message-----
From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of sarlav kumar
Sent: Friday, January 14, 2005 9:40 AM
To: pgsqlnovice; pgsqlperform
Subject: [PERFORM] query optimization help
Hi All,
I have the following query to generate a report grouped by "states".
SELECT distinct upper(cd.state) as mystate, SUM(d.amount) as total_amount, SUM(COALESCE(d.fee,0) + COALESCE(mp.seller_fee, 0) + COALESCE(mp.buyer_fee,0)) as total_fee FROM data d left JOIN customerdata cd ON d.uid = cd.uid LEFT JOIN merchant_purchase mp ON d.id = mp.data_id WHERE d.what IN (26,0, 15) AND d.flags IN (1,9,10,12 ) AND lower(cd.country) = 'us' AND date_part('year',d.time)= 2004 GROUP BY myst
ate ORDER BY mystate;
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
|
|
| | From: | sarlav kumar | | Subject: | Re: query optimization help | | Date: | Fri, 14 Jan 2005 07:27:06 -0800 (PST) |
|
|
| --0-1162276892-1105716426=:89316
Content-Type: text/plain; charset=us-ascii
Hi,
Thanks for the help. I actually got around with it by doing the following.
I created a temporary table:
create table statesnew as select distinct state,state_code from postalcode where lower(country)='us';
And then changed the query to :
SELECT (SELECT sn.state FROM statesnew sn WHERE UPPER(cd.state) IN (sn.state, sn.state_code)) as mystate, SUM(d.amount) as total_amount, SUM(COALESCE(d.fee,0) + COALESCE(mp.seller_fee, 0) + COALESCE(mp.buyer_fee,0)) as total_fee FROM data d JOIN customerdata cd ON d.uid = cd.uid LEFT JOIN merchant_purchase mp ON d.id = mp.data_id WHERE d.what IN (26,0, 15) AND d.flags IN (1,9,10,12 ) AND lower(cd.country
) = 'us' AND date_part('year', d.time) = 2004 GROUP BY mystate ORDER BY mystate;
This worked well, as it reduced the number of entries it had to search from.
I am not sure how to use the function you have written. Can you give me pointers on that?
Thanks,
Saranya
Merlin Moncure wrote:
Please post in plaintext, not html where possible.
Your group by clause was 'myst'...was that supposed to be mystate?
Yes, It is mystate. It continues on the next line:)
Her is something you might try...use the original query form and create a function which resolves the state code from the input data...you are already doing that with upper.
So,
create function get_state_code(text) returns char(2) as
$$
select case when len($1) = 2
then upper($1)
else lookup_state_code($1)
end;
$$
language sql stable;
lookup_state_code is a similar function which is boils down to a select from a lookup table. Or, you could make a giant cast statement (when GEORGIA then GA, etc). and now your function becomes IMMUTABLE and should execute very fast. Just make sure all the states are spelled correct in the original table via domain constraint.
Merlin
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--0-1162276892-1105716426=:89316
Content-Type: text/html; charset=us-ascii
Hi,
Thanks for the help. I actually got around with it by doing the following.
I created a temporary table:
create table statesnew as select distinct state,state_code from postalcode where lower(country)='us';
And then changed the query to :
SELECT (SELECT sn.state FROM statesnew sn WHERE UPPER(cd.state) IN (sn.state, sn.state_code)) as mystate, SUM(d.amount) as total_amount, SUM(COALESCE(d.fee,0) + COALESCE(mp.seller_fee, 0) + COALESCE(mp.buyer_fee,0)) as total_fee FROM data d JOIN customerdata cd ON d.uid = cd.uid LEFT JOIN merchant_purchase mp ON d.id = mp.data_id WHERE d.what IN (26,0, 15) AND d.flags IN (1,9,10,12 ) AND lower(cd.country
) = 'us' AND date_part('year', d.time) = 2004 GROUP BY mystate ORDER BY mystate;
This worked well, as it reduced the number of entries it had to search from.
I am not sure how to use the function you have written. Can you give me pointers on that?
Thanks,
Saranya
Merlin Moncure <merlin.moncure@rcsonline.com> wrote:
Please post in plaintext, not html where possible.
Your group by clause was 'myst'...was that supposed to be mystate?
Yes, It is mystate. It continues on the next line:)
Her is something you might try...use the original query form and create a function which resolves the state code from the input data...you are already doing that with upper.
So,
create function get_state_code(text) returns char(2) as
$$
select case when len($1) = 2
then upper($1)
else lookup_state_code($1)
end;
$$
language sql stable;
lookup_state_code is a similar function which is boils down to a select from a lookup table. Or, you could make a giant cast statement (when GEORGIA then GA, etc). and now your function becomes IMMUTABLE and should execute very fast. Just make sure all the states are spelled correct in the original table via domain constraint.
Merlin
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--0-1162276892-1105716426=:89316--
|
|
|
