Re: [SQL] Query is fast and function is slow
Hi,
On Wednesday 06 December 2006 16:44, Richard Ray wrote:
| select count(*) from documents where doc_num = '106973821' and (select
| bit_or(group_access) from mda_groups where group_name in (select groname
| from pg_user,pg_group where usename = 'bbob' and usesysid = any(grolist)
| and (groname ~ '.*owner$' or groname = 'admin'))) & access >
| '0'::bit(100);
|
| returns very fast
|
| If I create function
|
| create or replace function check_for_update_permission(text,text) returns
| boolean as '
| declare
|doc_number alias for $1;
|user alias for $2;
|doc_count integer;
| begin
...
| end;
| ' language 'plpgsql';
|
|
| and run "select check_for_update_permission('106973821','bbob');"
| it returns the correct info but takes several minutes
| Would someone please enlighten me.
| Can you do something like explain analyze on a function
Just a guess: is the column "doc_num" really of type text? Maybe using "text"
in the function lets the planner choose a sequential scan?
I'd try putting a "raise notice '%', explain analyze ..." statement into the
function and check the log file.
Ciao,
Thomas
--
Thomas Pundt <[EMAIL PROTECTED]> http://rp-online.de/
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] Query is fast and function is slow
On Thu, 7 Dec 2006, Thomas Pundt wrote:
Hi,
On Wednesday 06 December 2006 16:44, Richard Ray wrote:
| select count(*) from documents where doc_num = '106973821' and (select
| bit_or(group_access) from mda_groups where group_name in (select groname
| from pg_user,pg_group where usename = 'bbob' and usesysid = any(grolist)
| and (groname ~ '.*owner$' or groname = 'admin'))) & access >
| '0'::bit(100);
|
| returns very fast
|
| If I create function
|
| create or replace function check_for_update_permission(text,text) returns
| boolean as '
| declare
|doc_number alias for $1;
|user alias for $2;
|doc_count integer;
| begin
...
| end;
| ' language 'plpgsql';
|
|
| and run "select check_for_update_permission('106973821','bbob');"
| it returns the correct info but takes several minutes
| Would someone please enlighten me.
| Can you do something like explain analyze on a function
Just a guess: is the column "doc_num" really of type text? Maybe using "text"
in the function lets the planner choose a sequential scan?
Actually "doc_num" is char(9)
I changed text to char(9) and got same slow results
I'd try putting a "raise notice '%', explain analyze ..." statement into the
function and check the log file.
It appears that the function is not using the index
The table documents has a index on doc_num and doc_num is a unique value
dcc=# explain analyze select doc_num from documents where doc_num =
'106973821';
QUERY PLAN
-
Index Scan using documents_pkey on documents (cost=0.00..5.48 rows=1
width=13) (actual time=37.475..37.481 rows=1 loops=1)
Index Cond: (doc_num = '106973821'::bpchar)
Total runtime: 37.535 ms
(3 rows)
dcc=#
But this same statement in a function takes several minutes;
My SQL knowledge is pitiful so would you explain how to use
"explain analyze" in the function
I get errors when I try to load the file with
raise notice ''%'',explain analyze select doc_num from documents where
doc_num = doc_number;
dcc=# \i
/src/check_for_update_permission
psql:/src/check_for_update_permission:52:
ERROR: syntax error at or near "analyze" at character 16
QUERY: SELECT explain analyze select doc_num from documents where doc_num
= $1
CONTEXT: SQL statement in PL/PgSQL function "check_for_update_permission"
near line 18
psql:/src/check_for_update_permission:52:
LINE 1: SELECT explain analyze select doc_num from documents where d...
psql:/src/check_for_update_permission:52:
^
dcc=#
Ciao,
Thomas
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] Query is fast and function is slow
Hi, On Thursday 07 December 2006 15:53, Richard Ray wrote: | But this same statement in a function takes several minutes; | | My SQL knowledge is pitiful so would you explain how to use | "explain analyze" in the function | | I get errors when I try to load the file with | raise notice ''%'',explain analyze select doc_num from documents where | doc_num = doc_number; | | dcc=# \i | /src/check_for_update_permission | psql:/src/check_for_update_permission:52: | ERROR: syntax error at or near "analyze" at character 16 | QUERY: SELECT explain analyze select doc_num from documents where doc_num | = $1 | CONTEXT: SQL statement in PL/PgSQL function "check_for_update_permission" | near line 18 | psql:/src/check_for_update_permission:52: | LINE 1: SELECT explain analyze select doc_num from documents where d... | psql:/src/check_for_update_permission:52: | ^ | dcc=# ok, seems you can't use a SQL statement as expression here; instead try using a "for statement" then: for v_rec in explain analyze loop raise notice '%', v_rec; end loop; don't forget to declare "v_rec text;" Ciao, Thomas -- Thomas Pundt <[EMAIL PROTECTED]> http://rp-online.de/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Query is fast and function is slow
Richard Ray <[EMAIL PROTECTED]> writes: > On Thu, 7 Dec 2006, Thomas Pundt wrote: >> Just a guess: is the column "doc_num" really of type text? Maybe using "text" >> in the function lets the planner choose a sequential scan? > Actually "doc_num" is char(9) > I changed text to char(9) and got same slow results You need to make the second argument type "name", too, if you have a lot of users. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Query is fast and function is slow
On Thu, 7 Dec 2006, Tom Lane wrote: Richard Ray <[EMAIL PROTECTED]> writes: On Thu, 7 Dec 2006, Thomas Pundt wrote: Just a guess: is the column "doc_num" really of type text? Maybe using "text" in the function lets the planner choose a sequential scan? Actually "doc_num" is char(9) I changed text to char(9) and got same slow results You need to make the second argument type "name", too, if you have a lot of users. I said my knowledge was pitiful Changing both parameters to char(9) and name fixed the problem It appears to be using the index If time allows could you explain this a bit Thanks Richard regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] could not find pathkey item to sort
Hi All, Since upgrading to PG 8.2, I'm getting this error on a nightly (bash) script that calls psql (it's purpose is to find stock codes that share an EAN Barcode with another code - it used to work, now doesn't): ERROR: could not find pathkey item to sort SQL state: XX000 The error is because of the ORDER BY clause of this query: SELECT code, description, ean FROM$TMPTABLE WHERE ean <> '' ANDean IN (SELECT ean FROM $TMPTABLE GROUP BY ean HAVING count(ean) > 1) ORDER BY ean The $TMPTABLE is created as per: CREATE TEMP TABLE $TMPTABLE ( code varchar(6), description varchar(38), grp varchar(4), brand text, style text, supplier varchar(6), supplier_code text, wholesale numeric(10,2), retail numeric(10,2), ean varchar(13) ) WITHOUT OIDS; -- Import the file from WB Database COPY $TMPTABLE FROM '$TMPFILE'; I've tried creating an index and doing a reindex on the temp table because the select to no avail. Any help would be great - I've "fixed" it at the moment by commenting the ORDER BY clause, but it would be nice to have it sorted properly, and also in case it happens in any other tables. Thanks, -p Phillip Smith IT Coordinator Weatherbeeta P/L 8 Moncrief Rd Nunawading, VIC, 3131 AUSTRALIA E. [EMAIL PROTECTED] ***Confidentiality and Privilege Notice*** The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email. Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments
Re: [SQL] Query is fast and function is slow
Richard Ray <[EMAIL PROTECTED]> writes: > Changing both parameters to char(9) and name fixed the problem > It appears to be using the index > If time allows could you explain this a bit EXPLAIN will show you what's going on: regression=# create table foo (f1 char(9) unique); NOTICE: CREATE TABLE / UNIQUE will create implicit index "foo_f1_key" for table "foo" CREATE TABLE regression=# explain select * from foo where f1 = 'bar'; QUERY PLAN --- Index Scan using foo_f1_key on foo (cost=0.00..8.02 rows=1 width=13) Index Cond: (f1 = 'bar'::bpchar) (2 rows) regression=# explain select * from foo where f1 = 'bar'::text; QUERY PLAN - Seq Scan on foo (cost=0.00..35.95 rows=9 width=13) Filter: ((f1)::text = 'bar'::text) (2 rows) The second case is unable to use the index because the query is not really interrogating the value of f1, but the value of CAST(f1 AS text), and that's not what's indexed. This is not just an academic point, because the semantics of comparison for char(n) and text are actually different --- text is sensitive to trailing whitespace, char(n) isn't. So if we ignored the distinction and tried to use the index anyway, we'd probably get wrong answers. The reason the handwritten query comes out OK is that you've got an untyped literal constant, and the heuristic the parser likes to use for resolving the type of such a literal is "make it the same type as whatever it's being compared to". So 'bar' is assumed to be char(n) and all is well. In your function, though, the parameter is specifically declared to be text, so you wrote a char(n) vs text comparison, and that's resolved to mean "promote the char(n) to text and do a text comparison". Which is exactly what we can see it doing in my second example above. Same problem with the other thing: pg_user.usename is type name, not type text. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] could not find pathkey item to sort
"Phillip Smith" <[EMAIL PROTECTED]> writes: > Since upgrading to PG 8.2, I'm getting this error on a nightly (bash) > script > ERROR: could not find pathkey item to sort I think this is a variant manifestation of the same bug already reported here: http://archives.postgresql.org/pgsql-hackers/2006-12/msg00284.php and fixed here: http://archives.postgresql.org/pgsql-committers/2006-12/msg00056.php Leastwise, it went away for me after applying that patch ... regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
