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
