Skip site navigation (1) Skip section navigation (2)

Re: Cursor fetch performance issue

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: Andy Colson <andy(at)squeakycode(dot)net>
Cc: Tony Capobianco <tcapobianco(at)prospectiv(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Cursor fetch performance issue
Date: 2012-01-24 21:17:11
Message-ID: CAFj8pRDyZRb1Q3574dZ2VfQnBRRPvnrwGuhLvh6qRK-ByHsk+A@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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
>
>

In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2012-01-24 21:28:17
Subject: Re: Cursor fetch performance issue
Previous:From: Pavel StehuleDate: 2012-01-24 21:11:29
Subject: Re: Cursor fetch performance issue

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group