Re: Cursor fetch performance issue

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