Re: query optimization help

Поиск
Список
Период
Сортировка
От Merlin Moncure
Тема Re: query optimization help
Дата
Msg-id 6EE64EF3AB31D5448D0007DD34EEB3412A75B3@Herge.rcsinc.local
обсуждение исходный текст
Ответ на query optimization help  (sarlav kumar <sarlavk@yahoo.com>)
Ответы Re: query optimization help  (sarlav kumar <sarlavk@yahoo.com>)
Список pgsql-performance
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
theinput 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
giantcast 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.uidLEFT 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;


В списке pgsql-performance по дате отправления:

Предыдущее
От: sarlav kumar
Дата:
Сообщение: query optimization help
Следующее
От: sarlav kumar
Дата:
Сообщение: Re: query optimization help