Re: Cursor fetch performance issue

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

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



-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux