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@postgresql.org
[mailtogsql-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

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@technology.de]
Sent: Tue 1/9/2007 4:50 AM
To: db@zigo.dhs.org; Gregory S. Williamson
Cc: pgsql-performance@postgresql.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@postgresql.org
[mailtogsql-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




-------------------------------------------------------
Click link below if it is SPAM gsw@globexplorer.com
"https://mailscanner.globexplorer.com/dspam/dspam.cgi?signatureID=45a38ea050372117817174&user= gsw@globexplorer.com&retrain=spam&template=history &history_page=1"
!DSPAM:45a38ea050372117817174!
-------------------------------------------------------






---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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
> [mailtogsql-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
>


---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate