Re: Cursor fetch performance issue

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux