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(dot)email(at)hotmail(dot)com'
pg-# AND m.changedate_id < 5868 ORDER BY m.emailaddress, m.websiteid;
memberid | websiteid | emailaddress | firstname | lastname | regcomplete | emailok
-----------+-----------+------------------------+-----------+----------+-------------+---------
247815829 | 1 | test(dot)email(at)hotmail(dot)com | email | test | 1 | 1
300960335 | 62 | test(dot)email(at)hotmail(dot)com | | | 1 | 1
300959937 | 625 | test(dot)email(at)hotmail(dot)com | | | 1 | 1
260152830 | 1453 | test(dot)email(at)hotmail(dot)com | | | 1 | 1
300960163 | 1737 | test(dot)email(at)hotmail(dot)com | email | test | 1 | 1
300960259 | 1824 | test(dot)email(at)hotmail(dot)com | email | test | 1 | 1
300959742 | 1928 | test(dot)email(at)hotmail(dot)com | email | test | 1 | 1
368122699 | 2457 | test(dot)email(at)hotmail(dot)com | email | test | 1 | 1
403218613 | 2464 | test(dot)email(at)hotmail(dot)com | email | test | 1 | 0
378951994 | 2656 | test(dot)email(at)hotmail(dot)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(at)prospectiv(dot)com>:
> > We are migrating our Oracle warehouse to Postgres 9.
> >
> > This function responds well:
> >
> > pg=# select public.getMemberAdminPrevious_sp2(247815829, 1,'test(dot)email(at)hotmail(dot)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(dot)email(at)hotmail(dot)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(at)postgresql(dot)org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-performance
>