In-Reply-To: 
 <16F953410A0F1346848DCB476A989CFE087890@swtexchange2.technology.de>
References: 
 <16F953410A0F1346848DCB476A989CFE087890@swtexchange2.technology.de>
Mime-Version: 1.0 (Apple Message framework v752.3)
Content-Type: text/plain; charset=ISO-8859-1; delsp=yes; format=flowed
Message-Id: <5FF559BF-4923-4122-AFC4-73F2ADAD9BC6@fastcrypt.com>
Cc: <db@zigo.dhs.org>, "Gregory S. Williamson" <gsw@globexplorer.com>,
	<pgsql-performance@postgresql.org>
Content-Transfer-Encoding: quoted-printable
From: Dave Cramer <pg@fastcrypt.com>
Subject: Re: Horribly slow query/ sequential scan
Date: Tue, 9 Jan 2007 09:16:57 -0500
To: =?ISO-8859-1?Q?"N=F6rder-Tuitje,_Marcus"?= <noerder-tuitje@technology.de>


On 9-Jan-07, at 7:50 AM, N=F6rder-Tuitje, Marcus wrote:

> Forget abount "IN". Its horribly slow.

I think that statement above was historically correct, but is now =20
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 =20
> b.report_s_date =3D '2006-09-30' and w.report_id =3D 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), =20
> consider extending w with cols 1,2,3.
>
>
> regards,
> marcus
>
> -----Urspr=FCngliche 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 =20
> 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 =20=

>> 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 =3D
>> '2006-09-30')
>> and (w.client_id =3D '227400001' or w.client_id =3D '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 =20
> b.report_s_date =3D
> '2006-09-30')
>    and (w.client_id =3D '227400001' or w.client_id =3D '2274000010')
> group by 1,2,3
> order by 1,2,3;
>
> /Dennis
>
>
> ---------------------------(end of =20
> broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
>                 http://www.postgresql.org/about/donate
>
>
>
> ---------------------------(end of =20
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>