[SQL] 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?
Re: [SQL] Functions too slow, even with iscachable?
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 |/
Re: [SQL] Functions too slow, even with iscachable?
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 |/
[SQL] Subselect and limit/order?
Just wondering why subselect expressions can not have a limit/order clause, eg. select id,(select id from tbl where id > ? order by id limit 1) as nextid from tbl where id = ?; is quite usefull. It can obviously be done in two select statements, but I was just wondering if it's an oversight or a planner problem? 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 |/
Re: [SQL] Functions too slow, even with iscachable?
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., <[EMAIL PROTECTED]>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005
Re: [SQL] Functions too slow, even with iscachable?
Ang Chin Han <[EMAIL PROTECTED]> 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
Re: [SQL] Functions too slow, even with iscachable?
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 |/
Re: [SQL] Functions too slow, even with iscachable?
Philip Warner <[EMAIL PROTECTED]> 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
[SQL] Changing user passwords
Hello, Is it possible to change a user's password without using the pg_passwd command? For example, how does a user changes his password from the interactive monitor? In case you were wondering, I'm scripting an interface to a pg database using Perl/DBI and the "administrator" web user should be able to change the default password. I'm a pg/SQL novice, yet I'm always willing to learn new things, references to documentation will be appreciated as much as solutions. Apologies in advance for my ignorance. -- Rob van der Leek E-mail: [EMAIL PROTECTED] FROG Navigation Systems b.v. Cartesiusweg 120 3534 BD Utrecht Tel. 030-2440550 Fax. 030-2440700 http://www.frog.nl
[SQL] foreign key take too much time to check
Hi, there, I want add a constraint to my tables: I have 2 tables: 1. Table_A(id int 4 primary key, item text, ..), 2. Table_B(id int4, cid int4, constraint b_fk foreign key (id) refereneces Table_A(id) on delete cascade on update cascade); Table_A has ~900,000 rows, Table_B has ~1,200,000 rows. I tried serveral ways to generate the b_fk 1. alter table add constraint b_fk foreign key (id) refereneces Table_A(id) on delete cascade on update cascade, 2. pg_dump -d -t Table_B -f B dbname, then psql dbname < B even before that , I create an index for id of Table_B, and vacuum table Table_A and Table_B, insert (check) speed is very slow, ~100 rows per minute, for my table has ~1.2M rows will take more than 200hours, I wonder to compare 2 integers why takes so long, if no b_fk , the Table_B can be reload in 5 minutes... Is anybody know how reslove this problem? I 'll appreciate. -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
Re: [SQL] foreign key take too much time to check
Try creating the tables without the constraint first, then populate them, and then add the foreign key constaint as the last step using ALTER TABLE/ADD CONSTRAINT. Mark Jie Liang wrote: > > Hi, there, > > I want add a constraint to my tables: > I have 2 tables: > 1. Table_A(id int 4 primary key, item text, ..), > 2. Table_B(id int4, cid int4, constraint b_fk foreign key (id) > refereneces Table_A(id) > on delete cascade on update cascade); > Table_A has ~900,000 rows, > Table_B has ~1,200,000 rows. > I tried serveral ways to generate the b_fk > 1. alter table add constraint b_fk foreign key (id) refereneces > Table_A(id) > on delete cascade on update cascade, > 2. pg_dump -d -t Table_B -f B dbname, then psql dbname < B > even before that , I create an index for id of Table_B, and vacuum table > Table_A and Table_B, > > insert (check) speed is very slow, ~100 rows per minute, for my table > has ~1.2M rows will > take more than 200hours, I wonder to compare 2 integers why takes so > long, if no b_fk , the Table_B > can be reload in 5 minutes... > > Is anybody know how reslove this problem? I 'll appreciate. > > -- > Jie LIANG > > Internet Products Inc. > > 10350 Science Center Drive > Suite 100, San Diego, CA 92121 > Office:(858)320-4873 > > [EMAIL PROTECTED] > www.ipinc.com
[SQL] PL/pgSQL
Hi, there, 1. Is anybody know how to declare a explicit cursor in PL/SQL in postgres? 1. I want create a function use PL/pgSQL: create function foo() returns whatevertype as ' declare v_asda int2; cursor is select a,b,c from AAA; -- what is the syntex? begin statements end; ' langauge 'plpgsql'; 2. for the record type, can I explicitly define my own record like: declare type blah is record(ename text,job text,id int 4); type blahblah is table of blah index by int4;-- like in Oracle .. 3. how to define an array in PL/pgSQL declare block and assign it in execuable block? like: declare ary int[5];-- in PL/pgSQL maybe is _int; began ary[1]:= 3;-- give me error when I run it; end; Thanks. -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
Re: [SQL] Subselect and limit/order?
At 11:46 7/08/00 -0400, Tom Lane wrote: >Philip Warner <[EMAIL PROTECTED]> writes: >> Just wondering why subselect expressions can not have a limit/order clause, > >We could ignore the spec and implement this as an extension, but I'd >want to see some fairly compelling arguments why it's a good idea. >(In other words, why do you know better than the designers of SQL?) Well, it's not *just* me. Both Dec/RDB and SQL/Server implement ORDER BY in subqueries. SQL/Server insists on a limit statement in this case, whereas Dec/Rdb just dies if more than one row is returned. It is a very useful thing when you are scanning though sequential records that are time based, or have ID's that have 'holes' in the sequence. eg. in the case of time-based data, you can derive durations. But it can be done by writing a plpgsql function, so it's not a big issue. >> It can obviously be done in two select statements, but I was just wondering >> if it's an oversight or a planner problem? > >I'd say mostly an executor problem, actually. Nobody's figured out >where the executor would need to be hacked to support tuple-limits >applied elsewhere than the top level of a select. Wouldn't is also have a fair impact on the planner? Or does it always assume that subselects only return one row? 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 |/
Re: [SQL] Subselect and limit/order?
At 11:46 7/08/00 -0400, Tom Lane wrote: > >I'd say mostly an executor problem, actually. Nobody's figured out >where the executor would need to be hacked to support tuple-limits >applied elsewhere than the top level of a select. Shouldn't the optimizer be able to get some benefit (in colun select expressions at least) by being able to do an implied 'limit 2'? 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 |/
