From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Ang Chin Han <angch(at)pintoo(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Functions too slow, even with iscachable? |
Date: | 2000-08-07 14:58:27 |
Message-ID: | 6058.965660307@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Philip Warner | 2000-08-07 15:07:38 | Re: Functions too slow, even with iscachable? |
Previous Message | Ross J. Reedstrom | 2000-08-07 14:47:53 | Re: Functions too slow, even with iscachable? |