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@xxxxxxxxxxxxxx
[mailto:pgsql-performance-owner@xxxxxxxxxxxxxx]Im Auftrag von
db@xxxxxxxxxxxx
Gesendet: Dienstag, 9. Januar 2007 13:36
An: Gregory S. Williamson
Cc: pgsql-performance@xxxxxxxxxxxxxx
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
