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 infomrix is 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 = ? order by 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 = ? order by 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(at)postgresql(dot)org on behalf of Plugge, Joe R.
Sent: Tue 1/9/2007 7:36 AM
To: pgsql-performance(at)postgresql(dot)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(at)postgresql(dot)org
[mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of Tom Lane
Sent: Tuesday, January 09, 2007 9:13 AM
To: Gregory S. Williamson
Cc: db(at)zigo(dot)dhs(dot)org; pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] Horribly slow query/ sequential scan

"Gregory S. Williamson" <gsw(at)globexplorer(dot)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(at)globexplorer(dot)com
"https://mailscanner.globexplorer.com/dspam/dspam.cgi?signatureID=45a3b93d75271019119885&user=gsw(at)globexplorer(dot)com&retrain=spam&template=history&history_page=1"
!DSPAM:45a3b93d75271019119885!
-------------------------------------------------------