From kyle@actarg.com Tue May 16 15:29:26 2000 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-- From tgl@sss.pgh.pa.us Tue May 16 15:50:12 2000 Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id LAA04190 for ; Tue, 16 May 2000 11:50:12 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id LAA21404; Tue, 16 May 2000 11:50:04 -0400 (EDT) To: Kyle Bateman cc: pgsql-sql@postgresql.org, merrill@actarg.com Subject: Re: Index not used in functions in 7.0? In-reply-to: <3921689A.B70CFAC8@actarg.com> References: <3921689A.B70CFAC8@actarg.com> Comments: In-reply-to Kyle Bateman message dated "Tue, 16 May 2000 09:26:18 -0600" Date: Tue, 16 May 2000 11:50:03 -0400 Message-ID: <21401.958492203@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200005/12 Kyle Bateman writes: > 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. Yikes, that does sound like 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. How big would the dump file be? I'm willing to look at this, and if the dump isn't very large then it'd be easier to just install your DB here instead of trying to gin up my own test case. regards, tom lane From tgl@sss.pgh.pa.us Tue May 16 16:36:35 2000 Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id MAA94888 for ; Tue, 16 May 2000 12:36:35 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id MAA21657; Tue, 16 May 2000 12:36:28 -0400 (EDT) To: Kyle Bateman , pgsql-sql@postgresql.org, merrill@actarg.com Subject: Re: Index not used in functions in 7.0? In-reply-to: <21401.958492203@sss.pgh.pa.us> References: <3921689A.B70CFAC8@actarg.com> <21401.958492203@sss.pgh.pa.us> Comments: In-reply-to Tom Lane message dated "Tue, 16 May 2000 11:50:03 -0400" Date: Tue, 16 May 2000 12:36:28 -0400 Message-ID: <21654.958494988@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200005/15 Tom Lane writes: > Kyle Bateman writes: >> 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. > Yikes, that does sound like a bug. Actually, after further thought I realize that there is a reason for plans within functions to be different from plans of hand-entered queries. In the latter case the optimizer knows the constant values (eg, it sees "WHERE to_proj = 50"), in the former case it doesn't (eg, it sees "WHERE to_proj = $1") and has to fall back on guesses about selectivities. In the particular case at hand I've have expected it to pick an indexscan anyway, but maybe there's just something weird about your data. Could I trouble you for the vacuum stats for that table? Easiest way to get them is select attname,attdisbursion,s.* from pg_statistic s, pg_attribute a, pg_class c where starelid = c.oid and attrelid = c.oid and staattnum = attnum and relname = 'FOO'; (I'm beginning to think we should create a standard system view for this query ;-)) regards, tom lane From mig@utdt.edu Tue May 16 18:04:31 2000 Received: from ant.utdt ([200.41.62.131]) by hub.org (8.9.3/8.9.3) with ESMTP id OAA45370 for ; Tue, 16 May 2000 14:04:31 -0400 (EDT) (envelope-from mig@utdt.edu) From: mig@utdt.edu Received: (from mig@localhost) by ant.utdt (8.8.7/8.8.7) id PAA21319; Tue, 16 May 2000 15:00:21 -0300 Date: Tue, 16 May 2000 15:00:21 -0300 Message-Id: <200005161800.PAA21319@ant.utdt> X-Authentication-Warning: ant.utdt: mig set sender to mig@utdt.edu using -f To: tgl@sss.pgh.pa.us CC: kyle@actarg.com, pgsql-sql@postgresql.org, merrill@actarg.com In-reply-to: <21654.958494988@sss.pgh.pa.us> (message from Tom Lane on Tue, 16 May 2000 12:36:28 -0400) Subject: Re: Index not used in functions in 7.0? References: <3921689A.B70CFAC8@actarg.com> <21401.958492203@sss.pgh.pa.us> <21654.958494988@sss.pgh.pa.us> X-Archive-Number: 200005/18 I am probably completely wrong, but at least it will be short ... Could it be that select sum(tquant) from mtr_reg where to_proj = $2 and pnum = $1 and (status = 'clsd' or status = 'open' or status = 'prip') ; makes it harder for the optimizer due to the ORs, and that select sum(tquant) from mtr_reg where to_proj = $2 and pnum = $1 and status in ('clsd','open','prip') --- last line changed ; would produce a better cost estimate? Miguel Sofer From kyle@actarg.com Tue May 16 18:13:13 2000 Received: from www.actarg.com (root@[209.180.91.25]) by hub.org (8.9.3/8.9.3) with ESMTP id OAA60847 for ; Tue, 16 May 2000 14:13:13 -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 LAA18023; Tue, 16 May 2000 11:14:25 -0600 Received: from actarg.com (IDENT:kyle@localhost [127.0.0.1]) by actarg.com (8.9.3/8.9.3) with ESMTP id MAA09544; Tue, 16 May 2000 12:13:10 -0600 Message-ID: <39218FB6.450431A8@actarg.com> Date: Tue, 16 May 2000 12:13:10 -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: mig@utdt.edu CC: pgsql-sql@postgresql.org Subject: Re: Index not used in functions in 7.0? References: <3921689A.B70CFAC8@actarg.com> <21401.958492203@sss.pgh.pa.us> <21654.958494988@sss.pgh.pa.us> <200005161800.PAA21319@ant.utdt> Content-Type: multipart/mixed; boundary="------------2C66A82716826C09704CB159" X-Archive-Number: 200005/19 This is a multi-part message in MIME format. --------------2C66A82716826C09704CB159 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit mig@utdt.edu wrote: > I am probably completely wrong, but at least it will be short ... > > Could it be that > select sum(tquant) > from mtr_reg > where to_proj = $2 > and pnum = $1 > and (status = 'clsd' or status = 'open' or status = 'prip') > ; > makes it harder for the optimizer due to the ORs, and that > select sum(tquant) > from mtr_reg > where to_proj = $2 > and pnum = $1 > and status in ('clsd','open','prip') > --- last line changed > ; > would produce a better cost estimate? > > Miguel Sofer Thanks for the input. Your point might be correct, but my results are the same regardless of the clause attached to the status field. In one of my tests, the query simply had "and status = 'clsd'." The issue here is that a sequential scan is being chosen over an indexed scan when the query is run from within a function. --------------2C66A82716826C09704CB159 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 --------------2C66A82716826C09704CB159-- From tgl@sss.pgh.pa.us Tue May 16 19:12:05 2000 Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id PAA60061 for ; Tue, 16 May 2000 15:12:05 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id PAA14058; Tue, 16 May 2000 15:11:56 -0400 (EDT) To: Kyle Bateman cc: pgsql-sql@postgresql.org, merrill@actarg.com Subject: Re: Index not used in functions in 7.0? In-reply-to: <3921689A.B70CFAC8@actarg.com> References: <3921689A.B70CFAC8@actarg.com> Comments: In-reply-to Kyle Bateman message dated "Tue, 16 May 2000 09:26:18 -0600" Date: Tue, 16 May 2000 15:11:56 -0400 Message-ID: <14055.958504316@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200005/20 Kyle Bateman writes: > 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'; Ah, I've sussed it. The difference between the environment in your function and the environment in a hand-entered query is that you've declared $1 to be type 'text', whereas in the hand-entered query the parser resolves the unknown-type string literal into type 'varchar' to match the type of what it's being compared to. What you effectively have inside the function is pnum::text = $1::text, and the planner is not able to figure out that it can use a varchar index for that. If you had written the hand-entered query as "... pnum = '1051'::text ..." it wouldn't have been done with an indexscan either. Best short-term solution is to declare the function as taking varchar in the first place. This behavior is a regression from 6.5, which handled implicit coercions differently and could recognize the clause as indexable despite the inserted coercion. I'll look into whether it's patchable for 7.0.1. A proper fix might be too complex to risk patching into 7.0.1 though :-( ... might have to wait for 7.1. regards, tom lane From peter_e@gmx.net Wed May 17 17:14:04 2000 Received: from merganser.its.uu.se (merganser.its.uu.se [130.238.6.236]) by hub.org (8.9.3/8.9.3) with ESMTP id NAA61873 for ; Wed, 17 May 2000 13:14:04 -0400 (EDT) (envelope-from peter@localhost.its.uu.se) Received: from regulus.student.UU.SE ([130.238.5.2]:38170 "EHLO regulus.its.uu.se") by merganser.its.uu.se with ESMTP id ; Wed, 17 May 2000 19:13:17 +0200 Received: from peter (helo=localhost) by regulus.its.uu.se with local-esmtp (Exim 3.02 #2) id 12s7Ss-0000A5-00; Wed, 17 May 2000 19:18:34 +0200 Date: Wed, 17 May 2000 19:18:34 +0200 (CEST) From: Peter Eisentraut To: Tom Lane cc: Kyle Bateman , pgsql-sql@postgresql.org, merrill@actarg.com Subject: Re: Index not used in functions in 7.0? In-Reply-To: <21654.958494988@sss.pgh.pa.us> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=ISO-8859-1 Content-Transfer-Encoding: 8BIT X-Archive-Number: 200005/29 Tom Lane writes: > select attname,attdisbursion,s.* > from pg_statistic s, pg_attribute a, pg_class c > where starelid = c.oid and attrelid = c.oid and staattnum = attnum > and relname = 'FOO'; > > (I'm beginning to think we should create a standard system view > for this query ;-)) \dstats? -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden