Re: Cursor fetch performance issue

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