Re: [PERFORM] Cursor fetch performance issue

2012-01-25 Thread Heikki Linnakangas
On 24.01.2012 23:34, Tony Capobianco wrote: Here's the explain: pg=# explain select getMemberAdminPrevious_sp(247815829, 1,'test.em...@hotmail.com', 'Email', 'Test'); QUERY PLAN -- Result (cost=0.00..0.26 rows=1 width=0) (1 row) Time:

Re: [PERFORM] Cursor fetch performance issue

2012-01-24 Thread Tony Capobianco
Here's the explain: pg=# explain select getMemberAdminPrevious_sp(247815829, 1,'test.em...@hotmail.com', 'Email', 'Test'); QUERY PLAN -- Result (cost=0.00..0.26 rows=1 width=0) (1 row) Time: 1.167 ms There was discussion

Re: [PERFORM] Cursor fetch performance issue

2012-01-24 Thread Tom Lane
Tony Capobianco writes: > Running just the sql of the function returns only 10 rows: > pg=# SELECT m.memberid, m.websiteid, m.emailaddress, > pg-#m.firstname, m.lastname, m.regcomplete, m.emailok > pg-# FROM members m > pg-# WHERE m.emailaddress LIKE 'test.em...

Re: [PERFORM] Cursor fetch performance issue

2012-01-24 Thread Pavel Stehule
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 >    

Re: [PERFORM] Cursor fetch performance issue

2012-01-24 Thread Pavel Stehule
2012/1/24 Tony Capobianco : > Running just the sql of the function returns only 10 rows: > > pg=# SELECT m.memberid, m.websiteid, m.emailaddress, > pg-#                m.firstname, m.lastname, m.regcomplete, m.emailok > pg-#         FROM   members m > pg-#         WHERE  m.emailaddress LIKE 'test.e

Re: [PERFORM] Cursor fetch performance issue

2012-01-24 Thread Andy Colson
On Tue, 2012-01-24 at 21:47 +0100, Pavel Stehule wrote: Hello 2012/1/24 Tony Capobianco: We are migrating our Oracle warehouse to Postgres 9. This function responds well: pg=# select public.getMemberAdminPrevious_sp2(247815829, 1,'test.em...@hotmail.com', 'email', 'test'); getmemberadminpr

Re: [PERFORM] Cursor fetch performance issue

2012-01-24 Thread Tony Capobianco
Running just the sql of the function returns only 10 rows: pg=# SELECT m.memberid, m.websiteid, m.emailaddress, pg-#m.firstname, m.lastname, m.regcomplete, m.emailok pg-# FROM members m pg-# WHERE m.emailaddress LIKE 'test.em...@hotmail.com' pg-# AND

Re: [PERFORM] Cursor fetch performance issue

2012-01-24 Thread Pavel Stehule
Hello 2012/1/24 Tony Capobianco : > We are migrating our Oracle warehouse to Postgres 9. > > This function responds well: > > pg=# select public.getMemberAdminPrevious_sp2(247815829, > 1,'test.em...@hotmail.com', 'email', 'test'); >  getmemberadminprevious_sp2 > >   >

[PERFORM] Cursor fetch performance issue

2012-01-24 Thread Tony Capobianco
We are migrating our Oracle warehouse to Postgres 9. This function responds well: pg=# select public.getMemberAdminPrevious_sp2(247815829, 1,'test.em...@hotmail.com', 'email', 'test'); getmemberadminprevious_sp2 (1 row) Time: 7.549 ms However, when testing, thi