Re: Functions too slow, even with iscachable?

Ang Chin Han <angch(at)pintoo(dot)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 of
tmp1 = ticket2name($1);
tmp2 = ticket2survey_id($1);
SELECT passwd FROM ticket WHERE name = tmp1 AND 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