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@xxxxxxxxxxxxxx on behalf of Plugge, Joe R.
Sent: Tue 1/9/2007 7:36 AM
To: pgsql-performance@xxxxxxxxxxxxxx
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@xxxxxxxxxxxxxx
[mailto:pgsql-performance-owner@xxxxxxxxxxxxxx] On Behalf Of Tom Lane
Sent: Tuesday, January 09, 2007 9:13 AM
To: Gregory S. Williamson
Cc: db@xxxxxxxxxxxx; pgsql-performance@xxxxxxxxxxxxxx
Subject: Re: [PERFORM] Horribly slow query/ sequential scan
"Gregory S. Williamson" <gsw@xxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxxxx
"https://mailscanner.globexplorer.com/dspam/dspam.cgi?signatureID=45a3b93d75271019119885&user=gsw@xxxxxxxxxxxxxxxx&retrain=spam&template=history&history_page=1";
!DSPAM:45a3b93d75271019119885!
-------------------------------------------------------
