Re: Cursor fetch performance issue

Here's the explain:

pg=# explain select getMemberAdminPrevious_sp(247815829, 1,'test(dot)email(at)hotmail(dot)com', 'Email', 'Test');
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.26 rows=1 width=0)
(1 row)

Time: 1.167 ms

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';

On Tue, 2012-01-24 at 22:17 +0100, Pavel Stehule wrote:
> Hello
>
> >
> > So, is getMemberAdminPrevious_sp2() preparing a statement with wildcards?
> >
> > 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;
> >
> > Or is it creating the string and executing it:
> >
> > sql = 'SELECT m.memberid, m.websiteid, m.emailaddress, '
> > || ' m.firstname, m.lastname, m.regcomplete, m.emailok '
> > || ' FROM members m
> > || ' WHERE m.emailaddress LIKE ' || arg1
> > || ' AND m.changedate_id < ' || arg2
> > || ' ORDER BY m.emailaddress, m.websiteid ';
> > execute(sql);
> >
> > Maybe its the planner doesnt plan so well with $1 arguments vs actual
> > arguments thing.
> >
>
> sure, it could be blind optimization problem in plpgsql. Maybe you
> have to use a dynamic SQL - OPEN FOR EXECUTE stmt probably
>
> http://www.postgresql.org/docs/9.1/interactive/plpgsql-cursors.html
>
> Regards
>
> Pavel Stehule
>
> > -Andy
> >
> >
>