On 24.01.2012 23:34, Tony Capobianco wrote:
Here's the explain:
pg=# explain select getMemberAdminPrevious_sp(247815829, 1,'test.email@xxxxxxxxxxx', 'Email', 'Test');
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.26 rows=1 width=0)
(1 row)
Time: 1.167 ms
That's not very helpful. We'd need to see the plan of the query within
the function, not the plan on invoking the function. The auto_explain
contrib module with auto_explain_log_nested_statements=on might be
useful to get that.
There was discussion of 'LIKE' v. '=' and wildcard characters are not
being entered into the $1 parameter.
This is not generating a sql string. I feel it's something to do with
the fetch of the refcursor. The cursor is a larger part of a function:
CREATE OR REPLACE FUNCTION PUBLIC.GETMEMBERADMINPREVIOUS_SP2 (
p_memberid IN numeric,
p_websiteid IN numeric,
p_emailaddress IN varchar,
p_firstname IN varchar,
p_lastname IN varchar)
RETURNS refcursor AS $$
DECLARE
ref refcursor;
l_sysdateid numeric;
BEGIN
l_sysdateid := sysdateid();
if (p_memberid != 0) then
if (p_emailaddress IS NOT NULL) then
OPEN ref FOR
SELECT m.memberid, m.websiteid, m.emailaddress,
m.firstname, m.lastname, m.regcomplete, m.emailok
FROM members m
WHERE m.emailaddress LIKE p_emailaddress
AND m.changedate_id< l_sysdateid ORDER BY m.emailaddress,
m.websiteid;
end if;
end if;
Return ref;
EXCEPTION
WHEN NO_DATA_FOUND THEN
Return null;
END;
$$ LANGUAGE 'plpgsql';
The theory that the query takes a long time because "LIKE
p_emailaddress" is not optimizeable by the planner seems the most likely
to me.
If you don't actually use any wildcards in the email, try replacing LIKE
with =. If you do, then you can try the "OPEN ref FOR EXECUTE" syntax.
That way the query is re-planned every time, and the planner can take
advantage of the parameter value. That enables it to use an index on the
email address column, when there isn't in fact any wildcards in the
value, and also estimate the selectivities better which can lead to a
better plan. Like this:
CREATE OR REPLACE FUNCTION public.getmemberadminprevious_sp2(p_memberid
numeric, p_websiteid numeric, p_emailaddress character varying,
p_firstname character varying, p_lastname character varying)
RETURNS refcursor
LANGUAGE plpgsql
AS $function$
DECLARE
ref refcursor;
l_sysdateid numeric;
BEGIN
l_sysdateid := sysdateid();
if (p_memberid != 0) then
if (p_emailaddress IS NOT NULL) then
OPEN ref FOR EXECUTE $query$
SELECT m.memberid, m.websiteid, m.emailaddress,
m.firstname, m.lastname, m.regcomplete, m.emailok
FROM members m
WHERE m.emailaddress LIKE $1
AND m.changedate_id < $2 ORDER BY m.emailaddress,
m.websiteid;
$query$ USING p_emailaddress, l_sysdateid;
end if;
end if;
Return ref;
EXCEPTION
WHEN NO_DATA_FOUND THEN
Return null;
END;
$function$
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
