Re: [PERFORM] Can lots of small writes badly hamper reads from other tables?

2012-01-24 Thread Tomas Vondra
On 24.1.2012 22:36, Dave Crooke wrote: > Hi guys > > Thanks for the quick followups folks please note I am deliberately > running a setup without commit guarantees, so a lot of the conventional > advice about not doing small writes isn't applicable, but I do want to > understand more about how

Re: [PERFORM] Can lots of small writes badly hamper reads from other tables?

2012-01-24 Thread Dave Crooke
Hi guys Thanks for the quick followups folks please note I am deliberately running a setup without commit guarantees, so a lot of the conventional advice about not doing small writes isn't applicable, but I do want to understand more about how this affects PG internals even if the I/O is smoo

Re: [PERFORM] Cursor fetch performance issue

2012-01-24 Thread Tony Capobianco
Here's the explain: pg=# explain select getMemberAdminPrevious_sp(247815829, 1,'test.em...@hotmail.com', 'Email', 'Test'); QUERY PLAN -- Result (cost=0.00..0.26 rows=1 width=0) (1 row) Time: 1.167 ms There was discussion

Re: [PERFORM] Cursor fetch performance issue

2012-01-24 Thread Tom Lane
Tony Capobianco writes: > 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.em...

Re: [PERFORM] Cursor fetch performance issue

2012-01-24 Thread Pavel Stehule
Hello > > 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 >    

Re: [PERFORM] Cursor fetch performance issue

2012-01-24 Thread Pavel Stehule
2012/1/24 Tony Capobianco : > 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.e

Re: [PERFORM] Cursor fetch performance issue

2012-01-24 Thread Andy Colson
On Tue, 2012-01-24 at 21:47 +0100, Pavel Stehule wrote: Hello 2012/1/24 Tony Capobianco: We are migrating our Oracle warehouse to Postgres 9. This function responds well: pg=# select public.getMemberAdminPrevious_sp2(247815829, 1,'test.em...@hotmail.com', 'email', 'test'); getmemberadminpr

Re: [PERFORM] Can lots of small writes badly hamper reads from other tables?

2012-01-24 Thread Tomas Vondra
On 24 Leden 2012, 21:16, Dave Crooke wrote: > Hi folks > > This could be a sheer volume issue, but I though I would ask the wisdom of > this forum as to next investigative steps. > > > > We use PostgreSQL 8.4.4 which is bundled with our application as a VMware > virtual appliance. The bulk of

Re: [PERFORM] Can lots of small writes badly hamper reads from other tables?

2012-01-24 Thread Andy Colson
On 1/24/2012 2:16 PM, Dave Crooke wrote: Hi folks This could be a sheer volume issue, but I though I would ask the wisdom of this forum as to next investigative steps. We use PostgreSQL 8.4.4 which is bundled with our application as a VMware virtual appliance. The bulk of the app's databa

Re: [PERFORM] Cursor fetch performance issue

2012-01-24 Thread Tony Capobianco
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.em...@hotmail.com' pg-# AND

Re: [PERFORM] Cursor fetch performance issue

2012-01-24 Thread Pavel Stehule
Hello 2012/1/24 Tony Capobianco : > We are migrating our Oracle warehouse to Postgres 9. > > This function responds well: > > pg=# select public.getMemberAdminPrevious_sp2(247815829, > 1,'test.em...@hotmail.com', 'email', 'test'); >  getmemberadminprevious_sp2 > >   >

[PERFORM] Cursor fetch performance issue

2012-01-24 Thread Tony Capobianco
We are migrating our Oracle warehouse to Postgres 9. This function responds well: pg=# select public.getMemberAdminPrevious_sp2(247815829, 1,'test.em...@hotmail.com', 'email', 'test'); getmemberadminprevious_sp2 (1 row) Time: 7.549 ms However, when testing, thi

[PERFORM] Can lots of small writes badly hamper reads from other tables?

2012-01-24 Thread Dave Crooke
Hi folks This could be a sheer volume issue, but I though I would ask the wisdom of this forum as to next investigative steps. We use PostgreSQL 8.4.4 which is bundled with our application as a VMware virtual appliance. The bulk of the app's database activity is recording performance data p