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