Message-ID: <4F1FC428.5020703@enterprisedb.com>
Date: Wed, 25 Jan 2012 10:58:16 +0200
From: Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:8.0) Gecko/20120104 Icedove/8.0
MIME-Version: 1.0
To: Tony Capobianco <tcapobianco@prospectiv.com>
CC: Pavel Stehule <pavel.stehule@gmail.com>,
	Andy Colson <andy@squeakycode.net>, pgsql-performance@postgresql.org
Subject: Re: Cursor fetch performance issue
References: <1327437700.1968.10.camel@tony1.localdomain>
	<CAFj8pRD3g3WESOxES27t_pBh_30Ko7JO7nKCf77oRsjU==tFwQ@mail.gmail.com>
	<1327438657.1968.15.camel@tony1.localdomain>
	<4F1F1E7A.4080403@squeakycode.net>
	<CAFj8pRDyZRb1Q3574dZ2VfQnBRRPvnrwGuhLvh6qRK-ByHsk+A@mail.gmail.com>
	<1327440846.1968.20.camel@tony1.localdomain>
In-Reply-To: <1327440846.1968.20.camel@tony1.localdomain>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

On 24.01.2012 23:34, Tony Capobianco wrote:
> Here's the explain:
>
> pg=# explain select getMemberAdminPrevious_sp(247815829, 1,'test.email@hotmail.com', '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