Re: Functions too slow, even with iscachable?

Ang Chin Han <angch@pintoo.com> writes:
> I have a query which runs fast:
> SELECT passwd FROM ticket WHERE name = ticket2name('test-006kdt') AND
>     survey_id = ticket2survey_id('test-006kdt');

> But slows down to a crawl when I wrapped it in a function:

> CREATE FUNCTION ticket2passwd(text) RETURNS text AS
>     'SELECT passwd FROM ticket WHERE name = ticket2name($1) AND
>     ticket2survey_id($1)' LANGUAGE 'sql' WITH (iscachable);

(I assume the lack of "survey_id =" here is just a cut-and-paste error?)

I think what you're getting bit by is that the optimizer doesn't
recognize "var = function(param)" as being a potential indexscan clause.
Does EXPLAIN show that the first query is producing an indexscan plan?

I have not tried it, but I think you could get around this problem in
plpgsql, along the lines oftmp1 = ticket2name($1);tmp2 = ticket2survey_id($1);SELECT passwd FROM ticket WHERE name =
tmp1AND survey_id = tmp2;
 
since the tmp vars will look like params to the optimizer and "var = param"
is indexable.

Looks like we need to teach the optimizer that expressions involving
params can be treated like simple params for the purposes of
optimization.
        regards, tom lane