Re: Horribly slow query/ sequential scan - Mailing list pgsql-performance

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
referenceor explain what the difference might be ... I can see it with the eyes but I am having trouble understanding
whatInformix 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)
(actualtime=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=37loops=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.053rows=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.024rows=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@zigo.dhs.org [mailto:db@zigo.dhs.org]
Sent:    Tue 1/9/2007 4:35 AM
To:    Gregory S. Williamson
Cc:    pgsql-performance@postgresql.org
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@globexplorer.com

"https://mailscanner.globexplorer.com/dspam/dspam.cgi?signatureID=45a38b1548991076418835&user=gsw@globexplorer.com&retrain=spam&template=history&history_page=1"
!DSPAM:45a38b1548991076418835!
-------------------------------------------------------




Voi