Yes it does:

SET EXPLAIN ON;

It writes the file to sqexplain.out

-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailtogsql-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

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@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
[mailtogsql-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!
-------------------------------------------------------






---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

"Gregory S. Williamson" <gsw@globexplorer.com> writes:
> As Joe indicated, there is indeed an Informix explain, appended below my signature ...

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

Interesting! "Semi join" is the two-dollar technical term for what our
code calls an "IN join", viz a join that returns at most one copy of a
left-hand row even when there's more than one right-hand join candidate
for it. So I think there's not any execution mechanism here that we
don't have. What seems to be happening is that Informix is willing to
flatten the sub-SELECT into an IN join even though the sub-SELECT is
correlated to the outer query (that is, it contains outer references).
I'm not sure whether we're just being paranoid by not doing that, or
whether there are special conditions to check before allowing it, or
whether Informix is wrong ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

I wrote:
> ... What seems to be happening is that Informix is willing to
> flatten the sub-SELECT into an IN join even though the sub-SELECT is
> correlated to the outer query (that is, it contains outer references).

I did some googling this morning and found confirmation that recent
versions of Informix have pretty extensive support for optimizing
correlated subqueries:
http://www.iiug.org/waiug/archive/iu...turesIDS73.htm

This is something we've not really spent much time on for Postgres,
but it might be interesting to look at someday. Given that the problem
with your query was really a mistake anyway, I'm not sure that your
example is compelling evidence for making it a high priority.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings