Re: Cursor fetch performance issue - Mailing list pgsql-performance
From | Andy Colson |
---|---|
Subject | Re: Cursor fetch performance issue |
Date | |
Msg-id | 4F1F1E7A.4080403@squeakycode.net Whole thread Raw |
In response to | Re: Cursor fetch performance issue (Tony Capobianco <tcapobianco@prospectiv.com>) |
Responses |
Re: Cursor fetch performance issue
|
List | pgsql-performance |
> 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 >> > > > On 1/24/2012 2:57 PM, Tony Capobianco wrote: > 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? > > 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 ORDER BY m.emailaddress, m.websiteid; Or is it creating the string and executing it: sql = 'SELECT m.memberid, m.websiteid, m.emailaddress, ' || ' m.firstname, m.lastname, m.regcomplete, m.emailok ' || ' FROM members m || ' WHERE m.emailaddress LIKE ' || arg1 || ' AND m.changedate_id < ' || arg2 || ' ORDER BY m.emailaddress, m.websiteid '; execute(sql); Maybe its the planner doesnt plan so well with $1 arguments vs actual arguments thing. -Andy
pgsql-performance by date: