Thread: Functions too slow, even with iscachable?
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); which should be a shortform for the first query: SELECT ticket2passwd('test-006kdt'); Any ideas? Thanks in advance. ticket2name and ticket2survey_id are both iscachable. BTW, pg_dump doesn't seem to preserve the iscachable attribute. Bug?
At 17:11 7/08/00 +0800, Ang Chin Han wrote: > >BTW, pg_dump doesn't seem to preserve the iscachable attribute. Bug? > Don't know about the rest of your message, but this seems to be a bug. I'll look into it some more... ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.C.N. 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 0500 83 82 82 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
At 17:11 7/08/00 +0800, Ang Chin Han wrote: > >BTW, pg_dump doesn't seem to preserve the iscachable attribute. Bug? > Here is a patch for 7.0.2 sources which adds support for ischachable to pg_dump. ------------------------------------------------- diff -Naur pg_dump/pg_dump.c zzz/pg_dump.c --- pg_dump/pg_dump.c Fri Apr 14 11:34:24 2000 +++ zzz/pg_dump.c Mon Aug 7 21:51:21 2000 @@ -1456,13 +1456,15 @@ int i_proretset; int i_prosrc; int i_probin; + int i_iscachable; int i_usename; /* find all user-defined funcs */ appendPQExpBuffer(query, "SELECT pg_proc.oid, proname, prolang, pronargs, prorettype, " - "proretset, proargtypes, prosrc, probin, usename " + "proretset, proargtypes, prosrc, probin, usename, " + "proiscachable " "from pg_proc, pg_user " "where pg_proc.oid> '%u'::oid and proowner = usesysid", g_last_builtin_oid); @@ -1492,6 +1494,7 @@ i_proretset = PQfnumber(res, "proretset"); i_prosrc = PQfnumber(res, "prosrc"); i_probin =PQfnumber(res, "probin"); + i_iscachable = PQfnumber(res, "proiscachable"); i_usename = PQfnumber(res, "usename"); for (i = 0; i < ntups;i++) @@ -1507,6 +1510,7 @@ finfo[i].nargs = atoi(PQgetvalue(res, i, i_pronargs)); finfo[i].lang = atoi(PQgetvalue(res,i, i_prolang)); finfo[i].usename = strdup(PQgetvalue(res, i, i_usename)); + finfo[i].iscachable = (strcmp(PQgetvalue(res, i, i_iscachable),"t") == 0); if (finfo[i].nargs < 0 || finfo[i].nargs> FUNC_MAX_ARGS) { fprintf(stderr, "failed sanity check: %s has %d args\n", @@ -2663,11 +2667,18 @@ (j > 0) ? "," : "", fmtId(typname, false)); } - appendPQExpBuffer(q, " ) RETURNS %s%s AS '%s' LANGUAGE '%s';\n", + appendPQExpBuffer(q, " ) RETURNS %s%s AS '%s' LANGUAGE '%s'", (finfo[i].retset) ? " SETOF " : "", fmtId(findTypeByOid(tinfo, numTypes, finfo[i].prorettype), false), func_def, func_lang); + if (finfo[i].iscachable) /* OR in new attrs here */ + { + appendPQExpBuffer(q, " WITH (iscachable)"); + } + + appendPQExpBuffer(q, ";\n"); + fputs(q->data, fout); /*** Dump Function Comments ***/ diff -Naur pg_dump/pg_dump.h zzz/pg_dump.h --- pg_dump/pg_dump.h Thu Apr 13 03:16:15 2000 +++ zzz/pg_dump.h Mon Aug 7 21:49:05 2000 @@ -61,6 +61,7 @@ char *prosrc; char *probin; char *usename; + int iscachable; /* Attr */ int dumped; /* 1 if already dumped */} FuncInfo; ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.C.N. 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 0500 83 82 82 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
On Mon, Aug 07, 2000 at 05:11:10PM +0800, Ang Chin Han wrote: > 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); Is this a cut-n-paste error, or did you drop half a term from your WHERE clause? "survey_id =" seems to be missing in the functional form. I din't see how that would make it run slowly, however. Ross -- Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
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
At 10:58 7/08/00 -0400, Tom Lane wrote: > >Looks like we need to teach the optimizer that expressions involving >params can be treated like simple params for the purposes of >optimization. > So long as the function is cacheable... ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.C.N. 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 0500 83 82 82 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
Philip Warner <pjw@rhyme.com.au> writes: > At 10:58 7/08/00 -0400, Tom Lane wrote: >> Looks like we need to teach the optimizer that expressions involving >> params can be treated like simple params for the purposes of >> optimization. > So long as the function is cacheable... Yes, of course --- but the same problem currently arises for cases like "var = param + 1" ... regards, tom lane
On Mon, Aug 07, 2000 at 10:58:27AM -0400, Tom Lane wrote: > (I assume the lack of "survey_id =" here is just a cut-and-paste error?) Yup. :) > 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? It did. I'll try to make up a reproducible test case, if you need it. > 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. Yup, it did work. Thanks! > Looks like we need to teach the optimizer that expressions involving > params can be treated like simple params for the purposes of > optimization. That'll be good. Anything to speed up the stored procedures are good: encourage people to put logic processing into the RDBMS where it should be.