Re: Cursor fetch performance issue

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.email@hotmail.com'
pg-#         AND    m.changedate_id < 5868 ORDER BY m.emailaddress, m.websiteid;
 memberid  | websiteid |    emailaddress        | firstname | lastname | regcomplete | emailok
-----------+-----------+------------------------+-----------+----------+-------------+---------
 247815829 |         1 | test.email@hotmail.com | email     | test     |           1 |       1
 300960335 |        62 | test.email@hotmail.com |           |          |           1 |       1
 300959937 |       625 | test.email@hotmail.com |           |          |           1 |       1
 260152830 |      1453 | test.email@hotmail.com |           |          |           1 |       1
 300960163 |      1737 | test.email@hotmail.com | email     | test     |           1 |       1
 300960259 |      1824 | test.email@hotmail.com | email     | test     |           1 |       1
 300959742 |      1928 | test.email@hotmail.com | email     | test     |           1 |       1
 368122699 |      2457 | test.email@hotmail.com | email     | test     |           1 |       1
 403218613 |      2464 | test.email@hotmail.com | email     | test     |           1 |       0
 378951994 |      2656 | test.email@hotmail.com |           |          |           1 |       1
(10 rows)

Time: 132.626 ms

So, it would seem that's a small enough number of rows.  Unfortunately, issuing:

set cursor_tuple_fraction to 1.0;

Did not have an effect on performance.  Is it common to modify this
cursor_tuple_fraction parameter each time we execute the function?


On Tue, 2012-01-24 at 21:47 +0100, Pavel Stehule wrote:
> Hello
>
> 2012/1/24 Tony Capobianco <tcapobianco@prospectiv.com>:
> > We are migrating our Oracle warehouse to Postgres 9.
> >
> > This function responds well:
> >
> > pg=# select public.getMemberAdminPrevious_sp2(247815829, 1,'test.email@hotmail.com', 'email', 'test');
> >  getmemberadminprevious_sp2
> > ----------------------------
> >  <unnamed portal 1>
> > (1 row)
> >
> > Time: 7.549 ms
> >
> > However, when testing, this fetch takes upwards of 38 minutes:
> >
> > BEGIN;
> > select public.getMemberAdminPrevious_sp2(247815829, 1,'test.email@hotmail.com', 'email', 'test');
> > FETCH ALL IN "<unnamed portal 2>";
> >
> > How can I diagnose any performance issues with the fetch in the cursor?
> >
>
> Cursors are optimized to returns small subset of result - if you plan
> to read complete result, then set
>
> set cursor_tuple_fraction to 1.0;
>
> this is session config value, you can set it before selected cursors queries
>
> Regards
>
> Pavel Stehule
>
> > Thanks.
> > Tony
> >
> >
> > --
> > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-performance
>