pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Functions 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.patchdiff -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