Re: Horribly slow query/ sequential scan

As Joe indicated, there is indeed an Informix explain, appended below my signature ...

This table has 5565862 total rows, and 37 target rows. So about twice the total data, but all of the "extra" data in
infomrixis much older. 

Thanks for the help, one and all!

Greg W.

QUERY:
------
SELECT collection_id,client_id,client_name,appid,SUM(hits),SUM(sius),SUM(royalty_total)
FROM bill_rpt_work WHERE report_id in
 (SELECT report_id FROM billing_reports WHERE report_s_date = '2004-09-10')
 GROUP BY collection_id, client_id,client_name,appid
 ORDER BY collection_id,client_id,appid

Estimated Cost: 2015
Estimated # of Rows Returned: 481
Temporary Files Required For: Order By  Group By

  1) informix.bill_rpt_work: INDEX PATH

    (1) Index Keys: report_id   (Serial, fragments: ALL)
        Lower Index Filter: informix.bill_rpt_work.report_id = ANY <subquery>

    Subquery:
    ---------
    Estimated Cost: 44
    Estimated # of Rows Returned: 1

      1) informix.billing_reports: SEQUENTIAL SCAN

            Filters: informix.billing_reports.report_s_date = datetime(2004-09-10) year to day



QUERY:
------
select count(*) from informix.systables;

Estimated Cost: 1
Estimated # of Rows Returned: 1

  1) informix.systables: INDEX PATH

    (1) Index Keys: (count)


QUERY:
------
 select tabname , tabid , owner from informix . systables where tabname != 'ANSI' and tabtype != 'P' order by tabname

Estimated Cost: 30
Estimated # of Rows Returned: 196

  1) informix.systables: INDEX PATH

        Filters: informix.systables.tabtype != 'P'

    (1) Index Keys: tabname owner   (Key-First)
        Key-First Filters:  (informix.systables.tabname != 'ANSI' )


QUERY:
------
select tabid, tabtype, tabname, owner from informix.systables where (tabname = ? and owner like ?)

Estimated Cost: 2
Estimated # of Rows Returned: 1

  1) informix.systables: INDEX PATH

    (1) Index Keys: tabname owner   (Key-First)
        Lower Index Filter: informix.systables.tabname = 'bill_rpt_work'
        Key-First Filters:  (informix.systables.owner LIKE '%' )


QUERY:
------
select count(*) from             informix.systables where tabname = 'sysindices';

Estimated Cost: 2
Estimated # of Rows Returned: 1

  1) informix.systables: INDEX PATH

    (1) Index Keys: tabname owner
        Lower Index Filter: informix.systables.tabname = 'sysindices'


QUERY:
------
select colno, colname, coltype, collength, informix.syscolumns.extended_id, name from informix.syscolumns,
informix.systables,outer informix.sysxtdtypes where  informix.syscolumns.tabid = informix.systables.tabid and
informix.syscolumns.extended_id= informix.sysxtdtypes.extended_id and tabname = ? and informix.systables.owner = ?
orderby informix.syscolumns.colno; 

Estimated Cost: 9
Estimated # of Rows Returned: 7
Temporary Files Required For: Order By

  1) informix.systables: INDEX PATH

    (1) Index Keys: tabname owner
        Lower Index Filter: (informix.systables.tabname = 'bill_rpt_work' AND informix.systables.owner = 'informix
                 ' )  

  2) informix.syscolumns: INDEX PATH

    (1) Index Keys: tabid colno
        Lower Index Filter: informix.syscolumns.tabid = informix.systables.tabid
NESTED LOOP JOIN

  3) informix.sysxtdtypes: INDEX PATH

    (1) Index Keys: extended_id
        Lower Index Filter: informix.syscolumns.extended_id = informix.sysxtdtypes.extended_id
NESTED LOOP JOIN


QUERY:
------
select tabid, tabtype, tabname, owner from informix.systables where (tabname = ? and owner like ?)

Estimated Cost: 2
Estimated # of Rows Returned: 1

  1) informix.systables: INDEX PATH

    (1) Index Keys: tabname owner   (Key-First)
        Lower Index Filter: informix.systables.tabname = 'bill_rpt_work'
        Key-First Filters:  (informix.systables.owner LIKE '%' )


QUERY:
------
select count(*) from             informix.systables where tabname = 'sysindices';

Estimated Cost: 2
Estimated # of Rows Returned: 1

  1) informix.systables: INDEX PATH

    (1) Index Keys: tabname owner
        Lower Index Filter: informix.systables.tabname = 'sysindices'


QUERY:
------
select idxtype, clustered,idxname, informix.sysindices.owner, indexkeys::lvarchar, amid, am_name from
informix.sysindices,informix.systables, informix.sysams where informix.systables.tabname = ? and
informix.systables.tabid = informix.sysindices.tabid and informix.systables.owner like ? and informix.sysindices.amid =
informix.sysams.am_id;

Estimated Cost: 6
Estimated # of Rows Returned: 2

  1) informix.systables: INDEX PATH

    (1) Index Keys: tabname owner
        Lower Index Filter: (informix.systables.tabname = 'bill_rpt_work' AND informix.systables.owner = 'informix' )

  2) informix.sysindices: INDEX PATH

    (1) Index Keys: tabid
        Lower Index Filter: informix.systables.tabid = informix.sysindices.tabid
NESTED LOOP JOIN

  3) informix.sysams: INDEX PATH

    (1) Index Keys: am_id
        Lower Index Filter: informix.sysindices.amid = informix.sysams.am_id
NESTED LOOP JOIN

UDRs in query:
--------------
    UDR id  :    1
    UDR name:    indexkeyarray_out

QUERY:
------
select tabid, tabtype, tabname, owner from informix.systables where (tabname = ? and owner like ?)

Estimated Cost: 2
Estimated # of Rows Returned: 1

  1) informix.systables: INDEX PATH

    (1) Index Keys: tabname owner   (Key-First)
        Lower Index Filter: informix.systables.tabname = 'bill_rpt_work'
        Key-First Filters:  (informix.systables.owner LIKE '%' )


QUERY:
------
select count(*) from             informix.systables where tabname = 'sysindices';

Estimated Cost: 2
Estimated # of Rows Returned: 1

  1) informix.systables: INDEX PATH

    (1) Index Keys: tabname owner
        Lower Index Filter: informix.systables.tabname = 'sysindices'


QUERY:
------
select colno, colname, coltype, collength, informix.syscolumns.extended_id, name from informix.syscolumns,
informix.systables,outer informix.sysxtdtypes where  informix.syscolumns.tabid = informix.systables.tabid and
informix.syscolumns.extended_id= informix.sysxtdtypes.extended_id and tabname = ? and informix.systables.owner = ?
orderby informix.syscolumns.colno; 

Estimated Cost: 9
Estimated # of Rows Returned: 7
Temporary Files Required For: Order By

  1) informix.systables: INDEX PATH

    (1) Index Keys: tabname owner
        Lower Index Filter: (informix.systables.tabname = 'bill_rpt_work' AND informix.systables.owner = 'informix
                 ' )  

  2) informix.syscolumns: INDEX PATH

    (1) Index Keys: tabid colno
        Lower Index Filter: informix.syscolumns.tabid = informix.systables.tabid
NESTED LOOP JOIN

  3) informix.sysxtdtypes: INDEX PATH

    (1) Index Keys: extended_id
        Lower Index Filter: informix.syscolumns.extended_id = informix.sysxtdtypes.extended_id
NESTED LOOP JOIN



QUERY:
------
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

Estimated Cost: 3149
Estimated # of Rows Returned: 1
Temporary Files Required For: Order By  Group By

  1) informix.b: INDEX PATH

    (1) Index Keys: report_s_date   (Serial, fragments: ALL)
        Lower Index Filter: informix.b.report_s_date = datetime(2006-09-30) year to day

  2) informix.w: INDEX PATH

        Filters: (informix.w.client_id = '227400001' OR informix.w.client_id = '2274000010' )

    (1) Index Keys: report_id   (Serial, fragments: ALL)
        Lower Index Filter: informix.w.report_id = informix.b.report_id
NESTED LOOP JOIN

  3) informix.billing_reports: SEQUENTIAL SCAN  (First Row)
NESTED LOOP JOIN  (Semi Join)




-----Original Message-----
From:    pgsql-performance-owner@postgresql.org on behalf of Plugge, Joe R.
Sent:    Tue 1/9/2007 7:36 AM
To:    pgsql-performance@postgresql.org
Cc:
Subject:    Re: [PERFORM] Horribly slow query/ sequential scan

 Yes it does:

SET EXPLAIN ON;

It writes the file to  sqexplain.out

-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Tuesday, January 09, 2007 9:13 AM
To: Gregory S. Williamson
Cc: db@zigo.dhs.org; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Horribly slow query/ sequential scan

"Gregory S. Williamson" <gsw@globexplorer.com> writes:
> 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.

Me too.  Does informix have anything EXPLAIN-like to show what it's
doing?

            regards, tom lane

---------------------------(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


-------------------------------------------------------
Click link below if it is SPAM gsw@globexplorer.com

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