From: | "Gregory S(dot) Williamson" <gsw(at)globexplorer(dot)com> |
---|---|
To: | Nörder-Tuitje, Marcus <noerder-tuitje(at)technology(dot)de>, <db(at)zigo(dot)dhs(dot)org> |
Cc: | <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Horribly slow query/ sequential scan |
Date: | 2007-01-09 12:59:21 |
Message-ID: | 71E37EF6B7DCC1499CEA0316A256832802B3EB8D@loki.wc.globexplorer.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Thanks for the suggestion ... I will try it when I've had some sleep and the server is quiet again ... the IN seems to have improved markedly since the 7.4 release, as advertised, so I will be interested in trying this.
GSW
-----Original Message-----
From: Nörder-Tuitje, Marcus [mailto:noerder-tuitje(at)technology(dot)de]
Sent: Tue 1/9/2007 4:50 AM
To: db(at)zigo(dot)dhs(dot)org; Gregory S. Williamson
Cc: pgsql-performance(at)postgresql(dot)org
Subject: AW: [PERFORM] Horribly slow query/ sequential scan
Forget abount "IN". Its horribly slow.
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(at)postgresql(dot)org
[mailto:pgsql-performance-owner(at)postgresql(dot)org]Im Auftrag von
db(at)zigo(dot)dhs(dot)org
Gesendet: Dienstag, 9. Januar 2007 13:36
An: Gregory S. Williamson
Cc: pgsql-performance(at)postgresql(dot)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
-------------------------------------------------------
Click link below if it is SPAM gsw(at)globexplorer(dot)com
"https://mailscanner.globexplorer.com/dspam/dspam.cgi?signatureID=45a38ea050372117817174&user=gsw(at)globexplorer(dot)com&retrain=spam&template=history&history_page=1"
!DSPAM:45a38ea050372117817174!
-------------------------------------------------------
From | Date | Subject | |
---|---|---|---|
Next Message | Rolf Østvik | 2007-01-09 14:15:15 | Re: Worse perfomance on 8.2.0 than on 7.4.14 |
Previous Message | Gregory S. Williamson | 2007-01-09 12:56:43 | Re: Horribly slow query/ sequential scan |