Skip site navigation (1) Skip section navigation (2)

Re: Horribly slow query/ sequential scan

From: "Gregory S(dot) Williamson" <gsw(at)globexplorer(dot)com>
To: "Plugge, Joe R(dot)" <JRPlugge(at)west(dot)com>,<pgsql-performance(at)postgresql(dot)org>
Subject: Re: Horribly slow query/ sequential scan
Date: 2007-01-09 23:40:51
Message-ID: 71E37EF6B7DCC1499CEA0316A256832802B3EB9B@loki.wc.globexplorer.net (view raw or flat)
Thread:
Lists: pgsql-performance
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!
-------------------------------------------------------






In response to

Responses

pgsql-performance by date

Next:From: Scott MarloweDate: 2007-01-09 23:42:04
Subject: Re: group by will not use an index?
Previous:From: Adam RichDate: 2007-01-09 23:32:50
Subject: Re: group by will not use an index?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group