Voila ! You da man !
& other expressions of awe and appreciation ...
HAving burdened others with my foolishness too often, I hesitate to ask, but could someone either point me to a reference or explain what the difference might be ... I can see it with the eyes but I am having trouble understanding what Informix might have been doing to my (bad ?) SQL to "fix" the query. Seeing a redundancy and eliminating it ?
The explain analyze for "db"'s sql (slightly faster than Informix on an older Sun machine ... about 20%):
GroupAggregate (cost=64.35..64.75 rows=8 width=58) (actual time=0.612..0.629 rows=2 loops=1)
-> Sort (cost=64.35..64.37 rows=8 width=58) (actual time=0.463..0.476 rows=37 loops=1)
Sort Key: w.appid, w.rate, w.is_subscribed
-> Nested Loop (cost=8.11..64.23 rows=8 width=58) (actual time=0.130..0.211 rows=37 loops=1)
Join Filter: ("inner".report_id = "outer".report_id)
-> HashAggregate (cost=3.95..3.96 rows=1 width=4) (actual time=0.035..0.035 rows=1 loops=1)
-> Index Scan using billrpt_sdate_ndx on billing_reports b (cost=0.00..3.94 rows=1 width=4) (actual time=0.021..0.023 rows=1 loops=1)
Index Cond: (report_s_date = '2006-09-30'::date)
-> Bitmap Heap Scan on bill_rpt_work w (cost=4.17..59.92 rows=28 width=62) (actual time=0.084..0.111 rows=37 loops=1)
Recheck Cond: (((w.report_id = "outer".report_id) AND ((w.client_id)::text = '227400001'::text)) OR ((w.report_id = "outer".report_id) AND ((w.client_id)::text = '2274000010'::text)))
-> BitmapOr (cost=4.17..4.17 rows=28 width=0) (actual time=0.078..0.078 rows=0 loops=1)
-> Bitmap Index Scan on billrptw_ndx (cost=0.00..2.08 rows=14 width=0) (actual time=0.053..0.053 rows=22 loops=1)
Index Cond: ((w.report_id = "outer".report_id) AND ((w.client_id)::text = '227400001'::text))
-> Bitmap Index Scan on billrptw_ndx (cost=0.00..2.08 rows=14 width=0) (actual time=0.024..0.024 rows=15 loops=1)
Index Cond: ((w.report_id = "outer".report_id) AND ((w.client_id)::text = '2274000010'::text))
Total runtime: 6.110 ms
(16 rows)
Thanks again (and sorry for the top-posting but this particular interface is ungainly)
G
-----Original Message-----
From: db@xxxxxxxxxxxx [mailto:db@xxxxxxxxxxxx]
Sent: Tue 1/9/2007 4:35 AM
To: Gregory S. Williamson
Cc: pgsql-performance@xxxxxxxxxxxxxx
Subject: 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
-------------------------------------------------------
Click link below if it is SPAM gsw@xxxxxxxxxxxxxxxx
"https://mailscanner.globexplorer.com/dspam/dspam.cgi?signatureID=45a38b1548991076418835&user=gsw@xxxxxxxxxxxxxxxx&retrain=spam&template=history&history_page=1";
!DSPAM:45a38b1548991076418835!
-------------------------------------------------------
Voi
