Thread: Index not used in functions in 7.0?

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
 

Kyle Bateman <kyle@actarg.com> 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

Tom Lane <tgl@sss.pgh.pa.us> writes:
> Kyle Bateman <kyle@actarg.com> 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

    http://www.postgresql.org/docs/postgres/index.html

is not there

also the tag.gz  for it only contained images
is that right?

the reason i went there was to find if there was a reverse of
nextval('seqname')?

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

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.

Kyle Bateman <kyle@actarg.com> 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

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