Re: Horribly slow query/ sequential scan
От | Dave Cramer |
---|---|
Тема | Re: Horribly slow query/ sequential scan |
Дата | |
Msg-id | 5FF559BF-4923-4122-AFC4-73F2ADAD9BC6@fastcrypt.com обсуждение исходный текст |
Ответ на | Re: Horribly slow query/ sequential scan (Nörder-Tuitje, Marcus <noerder-tuitje@technology.de>) |
Список | pgsql-performance |
On 9-Jan-07, at 7:50 AM, Nörder-Tuitje, Marcus wrote: > Forget abount "IN". Its horribly slow. I think that statement above was historically correct, but is now incorrect. IN has been optimized quite significantly since 7.4 Dave > > try : > > select w.appid, > w.rate, > w.is_subscribed, > sum(w.hits) AS Hits, > sum(w.sius) AS IUs, > sum(w.total_amnt) AS Total, > sum(w.hits) * w.rate AS ByHits, > sum(w.sius) * w.rate AS BYIUS > from bill_rpt_work w > where (select b.report_id from billing_reports b where > b.report_s_date = '2006-09-30' and w.report_id = b.report_id) > and w.client_id IN ('227400001','2274000010') > group by 1,2,3 > order by 1,2,3; > > > > should by faster; > > assuming : index on report_id in b; index on report_id, client_id in w > > to enforce useage of indexes on grouping (depends on result size), > consider extending w with cols 1,2,3. > > > regards, > marcus > > -----Ursprüngliche Nachricht----- > Von: pgsql-performance-owner@postgresql.org > [mailto:pgsql-performance-owner@postgresql.org]Im Auftrag von > db@zigo.dhs.org > Gesendet: Dienstag, 9. Januar 2007 13:36 > An: Gregory S. Williamson > Cc: pgsql-performance@postgresql.org > Betreff: Re: [PERFORM] Horribly slow query/ sequential scan > > > I don't think I understand the idea behind this query. Do you > really need > billing_reports twice? > >> The query: >> explain analyze select >> w.appid,w.rate,w.is_subscribed,sum(w.hits) AS Hits ,sum(w.sius) AS >> IUs, >> sum(w.total_amnt) AS Total,sum(w.hits) * w.rate AS ByHits, >> sum(w.sius) * w.rate AS BYIUS >> from bill_rpt_work w, billing_reports b >> where w.report_id in >> (select b.report_id from billing_reports where b.report_s_date = >> '2006-09-30') >> and (w.client_id = '227400001' or w.client_id = '2274000010') >> group by 1,2,3 >> order by 1,2,3; > > Maybe this is the query you want instead? > > select w.appid, > w.rate, > w.is_subscribed, > sum(w.hits) AS Hits, > sum(w.sius) AS IUs, > sum(w.total_amnt) AS Total, > sum(w.hits) * w.rate AS ByHits, > sum(w.sius) * w.rate AS BYIUS > from bill_rpt_work w > where w.report_id in > (select b.report_id from billing_reports b where > b.report_s_date = > '2006-09-30') > and (w.client_id = '227400001' or w.client_id = '2274000010') > group by 1,2,3 > order by 1,2,3; > > /Dennis > > > ---------------------------(end of > broadcast)--------------------------- > TIP 7: You can help support the PostgreSQL project by donating at > > http://www.postgresql.org/about/donate > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings >
В списке pgsql-performance по дате отправления: