Received: from www.actarg.com (root@[209.180.91.25]) by hub.org (8.9.3/8.9.3) with ESMTP id LAA54078 for ; Tue, 16 May 2000 11:29:26 -0400 (EDT) (envelope-from kyle@actarg.com) Received: from actarg.com (root@[192.168.2.16]) by www.actarg.com (8.8.7/8.8.7) with ESMTP id IAA17165; Tue, 16 May 2000 08:27:30 -0600 Received: from actarg.com (IDENT:kyle@localhost [127.0.0.1]) by actarg.com (8.9.3/8.9.3) with ESMTP id JAA08709; Tue, 16 May 2000 09:26:19 -0600 Message-ID: <3921689A.B70CFAC8@actarg.com> Date: Tue, 16 May 2000 09:26:18 -0600 From: Kyle Bateman Organization: Action Target Inc X-Mailer: Mozilla 4.7 [en] (X11; I; Linux 2.2.12-20 i686) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgresql.org CC: tgl@sss.pgh.pa.us, merrill@actarg.com Subject: Index not used in functions in 7.0? Content-Type: multipart/mixed; boundary="------------2F7A50C65AD7BB6787C0FFB6" X-Archive-Number: 200005/11 This is a multi-part message in MIME format. --------------2F7A50C65AD7BB6787C0FFB6 Content-Type: multipart/alternative; boundary="------------0B541F1D56020955F34603F0" --------------0B541F1D56020955F34603F0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Help! We upgraded to 7.0 last week and all looks good except a problem with indexes and its a show-stopper! Any advice would be greatly appreciated. Here's the problem. I have a table with 50,000 entries. This function runs a sum on a column of the table: create function summtr_ocpt2(text, int4) returns int4 as ' select sum(tquant) from mtr_reg where to_proj = $2 and pnum = $1 and (status = \'open\' or status = \'clsd\' or status = \'prip\'); ' language 'sql'; We have a critical query (which calls this function) we run on stock levels that used to take about 30 seconds. Now it takes something like 30 hours (I've never seen it terminate). The problem seems to be that when a query is called from within a function, the scan is sequential even if an index exists. I tried entering the SQL directly as: select sum(tquant) from mtr_reg where to_proj = 50 and pnum = '1051' and (status = 'clsd' or status = 'open' or status = 'prip') ; And it accesses the index properly even though it is the exact same query. I dumped the log (-d 9) from the postmaster and the plan confirms that the scan is sequential when called from within the function but indexed when the SQL is called directly. Is this due to something I'm doing wrong or is this a bug? I'd be happy to provide a dump of the data, do other testing or whatever would help. I'm not sure who on the team is best to look at this. Thanks all! Kyle --------------0B541F1D56020955F34603F0 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit Help! We upgraded to 7.0 last week and all looks good except a problem with indexes and its a show-stopper!  Any advice would be greatly appreciated.

Here's the problem.  I have a table with 50,000 entries.  This function runs a sum on a column of the table:

create function summtr_ocpt2(text, int4) returns int4 as '
    select sum(tquant) from mtr_reg where to_proj = $2 and pnum = $1 and (status = \'open\' or status = \'clsd\' or status = \'prip\');
    ' language 'sql';

We have a critical query (which calls this function) we run on stock levels that used to take about 30 seconds.  Now it takes something like 30 hours (I've never seen it terminate).

The problem seems to be that when a query is called from within a function, the scan is sequential even if an index exists.  I tried entering the SQL directly as:

select sum(tquant)
        from mtr_reg
        where to_proj = 50
        and pnum = '1051'
        and (status = 'clsd' or status = 'open' or status = 'prip')
;

And it accesses the index properly even though it is the exact same query.

I dumped the log (-d 9) from the postmaster and the plan confirms that the scan is sequential when called from within the function but indexed when the SQL is called directly.

Is this due to something I'm doing wrong or is this a bug?

I'd be happy to provide a dump of the data, do other testing or whatever would help.  I'm not sure who on the team is best to look at this.

Thanks all!

Kyle
  --------------0B541F1D56020955F34603F0-- --------------2F7A50C65AD7BB6787C0FFB6 Content-Type: text/x-vcard; charset=us-ascii; name="kyle.vcf" Content-Transfer-Encoding: 7bit Content-Description: Card for Kyle Bateman Content-Disposition: attachment; filename="kyle.vcf" begin:vcard n:Bateman;Kyle tel;fax:801-377-8096 tel;work:801-377-8033x101 x-mozilla-html:FALSE url:www.actiontarget.com org:Action Target Inc adr:;;PO Box 636;Provo;UT;84603;US version:2.1 email;internet:kyle@actiontarget.com title:President x-mozilla-cpt:;-15520 fn:Kyle Bateman end:vcard --------------2F7A50C65AD7BB6787C0FFB6--