Index not used in functions in 7.0?

From: Kyle Bateman <kyle(at)actarg(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Cc: tgl(at)sss(dot)pgh(dot)pa(dot)us, merrill(at)actarg(dot)com
Subject: Index not used in functions in 7.0?
Date: 2000-05-16 15:26:18
Message-ID: 3921689A.B70CFAC8@actarg.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Attachment Content-Type Size
kyle.vcf text/x-vcard 291 bytes

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Jeff Hoffmann 2000-05-16 15:47:38 Re: Question about databases in alternate locations...
Previous Message Richard J Kuhns 2000-05-16 15:25:48 Question about databases in alternate locations...