pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFunctions too slow, even with iscachable?
8+ messages / 4 participants
[nested] [flat]
* Functions too slow, even with iscachable?
@ 2000-08-07 09:11 Ang Chin Han <angch@pintoo.com>
0 siblings, 4 replies; 8+ messages in thread
From: Ang Chin Han @ 2000-08-07 09:11 UTC (permalink / raw)
To: pgsql-sql
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?
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Functions too slow, even with iscachable?
@ 2000-08-07 09:57 Philip Warner <pjw@rhyme.com.au>
parent: Ang Chin Han <angch@pintoo.com>
3 siblings, 0 replies; 8+ messages in thread
From: Philip Warner @ 2000-08-07 09:57 UTC (permalink / raw)
To: Ang Chin Han <angch@pintoo.com>; pgsql-sql
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 |/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Functions too slow, even with iscachable?
@ 2000-08-07 12:12 Philip Warner <pjw@rhyme.com.au>
0 siblings, 0 replies; 8+ messages in thread
From: Philip Warner @ 2000-08-07 12:12 UTC (permalink / raw)
To: Ang Chin Han <angch@pintoo.com>; pgsql-sql
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.
-------------------------------------------------
inline patch 1: zzz/pg_dump.c, zzz/pg_dump.h (+14/-2 lines) download inline-1.patch
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 |/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Functions too slow, even with iscachable?
@ 2000-08-07 14:47 Ross J. Reedstrom <reedstrm@rice.edu>
parent: Ang Chin Han <angch@pintoo.com>
3 siblings, 0 replies; 8+ messages in thread
From: Ross J. Reedstrom @ 2000-08-07 14:47 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Functions too slow, even with iscachable?
@ 2000-08-07 14:58 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ang Chin Han <angch@pintoo.com>
3 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2000-08-07 14:58 UTC (permalink / raw)
To: Ang Chin Han <angch@pintoo.com>; +Cc: pgsql-sql
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 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Functions too slow, even with iscachable?
@ 2000-08-07 15:07 Philip Warner <pjw@rhyme.com.au>
parent: Ang Chin Han <angch@pintoo.com>
3 siblings, 1 reply; 8+ messages in thread
From: Philip Warner @ 2000-08-07 15:07 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Ang Chin Han <angch@pintoo.com>; +Cc: pgsql-sql
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 |/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Functions too slow, even with iscachable?
@ 2000-08-07 15:52 Tom Lane <tgl@sss.pgh.pa.us>
parent: Philip Warner <pjw@rhyme.com.au>
0 siblings, 0 replies; 8+ messages in thread
From: Tom Lane @ 2000-08-07 15:52 UTC (permalink / raw)
To: Philip Warner <pjw@rhyme.com.au>; +Cc: Ang Chin Han <angch@pintoo.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Functions too slow, even with iscachable?
@ 2000-08-09 06:50 Ang Chin Han <angch@pintoo.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 8+ messages in thread
From: Ang Chin Han @ 2000-08-09 06:50 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2000-08-09 06:50 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-08-07 09:11 Functions too slow, even with iscachable? Ang Chin Han <angch@pintoo.com>
2000-08-07 09:57 ` Philip Warner <pjw@rhyme.com.au>
2000-08-07 14:47 ` Ross J. Reedstrom <reedstrm@rice.edu>
2000-08-07 14:58 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-08-09 06:50 ` Ang Chin Han <angch@pintoo.com>
2000-08-07 15:07 ` Philip Warner <pjw@rhyme.com.au>
2000-08-07 15:52 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-08-07 12:12 Re: Functions too slow, even with iscachable? Philip Warner <pjw@rhyme.com.au>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox